InsertCommand gives "Must declare the scalar variable "@..."

[Klaasjan]

Hi,

I have a strange problem and i think the sollutions is simple, but I
don't get it.

I have the following piece of code

Dim pCommand As New System.Data.OleDb.OleDbCommand
Dim sSql As String = ""

sSql = "INSERT INTO dbo.Uitgaven ( "
sSql &= " UitgavenOmschrijving,"
sSql &= " UitgavenBedrag,"
sSql &= " UitgavenKostenPostID,"
sSql &= " UitgavenDatum"
sSql &= " ) "
sSql &= "VALUES ( "
sSql &= " @UitgavenOmschrijving,"
sSql &= " @UitgavenBedrag,"
sSql &= " @UitgavenKostenPostID,"
sSql &= " @UitgavenDatum"
sSql &= " ) "

pCommand.CommandText = sSql
pCommand.UpdatedRowSource = UpdateRowSource.Both

With pCommand
.Parameters.Add("@UitgavenOmschrijving", OleDbType.VarChar,
50, "UitgavenOmschrijving")
.Parameters.Add("@UitgavenBedrag", OleDbType.Double, 4,
"UitgavenBedrag")
.Parameters.Add("@uitgavenKostenPostID", OleDbType.Integer,
4, "UitgavenKostenPostID")
.Parameters.Add("@UitgavenDatum", OleDbType.Date, 0,
"UitgavenDatum")
End With

pCommand.Connection = pDB.Connection

For iTeller As Integer = 0 To pDS.Tables(0).Rows.Count - 1

Try

With pCommand
.Parameters("@UitgavenOmschrijving").Value =
pDS.Tables(0).Rows(iTeller).Item(3)
.Parameters("@UitgavenBedrag").Value =
pDS.Tables(0).Rows(iTeller).Item(1)
.Parameters("@uitgavenKostenPostID").Value =
pDS.Tables(0).Rows(iTeller).Item(2)
.Parameters("@UitgavenDatum").Value =
pDS.Tables(0).Rows(iTeller).Item(0)
End With
pCommand.CommandType = CommandType.Text
MessageBox.Show(pCommand.ExecuteNonQuery)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

Next

I get a error "Mus declare the scalar variable "@UitgavenOmschrijving".
I have not include the primary key because that is a database thing for
me.

Can anyone give me the sollution for my problem

Thanks already
Klaasjan

 

[William \(Bill\) Vaughn]

Provider? JET? SQL Server? Most likely the provider does not support named
parameters. You'll need to replace the named parameters in the INSERT with
"?" or the parameter placeholder defined by the provider.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)

 

[Klaasjan]

Hi Bill,

I have a sql2005 server and my connectionstring contains :
Dim _Connection As New OleDb.OleDbConnection

Dim sConnString As String = _
"Provider=sqloledb;" & _
"Data Source=""PC-1\sqlserver"";" & _
"Initial Catalog=HuisHoud;" & _
"Integrated Security=SSPI;"

I don't know wether this provider does not support named parameters?
You

thanks
Klaasjan

William (Bill) Vaughn schreef:

 

[William \(Bill\) Vaughn]

Most OleDb providers don't support named parameters--you'll have to use ? as
the positional parameter marker and declare the Parameters collection in the
correct order.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
-----------------------------------------------------------------------------------------------------------------------

 

[Klaasjan]

Hi Bill,

thanks for your reaction.

It works,

Klaasjan


William (Bill) Vaughn schreef: