Solved

oledbexception  must declare the scalar variable

Posted on 2007-03-29
6
1,617 Views
Last Modified: 2008-01-09
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.OleDbException: Must declare the scalar variable "@f1".

Connection string to Access Database:

 "Provider=Microsoft.Jet.OLEDB.4.0;Data 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=myPassword;"


Dim sql As String = "UPDATE Table1 SET f1=@f1,f2=@f2,f3=@f3 WHERE ID = 118"
                Dim conn As New OleDb.OleDbConnection(ConnectionString)
                Dim command As New OleDb.OleDbCommand(sql, conn)

                command.Parameters.Add("@f1", OleDb.OleDbType.Variant).Value = TextBox1.Text
                command.Parameters.Add("@f2", OleDb.OleDbType.Variant).Value = TextBox2.Text
                command.Parameters.Add("@f3", OleDb.OleDbType.Variant).Value = TextBox3.Text

                Try
                    conn.Open()

                    command.ExecuteNonQuery()
                Catch ex As Exception
                    MsgBox(ex.ToString)
                Finally
                    conn.Close()
                End Try

0
Comment
Question by:OFGemini
6 Comments
 
LVL 18

Expert Comment

by:DarrenD
ID: 18816350
Hi,

Here is an article which may help.

http://www.dotnet247.com/247reference/msgs/29/146328.aspx

Darren
0
 
LVL 35

Expert Comment

by:YZlat
ID: 18817026
change

 command.Parameters.Add("@f1", OleDb.OleDbType.Variant).Value = TextBox1.Text
                command.Parameters.Add("@f2", OleDb.OleDbType.Variant).Value = TextBox2.Text
                command.Parameters.Add("@f3", OleDb.OleDbType.Variant).Value = 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
0
 

Author Comment

by:OFGemini
ID: 18817631
YZlat,

I tried that, and It didn't work.  I get the same error
0
 

Author Comment

by:OFGemini
ID: 18846914
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.


0
 
LVL 1

Accepted Solution

by:
Vee_Mod earned 0 total points
ID: 19148947
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now