Question Insert Into Syntax Error when adding new rows

[Pinto]

Tried This

   Dim drNewRow As DataRow = m_dtContacts.NewRow()
        With frmMain
            drNewRow("ID") = .txtID.Text
            drNewRow("Contact Name") = .txtContactName.Text
            drNewRow("Address") = .txtAddress.Text
            drNewRow("Town") = .txtTown.Text
            drNewRow("County") = .txtCounty.Text
            drNewRow("PostCode") = .txtPostCode.Text
            drNewRow("PhoneNo") = .txtPhoneNo.Text
            drNewRow("MobileNo") = .txtMobileNo.Text
            drNewRow("Fax") = .txtFax.Text
            drNewRow("email") = .txtemail.Text
        End With
        m_dtContacts.Rows.Add(drNewRow)
        m_daContacts.Update(m_dtContacts)
        m_intRowPosition = m_dtContacts.Rows.Count - 1

and I also tried this

   Dim drNewRow As DataRow = m_dtContacts.NewRow()
        With frmMain
            drNewRow("ID") = .txtID.Text
            drNewRow([Contact Name]") = .txtContactName.Text
            drNewRow("Address") = .txtAddress.Text
            drNewRow("Town") = .txtTown.Text
            drNewRow("County") = .txtCounty.Text
            drNewRow("PostCode") = .txtPostCode.Text
            drNewRow("PhoneNo") = .txtPhoneNo.Text
            drNewRow("MobileNo") = .txtMobileNo.Text
            drNewRow("Fax") = .txtFax.Text
            drNewRow("email") = .txtemail.Text
        End With
        m_dtContacts.Rows.Add(drNewRow)
        m_daContacts.Update(m_dtContacts)
        m_intRowPosition = m_dtContacts.Rows.Count - 1

and I also tried this

Dim drNewRow As DataRow = m_dtContacts.NewRow()
        With frmMain
            drNewRow(0) = CLng(.txtID.Text)
            drNewRow(1) = .txtContactName.Text
            drNewRow(2) = .txtAddress.Text
            drNewRow(3) = .txtTown.Text
            drNewRow(4) = .txtCounty.Text
            drNewRow(5) = .txtPostCode.Text
            drNewRow(6) = .txtPhoneNo.Text
            drNewRow(7) = .txtMobileNo.Text
            drNewRow(8) = .txtFax.Text
            drNewRow(9) = .txtemail.Text
        End With
        m_dtContacts.Rows.Add(drNewRow)
        m_daContacts.Update(m_dtContacts)
        m_intRowPosition = m_dtContacts.Rows.Count - 1

I do not have control to change the field "Contact Name". When I run the code I get
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
{"Syntax error in INSERT INTO statement."}

 

[jmcilhinney]

The error message says that there's an error in your INSERT statement but you haven't shown us your INSERT statement. I'm guessing that that's because you didn't write it yourself but rather had it generated by a command builder, but that would be relevant information worth sharing at the outset. If that's the case then I'm guessing that the issue is that you used a wildcard (*) in your SELECT statement and the command builder is not handling that column name with the space in it properly.

It is ALWAYS a bad idea to put spaces or other special characters in column names or other identifiers. If it's within your power you should edit the database schema to remove that space. If my guess is correct then your issue will go away. If you can't change the schema then you will need to set the QuotePrefix and QuoteSuffix of the command builder to "[" and "]" respectively, so that it will wrap all your column names in brackets and the one with the space will then be interpreted correctly. What you do when setting the parameters is completely irrelevant because that doesn't affect the SQL code that gets executed.

 

[Pinto]

The line of code that causes the proble is

m_daContacts.Update(m_dtContacts)

I do not create the insert statement so I would have to guess it is created internally.

I do know that it is very bad practice to use spaces in a name but I do not have control over it. Otherwise I would just chnage the name.

How do I set the QuotePrefix and QuoteSuffix?

 

[Pinto]

jmcilhinney

Thank you so much for the advice. It appears that the QuotePrefix and QuoteSuffix solved the problem. I changed my code to

      sql = "SELECT Contacts.* FROM Contacts"
        m_cnContacts.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Windows.Forms.Application.StartupPath & "\Addressbook.mdb"
        m_cnContacts.Open()
        m_daContacts = New OleDb.OleDbDataAdapter(sql, m_cnContacts)
        m_cbContacts = New OleDb.OleDbCommandBuilder(m_daContacts)
        m_cbContacts.QuotePrefix = "["
        m_cbContacts.QuoteSuffix = "]"
        m_daContacts.Fill(m_dtContacts)

everything seems to work now that I changed the above code and I can use the add code like this

Dim drNewRow As DataRow = m_dtContacts.NewRow()
        With frmMain
            drNewRow("ID") = .txtID.Text
            drNewRow("Contact Name") = .txtContactName.Text
            drNewRow("Address") = .txtAddress.Text
            drNewRow("Town") = .txtTown.Text
            drNewRow("County") = .txtCounty.Text
            drNewRow("PostCode") = .txtPostCode.Text
            drNewRow("PhoneNo") = .txtPhoneNo.Text
            drNewRow("MobileNo") = .txtMobileNo.Text
            drNewRow("Fax") = .txtFax.Text
            drNewRow("email") = .txtemail.Text
        End With
        m_dtContacts.Rows.Add(drNewRow)
        m_daContacts.Update(m_dtContacts)
        m_intRowPosition = m_dtContacts.Rows.Count - 1

Thanks again for your help