Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Syntax error in Insert into statement in VB2008

Posted on 2009-03-30
13
Medium Priority
?
634 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 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 2000 total points
ID: 24022106
it would work with a _
0
 

Author Comment

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

885 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