"Syntax Error in INSERT INTO" - OleDbCommandBuilder 1

[Melagan]

Access 2003 table, unable to insert new records. There are four fields:
1) UserID - AutoNum - Primary Key
2) First - Text
3) Last - Text
4) Email - Text

When debugging, it catches an exception when I call the OleDbDataAdapter.Update method and kicks out the following error: "Syntax Error in INSERT INTO Statement"

Any ideas?

    Sub page_load(ByVal sender As Object, ByVal e As EventArgs)
        'Instantiate DataSet
        Dim ds As New DataSet("myDataSet")
        Try
            'Define Connection Object
            Dim myConn As New OleDbConnection(ConfigurationManager.ConnectionStrings("Conn").ConnectionString)
            
            'Create an adapter to get records from database
            Dim objCmd As New OleDbDataAdapter("SELECT * FROM tblRegistery", myConn)
            
            'Define each column to map
            Dim dcmUserID As New DataColumnMapping("UserID", "UserID")
            Dim dcmFirst As New DataColumnMapping("First", "First")
            Dim dcmLast As New DataColumnMapping("Last", "Last")
            Dim dcmEmail As New DataColumnMapping("Email", "Email")
            
            'Define table containing column mappings
            Dim dtmRegistery As New DataTableMapping("tblRegistery", "table")
            dtmRegistery.ColumnMappings.Add(dcmUserID)
            dtmRegistery.ColumnMappings.Add(dcmFirst)
            dtmRegistery.ColumnMappings.Add(dcmLast)
            dtmRegistery.ColumnMappings.Add(dcmEmail)
            
            'Activate mapping mechanism
            objCmd.TableMappings.Add(dtmRegistery)
            
            'Fill DataSet
            objCmd.Fill(ds)
            
            'Create command builder to create SQL instructions
            Dim objAuto As New OleDbCommandBuilder(objCmd)
            
            'Insert new record into DataSet
            Dim dr As DataRow = ds.Tables(0).NewRow
            dr("First") = "Jason"
            dr("Last") = "Kinsey"
            dr("Email") = "jkinsey@nat.com"
            ds.Tables(0).Rows.Add(dr)
            
            'Insert changes to database
            objCmd.Update(ds.GetChanges())
            
            'Align in-memory changes with datasource
            ds.AcceptChanges()
            
            Response.Write("Record inserted successfully.")
            
        Catch ex As Exception
            ds.RejectChanges()
            Response.Write(ex.Message)
        End Try
        
    End Sub

~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

Furthermore, I realize this is more of an ADO.NET issue than ASP.NET, but this is the closest forum I can find anywhere to meeting this topic.


~Melagan
______
"It's never too late to become what you might have been.

 

[vbkris]

hi,

since the coding looks fine and we do not have tools similar to the profiler in Access i will have to use only the error to go by.

This is what google gave me:

http://www.xlinesoft.com/asprunner/docs/error_syntax_error_in_insert_into_statement.htm

It looks like some keywords have been used as column names. from your Table the only thing that remotely resembles a keyword is UserId. Try changing the column name for testing purposes and see if the error is done with...

Known is handfull, Unknown is worldfull

 

[Melagan]

I want to do a cartwheel right now, and at the same time scream. It was so simple -- and it was something that I've worked with before when I had a field named "password".

Renaming the columns in the DB did the trick. A simple fix, but a star nontheless. Thank you!


~Melagan
______
"It's never too late to become what you might have been.

 

[vbkris]

your welcome

Known is handfull, Unknown is worldfull