Solved

oledbexception  must declare the scalar variable

Posted on 2007-03-29
6
1,649 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

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 article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

737 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