Re: InsertCommand gives "Must declare the scalar variable "@..." by Klaasjan
Klaasjan
Sun Nov 26 02:43:49 CST 2006
Hi Bill,
thanks for your reaction.
It works,
Klaasjan
William (Bill) Vaughn schreef:
> 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" <k.mors@hccnet.nl> wrote in message
> news:1164191256.402200.202130@h54g2000cwb.googlegroups.com...
> > 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:
> >
> >> 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" <k.mors@hccnet.nl> wrote in message
> >> news:1164058901.416448.150890@k70g2000cwa.googlegroups.com...
> >> > 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
> >> >
> >