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
"Tom Nowak" wrote:
> 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")
>
>
>
> "Vivek Thakur" wrote:
>
> > 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.
> >
> > Are you using typed datasets?
> >
> > Are you using DataAdapter.Update(), with command builder? Can u paste yr code?
> >
|
Similar Threads
