add record to Access Database

[thomasp]

I am using the following code to add a record to an Access database. If I
run the code and go to the database the code is there, but if as soon as I
run the code I try to access the record with the SQL statement: strSQL =
"SELECT Target_NO FROM " & strtablename & " WHERE ID = " & intMaxID, I don't
get the record that I just added although it does have the highest ID
number. Is the code just running faster than the database gets updated?

Thanks,

Thomas
'set up the database connection, dataAdapter, and dataSet

Dim myConnection As OleDbConnection
myConnection = New OleDbConnection(strConn)
myConnection.Open()

Dim da As New OleDb.OleDbDataAdapter(strSQL, myConnection)
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim ds As New DataSet
Dim newRow As DataRow

Try
da.FillSchema(ds, SchemaType.Source, strTable)
newRow = ds.Tables(strTable).NewRow()
newRow("ID") = aryAddRecord(0)
newRow("DTG") = aryAddRecord(1)
newRow("POO") = aryAddRecord(2)
newRow("POO_Alt") = Val(aryAddRecord(3))
newRow("POI") = aryAddRecord(4)
newRow("POI_Alt") = Val(aryAddRecord(5))
newRow("Distance") = Val(aryAddRecord(6))
newRow("Direction") = Val(aryAddRecord(7))
If intImportType = 2 Then
newRow("Db") = Val(aryAddRecord(8))
newRow("Velocity") = Val(aryAddRecord(9))
End If
newRow("Target_NO") = aryAddRecord(10)
If intImportType = 2 Then
If Len(aryAddRecord(11)) < 1 Then
aryAddRecord(11) = "false"
End If
End If
newRow("Weapon_Type") = aryAddRecord(11)
newRow("Confirmed") = Val(aryAddRecord(12))

'add the new row to the dataSet
ds.Tables(strTable).Rows.Add(newRow)

'sent the updated dataSet to the database
da.Update(ds, strTable)

Catch oException As OleDbException
MessageBox.Show(oException.Message)

Catch oException As Exception
MessageBox.Show(oException.Message)

End Try

myConnection.Close()

--
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
------->>>>>>http://www.NewsDemon.com<<<<<<------
Unlimited Access, Anonymous Accounts, Uncensored Broadband Access

[Bernie Yaeger]

Hi Thomas,

Shouldn't be. How are you setting the value of intmaxid? Which row do you
get?

Bernie Yaeger

<th*****@msala.net> wrote in message
news:42***********************@news.newsdemon.com. ..

I am using the following code to add a record to an Access database. If I
run the code and go to the database the code is there, but if as soon as I
run the code I try to access the record with the SQL statement: strSQL =
"SELECT Target_NO FROM " & strtablename & " WHERE ID = " & intMaxID, I
don't
get the record that I just added although it does have the highest ID
number. Is the code just running faster than the database gets updated?

Thanks,

Thomas
'set up the database connection, dataAdapter, and dataSet

Dim myConnection As OleDbConnection
myConnection = New OleDbConnection(strConn)
myConnection.Open()

Dim da As New OleDb.OleDbDataAdapter(strSQL, myConnection)
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim ds As New DataSet
Dim newRow As DataRow

Try
da.FillSchema(ds, SchemaType.Source, strTable)
newRow = ds.Tables(strTable).NewRow()
newRow("ID") = aryAddRecord(0)
newRow("DTG") = aryAddRecord(1)
newRow("POO") = aryAddRecord(2)
newRow("POO_Alt") = Val(aryAddRecord(3))
newRow("POI") = aryAddRecord(4)
newRow("POI_Alt") = Val(aryAddRecord(5))
newRow("Distance") = Val(aryAddRecord(6))
newRow("Direction") = Val(aryAddRecord(7))
If intImportType = 2 Then
newRow("Db") = Val(aryAddRecord(8))
newRow("Velocity") = Val(aryAddRecord(9))
End If
newRow("Target_NO") = aryAddRecord(10)
If intImportType = 2 Then
If Len(aryAddRecord(11)) < 1 Then
aryAddRecord(11) = "false"
End If
End If
newRow("Weapon_Type") = aryAddRecord(11)
newRow("Confirmed") = Val(aryAddRecord(12))

'add the new row to the dataSet
ds.Tables(strTable).Rows.Add(newRow)

'sent the updated dataSet to the database
da.Update(ds, strTable)

Catch oException As OleDbException
MessageBox.Show(oException.Message)

Catch oException As Exception
MessageBox.Show(oException.Message)

End Try

myConnection.Close()

--
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
------->>>>>>http://www.NewsDemon.com<<<<<<------
Unlimited Access, Anonymous Accounts, Uncensored Broadband Access

[thomasp]

Most of my records are added in large groups from text files. The code
queries for the largest ID number, adds one to it, then starts adding
records. Works great for my situation.

The form I am working on, that has this problem, is used to manual add one
record at a time. When the form opens it queries for the largest ID, adds
one, and uses that for the ID of the first added record. When the user
clicks the add record button, the record is added and the query for the
highest ID is ran again. The ID it returns is the same as before the record
was added. If I go to the database and look, the record I just added is
there.

Hope all that makes sense.

If you have time look at my other post, you may can help me with it.

Thanks,

Thomas

--
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
------->>>>>>http://www.NewsDemon.com<<<<<<------
Unlimited Access, Anonymous Accounts, Uncensored Broadband Access