Recordset AddNew - Convert To SQL Server

[edsuk]

Hi

I have been asked to upgrade an Access 2000 Application to use SQL Server as the back end, tables are linked using ODBC and testing has gone quite well (after a few code amendments), however one issue I am getting is adding a new record using recordset operations then opening a form using the primary key value. The code below works with access as the back end:

Dim rst As Recordset
Dim i As Long
Dim ctl As Control

Set rst = CurrentDb().OpenRecordset("T_PurchaseOrder", dbOpenDynaset, dbAppendOnly)

rst.AddNew
rst(2) = Date
rst(3) = Time()
rst(5) = UserID
i = rst("Order_ID")
rst.Update

rst.close
Set rst = Nothing

DoCmd.OpenForm "FrmNewPO", , , "[Order_ID]=" & i

I have created a stored procedure which is executed by the code below and this works (@OrderID = Scope_Identity).

Dim cnn As ADODB.Connection
Dim com As ADODB.Command
Dim prm As ADODB.Parameter
Dim i As Long, iCount As Long
Dim ctl As Control

i = 0

Set cnn = New ADODB.Connection
Set com = New ADODB.Command

If cnn.State = adStateClosed Then cnn.Open GetCnn

With com
    .ActiveConnection = cnn
    .CommandText = "sp_Insert_New_PO"
    .CommandType = adCmdStoredProc
    Set prm = .CreateParameter("@UserID", adInteger, adParamInput, , UserID)
    .Parameters.Append prm
    Set prm = .CreateParameter("@OrderID", adInteger, adParamOutput)
    .Parameters.Append prm
    .Execute RecordsAffected:=iCount, Options:=adExecuteNoRecords
    If iCount <> 0 Then
     i = .Parameters("@OrderID")
   End If

End With


cnn.close
Set cnn = Nothing
Set com = Nothing

If i = 0 Then Exit Sub

DoCmd.OpenForm "FrmNewPO", , , "[Order_ID]=" & i

My question is:

Is there any other way, using ADO to insert a record into an SQL Server table and have the primary key value returned? Or is using a stored procedure the preferred method.

I am not terribly familiar with SQL Server, having normally used Access as the back end with DAO, so have anticipated a sharp learning curve!

So if anyone has any wise words of wisdom I would be glad to hear them!

Thanks

 

[AlexCuse]

What are you using for front end? If it's vb6, then you would work with recordsets basically the same way, no?

If you are using your sp to populate the recordset, it might be helpful to know what that does as well.

Good Luck!

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[fredericofonseca]

Change your SP to have a OUT parameter, and set this one to @@SCOPE_IDENTITY() upon inserting the record.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com