问题描述

如何在Access中删除一行数据，怎么出错？？ String sql="DELETE FROM T

    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                Connection conn= DriverManager.getConnection("jdbc:odbc:DDEmployer");
                Statement stat=conn.createStatement();
                String id=javax.swing.JOptionPane.showInputDialog(null,"请输入要删除的员工编号");
                String sql="DELETE FROM T_EMPLOYER WHERE ID=id";
                int i=stat.executeUpdate(sql);
                 System.out.println("成功删除"+i+"行");
如果执行这条语句，是删除了，但是删除的是全部啊？？怎么接？？
如果将SQL语句改为下面这个
String sql="DELETE FROM T_EMPLOYER WHERE T_EMPLOYER.ID=10";
则正常删除

解决方案

String sql="DELETE FROM T_EMPLOYER WHERE ID=id";
这个只会删除ID=id的，不会全部删除，除非你的所有ID字段都相同，并且等于id

解决方案二：

T-SQL级联删除——ON DELETE CASCADE

解决方案三：

ID NAME SEX NUMBERANDPHONE REGISTERDATE ADDRESS
6 张三 男 432424 2016/3/29 星期二 甘肃兰州
7 女 432432 2016/3/29 星期二 北京石景山
8 女 432424 2016/3/24 星期四 上海昆山
9 男 43234324 2016/3/3 星期四 广州深圳
10 男 100000000 2016/3/26 星期六 广州深圳
上面是我的数据库内容

解决方案四：

你把sql语句改成这样试试 DELETE FROM T_EMPLOYER WHERE ID=@id

解决方案五：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Data.SqlClient;

namespace Common
{
public static class AccessHelper
{
#region 基本功能
private static string connectionString;
public static string ConnectionString
{
get
{
return connectionString;
}
set
{
connectionString = value;
}
}
private static OleDbConnection GetOleDbConnection()
{
return new OleDbConnection(ConnectionString);
}

    public static DataSet ExecuteDataSet(string cmdText, params OleDbParameter[] p)
    {
        OleDbDataAdapter da = new OleDbDataAdapter(cmdText, ConnectionString);
        if (p != null)
        {
            da.SelectCommand.Parameters.AddRange(p);
        }
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public static DataTable ExecuteDataTable(string cmdText, params OleDbParameter[] p)
    {
        OleDbDataAdapter da = new OleDbDataAdapter(cmdText, ConnectionString);
        if (p != null)
        {
            da.SelectCommand.Parameters.AddRange(p);
        }
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataRow ExecuteDataRow(string cmdText, params OleDbParameter[] p)
    {
        DataTable dt = ExecuteDataTable(cmdText, p);
        if (dt != null && dt.Rows.Count > 0)
            return dt.Rows[0];
        return null;
    }

    public static int ExecuteNonQuery(string cmdText, params OleDbParameter[] p)
    {
        OleDbConnection connection = GetOleDbConnection();
        OleDbCommand command = new OleDbCommand(cmdText, connection);
        connection.Open();
        if (p != null)
        {
            command.Parameters.AddRange(p);
        }
        int i = command.ExecuteNonQuery();
        connection.Close();
        return i;
    }

    public static object ExecuteScalar(string cmdText, params OleDbParameter[] p)
    {
        OleDbConnection connection = GetOleDbConnection();
        OleDbCommand cmd = new OleDbCommand(cmdText, connection);
        connection.Open();
        if (p != null)
        {
            cmd.Parameters.AddRange(p);
        }
        object obj = cmd.ExecuteScalar();
        connection.Close();
        return obj;
    }
    #endregion

    #region 扩展功能
    /// <summary>
    /// 根据唯一ID获取Hashtable
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="pkName">字段主键</param>
    /// <param name="pkVal">字段值</param>
    /// <returns></returns>
    public static Hashtable GetHashtableByGUID(string tableName, string pkName, string pkVal)
    {
        string cmdText = "SELECT * FROM " + tableName + " WHERE " + pkName + " = @GUID值";

        OleDbParameter[] parameters = new OleDbParameter[]
        {
            new OleDbParameter("@GUID值",pkVal)
        };

        DataTable dt = ExecuteDataTable(cmdText, parameters);
        return DataTableHelper.DataTableToHashtable(dt);
    }
    public static Hashtable GetHashtableById(string tableName, string pkName, string pkVal)
    {
        string cmdText = "SELECT * FROM " + tableName + " WHERE " + pkName + " = @ID";

        OleDbParameter[] parameters = new OleDbParameter[]
        {
            new OleDbParameter("@ID",pkVal)
        };

        DataTable dt = ExecuteDataTable(cmdText, parameters);
        return DataTableHelper.DataTableToHashtable(dt);
    }

    /// <summary>
    /// 对象参数转换
    /// </summary>
    /// <param name="ht"></param>
    /// <returns></returns>
    private static OleDbParameter[] GetParameter(Hashtable ht)
    {
        OleDbParameter[] parameters = new OleDbParameter[ht.Count];
        int i = 0;
        foreach (string key in ht.Keys)
        {
            parameters[i] = new OleDbParameter("@" + key, ht[key]);
            i++;
        }
        return parameters;
    }

    /// <summary>
    /// 通过Hashtable插入数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="ht">Hashtable</param>
    /// <returns>int</returns>
    public static int InsertByHashtable(string tableName, Hashtable ht)
    {
        StringBuilder sb = new StringBuilder();
        sb.Append(" Insert Into ");
        sb.Append(tableName);
        sb.Append("(");
        StringBuilder sp = new StringBuilder();
        StringBuilder sb_prame = new StringBuilder();
        foreach (string key in ht.Keys)
        {
            sb_prame.Append("," + key);
            sp.Append(",@" + key);
        }
        sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
        sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")");
        return ExecuteNonQuery(sb.ToString(), GetParameter(ht));
    }

    /// <summary>
    /// 通过Hashtable修改数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="pkName">字段主键</param>
    /// <param name="pkValue"></param>
    /// <param name="ht">Hashtable</param>
    /// <returns>int</returns>
    public static int UpdateByHashtable(string tableName, string pkName, string pkVal, Hashtable ht)
    {
        StringBuilder sb = new StringBuilder();
        sb.Append(" UPDATE ");
        sb.Append(tableName);
        sb.Append(" SET ");
        bool isFirstValue = true;
        foreach (string key in ht.Keys)
        {
            if (isFirstValue)
            {
                isFirstValue = false;
                sb.Append(key);
                sb.Append("=");
                sb.Append("@" + key);
            }
            else
            {
                sb.Append("," + key);
                sb.Append("=");
                sb.Append("@" + key);
            }
        }

        string where = string.Format(" WHERE {0} = '{1}'",pkName,pkVal);
        sb.Append(where);

        OleDbParameter[] parameters = GetParameter(ht);
        return ExecuteNonQuery(sb.ToString(), parameters);
    }
    /// <summary>
    /// 表单提交：新增，修改
    ///     参数：
    ///     tableName:表名
    ///     pkName：字段主键
    ///     pkVal：字段值
    ///     ht：参数
    /// </summary>
    /// <returns></returns>
    public static bool AddOrEdit(string tableName, string pkName, string pkVal, Hashtable ht)
    {
        if (string.IsNullOrEmpty(pkVal))
        {
            if (InsertByHashtable(tableName, ht) > 0)
                return true;
            else
                return false;
        }
        else
        {
            if (UpdateByHashtable(tableName, pkName, pkVal, ht) > 0)
                return true;
            else
                return false;
        }
    }

    /// <summary>
    /// 根据主键删除数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="pkName">主键字段名（也可以不是主键）</param>
    /// <param name="pkVal">字段值</param>
    /// <returns></returns>
    public static int DeleteData(string tableName, string pkName, string pkVal)
    {
        string cmdText = "DELETE FROM " + tableName + " WHERE " + pkName + " = @ID";
        OleDbParameter[] parameters = new OleDbParameter[]
        {
            new OleDbParameter("ID",pkVal),
        };

        return ExecuteNonQuery(cmdText, parameters);
    }

    /// <summary>
    /// 根据条件删除数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="where">条件</param>
    /// <returns></returns>
    public static int DeleteData(string tableName, string where)
    {
        string cmdText = "DELETE FROM " + tableName + " " + where;
        return ExecuteNonQuery(cmdText, null);
    }

    /// <summary>
    /// 批量删除
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="pkName">主键字段名（也可以不是主键）</param>
    /// <param name="pkVal">字段值</param>
    /// <returns></returns>
    public static int BatchDeleteData(string tableName, string pkName, object[] pkValues)
    {
        OleDbParameter[] parameters = new OleDbParameter[pkValues.Length];
        int index = 0;
        string str = "@ID" + index;
        StringBuilder sql = new StringBuilder("DELETE FROM " + tableName + " WHERE " + pkName + " IN (");
        for (int i = 0; i < (parameters.Length - 1); i++)
        {
            object obj2 = pkValues[i];
            str = "@ID" + index;
            sql.Append(str).Append(",");
            parameters[index] = new OleDbParameter(str, obj2);
            index++;
        }
        str = "@ID" + index;
        sql.Append(str);
        parameters[index] = new OleDbParameter(str, pkValues[index]);
        sql.Append(")");
        return ExecuteNonQuery(sql.ToString(), parameters);
    }

    /// <summary>
    /// 判断数据是否存在
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="pkName">主键字段名（也可以不是主键）</param>
    /// <param name="pkVal">字段值</param>
    /// <returns></returns>
    public static bool IsExist(string tableName, string pkName, string pkVal)
    {
        string cmdText = "SELECT COUNT(1) FROM " + tableName + " WHERE " + pkName + " = @" + pkName;
        OleDbParameter[] parameters = {
                                     new OleDbParameter("@" + pkName,pkVal)};
        return (int)ExecuteScalar(cmdText, parameters) == 1;
    }

    /// <summary>
    /// 判断数据是否存在
    /// </summary>
    /// <param name="tableName"></param>
    /// <param name="where"></param>
    /// <returns></returns>
    public static bool IsExist(string tableName, string where)
    {
        string cmdText = "SELECT COUNT(1) FROM " + tableName + " " + where;
        return (int)ExecuteScalar(cmdText, null) == 1;
    }

    /// <summary>
    /// 批量拷贝数据
    /// </summary>
    /// <param name="maplist">列名映射</param>
    /// <param name="tableName">目标表名</param>
    /// <param name="dt">源数据源</param>
    /// <returns></returns>
    public static bool SqlBulkCopyImport(List<string> maplist, string tableName, DataTable dt)
    {
        SqlConnection conn = new SqlConnection(connectionString);
        conn.Open();
        SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString);
        bulkCopy.DestinationTableName = tableName;
        foreach (string a in maplist)
        {
            bulkCopy.ColumnMappings.Add(a, a);
        }
        bulkCopy.BatchSize = 1000;
        bulkCopy.WriteToServer(dt);

        conn.Close();
        return true;
    }
    public static void insertByDataTable(string tableName, DataTable dt)
    {
        for (int i = 1; i < dt.Rows.Count; i++)
        {
            string sb = " insert into " + tableName + "(";
            string columns = string.Empty;
            string values = string.Empty;
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                if (!string.IsNullOrEmpty(dt.Rows[i][dt.Columns[j].ColumnName].ToString()))
                {
                    columns += dt.Columns[j].ColumnName + ",";
                    if (dt.Columns[j].ColumnName.Contains("时间"))
                    {
                        values += "#" + dt.Rows[i][dt.Columns[j].ColumnName] + "#,";
                    }
                    else
                    {
                        values += "'" + dt.Rows[i][dt.Columns[j].ColumnName] + "',";
                    }
                }
            }
            sb = sb + columns.TrimEnd(',') + ") values(" + values.TrimEnd(',') + ")";
            ExecuteNonQuery(sb, null);
        }
    }
    public static bool IsExitColumn(string[] columns, string column)
    {
        bool flag = false;
        for (int i = 0; i < columns.Length; i++)
        {
            if (columns[i] == column)
            {
                flag = true;
                break;
            }
        }
        return flag;
    }
    #endregion
}

}