Solved

Syntax error in Insert into statement in VB2008

Posted on 2009-03-30
13
623 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
  • 7
  • 6
13 Comments
 
LVL 69

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 69

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
 
LVL 69

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 69

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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 69

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 69

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 69

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now