ASP.NET, SQL Server, WPF, C#, VB.NET

I use an ADO connection to add new records to a SQL Server database. After a 
 
new record is added iit is supposed to be the current record. Yet, I cannot 
 
seem to retrieve the primary key (autogenerated) for the record. I just get a 
 
zero. The added data goes in and can be queried and viewed. Here is a code 
 
snippet: 
 
Dim cnSQL As ADODB.Connection   ' SQL server 
 
Dim rsTR As ADODB.Recordset     ' SQL table 
 
Set cnSQL = New ADODB.Connection 
 
Set rsTR = New ADODB.Recordset  ' transcript table 
 
Set rsGr = New ADODB.Recordset   ' source data 
 
rsGr.Open "qryDataForTranscript_V2", cnACC, adOpenForwardOnly, 
 
adLockReadOnly  ' ** This recordset contains the data I append into SQL Svr. 
 
With rsTR 
 
.Open "TranscriptCourse", cnSQL, adOpenStatic, adLockPessimistic 
 
.AddNew 
 
.Fields("personid") = rsGr.Fields("personID") 
 
.Fields("coursenumber") = rsGr.Fields("coursenum") 
 
.Fields("courseName") = rsGr.Fields("ALC Coursename") 
 
.Update   ' force update in place. 
 
Debug.Print "New key is "; .Fields("transcriptID") 
 
End With 
 
' ******** end of snippet 
 
The debug.print displays a zero for the transcriptID value. how can I get 
 
the current primary key value of the new record? 
 
I am using the following ADO libraries in Access 2003: 
 
MS ActiveX Data Objects 2.8 library 
 
MS ActiveX Data Objects Recordset Libarary 
 
Thanks for any ideas!

Looks like you need to use a different cursor type. This worked for me when 
 
I changed the cursor type from adOpenStatic to adOpenKeyset ... 
 
Public Sub TestGetId() 
 
Const strcConnect As String = "Provider=SQLOLEDB.1;" & _ 
 
"Integrated Security=SSPI;" & _ 
 
"Persist Security Info=False;" & _ 
 
"Initial Catalog=Northwind;" & _ 
 
"Data Source=(local)" 
 
Dim cn As ADODB.Connection 
 
Dim rst As ADODB.Recordset 
 
Dim fld As ADODB.Field 
 
Set cn = New ADODB.Connection 
 
cn.ConnectionString = strcConnect 
 
cn.Open 
 
Set rst = New ADODB.Recordset 
 
'    rst.Open "SELECT * FROM Categories", cn, adOpenStatic, _ 
 
'        adLockPessimistic 
 
rst.Open "SELECT * FROM Categories", cn, adOpenKeyset, _ 
 
adLockPessimistic 
 
rst.AddNew 
 
rst.Fields("CategoryName") = "Test" 
 
rst.Fields("Description") = "A Test Category" 
 
rst.Update 
 
Debug.Print rst.Fields("CategoryID"), _ 
 
rst.Fields("CategoryName"), rst.Fields("Description") 
 
rst.Close 
 
cn.Close 
 
End Sub 
 
-- 
 
Brendan Reynolds 
 
Access MVP 
 
"GeorgeAtkins" <GeorgeAtkins@discussions.microsoft.com> wrote in message 
 
news:B75D59E3-959A-4F37-BC27-8DD9B5A0715E@microsoft.com...