Click here to Skip to main content
14,937,132 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
This code is written in VWD 2010 express. This code is trying to add one row of five columns to the table KDistrictBridge. It doesn't do it.
I got a message about declaring a scalar variable @EeID. Looking at forums, it appeared that I should write EeID=?. And so I made the change. (see first dim statement)
Now, I get a message "Incorrect syntax near ?".

Does anyone know how to write the code to put one row of five columns in a table? That what I am actually trying to do. When I can do that, then I will try to get an array copied into KDisctBridge. But first things first.

Many thanks for your help.
VB
     ' Ddataset is defined at start of Sub Button1_Click()

       Dim strKDisctBridge As String = "SELECT * FROM KDisctBridge WHERE  EeID=? "
       Dim tblKDisctBridge As New SqlCommand(strKDisctBridge, Ddataset)
       Dim sqlInsert As String = "INSERT INTO KDistrictBridge " & _
         "(EeID,DdID,District,CcID,DisctSet) VALUES " & _
         "(@EeID,@DdID,@District,@CcID,@DisctSet)"
Try
             '  make the data adapter
       Dim da As New SqlDataAdapter
          da.SelectCommand = New SqlCommand(strKDisctBridge, Ddataset)
             ' make and fill the KDisctBridge dataset
       Dim ds As New DataSet
          da.Fill(ds, "KDisctBridge")
             ' get the datatable out of the Ddataset list of tables
       Dim dt As DataTable = ds.Tables("KDisctBridge")
             ' add a row to the table
       Dim newrow As DataRow = dt.NewRow
          newrow("EeID") = 1
          newrow("DdID") = 2
          newrow("District") = "Able"
          newrow("CcID") = 3
          newrow("DisctSet") = "Baker"
          dt.Rows.Add(newrow)
             ' insert the new row
             ' create the command
       Dim insertCmd As New SqlCommand(sqlInsert, Ddataset)
          insertCmd.Parameters.Add(New SqlParameter("", "EeID"))
          insertCmd.Parameters.Add(New SqlParameter("", "DdID"))
          insertCmd.Parameters.Add(New SqlParameter("", "District"))
          insertCmd.Parameters.Add(New SqlParameter("", "CcID"))
          insertCmd.Parameters.Add(New SqlParameter("", "DisctSet"))
          da.InsertCommand = insertCmd
          da.Update(ds, "KDisctBridge")
 Catch ex As SqlException
          MsgBox("error at catch" & ex.ToString())
          Console.WriteLine("Error: " & ex.ToString())
 Finally
          Ddataset.Close()
 End Try
 End Using
 End Sub
Posted
Updated 25-Jan-11 6:17am
v2

Your parameter names in all of your insertCmd.Parameters.Add statements must match the names. For example, in you're SQL statement, your values are specified by @EeID, @DdID, and so on... In your Parameters, you're leaving out the "@" character. Those parameter names don't match, so you'll get this error. Change the Parameter statements "names" to match what you have in the SQL statements.
   
See modification of your code below where the parameters are set.

VB
' Ddataset is defined at start of Sub Button1_Click()

       Dim strKDisctBridge As String = "SELECT * FROM KDisctBridge WHERE  EeID=? "
       Dim tblKDisctBridge As New SqlCommand(strKDisctBridge, Ddataset)
       Dim sqlInsert As String = "INSERT INTO KDistrictBridge " & _
         "(EeID,DdID,District,CcID,DisctSet) VALUES " & _
         "(@EeID,@DdID,@District,@CcID,@DisctSet)"
Try
             '  make the data adapter
       Dim da As New SqlDataAdapter
          da.SelectCommand = New SqlCommand(strKDisctBridge, Ddataset)
             ' make and fill the KDisctBridge dataset
       Dim ds As New DataSet
          da.Fill(ds, "KDisctBridge")
             ' get the datatable out of the Ddataset list of tables
       Dim dt As DataTable = ds.Tables("KDisctBridge")
             ' add a row to the table
       Dim newrow As DataRow = dt.NewRow
          newrow("EeID") = 1
          newrow("DdID") = 2
          newrow("District") = "Able"
          newrow("CcID") = 3
          newrow("DisctSet") = "Baker"
          dt.Rows.Add(newrow)
             ' insert the new row
             ' create the command
       Dim insertCmd As New SqlCommand(sqlInsert, Ddataset)

          insertCmd.Parameters.Add("@EeID",SqlDbType.Int).Value=1
          insertCmd.Parameters.Add("@DdID",SqlDbType.Int).Value=2
          insertCmd.Parameters.Add("@CcID",SqlDbType.Int).Value=3

          insertCmd.Parameters.Add("@District",SqlDbType.Char).Value="Able"
          insertCmd.Parameters.Add("@DisctSet",SqlDbType.Char).Value="Baker"

          da.InsertCommand = insertCmd
          da.Update(ds, "KDisctBridge")
 Catch ex As SqlException
          MsgBox("error at catch" & ex.ToString())
          Console.WriteLine("Error: " & ex.ToString())
 Finally
          Ddataset.Close()
 End Try
 End Using
 End Sub
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900