add row to table

Question

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 25-Jan-11 6:03am

mhh12

Updated 25-Jan-11 6:17am

Sandeep Mewara

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Dave Kreskowiak · Answer 1 · 2011-01-25T07:01:00

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.

Manfred Rudolf Bihy · Answer 2 · 2011-01-25T07:09:00

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

add row to table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0