Solved

SQL Insert statement not working ...

Posted on 2011-03-02
9
272 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
send messages to whatsapp programatically 2 65
VB.Net WebRequest Code from JSON curl 7 50
VB.net and sql server 4 45
Visual studio 2015 1 22
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 …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

861 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