Solved

SQL Insert statement not working ...

Posted on 2011-03-02
9
274 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
[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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

691 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