syntax error in insert into statement on access and vb.net

Question

Im having a error but i dont know what part but i check my tables but it is the exact column im using ms access2010 as database and every time i add a new record theres a msgbox that show (syntax error in insert into statement) heres my code:

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


End Sub

Private Sub GroupBox1_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox1.Enter

End Sub

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim add As String = "insert into setplan(ma,planqty,side,start,end,total,remarks) values ('" & cmbshift.SelectedItem & "','" & txtplqty.Value & "','" & cmbside.SelectedItem & "','" & timestart.Text & "','" & timeend.Text & "','" & txttotal.Text & "','" & txtrem.Text & "')"

    Dim connection As String = "Provider=Microsoft.Ace.Oledb.12.0; Data Source=C:\Users\Administrator\Documents\plan.mdb; Persist Security Info=False;"
    Using conn As New OleDb.OleDbConnection(connection)
        Try

            conn.Open()
            If cmbshift.SelectedItem = "" Then
                MsgBox("Please Select Shift Schedule")
            ElseIf txtplqty.Value = 0 Then
                MsgBox("Please Input Plan Quantity")
            ElseIf cmbside.SelectedItem = "" Then
                MsgBox("Please select Side")
            ElseIf timestart.Text = "" Then
                MsgBox("Please Select A Start Time")
            ElseIf timeend.Text = "" Then
                MsgBox("Please Select A Start Time")
            ElseIf timeend.Text = timestart.Text Then
                MsgBox("Time end must not be equal to Time Start")
            Else
                MsgBox(add)
                Dim cmd As New OleDb.OleDbCommand(add, conn)
                cmd.ExecuteNonQuery()

                MsgBox("New Schedule Added")

            End If
            conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Using



End Sub

Private Sub timestart_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles timestart.ValueChanged

End Sub

End Class

Start your debugger and have a look at your insert string with all variable contents that should be inserted. Alternatively open a MessageBox with the content of your `add` variable. — MarkusEgle, Jan 30 '18 at 06:53
Use parameters instead of concatenating values in your sql statement — F0r3v3r-A-N00b, Jan 30 '18 at 08:43
Put a line of code just below Dim add as String Debug.Print(add) Then run your code. Close your program and look at the immediate window. You can reach this window from the debug menu, Windows. This should show you what you are sending to Access. Check back after you do this. — Mary, Jan 30 '18 at 09:39
That is not the correct way to compose SQL and hasnt been so for a very long time. It is doubtful that all those columns are string, but that is what you are passing. Use SQL Parameters always and check a good reference for Keywords and Reserved words for your DB (like `End` - not so sure about `start`)., — Ňɏssa Pøngjǣrdenlarp, Jan 30 '18 at 15:59

score 1 · Answer 1 · answered Jan 30 '18 at 06:59

1

start and end words are unique for sql so those keywords might cause the problem. Try to switch those column names into something like startTime and endTime and check if that works.

answered Jan 30 '18 at 06:59

Atilla Arda Açıkgöz

827
2
15
29

G_Hosa_Phat · Answer 2 · 2018-02-01T01:07:52.837

As has been addressed in the comments, it would be much better if you were to make this a parameterized query instead of concatenating the strings into an explicit SQL command. That being said, however, using your example, there are a couple of things that could be causing the error you describe, some of which have been mentioned in the comments (and the answer from Atilla).

Start and End are reserved keywords in SQL. Using them as column names can cause unexpected behavior when executing a query against those columns from a .NET application through OleDb. There are basically two ways to get around this:
- Rename these columns in the database - Atilla suggested StartTime and EndTime, which would probably work nicely.
- If renaming the columns is not an option (these columns are used by some other system/process you have in place), your best bet is to modify the query. Since it appears that you're working with an Access database (.mdb) file, you can enclose the column names in your query in square brackets (e.g., [Start] and [End]).

I've actually taken to enclosing all of my table and column names this way when working with Access databases because we have some databases with column names that include spaces and such, so this helps tremendously.

You also need to take into account the actual data types of the columns into which you're attempting to INSERT the data. Again, since it seems that you're using an Access database file, there are a couple of syntactical things to look at.
- Values being inserted into a Date/Time field should be wrapped with the # character instead of the ' character.
- Numeric field types (e.g., Number or Currency) should not be wrapped with the ' character (or any other characters, for that matter).
- If the string values you intend to insert into text fields (e.g., Short Text or Long Text) contain any of a number of "special/invalid characters" including single and/or double quotation marks, these need to be "cleaned up" before executing the query. If this is the case (or potentially could be the case), you could create a method to clean up the string value prior to use in your SQL command. See an example at the bottom of this post in which most, if not all of the potentially invalid characters are simply stripped from the string value.

Please note that, for the purposes of this answer, I've used the data type names from the MS Access UI rather than the actual OleDb/Odbc data types to try to simplify things.

Without knowing the actual data types used in your database table or the values that are coming from the form controls, I can only make assumptions, but, if I absolutely had to use this type of query building (meaning, it's not possible to make it parameterized for some reason), I would probably create the query to looks something more like this:

Dim add As String = "INSERT INTO setplan " & _
    "([ma], [planqty], [side], [start], [end], [total], [remarks]) " & _
    "VALUES ('" & cmbshift.SelectedItem & "', " & _
    txtplqty.Value & ", " & _
    "'" & cmbside.SelectedItem & "', " & _
    "#" & timestart.Text & "#, " & _
    "#" & timeend.Text & "#, " & _
    txttotal.Text & ", " & _
    "'" & txtrem.Text & "')"

This assumes that the [start] and [end] columns are Date/Time columns, and the [planqty] and [total] columns are some type of Number columns (Integer, Single, etc.).

HOWEVER: as mentioned above, it would be much preferred to make this a parameterized query. Check out the accepted answer to this SO question for more information on how to do this: VB.Net - Inserting data to Access Database using OleDb

Example of cleanup function for String values when concatenating SQL command:

Friend Function CleanStringForSQL(ByVal DirtyString As String) As String
    Dim CleanString As String = DirtyString.Replace("'", "")

    CleanString = CleanString.Replace("""", "")
    CleanString = CleanString.Replace("*", "")
    CleanString = CleanString.Replace("\", "")
    CleanString = CleanString.Replace("/", "")
    CleanString = CleanString.Replace(";", "")
    CleanString = CleanString.Replace("%", "")
    CleanString = CleanString.Replace("#", "")
    CleanString = CleanString.Replace("(", "")
    CleanString = CleanString.Replace(")", "")
    CleanString = CleanString.Replace("[", "")
    CleanString = CleanString.Replace("]", "")

    Return CleanString
End Function

Which could then be used in your declaration statement for the SQL command string like:

...
"VALUES ('" & CleanStringForSQL(cmbshift.SelectedItem) & "', " & _
...

As an aside, you can't get lulled into a sense of complacency with "standard" SQL syntax. Other databases may use a different syntax for situations where column names aren't "plain" (camel/pascal-case, non-keyword, no spaces, etc.). For example, in PostgreSQL, if the column name is mixed-case or contains spaces, the column name *must* be enclosed in double quotation marks (`"`). If the column name in a PostgreSQL database is `SetPlan`, the `INSERT` SQL command would have to be `INSERT INTO "SetPlan" (...` — G_Hosa_Phat, Jan 31 '18 at 16:57
Additionally, please note that these issues explicitly arise when attempting to run this query through the `OleDb` or `Odbc` data providers. ***IF*** the values from the form controls do not contain any invalid characters and you were to manually run the exact SQL statement produced by the concatenation directly within MS Access (even without taking any of the advice listed here), the `INSERT` would actually execute without error. — G_Hosa_Phat, Jan 31 '18 at 17:16

syntax error in insert into statement on access and vb.net

2 Answers2