Question

.net 必须声明标量变量 "@UserID"。

public static DataSet Query(string SQLString, params OleDbParameter[] cmdParms) { using (OleDbConnection connection = new OleDbConnection(connectionString)) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (OleDbDataAdapter da = new OleDbDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (OleDbException ex) { throw new Exception(ex.Message); } return ds; } }
}
private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text;//cmdType; if (cmdParms != null) { foreach (OleDbParameter parameter in cmdParms) { if (parameter.Value == null) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } }
}
问题已解决，OleDb的参数用“问号”

Answer 1

问题出在OleDb和SqlClient 两种Sql参数占位符语法不一样，SqlClient用@代表参数
string str = "select * from user where UserID=@UserID"
而在OleDb中应写成
string str = "select * from user where UserID=?"

然后在OleDbParameter传参中new OleDbParameter[] { new OleDbParameter("@P1", OleDbType.Integer, 4) };
"@P1"代替的就是Sql语句中的参数

Answer 2

sql语句错误，将@UserID替换成值

Answer 3

Query方法调用怎么写的？

追问

string str = "select * from user where UserID=@UserID"

OleDbParameter[] cmdParms = new OleDbParameter[] { new OleDbParameter("@UserID", OleDbType.Integer, 4) };
cmdParms[0].Value = UserID;
DataSet set = DbHelperOleDb.Query(str.ToString(), cmdParms);

Answer 4

你的数据访问层是在代码里面写的SQL 语句，还是在数据库中写的存储过程啊。如果是在数据库写的存储过程，那么你的存储过程中有没有声明这个变量啊

Answer 5

请检查sql语句或存储过程是否出错