Solved

oledbexception  must declare the scalar variable

Posted on 2007-03-29
6
1,624 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

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…
Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

815 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

8 Experts available now in Live!

Get 1:1 Help Now