Do not know whether it is "your" case, but while trying to apply ADODB as
you are doing, I met some limitations.

As it is explained in :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscusingadowithmicrosoftvisualbasic.asp,

you can use the New keyword with Dim to declare and instantiate ADO objects:

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

or

Dim conn1
Set conn1 = CreateObject("ADODB.Connection") As Object

AFAIK, with the first approach you need to add a ADODB reference to the
project. Second approach does not require the additional reference.

When following the first approach and making a LOCAL copy of the reference
(in VB.NET), I discovered that it is actually the adodb.dll additional file,
and I met some limitations (probably because adodb.dll is a wrapper). One of
those limitations I discussed here in the NET forum during the past month.

With the second approach I did not meet limitations, and I did not need a
reference.


Again, I do not know whether it is your case, but if there will no other
opinions/advices I would try the second approach to see whether it helps.



>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