Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

SQL Insert statement not working ...

Posted on 2011-03-02
9
Medium Priority
?
276 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
Independent Software Vendors: 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 1000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

647 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