[Error 1] SqlServer prompts error: OleDbException scalar variables must be declared
Today, I use .net to operate SqlServer using Oledb, but it always reports "A scalar variable must be declared..." inexplicably. I have adopted the following two methods in the code, but to no avail:
Method 1: OleDbParameter[] p = new OleDbParameter[] {
new OleDbParameter("@aa",1),
new OleDbParameter("@bb","shelley"),
new OleDbParameter("@cc",DateTime.Now)
};
Objcmd.Parameters.AddRange(p);
Method 2: OleDbParameter pp;
pp = new OleDbParameter("@aa",OleDbType.Integer);
pp.Value = 1;
Objcmd.Parameters.Add(pp);
pp = new OleDbParameter("@bb", OleDbType.LongVarChar);
pp.Value = "shelley";
Objcmd.Parameters.Add(pp);
pp = new OleDbParameter("@cc", OleDbType.Date);
pp.Value = DateTime.Now;
Objcmd.Parameters.Add(pp); No matter how you write it, it will prompt "Scalar variable @aa must be declared". The final reason is the following sentence: OleDbCommand Objcmd = new OleDbCommand("insert into test (a,b,c) values(@aa,@bb,@cc)", Objconn);Correct writing: OleDbCommand Objcmd = new OleDbCommand("insert into test (a,b,c)values(?,?,?)", Objconn);This way It passed normally. [Error 2] Sybase prompts an error: This interface is not supported (the exception comes from HRESULT:0x80004002 (E_NOINTERFACE)).net uses Oledb to operate Sybase because the same parameter may be used multiple times in a SQL statement. The code is as follows:
OleDbCommand Objcmd = new OleDbCommand("update test set a=@aa where a=@aa ", Objconn);
OleDbParameter[] p = new OleDbParameter[] {
new OleDbParameter("aa",1),
new OleDbParameter("aa",1)
};
Objcmd.Parameters.AddRange(p);
Objconn.Open();
Objcmd.ExecuteNonQuery();
Objconn.Close(); The above code will prompt the error "This interface is not supported (the exception comes from HRESULT: 0x80004002 (E_NOINTERFACE))". The cause of the problem is that there are two identical names in the parameters. In this case, you only need to write one parameter. The changes are as follows:
OleDbCommand Objcmd = new OleDbCommand("update test set a=@aa where a=@aa ", Objconn);
OleDbParameter[] p = new OleDbParameter[] {
new OleDbParameter("aa",1)
};or
OleDbCommand Objcmd = new OleDbCommand("update test set a=@aa where a=@aa2 ", Objconn);
OleDbParameter[] p = new OleDbParameter[] {
new OleDbParameter("aa",1),
new OleDbParameter("aa2",1)
};【Summarize】
If you write a program that needs to support SqlServer and Sybase, you need to pay special attention to this point:
1.SqlServer needs to use "?" as a parameter placeholder, which requires that even if the same parameter is used, Parameter must be added multiple times;
2. If Sybase uses the same parameter multiple times, Parameter cannot be added multiple times, otherwise an error will be reported;
http://www.cnblogs.com/shelley/archive/2010/05/25/1743438.html