Records not updating, datareader not showing new records

[JunglesMcBeef]

Hi,

I'm having trouble retrieving an ID field from records I insert into an Access database. The record inserts fine, but then when I read the database again, even if i create new oledb connection and command objects, it doesn't add the new record to the reader, but when i open the database, the record is there saved. Why is this happening?

    cn = New OleDbConnection(DBCONN)
    cn.Open()

    str = "INSERT INTO [CatchShot] ([Vessel], [Date],     [LogBookSheetNo], [ShotNo], [Latitude], " & _
                  "[Longitude], [WaterTemp], [SubSample], [Volume], [Comments]) VALUES ('" & cboVessel.Text & _
                  "',#" & CDate(txtDate.Text) & "#," & CInt(txtLogBookSheetNo.Text) & "," & _
                  CInt(txtShotNo.Text) & "," & CSng(txtLatDeg.Text) & "," & CSng(txtLongDeg.Text) & _
                  "," & CSng(txtWaterTemp.Text) & "," & chkSubSample.Checked & "," & CSng(txtVolume.Text) & _
                  ",'" & txtComments.Text & "')"
    cmd = New OleDbCommand(str, cn)
    cmd.ExecuteNonQuery()

    cmd = New OleDbCommand("SELECT * FROM CatchShot", cn)
    dr = cmd.ExecuteReader
    While dr.Read
        iCatchID = dr.Item("ID")
    End While

 

[maytel]

i think
its because you are accessing the wrong data
dr.read - reads the topmost row
the row you've just saved
might be located down below.
you can include where in your command sql

 

[JunglesMcBeef]

I worked out that i needed to sort by ID (ORDER BY) in the readers SQL statement. The reader will cycle through all the records and the last ID will be saved to my variable. Thanks for replying though maytel