[RESOLVED] [2005] Syntax error in INSERT INTO statement???

[phenom]

Dear all,
Could anyone please tell me what’s wrong with this code?!?! I’m sure that there is no error, but it keeps showing this error message (Syntax error in INSERT INTO statement) as shown in the attached picture.

I’m developing software for my organization, part of this software allow the user to generate a PC name, everything is working fine except when it comes to save the date. I’ve created new Database, rewrite the code, and still same problem.

Just to mention that all the other part of this software is 100% working, like generating a serial number, add new PC etc… all are working and saving data into the data base correctly. I’m facing only this problem in this part only!!!! I don’t know why…

Appreciate your help…

Regards,

Private Sub Save_info()

        m_Connection.Close()
        m_NewName.Reset()
        m_NewName.Dispose()

        Try
            m_Connection.ConnectionString = _
            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\DB\MainDB.accdb;Jet OLEDB:Database Password=MyDbPassword;"

            m_Connection.Open()
            m_DataAdapter = New _
            OleDb.OleDbDataAdapter("Select * From Name_PC", m_Connection)
            m_cmb = New OleDb.OleDbCommandBuilder(m_DataAdapter)
            m_DataAdapter.Fill(m_NewName)

        Catch objException As Exception
            Dim what As MsgBoxResult
            what = MessageBox.Show("An Error Occurred: " & objException.Message, _
            "Message Type: Error In Connection 'Step 1' in Save_Info (fclsPCName)", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        'Add New PCNAME
        Try

            Dim AllDept As String = Me.cmbDeptName.SelectedItem.ToString
            Dim fullDeptCode As String = AllDept
            fullDeptCode = fullDeptCode.Substring(fullDeptCode.IndexOf(" "))

            Dim DeptName As String = ""
            DeptName = Microsoft.VisualBasic.Left(AllDept, AllDept.Length - fullDeptCode.Length)
            'MessageBox.Show(DeptName & DeptName.Length)

            Dim DeptCode As String = AllDept
            DeptCode = DeptCode.Substring(DeptCode.LastIndexOf(" ") + 1)

            Dim Lc As String = ""
            If Me.rbHQ.Checked = True Then
                Lc = "HQ"
            ElseIf Me.rbSite.Checked = True Then
                Lc = "SO"
            End If

            Dim Final_PC_Name As String = Lc & DeptCode & Me.txtNumber.Text & "-" & Me.txtUserName.Text.ToUpper

            Dim NewInfo As DataRow
            NewInfo = m_NewName.NewRow

            NewInfo("Place") = Lc
            NewInfo("Dept") = DeptName
            NewInfo("NB") = txtNumber.Text
            NewInfo("User_Name") = txtUserName.Text
            NewInfo("PC_Name") = Final_PC_Name
            NewInfo("DoneBy") = fclsMain.lblUserName.Text
            NewInfo("On") = Now

            
            m_NewName.Rows.Add(NewInfo)

            Try
                m_DataAdapter.Update(m_NewName)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try


            m_Connection.Close()
            m_NewName.Reset()
            m_NewName.Dispose()

            Me.txtID.Text = Final_PC_Name

        Catch objException As Exception
            Dim what As MsgBoxResult
            what = MessageBox.Show("An Error Occurred: " & objException.Message, _
            "Message Type: Error In Update Save_Info (fclsPCName)", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        m_Connection.Close()
        m_NewName.Reset()
        m_NewName.Dispose()
    End Sub

[jmcilhinney]

This same question has been asked and answered many times. This is one of the weaknesses of CommandBuilders. They take their cue from the query as to what columns to write into the other SQL statements. When you use a wild card in the query the column names are just used "as is". This is a problem if one of your column names is an SQL reserved word. In your case the culprit is "On". That will be interpreted as an SQL reserved word unless you enclose it in brackets. To get a CommandBuilder to do that you must do it in the query too. That means that you must write out the full column list rather than use a wild card. The alternative is to not use a CommandBuilder and create the other SQL statements yourself.

[phenom]

This same question has been asked and answered many times. This is one of the weaknesses of CommandBuilders. They take their cue from the query as to what columns to write into the other SQL statements. When you use a wild card in the query the column names are just used "as is". This is a problem if one of your column names is an SQL reserved word. In your case the culprit is "On". That will be interpreted as an SQL reserved word unless you enclose it in brackets. To get a CommandBuilder to do that you must do it in the query too. That means that you must write out the full column list rather than use a wild card. The alternative is to not use a CommandBuilder and create the other SQL statements yourself.

Thanks jmcilhinney,

I was doing a search and I found your answer there thanks again what can I say you are genius

Have a nice day…

[jmcilhinney]

Don't mistake experience for intelligence.

[phenom]

Don't mistake experience for intelligence.

Sure you are Genius & Expert (MVP very Pro) man too

[dminder]

I apologize ahead of time, this is off topic and I do not want to hijack this thread. Phenom, what are you using to get the forms to look like that? Do you currently have Vista or XP? I would really like to get my forms and such to have that kind of border, button look, etc.

Thanks,

D