2 suwu150 suwu150 于 2016.04.02 01:01 提问

如何在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";
则正常删除

5个回答

caozhy
caozhy   Ds   Rxr 2016.04.02 01:06
已采纳

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

suwu150
suwu150 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 星期六 广州深圳
一年多之前 回复
suwu150
suwu150 就是删除了所有的, 我总共有5行,System.out.println("成功删除"+i+"行");执行后提示删除5行,我去Access里边刷新看,表内容都是#已删除内容
一年多之前 回复
CSDNXIAOD
CSDNXIAOD   2016.04.02 01:12

T-SQL级联删除——ON DELETE CASCADE
----------------------biu~biu~biu~~~在下问答机器人小D,这是我依靠自己的聪明才智给出的答案,如果不正确,你来咬我啊!

suwu150
suwu150   2016.04.02 07: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
u010222827   2016.04.02 08:24

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

u010222827
u010222827 你调用我下面代码中的删除函数
一年多之前 回复
suwu150
suwu150 String sql="DELETE FROM T_EMPLOYER WHERE ID=@id";改成这个之后就出现”java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] 参数不足,期待是 1。“这样的提示
一年多之前 回复
u010222827
u010222827   2016.04.02 09: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
}

}

u010222827
u010222827 回复suwu150: 这是操作Access工具类,直接调用就行了,很方便,以后不用再写类了
一年多之前 回复
suwu150
suwu150 有点麻烦啊,谢谢啊
一年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!