Execute Stored procdure, no results show up

[FA]

Hi All,
I need to execute the stored procedure taking a input parameter @ID
from a text box control of frmTest and on the same form theere is
command button to run the SP and view the results.

I tried the following to test the stored procedure calling but it is
not opening the results set in a query.


Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objParm1 As New ADODB.Parameter
Dim objRs As New ADODB.Recordset
Dim strCnxn As String
' Set CommandText equal to the stored procedure name.
objCmd.CommandText = "spTest"
objCmd.CommandType = adCmdStoredProc


' Connect to the data source.
strCnxn = "Provider='SQLOLEDB';Data Source='TestServer';" & _
"Initial Catalog='ABC';User ID='XYZ';Password='p123';"


objConn.open strCnxn
' Automatically fill in parameter info from stored procedure.
objCmd.Parameters.Refresh


' Set the param value.
objCmd(1) = Me.txtAbc.value


' Execute once and display...
Set objRs = objCmd.Execute


End If


The above codes are behind a command button and when i click on it,
nothing happen. It suppose to open up the results or the stored
procedure in the query but it does not do anything.

 

[Guest]

Try changing your SQLOLDB provider to SQLOLDB.1 see if that makes any change...

 

[Sylvain Lafontaine]

First, do not use the syntaxe:

Dim objConn As New ADODB.Connection

and use instead:

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

The difference in VBA is more than simply syntaxique: with the first
version, you never know when the required object will be created and it can
even be created multiple times. When using a hierarchical structure such as
ADO, this can lead to subtle bugs (and a loss of performance, too).

In your case, you are creating a Connection object and a Command object but
you don't set any explicit reference between these two objects:

Dim objCmd As ADODB.Command
Set objCmd = New ADODB.Command
Set objCmd.ActiveConnection = objConn

Notice that there will be a big difference if you forget to use the word Set
at the beginning of the last instruction: instead of associating the
connection object with the command object, the command object will create a
new (and anonymous) connection object using the connection string of the
other connection string.

Finally, using the Refresh command instead of creating explicitely all
parameters is a bad thing because it will require more round-trip to the
server each time.

Using ADO is easy but you must closely follow the instructions. On this
newsgroup and other newsgroups like .access, .access.odbcclientsvr or
m.p.data.ado, you will find numerous examples.