Getting Desparate!!! - Must declare the variable '@CategoryName'.

  • Thread starter Thread starter James Cooke
  • Start date Start date
J

James Cooke

Trying to update nortwhind table, no matter what I do, I
always get this error:

Server Error in '/WebApplication1' Application.
Must declare the variable '@CategoryName'.

The connection is definitely working, the user has
read/write authority and I can read the database. Updating
it with parameters fails. This is the code:

Public Class WebForm1
Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
Dim OLEDBConnection As
System.Data.OleDb.OleDbConnection
Dim ConnectionString As String
ConnectionString = "Provider=SQLOLEDB.1;Persist
Security Info=True;Password=crow;User ID=cladion;Initial
Catalog=Northwind;Data Source=IS-JCOOKE0"
OLEDBConnection = New
System.Data.OleDb.OleDbConnection(ConnectionString)
OLEDBConnection.Open()
Dim OLEDBCommandObject As
System.Data.OleDb.OleDbCommand
Dim OLEDBParam As System.Data.OleDb.OleDbParameter
Dim SQL As String


SQL = "UPDATE categories SET
CategoryName=@CategoryName WHERE CategoryID =1"
OLEDBCommandObject = New
System.Data.OleDb.OleDbCommand(SQL, OLEDBConnection)

OLEDBParam = OLEDBCommandObject.Parameters.Add(New
System.Data.oledb.OleDbParameter("@CategoryName",
System.Data.OleDb.OleDbType.VarChar, 15))
OLEDBParam.Value = 1
OLEDBParam.SourceColumn = "CategoryName"
OLEDBCommandObject.ExecuteNonQuery()
OLEDBConnection.Close()
End Sub

End Class
 
Thanks, I tried this. Having changed both '@' to '?' to the following, as in the lines below, I get a similar error:

Exception Details: System.Data.OleDb.OleDbException: Must declare the variable '@P1CategoryName'.

Dim ConnectionString As String = "etc etc"
Dim OLEDBConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
Dim SQL As String = "UPDATE categories SET CategoryName=?CategoryName WHERE CategoryID =1"
Dim OLEDBCommandObject As New System.Data.OleDb.OleDbCommand(SQL, OLEDBConnection)
Dim OLEDBParam As System.Data.OleDb.OleDbParameter

OLEDBParam = New System.Data.oledb.OleDbParameter("?CategoryName", System.Data.OleDb.OleDbType.VarChar, 15)
OLEDBParam.Value = 1
OLEDBParam.SourceColumn = "CategoryName"
OLEDBCommandObject.Parameters.Add(OLEDBParam)

OLEDBConnection.Open()
OLEDBCommandObject.ExecuteNonQuery()
OLEDBConnection.Close()


Thanks in advance

James


For OLE parameters should start with ? instead of @. @ is for SqlClient
 
U can try

Dim SQL As String = "UPDATE categories SET CategoryName=? WHERE CategoryID
=1"

and then

OLEDBParam = New System.Data.oledb.OleDbParameter("CategoryName",
System.Data.OleDb.OleDbType.VarChar, 15)

James Cooke said:
Thanks, I tried this. Having changed both '@' to '?' to the following, as
Click to expand...
in the lines below, I get a similar error:
Exception Details: System.Data.OleDb.OleDbException: Must declare the variable '@P1CategoryName'.

Dim ConnectionString As String = "etc etc"
Dim OLEDBConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
Dim SQL As String = "UPDATE categories SET
Click to expand...
CategoryName=?CategoryName WHERE CategoryID =1"
Dim OLEDBCommandObject As New System.Data.OleDb.OleDbCommand(SQL, OLEDBConnection)
Dim OLEDBParam As System.Data.OleDb.OleDbParameter

OLEDBParam = New System.Data.oledb.OleDbParameter("?CategoryName",
Click to expand...
System.Data.OleDb.OleDbType.VarChar, 15)
 
Back
Top