Solved

Syntax error in Insert into statement in VB2008

Posted on 2009-03-30
13
632 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:guavamay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 24020418
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
 

Author Comment

by:guavamay
ID: 24020645
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 24020759
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 24020784
could you also paste the content of cb.GetInsertCommand.CommandText here so that we can see what your query looks like?
0
 

Author Comment

by:guavamay
ID: 24020927
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 24020987
yes the command should be autogenerated. do a debug.print cb.GetInsertCommand.CommandText to see the content.
0
 

Author Comment

by:guavamay
ID: 24021494
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 24021511
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
 

Author Comment

by:guavamay
ID: 24021608
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 24021666
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
 

Author Comment

by:guavamay
ID: 24022038
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
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 24022106
it would work with a _
0
 

Author Comment

by:guavamay
ID: 24022241
Thats good.
Thanks again.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This collection of functions covers all the normal rounding methods of just about any numeric value.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question