Inserting records into an Access database with ADO.NET

[cpluspluser]

Hi,

I'm very simply trying to insert records into an Access DB using ADO.NET. I've tried the 2 following ways and both give me an "oledb.oledbexception" but no othewr info.

The 1st method uses a command and executenonquery() method. The second uses a data adapter.

Method 1

        Dim conn As New OleDb.OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Richard\Account_Manager\Account_Manager.mdb"
        conn.Open()
        Debug.WriteLine("conn state:" & conn.State)

        Dim comm As New OleDb.OleDbCommand()
        comm.Connection = conn
        Debug.WriteLine(comm.Connection)
        comm.CommandText = "insert into Employee(name,position,status) values('name','position','status')"
        Debug.WriteLine(comm.CommandText)
        Try
            comm.ExecuteNonQuery()
            conn.Close()
        Catch ex As System.Exception
            Debug.WriteLine(ex.Message)
            Debug.WriteLine(ex.Source)
            Debug.WriteLine(ex.StackTrace)

        End Try

Method 2

        Dim conn As New OleDb.OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Richard\Account_Manager\Account_Manager.mdb"
        conn.Open()
        Debug.WriteLine("conn state:" & conn.State)
        Dim sql As String
        sql = "select * from employee"
        Dim da As New OleDb.OleDbDataAdapter(sql, conn)

        Dim ds As New DataSet()
        da.Fill(ds, "test")

        Dim dt As DataTable
        dt = ds.Tables("test")

        Dim commB As New OleDb.OleDbCommandBuilder(da)
        da.InsertCommand = commB.GetInsertCommand()

        Dim dr As DataRow
        dr = dt.NewRow
        dr.Item("name") = "name"
        dr.Item("position") = "position"
        dr.Item("status") = "status"
        dt.Rows.Add(dr)
        da.Update(ds, "test")
    End Sub

[Lunatic3]

'Position' seems to be a reserved word. Change that column name, everything should work fine then.

[cpluspluser]

Much Thanks!!!


Position was a keyword and that was the problem.