?
Solved

SQL Insert statement not working ...

Posted on 2011-03-02
9
Medium Priority
?
275 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

771 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