October 27, 2004 at 9:49 pm
I have noticed over and over again that when I use the OLEDB driver for SQL Server, I do not get a returned recordset filled-in with the @@Identity integer when I request that this be returned.
***However, the same code works OK when using the ODBC driver for SQL Server.
Other select queries work just fine with both OLEDB and ODBC.
Has anyone experienced the same?
Here is the code that works for ODBC but does NOT work with OLEDB driver. I am using ADO 2.8.
. . . Basically OLEDB does NOT return the recordset with the @@Idenity value
Create PROC [insert_MyRecord]
@MyRecordName nvarchar(150),
@MyRecordValue real,
AS
INSERT INTO [MyTable]
([RecNm],
[Value])
VALUES
(@MyRecordName,
@MyRecordValue)
SELECT @@IDENTITY as NewRecID
GO
October 29, 2004 at 1:46 am
Anthony,
There should not be any reason why you can't use the OLEDB provider -we use both and have not noticed this problem.
Is the comma at the end of the third line of the proc a mistype or is it really in your procedure? If so then this obviuosly should not be there and may explain the problem.
However as a generalisation I would suggest that if you just want to return the @@Identity value that you use an output parameter and just return this rather than returning a single value in a recordset - which has much higher overheads (and on the same track also include the SET NOCOUNT ON option which prevents the count of records affected from being returned).
Hope this is of some help
David Saville
October 29, 2004 at 9:10 am
1) Create a database called PlayGround on my (local) SQL Server 2000
2) Create a login called PlayGround_DBO with a password of "xyzabc123" and grant it DBO access to the PlayGround database
3) Create a table within the PlayGround database using the follow SQL:
CREATE TABLE [dbo].[MyTable] (
[RecID] [int] IDENTITY (1, 1) NOT NULL ,
[RecNm] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Value] [real] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(RecID) ON [PRIMARY]
GO
4) Create a stored procedure in the PlayGround database using the following SQL. Please pay special note to the SCOPE_IDENTITY() function:
CREATE PROCEDURE [dbo].[insert_MyRecord]
@MyRecordName nvarchar(150),
@MyRecordValue real
AS
SET NOCOUNT ON;
INSERT INTO [MyTable]
([RecNm],
[Value])
VALUES
(@MyRecordName,
@MyRecordValue);
SELECT SCOPE_IDENTITY() as NewRecID;
GO
5) Use the following code in a form within a Visual Basic 6 project
Option Explicit
Private Sub Command1_Click()
Const CONNSTRING = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=PlayGround;User ID=PlayGround_DBO;Password=xyzabc123;"
Dim lNewRecID As Long
Dim lRcdsAffected As Long
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim oRS As ADODB.Recordset
lNewRecID = 0
lRcdsAffected = 0
Set oConn = New ADODB.Connection
With oConn
.ConnectionString = CONNSTRING
.CursorLocation = adUseServer
.Open
Set oCmd = New ADODB.Command
With oCmd
Set .ActiveConnection = oConn
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@MyRecordName", adVarWChar, adParamInput, 150, "foo")
.Parameters.Append .CreateParameter("@MyRecordValue", adSingle, adParamInput, 4, 123.45)
.CommandText = "insert_MyRecord"
Set oRS = .Execute(lRcdsAffected)
With oRS
If Not (.BOF And .EOF) Then
lNewRecID = .Fields("NewRecID").Value
End If
.Close
End With
Set oRS = Nothing
End With
.Close
End With
Set oConn = Nothing
MsgBox lNewRecID, vbInformation, "NewRecID"
End Sub
5) Run the program and click on the command button
6) It should return to you the number of the new record just added.
November 10, 2004 at 7:49 am
A more efficient way of retrieving the identity value via a return value (as no return trip is required to fetch the new identity value) is as follows:
1) Using the same database and table mentioned in my previous post, create the following stored procedure. Please pay special attention to the return of the SCOPE_IDENTITY() at the end of the stored procedure.
CREATE PROCEDURE [dbo].[insert_MyRecord_Return]
@MyRecordName nvarchar(150),
@MyRecordValue real
AS
SET NOCOUNT ON;
INSERT INTO [MyTable]
([RecNm],
[Value])
VALUES
(@MyRecordName,
@MyRecordValue);
RETURN SCOPE_IDENTITY();
2) Use the following code in a form within a Visual Basic 6 project
Private Sub Command2_Click()
Const CONNSTRING = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=PlayGround;User ID=PlayGround_DBO;Password=xyzabc123;"
Dim lNewRecID As Long
Dim lRcdsAffected As Long
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
lNewRecID = 0
lRcdsAffected = 0
Set oConn = New ADODB.Connection
With oConn
.ConnectionString = CONNSTRING
.CursorLocation = adUseServer
.Open
Set oCmd = New ADODB.Command
With oCmd
Set .ActiveConnection = oConn
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@Return_Value", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@MyRecordName", adVarWChar, adParamInput, 150, "foo")
.Parameters.Append .CreateParameter("@MyRecordValue", adSingle, adParamInput, 4, 123.45)
.CommandText = "insert_MyRecord_Return"
.Execute (lRcdsAffected)
lNewRecID = .Parameters("@Return_Value").Value
End With
.Close
End With
Set oConn = Nothing
MsgBox lNewRecID, vbInformation, "NewRecID"
End Sub
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply