Results 1 to 3 of 3

Thread: Inserting a New Record error

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2010

    Arrow Inserting a New Record error


    Here's the code that fails on update with a fail on Insert Into syntax message:

    SQLConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\TechTest-NDT.accdb"
    ' SQLConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\TechTest\Database\NDTQA.mdb"
    con.ConnectionString = SQLConnect
    sql = "SELECT * FROM Questions"
    daQuestions = New OleDb.OleDbDataAdapter(sql, con)
    daQuestions.Fill(dsQuestions, "Questions")
    Dim cb As New OleDb.OleDbCommandBuilder(daQuestions)
    Dim dsNewRow As DataRow

    dsNewRow = dsQuestions.Tables("Questions").NewRow()

    'set fields

    dsNewRow.Item("Category") = "Materials" : dsNewRow.Item("Topic") = "NDT"
    dsNewRow.Item("Level") = DB_Level.Text : dsNewRow.Item("Question") = DB_Question.Text
    dsNewRow.Item("Answ_1") = DB_QuesA.Text : dsNewRow.Item("Answ_2") = DB_QuesB.Text
    dsNewRow.Item("Answ_3") = DB_QuesC.Text : dsNewRow.Item("Answ_4") = DB_QuesD.Text

    daQuestions.Update(dsQuestions, "Questions")

    Any ideas?

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Sydney, Australia

    Re: Inserting a New Record error

    If you get a SQL syntax error when using a CommandBuilder it is almost always because one of your column names is either a reserved word or contains illegal characters, e.g. spaces. There are three options:

    1. Change the offending column name(s) in the database.

    2. Don't use a wildcard, i.e. *, in your query. Write out the column list in full, which will force you to escape the offending column name(s), which the CommandBuilder will then copy.

    3. Don't use a CommandBuilder, instead creating the action commands yourself.

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002

    Re: Inserting a New Record error

    It would also help to know what the error is.

    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts


Click Here to Expand Forum to Full Width