Retrieving a key field using a variable
Posted on 2004-05-01
I am now able to insert a variable into my Table. Now I want to take this same variable and extract the key field value for this row.
This is an Access Table. The table name is tblAGMain. The fields in the table are: KeyAG (Autonumber field and keyed), dtDate (Date field type), dtTime (Text field type), Locator (Text field type), Initials (Text field type).
Now this is the bit of code that I am trying to use to retrieve the key field value, bu I always get a value of "0".
Dim cmdDate As OleDbCommand = cnAGPOUpDate.CreateCommand()
cmdDate.CommandText = "INSERT INTO tblAGMain (dtDate, tmTime, Initials, Locator) VALUES ('" & strToday & "', " & _
"Null, '" & strLocator & "', Null)"
'create a new command to retrieve the key field from the tblAGMain where the date was just inserted
Dim cmdAGKey As OleDbCommand = cnAGPOUpDate.CreateCommand()
cmdAGKey.CommandText = "SELECT KeyAg FROM tblAGMain WHERE Locator = '" & strLocator & "'"
intAGKey = CInt(cmdAGKey.ExecuteScalar())
strAGKey = intAGKey.ToString
What is wrong with my code, why am I not getting my key field valu back?