October 3, 2002 at 1:46 pm
Hello all,
Is it possible to capture the "10 row(s) affected" as an output parameter from a stored procedure? Really I just want to pass the number of rows returned by a select statement in my procedure to the calling Access 2000 standard module. I am running the procedure through an ADO cmd.
Thanks!
October 3, 2002 at 6:16 pm
Look at @@ROWCOUNT in SQL BOL. However with ADO depending on the cursor type there is a parameter of the recordset object .RecordCount that will work as well.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 10/03/2002 6:16:34 PM
October 4, 2002 at 2:47 am
If you use the EXECUTE method of the ADO CONNECTION object this takes Number Of Records affected as it's second parameter.
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
October 4, 2002 at 8:36 am
Thanks to all that responded. I have been banding my head against trying to use rstSQL.RecordCount. No matter what I do it returns -1.
So I am trying the cnnSQL.Execute, number of records....
On Error GoTo ErrorExecute
Dim cnnSQL As ADODB.Connection
Dim rstSQL As ADODB.Recordset
Dim cmdSQL As ADODB.Command
Dim prmSQL As ADODB.Parameter
Dim errLoop As ADODB.Error
Dim strError As String
Dim strcnn As String
Dim strFirstName As String
Dim strLastName As String
Dim strZip As String
Dim strCode As String
Dim strSQL As String
Dim intRows As Integer
'Set and open ADO connection, set cmdSQL.
Set cnnSQL = New ADODB.Connection
Set rstSQL = New ADODB.Recordset
Set cmdSQL = New ADODB.Command
cmdSQL.CommandType = adCmdStoredProc
cmdSQL.CommandText = "procAbcs_hit_DE"
cnnSQL.Open ("TM DirMail_test")
'Define the input parameters.
strFirstName = theFirstName
Set prmSQL = New ADODB.Parameter
prmSQL.Direction = adParamInput
prmSQL.Type = adVarChar
prmSQL.Size = 20
prmSQL.Value = strFirstName
cmdSQL.Parameters.Append prmSQL
strLastName = theLastName
Set prmSQL = New ADODB.Parameter
prmSQL.Direction = adParamInput
prmSQL.Type = adVarChar
prmSQL.Size = 20
prmSQL.Value = strLastName
cmdSQL.Parameters.Append prmSQL
strZip = theZip
Set prmSQL = New ADODB.Parameter
prmSQL.Direction = adParamInput
prmSQL.Type = adVarChar
prmSQL.Size = 20
prmSQL.Value = strZip
cmdSQL.Parameters.Append prmSQL
strCode = theCode
Set prmSQL = New ADODB.Parameter
prmSQL.Direction = adParamInput
prmSQL.Type = adVarChar
prmSQL.Size = 20
prmSQL.Value = strCode
cmdSQL.Parameters.Append prmSQL
'Open the recordset
Set rstSQL = cnnSQL.Execute(cmdSQL, intRows)
When I compile I get a "type mismatch" error on the ".Execute" in the final statement.
What am I missing here?
Thanks again for your help.
October 4, 2002 at 9:37 am
OK I found the problem and it was made way back at simply using ADO rstSQL.RecordCount.
I had originally rstSQL.Open cmdSQL.Execute
which makes no sense at all.
The code below is what should have been and returns the correct record count.
cnnSQL.Open ("TM DirMail_test")
cmdSQL.CommandType = adCmdStoredProc
cmdSQL.CommandText = "procAbcs_hit_DE"
cmdSQL.ActiveConnection = cnnSQL
rstSQL.CursorType = adOpenStatic
rstSQL.CursorLocation = adUseClient
rstSQL.LockType = adLockReadOnly
'Open the recordset
rstSQL.Open cmdSQL
Thanks again and watch out for that ADO.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply