OFGemini
asked on
oledbexception must declare the scalar variable
I tried the following code connecting to an access database and it worked fine, But I tried this code on SQL server 2005 and I get this error:
Error: System.Data.OleDb.OleDbExc eption: Must declare the scalar variable "@f1".
Connection string to Access Database:
"Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=C:\myDB.mdb;Persist Security Info=False;Jet OLEDB:Database Password=myPassword;"
Connection string to SQL server 2005:
"Provider=sqloledb;Data Source=192.168.0.129,1433; Initial Catalog=myDB;User Id=MyUser;Password=myPassw ord;"
Dim sql As String = "UPDATE Table1 SET f1=@f1,f2=@f2,f3=@f3 WHERE ID = 118"
Dim conn As New OleDb.OleDbConnection(Conn ectionStri ng)
Dim command As New OleDb.OleDbCommand(sql, conn)
command.Parameters.Add("@f 1", OleDb.OleDbType.Variant).V alue = TextBox1.Text
command.Parameters.Add("@f 2", OleDb.OleDbType.Variant).V alue = TextBox2.Text
command.Parameters.Add("@f 3", OleDb.OleDbType.Variant).V alue = TextBox3.Text
Try
conn.Open()
command.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
Finally
conn.Close()
End Try
Error: System.Data.OleDb.OleDbExc
Connection string to Access Database:
"Provider=Microsoft.Jet.OL
Connection string to SQL server 2005:
"Provider=sqloledb;Data Source=192.168.0.129,1433;
Dim sql As String = "UPDATE Table1 SET f1=@f1,f2=@f2,f3=@f3 WHERE ID = 118"
Dim conn As New OleDb.OleDbConnection(Conn
Dim command As New OleDb.OleDbCommand(sql, conn)
command.Parameters.Add("@f
command.Parameters.Add("@f
command.Parameters.Add("@f
Try
conn.Open()
command.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
Finally
conn.Close()
End Try
change
command.Parameters.Add("@f 1", OleDb.OleDbType.Variant).V alue = TextBox1.Text
command.Parameters.Add("@f 2", OleDb.OleDbType.Variant).V alue = TextBox2.Text
command.Parameters.Add("@f 3", OleDb.OleDbType.Variant).V alue = TextBox3.Text
to
command.Parameters.Add(New OleDbParameter("@f1", OleDb.OleDbType.Variant)). Value = TextBox1.Text
command.Parameters.Add(New OleDbParameter"@f2", OleDb.OleDbType.Variant)). Value = TextBox2.Text
command.Parameters.Add(New OleDbParameter"@f3", OleDb.OleDbType.Variant)). Value = TextBox3.Text
command.Parameters.Add("@f
command.Parameters.Add("@f
command.Parameters.Add("@f
to
command.Parameters.Add(New
command.Parameters.Add(New
command.Parameters.Add(New
ASKER
YZlat,
I tried that, and It didn't work. I get the same error
I tried that, and It didn't work. I get the same error
ASKER
I did some research. SQL server wants the command text to use '?'
Dim sql As String = "UPDATE Table1 SET f1=@f1,f2=@f2,f3=@f3 WHERE ID = 118"
was changed to:
Dim sql As String = "UPDATE Table1 SET f1=?,f2=?,f3=? WHERE ID = 118"
then I had to add the parameters in the order the questions marks appear in the command text.
Dim sql As String = "UPDATE Table1 SET f1=@f1,f2=@f2,f3=@f3 WHERE ID = 118"
was changed to:
Dim sql As String = "UPDATE Table1 SET f1=?,f2=?,f3=? WHERE ID = 118"
then I had to add the parameters in the order the questions marks appear in the command text.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here is an article which may help.
http://www.dotnet247.com/247reference/msgs/29/146328.aspx
Darren