AutoNumber field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am writing a program in VB NET and accesiing an Access 2003 database. I
have an ID field in my table that is AutoNumber. In my code, I use and
autogen statement to automatically generate the INSERT, UPDATE, and DELETE
SQL, but when the program runs, it never adds the ID field to the INSERT
statement so I receive Syntax error in INSERT INTO statement.

Please help.
 
Hi
In my code, I use and
autogen statement to automatically generate the INSERT, UPDATE, and DELETE
SQL, but when the program runs, it never adds the ID field to the INSERT
statement so I receive Syntax error in INSERT INTO statement.
Click to expand...

Are you using typed datasets?

Are you using DataAdapter.Update(), with command builder? Can u paste yr code?
 
Declarations:

' Open a database connection.
Dim strConnection As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=schedule.mdb"
Dim cn As OleDbConnection = New OleDbConnection(strConnection)

' Create a data adapter object and set its SELECT command.
Dim strSelect As String = _
"SELECT entryid, date, child, activity, starttime, endtime FROM entry"
Dim da As OleDbDataAdapter = New OleDbDataAdapter(strSelect, cn)

' Set the data adapter object's UPDATE, INSERT, and DELETE
' commands. Use the OleDBCommandBuilder class's ability to auto-
' generate these commands from the SELECT command.
Dim autogen As New OleDbCommandBuilder(da)

' Load a data set.
Dim ds As DataSet = New DataSet

Insert Button:

Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnInsert.Click

If cn.State = ConnectionState.Closed Then
cn.Open()
End If

' Get a reference to the "entry" DataTable.
Dim dt As DataTable = ds.Tables("entry")

Dim row As DataRow = ds.Tables("entry").NewRow()

' Add a record.
row = dt.NewRow()
row("entryID") = Integer.MaxValue
row("date") = txtDate.Text
row("child") = cboChild.Text
row("activity") = cboActivity.Text
row("starttime") = txtStart.Text
row("endtime") = txtEnd.Text
dt.Rows.Add(row)

txtDate.Text = ""
cboChild.Text = ""
cboActivity.Text = ""
txtStart.Text = ""
txtEnd.Text = ""

lblStatus.Text = "Add complete"

End Sub

Form Load:

da.Fill(ds, "entry")
 
Hi Tom,
At any point in your code, are you actually instantiating your datatable or
adding columns to it?

For example

Dim dt as New DataTable("entry")

dim colID as New DataColumn("EntryID", GetType(Int32))
..
..


dt.Columns.AddRange(New DataColumn(){colID,.....})

With colID
.AutoIncrement = True
.AutoIncrementStep = -1
.AutoIncrementSeed = -1
End With

Furthermore, you are opening your OleDbConn object just before you are
adding the row to your DataTable, which is a disconnected storage object that
is not a part of Access. You do not open a Connection object to do a row
insert on a datatable. You just add the row as you are doing. One hint here
too, you can speed your code by calling row.BeginEdit before you start
assinging column values, and row.EndEdit after you finish the assignment and
just before calling dt.Rows.Add(row). You do not want to assign a value to
your "entryID" column if you use the autoincrement code above.

After you have your values in the datatable, you would then actually insert
them into your Access Table by calling your dataAdapter's Update method on
your datatable. The dataAdapter will manage your Connection for you - you do
not need to explicitly open or close it. You might use a Try,Catch,EndTry
block to do your dataAdapter operation, and put a Finally block in that
assures that your connection is shut, like

Finally
If cn.State = ConnectionState.Open Then cn.Close()

HTH

JT
 
Back
Top