Solved

SQL Insert statement not working ...

Posted on 2011-03-02
9
269 Views
Last Modified: 2012-05-11
I've been receiving an error message stating that my Insert statement has a syntax error.  I initially had a very complex sql statement and have now stripped it down to a point where I am trying to see if I can either populate a record with two fields.  My statement is as follows:

sInsertQry = "INSERT INTO master (last, first) " & _
                    "VALUES ('Test', 'Sam')"

following my insert string are the following lines of code:

    conn.open()
    cmd = New OleDb.OleDbCommand(sInsertQry, conn)
    cmd.ExecuteNonQuery()
    conn.Close()


Would anyone know why this simple statement would not work?  I cannot see any syntax error.

Thank you.

0
Comment
Question by:CarmenMTorres
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35021917
What's the backend database and the exact error message?
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35021930
Also, do you have other statements in your code that are working?  On the meantime, you might what to try (I added a ; at the end):

sInsertQry = "INSERT INTO master (last, first) " & _
                    "VALUES ('Test', 'Sam');"
0
 
LVL 9

Expert Comment

by:joshbula
ID: 35022333
Are there any other fields in the master database that don't allow nulls?  If there is a primary key field, is it set to Identity so it will automatically generate the primary key?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35022444
"master", "last" or "first" may be reserved words depeding on what database system you are talking to. As has already been mentioned, we really need to the error message to be more precise.

You could try the following though in case they are reserved words:
sInsertQry = "INSERT INTO [master] ([last],[first]) "VALUES ('Test', 'Sam')

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35022448
Oops, quote in the wrong place. That should have said:
sInsertQry = "INSERT INTO [master] ([last],[first]) VALUES ('Test', 'Sam')"

Open in new window

0
 

Author Comment

by:CarmenMTorres
ID: 35026654
I hope someone can be of help.  I've provided below the information requested.  Thanks.

The exact error message is:

   "Syntax error in INSERT INTO statement."


The backend database is "Microsoft Access version 2002."

The primary key is defined as an AutoNumber datatype.

The insert a record section of the button click event code is as follows:


Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

        Dim errorText As String = String.Empty
        Dim invalidInput As Boolean = False
        Dim ctrl As Control
        Dim sMsg As String
        Dim sConn, sInsertQry, sUpdateQry As String
        Dim sYear As String
        Dim cmd As OleDbCommand
        Dim conn As OleDbConnection


        For Each ctrl In Me.Controls
            If Not ErrorProvider1.GetError(ctrl) = String.Empty Then
                errorText += "  * " & ErrorProvider1.GetError(ctrl) & ControlChars.NewLine
                invalidInput = True
            End If
        Next


        ' Errors exist; do not submit record.

        If invalidInput Then
            sMsg = String.Concat("This form contains the following unresolved errors:", ControlChars.NewLine, ControlChars.NewLine, errorText)
            MessageBox.Show(sMsg, "Invalid Input", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            sMsg = "Unable to submit record that contains unresolved errors."
            MessageBox.Show(sMsg, "Cancelling Submission", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            tbxLast.Focus()
            Exit Sub
        End If


        If existFlag = False Then           ' New record

            Try

                sYear = tbxYear.Text.Substring(2, 2)

                'SQL Connection String


                sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath() & "\PhyDB.mdb;"
                conn = New OleDbConnection(sConn)

                sInsertQry = "INSERT INTO master (last, first) VALUES ('TEST', 'SAM')"



                '"VALUES ('" & tbxLast.Text & "', '" & tbxFirst.Text & "', '" & tbxMInit.Text & "');"
                conn.Open()

                cmd = New OleDbCommand(sInsertQry, conn)

                cmd.ExecuteNonQuery()
                conn.Close()

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try


        Else
.....

       End If

End Sub

0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35026786
Have you tried bracketing the table and column names as I suggested ??
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 250 total points
ID: 35026827
As an extra note "first" and "last" are both reserved words, so they are the ones that MUST be bracketed.
0
 

Author Comment

by:CarmenMTorres
ID: 35027057
Thank you for your assistance.  I wasted an entire day trying to determine why a simple INSERT INTO statement did not work.  Keep up the good work.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 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

15 Experts available now in Live!

Get 1:1 Help Now