suwu150
suwu150
采纳率44.3%
2016-04-01 17:01 阅读 2.1k

如何在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";
则正常删除
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

4条回答 默认 最新

  • 已采纳
    caozhy 从今以后生命中的每一秒都属于我爱的人 2016-04-01 17:06

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

    点赞 1 评论 复制链接分享
  • suwu150 suwu150 2016-04-01 23:30

    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 星期六 广州深圳
    上面是我的数据库内容

    点赞 评论 复制链接分享
  • u010222827 殇或雨 2016-04-02 00:24

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

    点赞 评论 复制链接分享
  • u010222827 殇或雨 2016-04-02 01:44

    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
    }
    

    }

    点赞 评论 复制链接分享

相关推荐