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()

 

[Bernie Yaeger]

Hi Thomas,

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

Bernie Yaeger

 

[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

 

[Bernie Yaeger]

Hi Thomas,

I understand you perfectly.

I think the problem is your routine to get maxid. Is the table fully
updated BEFORE the routine reruns? Try adding the record and pausing before
you go any further; then use MS Access to see if the row is there; if it is,
then allow the routine to continue. My guess is that it will still fail,
because it isn't going to the refreshed table. Remember, if you are using
the table as, say, in a dataset, then it has to be refreshed before it knows
of the new row. I would change the maxid get routine to continually open
and close the dataset, reopening after the row has been added. Also, even
if you are using the 'old' dataset, it may not be adding the unique rowid
column to it, but rather only to the table itself - you are using a
commandbuilder and I don't know if they will automatically update the
datatable inside the dataset, even though we know that it properly updates
the actual table inside Access.

HTH,

Bernie Yaeger

 

[thomasp]

Not sure where you mean to put the refresh. As you saw in the earlier code:
'add the new row to the dataSet
ds.Tables(strTable).Rows.Add(newRow)
'sent the updated dataSet to the database
da.Update(ds, strTable)

is ran before the funGetMaxID is called.

Once again, thanks for all the help.

Thomas


Public Function funGetMaxID(ByVal strTableName As String) As Integer

'check the database connection
If cn.State = ConnectionState.Closed Then
Call subDBConnect()
End If

strSQL = "SELECT MAX(ID) AS MAXID FROM " & strTableName

cmd = New OleDb.OleDbCommand(strSQL, cn)

rdr = cmd.ExecuteReader
rdr.Read()

funGetMaxID = 0

If rdr.HasRows = True Then
If Not IsDBNull(rdr!MAXID) Then
funGetMaxID = Val(rdr!MAXID)
End If
End If

If rdr.IsClosed = False Then
rdr.Close()
End If

cmd = Nothing
rdr = Nothing

End Function

 

[Bernie Yaeger]

Hi Thomas,

I would refresh the datatable right after da.update. Note that the
rows.add(newrow) altered the datatable, but not the Access table; the
..update does that. However, the datatable probably does not have the unique
rowid as I mentioned before. Thus, refresh the datatable any where after
the the rows.add but before the fungetmaxid function; I'd do it right after
the da.update routine.

HTH,

Bernie