Solved

oledbexception  must declare the scalar variable

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

How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

626 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