More troubles with ADO and SQL Server 2005

[Guest]

Hi all!

Up to now, I was working with a SQL Server 7 and VBSQL. It was easy to call
a stored procedure with (usually) a SELECT parameter and retrieve a cursor
from the stored procedure.
As I am in the process to migrating to a SQL 2005 Server, I can't use VBSQL
anymore.
What I try to do is as follows:

I have a stored procedure like "SELECT * FROM <Table> WHERE <Table.Field> =
@Parameter"
I would like to retrieve a cursor (ADODB.Recordset) when calling that
procedure.

I tryied somothing like this but it does not work:
Dim cnnTest As New ADODB.Connection
Dim rstTest As New ADODB.Recordset
Dim strCnn As String
Dim prp As ADODB.Property

strCnn = "driver={SQL Server};SERVER=BRAVO-N;DATABASE=TEST_ADO;
Trusted_Connection=yes"
rstTest.CursorType = adOpenKeyset
rstTest.LockType = adLockOptimistic
' Open Table
'
Rem rstTest.Open "Table_1", strCnn, , , adCmdTable
' Open stored procedure (without parameter)
'
' rstTest.Open "NewSelectCommand", strCnn, adOpenDynamic,
adLockOptimistic
' Open stored procedure (with parameter)
'
Set prmAddress = rstTest.CreateParameter("Address", adUnsignedInt,
adParamInput, 4)
rstTest.Parameters.Append prmAddress
prmAddress.Value = 1
rstTest.Open "Test", strCnn, adOpenDynamic, adLockOptimistic
Do Until rstTest.EOF
MsgBox rstTest!col_1 & " : " & rstTest!col_2, , "TestADO_Recordset"
rstTest.MoveNext
Loop
rstTest.Close
Set rstTest = Nothing

When I try to call that function I receive an error message, at the line
"Set prmAddress = rstTest.CreateParameter("Address", adUnsignedInt,
adParamInput, 4)", saying that this member or method is not available.

What's wrong? I understand that, obviously, a recordset does not have a
parameters collection. But what have I to do? Do I have to use an
ADODB.Command to call the stored procedure? And if yes, how do I get a
cursor (recordset) from it?

Please help, I'm very confused right now.

 

[Brendan Reynolds]

You can do it using 'EXEC'. Here's an example using the CustOrderHist stored
procedure from the Northwind sample database ...

Public Sub TestSprocParam()

Const strcConnection As String = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=Northwind;Data Source=(local)"

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.ConnectionString = strcConnection
cnn.Open
Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = cnn
.Source = "EXEC CustOrderHist 'ALFKI'"
.Open
Debug.Print .Fields(0)
.Close
End With
cnn.Close

End Sub

 

[Ron Weiner]

Generally I set the Recordset object using the Execute method of the
Connection object. Code might look like this:

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
strSql = "Execute YourSproc @Param1 = 12345, @Param2 = 'ABC'"
strCon = GetADOConStr()
Set cn = New ADODB.Connection
cn.ConnectionString = strCon
cn.Open
Set rs = cn.Execute(strSql)
Do While Not rs.EOF
' Whatever here
Loop
rs.close
cn.close
Set rs = Nothing
Set cn = Nothing

 

[Guest]

Thank you so much, Brendan!

You can do it using 'EXEC'. Here's an example using the CustOrderHist
stored procedure from the Northwind sample database ...

Public Sub TestSprocParam()

Const strcConnection As String = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=Northwind;Data Source=(local)"

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.ConnectionString = strcConnection
cnn.Open
Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = cnn
.Source = "EXEC CustOrderHist 'ALFKI'"
.Open
Debug.Print .Fields(0)
.Close
End With
cnn.Close

End Sub

 

[Guest]

Thank you Ron!

The ADO model seems to be rather complex, and I begin to understand (I might
be a bit slow!) that there are several ways to get the same result.