Syntax error in Insert into statement in VB2008

Hello,
I just started developing an application in VB2008. I am trying to add record by clicking ADD button and keep getting 'Syntax error in Insert into Statement" and its pointing to the following line: da:Update(ds,"AccountProfile"). I am attaching the code for the purpose. I am using ACCESS db and the connection is okay.
Any help would be greatly appreciated.
Thanks so much in advance.
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        txtAcctID.Clear()
        txtName.Clear()
        txtCompany.Clear()
        txtNotes.Clear()
 
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow
        Dim acctidrow As Integer
 
        dsNewRow = ds.Tables("AccountProfile").NewRow()
        dsNewRow.Item("AccountNum") = txtAcctID.Text
        dsNewRow.Item("ContactName") = txtName.Text
        dsNewRow.Item("Company") = txtCompany.Text
        dsNewRow.Item("Notes") = txtNotes.Text
 
        ds.Tables("AccountProfile").Rows.Add(dsNewRow)
        '--cb.GetInsertCommand()
        '--da.InsertCommand = cb.GetInsertCommand
 
        da.Update(ds, "AccountProfile")

Open in new window

guavamayAsked:
Who is Participating?
 
Éric MoreauSenior .Net ConsultantCommented:
it would work with a _
0
 
Éric MoreauSenior .Net ConsultantCommented:
you are clearing the textboxes and then use those same textboxes to set the values in the new row. That means that you are adding empty values. Are your fields all supports that?
0
 
guavamayAuthor Commented:
Hi,
Well I thought I have to first clear all those text boxes before I start inputting when I click ADD button to add new records.  The data types are all character.
Anyhow, I just commented those lines and still getting the same error.
Is there anything else thats wrong  in the code?

Thanks.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Éric MoreauSenior .Net ConsultantCommented:
have you tried to set test values like this:

        dsNewRow.Item("AccountNum") = "123"
        dsNewRow.Item("ContactName") = "Name"
        dsNewRow.Item("Company") = "Company"
        dsNewRow.Item("Notes") = "Notes"
0
 
Éric MoreauSenior .Net ConsultantCommented:
could you also paste the content of cb.GetInsertCommand.CommandText here so that we can see what your query looks like?
0
 
guavamayAuthor Commented:
I created UI form where I can add the records through form and that is why I didn't hard coded the value in the program. I can try that to see if that works.
Regarding the cb.GetinsertCommand, my understanding is (since I am very new in vb!!) that command is going to generate the insert query for me. So I didn't add any insert query. Actually, I am not sure if I even coded right regarding this 'cb.GetinsertComman'  Here what I have
ds.Tables("AccountProfile").Rows.Add(dsNewRow)
        cb.GetInsertCommand()
        da.InsertCommand = cb.GetInsertCommand

        da.Update(ds, "AccountProfile")
How do I use that in my  code? What would be the correct syntax to be able to add records?

Thanks.
0
 
Éric MoreauSenior .Net ConsultantCommented:
yes the command should be autogenerated. do a debug.print cb.GetInsertCommand.CommandText to see the content.
0
 
guavamayAuthor Commented:
Hi,
Sorry I have to ask how do I see the content of the debug.print.
Heres the code looks like:
ds.Tables("AccountProfile").Rows.Add(dsNewRow)
        cb.GetInsertCommand()
        da.InsertCommand = cb.GetInsertCommand

        Debug.Print(cb.GetInsertCommand.CommandText)

        da.Update(ds, "AccountProfile")
I executed the application and I input all the text boxes and clicked Add button and only get that same error.
 I was expecting to come up the debug messages on the terminal but it didn't.
Also I was thinking is the error message has anything to do with indexing the field. My primary index ket is the AccountID, its the character data type and I tried both inputting as 123 or abc. Also right now the table has no records.

Thanks,


0
 
Éric MoreauSenior .Net ConsultantCommented:
debug.print sends the result into the Output window. Place a breakpoint on the da.Update line and when it will be reached, press CTRL-ALT-O
0
 
guavamayAuthor Commented:
Thank You.
Here is the insert statement came up from debug print.
INSERT INTO AcctProfile (AccountNum, ContactName, Address1, Address2, City, Postal Code, State, Company, Phone, Email, Notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

I see why now, is that because the values are all getting ?, ? , ?...
So all the values I am typing in the text box in the form, its somehow not assigning .....
If thats the case, then whats wrong with the assigning the values like the following:
 Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow
        'Dim acctidrow As Integer

        MsgBox("hello")
        dsNewRow = ds.Tables("AccountProfile").NewRow()

        dsNewRow.Item("AccountNum") = txtAcctID.Text
        dsNewRow.Item("ContactName") = txtName.Text
        dsNewRow.Item("Company") = txtCompany.Text
        dsNewRow.Item("Address1") = txtAddress1.Text
        dsNewRow.Item("Address2") = txtAddress2.Text
        dsNewRow.Item("Postal Code") = txtPostal.Text
        dsNewRow.Item("State") = ""
        dsNewRow.Item("Phone") = txtPhone.Text
        dsNewRow.Item("Email") = txtEmail.Text
        dsNewRow.Item("Notes") = txtNotes.Text

        ds.Tables("AccountProfile").Rows.Add(dsNewRow)

Thanks so much
0
 
Éric MoreauSenior .Net ConsultantCommented:
the ? will be replaced by values when the query is sent to the database.

I see another problem: Postal Code. can you remove the space between the 2 words?
0
 
guavamayAuthor Commented:
Thank You, Thank You, Thank You. That did it. I also changed in the db to read as PostalCode, one word.
Thank You so much for your help and not to mention so quickly you solve my problem. I can see the records in db and no more errors.
Just one more question(out of curiosity), if I may...
Would it work If I name the fields as Postal_Code? Or is it only have to be all in one word.

Thanks again.
0
 
guavamayAuthor Commented:
Thats good.
Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.