Chet,

Try

SET NOCOUNT ON

near the beginning of your stored procedure.

Kurt

>I am having an issue with an SQL Server stored procedure and using ADODB to connect From VB, VB.NET, VFP. If I use ODBC, I don't have issues.
>
>SQL STORED PROCEDURE:
>

>CREATE PROCEDURE [dbo].[MyKeyValue]
>(
>    @KeyValue_Key CHAR(40)
>)
>AS
>
>BEGIN
>declare @LastIKey int
>declare @keytype char(1)
>set @LastIKey = 4000
>
>	SELECT @keytype = KeyType
> 	   FROM KeyValues
>	   WHERE KeyValue_Key = @KeyValue_Key
>
>
>  	BEGIN TRANSACTION
>	UPDATE KeyValues SET LastIKey = @LastIKey WHERE KeyValue_Key = @KeyValue_Key
>	COMMIT
>
>         -- Return the LastIKey value as in a cursor/recordset
>	SELECT LastIKey From KeyValues WHERE KeyValue_Key = @KeyValue_Key
>
>
>END
>RETURN  @@ERROR
>
>GO
>

>
>When I call the SP from VB via ADODB (verion 2.8), I do not get my record set that I expect from the last Select Statement. If I call the code via ODBC or testing in SQLEXECMS, it works just fine. We are trying to use ADODB and not ODBC.
>
>VB Code:
>

>   Dim lnRSState As Integer
>   Dim cnn As ADODB.Connection
>   Dim crs As ADODB.Recordset
>   Dim cmd As ADODB.Command
>   Dim prm As ADODB.Parameter
>
>   Set cnn = New ADODB.Connection
>   Set cmd = New ADODB.Command
>
>   cnn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=Gavel;Data Source=ORACLEDEV;User ID=sa;Password=ntsadmin;"
>   cnn.CursorLocation = 3
>   cnn.Open
>
>   Set crs = New ADODB.Recordset
>   With crs
>       .ActiveConnection = cnn
>       .CursorType = 1        ' adOpenKeyset
>       .CursorLocation = 3    ' adUseClient
>       .LockType = 3          ' adLockOptimistic
>   End With
>
>   With cmd
>       Set .ActiveConnection = cnn
>       .CommandType = 4
>       .CommandText = "MyKeyValue"
>       '   .CommandText = "NTSSP_GETKEYVALUE"
>
>       Set prm = .CreateParameter("returnvalue", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamReturnValue)
>       .Parameters.Append prm
>       .Parameters("returnvalue").Value = Null
>
>       Set prm = .CreateParameter("KeyValue_Key", ADODB.DataTypeEnum.adChar, ADODB.ParameterDirectionEnum.adParamInput, 40)
>       .Parameters.Append prm
>       .Parameters("KeyValue_Key").Value = "JUROREXCUSE_PK"
>
>
>   End With
>
>   Set crs = cmd.Execute
>
>   ' crs.Open cmd, Options:=4
>
>   MsgBox (crs.State)
>   MsgBox (cmd.Parameters(0).Value)
>   MsgBox (cmd.Parameters(1).Value)
>

>
>crs.State is always 0 based on the SP in SQL. If I remove the "UPDATE" command in my SP, everything works fine? I need the update to happen. What am I missing?
>
>Thanks
>Mark