Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oledbexception  must declare the scalar variable

Posted on 2007-03-29
6
Medium Priority
?
1,683 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

Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

Question has a verified solution.

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

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

670 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