Syntax Error in INSERT INTO statement occured in Access Data insertion

Question

0.00/5 (No votes)

See more:

Here i am providing my total Project code .........
Whenever i click on button Access table has been Updated . But an "Syntax Error in INSERT INTO statement" has been Occured ... Any one plzz Help mee.......

Imports System.Data
Imports System.Windows.Forms.DataGrid
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Windows.Forms.DataGridView
Imports System.Data.OleDb

Public Class DataPreview
    Public stx As String
    Public str As String
    Public STX1 As String
    Public STX2 As String
    Dim i As Integer
    Dim j As Integer
    Public stxb() As Byte
    Dim cnnOLEDB As New OleDbConnection
    Dim cmdOLEDB As New OleDbCommand
    Dim cmdInsert As New OleDbCommand
    Dim cmdUpdate As New OleDbCommand
    Dim cmdDelete As New OleDbCommand
    Dim adpOLEDB As New OleDbDataAdapter
    Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\SMSDB.mdb"
    Enum com
        com1 = 1
        com2
        com3
        com4
        com5
        com6
        com7
        com8
        com9
        com10
        com11
        com12
        com13
        com14
        com15
        com16
        com17
        com18
        com19
        com20
    End Enum
    'Private Sub DataPreview_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    '    'TODO: This line of code loads data into the 'SQLDBCon1.SMSData1' table. You can move, or remove it, as needed.
    '    Me.SMSData1TableAdapter.Fill(Me.SQLDBCon1.SMSData1)
    '    'TODO: This line of code loads data into the 'SQLDBCon.SMSData1' table. You can move, or remove it, as needed.
    '    Me.SMSData1TableAdapter.Fill(Me.SQLDBCon.SMSData1)
    '    'TODO: This line of code loads data into the 'AccessDB.Modem_Automation' table. You can move, or remove it, as needed.
    '    Me.Modem_AutomationTableAdapter.Fill(Me.AccessDB.Modem_Automation)
    '    'Me.SMSData1TableAdapter.Fill(Me.SMSData1DataSet1.SMSData1)
    '    For Each sp As String In My.Computer.Ports.SerialPortNames
    '        ComboBox1.Items.Add(sp)
    '    Next

    'End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If Button1.Text = "Open" Then
            If LCase(ComboBox1.Text) = "com1" Then
                AxMSComm1.CommPort = 1
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com2" Then
                AxMSComm1.CommPort = 2
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com3" Then
                AxMSComm1.CommPort = 3
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com4" Then
                AxMSComm1.CommPort = 4
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com5" Then
                AxMSComm1.CommPort = 5
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com6" Then
                AxMSComm1.CommPort = 6
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com7" Then
                AxMSComm1.CommPort = 7
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com8" Then
                AxMSComm1.CommPort = 8
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com9" Then
                AxMSComm1.CommPort = 9
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com10" Then
                AxMSComm1.CommPort = 10
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com11" Then
                AxMSComm1.CommPort = 11
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com12" Then
                AxMSComm1.CommPort = 12
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com13" Then
                AxMSComm1.CommPort = 13
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com14" Then
                AxMSComm1.CommPort = 14
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com15" Then
                AxMSComm1.CommPort = 15
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com16" Then
                AxMSComm1.CommPort = 16
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com17" Then
                AxMSComm1.CommPort = 17
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com18" Then
                AxMSComm1.CommPort = 18
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com19" Then
                AxMSComm1.CommPort = 19
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            ElseIf LCase(ComboBox1.Text) = "com20" Then
                AxMSComm1.CommPort = 20
                AxMSComm1.PortOpen = True
                Button1.Text = "Close"
            End If
        ElseIf Button1.Text = "Close" Then
            AxMSComm1.PortOpen = False
            Button1.Text = "Open"
        End If
    End Sub

    'Private Sub Fill__ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
    '    Try
    '        Me.SMSData1TableAdapter.Fill__(Me.SQLDBCon.SMSData1)
    '    Catch ex As System.Exception
    '        System.Windows.Forms.MessageBox.Show(ex.Message)
    '    End Try

    'End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        For Me.i = 1 To 24
            If i = 1 Then
                STX1 = Mid(str, 1, 1)
            ElseIf i = 2 Then
                STX2 = Mid(str, 2, 2)
            ElseIf i = 3 Then
                stx = Mid(str, 4, 3)
                stx = stx / 100
            ElseIf i = 4 Then
                stx = Mid(str, 7, 10)
            ElseIf i = 5 Then
                For Me.j = 1 To 6
                    If j = 1 Then
                        stx = "Date: " & Mid(str, 17, 2)
                        stx = stx & "/"
                    ElseIf j = 2 Then
                        stx = stx & Mid(str, 19, 2)
                        stx = stx & "/"
                    ElseIf j = 3 Then
                        stx = stx & Mid(str, 21, 2)
                        stx = stx & "  Time: "
                    ElseIf j = 4 Then
                        stx = stx & Mid(str, 23, 2)
                        stx = stx & ":"
                    ElseIf j = 5 Then
                        stx = stx & Mid(str, 25, 2)
                        stx = stx & ":"
                    ElseIf j = 6 Then
                        stx = stx & Mid(str, 27, 2)
                    End If
                Next
            ElseIf i = 6 Then
                stx = Mid(str, 29, 4)
                'stxb = System.Text.Encoding.UTF8.GetBytes(stx)
                'MsgBox(System.Text.Encoding.UTF8.GetBytes(stx))
                'stx = Val(stx)
                Dim result As String = ""
                For i As Integer = 0 To stx.Length - 1
                    Dim bin As String = _
                        LongToBinary(Asc(stx.Substring(i, 1)))
                    result &= bin.Substring(bin.Length - 4)
                Next i
                MsgBox(result)
            ElseIf i = 7 Then
                stx = Mid(str, 33, 3)
            ElseIf i = 8 Then
                stx = Mid(str, 36, 3)
                stx = stx / 10
            ElseIf i = 9 Then
                stx = Mid(str, 39, 3)
                stx = stx / 100
            ElseIf i = 10 Then
                stx = Mid(str, 42, 8)
                stx = stx / 10
            ElseIf i = 11 Then
                stx = Mid(str, 50, 3)
            ElseIf i = 12 Then
                stx = Mid(str, 53, 3)
                stx = stx / 10
            ElseIf i = 13 Then
                stx = Mid(str, 56, 3)
                stx = stx / 100
            ElseIf i = 14 Then
                stx = Mid(str, 59, 8)
                stx = stx / 10
            ElseIf i = 15 Then
                stx = Mid(str, 67, 3)
            ElseIf i = 16 Then
                stx = Mid(str, 70, 3)
                stx = stx / 10
            ElseIf i = 17 Then
                stx = Mid(str, 73, 8)
                stx = stx / 10
            ElseIf i = 18 Then
                stx = Mid(str, 81, 3)
                stx = Val("&h" & stx)
                stx = stx / 10
            ElseIf i = 19 Then
                stx = Mid(str, 84, 3)
                stx = Val("&h" & stx)
                stx = stx / 10
            ElseIf i = 20 Then
                stx = Mid(str, 87, 8)
                stx = stx / 10
            ElseIf i = 21 Then
                stx = Mid(str, 95, 3)
                stx = Val("&h" & stx)
                stx = stx / 10
            ElseIf i = 22 Then
                stx = Mid(str, 98, 8)
                stx = stx / 10
            ElseIf i = 23 Then
                stx = Mid(str, 106, 4)
                'stx = Val(stx)
                Dim result As String = ""
                For i As Integer = 0 To stx.Length - 1
                    Dim bin As String = _
                        LongToBinary(Asc(stx.Substring(i, 1)))
                    result &= bin.Substring(bin.Length - 4)
                Next i
                MsgBox(result)
            ElseIf i = 24 Then
                stx = Mid(str, 110, 1)
            End If
            'stx = Convert.ToDecimal(str)
            MsgBox(stx)
            
        Next
        cmdInsert.CommandText = "INSERT INTO SMSDATA1DB (Start Of Packet, Packet Identifier) VALUES ('" & STX1 & "', '" & STX2 & "')"
        MsgBox(cmdInsert.CommandText)
        cmdInsert.CommandType = CommandType.Text
        cmdInsert.Connection = cnnOLEDB
        cmdInsert.ExecuteNonQuery()
        cmdInsert.Dispose()
    End Sub

    Private Sub DataPreview_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Dim str As String
        'Dim stx As String
        'OpenConnection()
        'objda.SelectCommand.CommandText = "Select * FROM SMSDATA1DB"
        'objda.Fill(objdataset, "SMSDATA1DB")
        'dv = New DataView(objdataset.Tables("SMSDATA1DB"))
        'DGView.DataSource = dv
        str = "#SP101PACE_TEST1221111153500073123012109712345678240101080987654302400219876543048111113579246155246813570003."
        cnnOLEDB.ConnectionString = strConnectionString
        'MsgBox(strConnectionString)
        cnnOLEDB.Open()
        cmdOLEDB.CommandText = "SELECT * FROM SMSDATA1DB"
        cmdOLEDB.Connection = cnnOLEDB
        Dim rdrOLEDB As OleDbDataReader = cmdOLEDB.ExecuteReader
        'stx = Convert.ToDecimal(str)
    End Sub
    ' This version of LongToBinary does not add a "&B" in the front.
    Private Function LongToBinary(ByVal long_value As Long, Optional ByVal separate_bytes As Boolean = True) As String
        ' Convert into hex.
        Dim hex_string As String = long_value.ToString("X")

        ' Zero-pad to a full 16 characters.
        hex_string = hex_string.PadLeft(16, "0")

        ' Read the hexadecimal digits
        ' one at a time from right to left.
        Dim result_string As String = ""
        For digit_num As Integer = 0 To 15
            ' Convert this hexadecimal digit into a
            ' binary nibble.
            Dim digit_value As Integer = _
                Integer.Parse(hex_string.Substring(digit_num, _
                1), Globalization.NumberStyles.HexNumber)

            ' Convert the value into bits.
            Dim factor As Integer = 4
            Dim nibble_string As String = ""
            For bit As Integer = 0 To 3
                If digit_value And factor Then
                    nibble_string &= "1"
                Else
                    nibble_string &= "0"
                End If
                factor \= 2
            Next bit

            ' Add the nibble's string to the left of the
            ' result string.
            result_string &= nibble_string
        Next digit_num

        ' Add spaces between bytes if desired.
        If separate_bytes Then
            Dim tmp As String = ""
            For i As Integer = 0 To result_string.Length - 4 _
                Step 4
                tmp &= result_string.Substring(i, 4) & " "
            Next i
            result_string = tmp.Substring(0, tmp.Length - 1)
        End If

        ' Return the result.
        Return result_string
    End Function

    Private Sub AxMSComm1_OnComm(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AxMSComm1.OnComm

    End Sub
End Class

Posted 29-Nov-11 1:30am

Himachandra

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

uspatel · Answer 1 · 2011-11-29T01:36:00

Solution 1

C#

cmdInsert.CommandText = "INSERT INTO SMSDATA1DB (Start Of Packet, Packet Identifier) VALUES ('" & STX1 & "', '" & STX2 & "')"

instead of,
try to use query like this

C#

cmdInsert.CommandText = "INSERT INTO SMSDATA1DB ([Start Of Packet], [Packet Identifier]) VALUES (@STX1,@STX1)";
cmdInsert.Parameters.AddWithValue("@STX1",STX1);
cmdInsert.Parameters.AddWithValue("@STX2",STX2);

Posted 29-Nov-11 1:36am

uspatel

Comments

RaisKazi 29-Nov-11 7:42am

Agree with the answer, My 5.

uspatel 29-Nov-11 7:47am

thanks...

RaisKazi · Answer 2 · 2011-11-29T01:41:00

Well, its difficult to point out exact problem from your entire code, But problem seems to be with Column names used in your Insert statement.

Check specified column names are correct, if they are correct then you may Encapsulate the column names in square brackets([]) as below.

VB

cmdInsert.CommandText = "INSERT INTO SMSDATA1DB ([Start Of Packet], [Packet Identifier]) VALUES ('" & STX1 & "', '" & STX2 & "')"

Have a look at below links for more information.

http://www.daniweb.com/web-development/databases/ms-access-and-filemaker-pro/threads/135895

http://forums.codecharge.com/posts.php?post_id=45537

**Manoj K Bhoir** · Answer 3 · 2011-11-29T01:43:00

Try this query :

VB

Dim objda As New OleDbDataAdapter
obj.SelectCommnd=New OledbCommand
objda.SelectCommand.CommandText="Insert into TableName (Field1, Field2, Field3) values(@Field1, @Field2, @Field3)"
objda.SelectCommand.Parameters.AddWithValue("@Field1", TextBox1.Text)
objda.SelectCommand.Parameters.AddWithValue("@Field2", TextBox2.Text)
objda.SelectCommand.Parameters.AddWithValue("@Field3", TextBox3.Text)
   Try                                     'To handle OleDb Exceptions
       objda.SelectCommand.ExecuteNonQuery()
   Catch ex As OleDb.OleDbException
       MessageBox.Show(ex.Message)
   End Try

It will help you :)

Syntax Error in INSERT INTO statement occured in Access Data insertion

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0