Re: Not Getting results from Stored procedure

I don't usually override other people's suggestions but in this case I must. It's not a good idea to use concatenation to build a CommandText string. This avoids the very common problem of SQL injection.

So, running a stored procedure from VB6 can be done fairly easily and while there are fewer whitepapers on how to best do so, I thought my website might still have a few but there are not that many left--the problem is, Microsoft is pulling old unsupported content from MSDN so a lot of those links are gone. I suggest getting one of my old books like ADO Examples and Best Practices or ADO and ADO.NET Examples and Best Practices (both Apress titles) as these discuss VB6 and the final versions of ADO. Consider that VB6 and ADO are obsolete by almost a decade now. More and more you'll find it harder to find information about its use on the web.

Good luck...


--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com/blog/billva
http://betav.com
____________________________________________________________________________________________



"jpm" <uh@Idetestspam@mars> wrote in message news:OrVupXiVJHA.1188@xxxxxxxxxxxxxxxxxxxxxxx
This problem is mostly resolved. The command object returns a 'forward only' navigable recordset. There are a couple of solutions, as you can find elsewhere on the www. In any event, when calling a stored procedure, you need to include phrase SET NOCOUNT ON, so that the 'records affected' message that occur from results of Stored proc processing aren't returned to the calling application. These messages appear to be returned to the calling application as a recordset. The result is that the code: Set rst = adocommand.execute() doesn't return an expected recordset of data. Perhaps there's a solution for it other than including SET NOCOUNT ON in the stored proc code.

Additionally, when calling a stored proc you need ignore the command object and use a syntactically correct string as the source parameter for the recordset's 'Open' method.
e.g.: rst.Open "{ CALL sp_GET_RST('" & "spparam1" & "', '" & "spparam2" & "') }", cnDb, adOpenKeyset, adLockOptimistic, adCmdText
Passing the command object to the recordset's 'Open' method will work too, provided you provide other parameter values as shown above.

I hope this information helps those still using Vb6 for data access.

I do seem to have an additional problem. I would like to use the recordcount property of the recordset object containing data returned from stored proc.
The Recordcount property seems to return accurate count initially. But it doesn't provide an accurate count after I invoke the .Filter method. An accurate count is provided from Recordcount property after calling Filter method on a recordset that isn't result of data from a stored procedure. Need to find a solution to that as well

JPM


"jpm" <uh@Idetestspam@mars> wrote in message news:exWYQFXVJHA.3740@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

I'm having a problem getting a resultset returned from a stored procedure on SQL 2000 database, using Vb6/Ado 2.7. I've called very similar stored procs on the same db using .net '03; the data was returned via the DataAdapter.fill(mydataset) method without issue.

Below is related vbA code:
'**********
Dim cnTest as ADODB.Connection: cmdTest as ADODB.Command: rstTestResults as ADODB.Recordset

cnTest.Open g_ADOCONNTEST

Set cmdTest = New ADODB.Command
With cmdTest
.ActiveConnection = cnTest
.CommandText = "sp_GET_ReptDETAILS"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters(1).Direction = adParamInput: .Parameters(2).Direction = adParamInput
.Parameters(1).Size = 6: .Parameters(2).Size = 6
.Parameters(1).Value = "081101"
.Parameters(2).Value = "081131"
.CommandTimeout = 240
Set rstTestResults = cmdTest.Execute()
Debug.Print rstTestResults.RecordCount ' function call results in error. Recordset isn't open; it would be had a resultset been returned
End With
'**********
I can call the stored procedure from SQL Query analyzer and the expected records are returned in the results window. i.e. I know the sp works.

Any pointers?

Thanks,
Jim M



.

Relevant Pages

  • RE: Capturing SQL Stored Procedure Return Codes
    ... For a Recordset-returning Command: ... Returns a Recordset object reference or Nothing. ... My point is that I can force the SQL stored procedure to return a negative ... How do I capture return codes from a SQL Stored Procedure? ...
    (microsoft.public.access.modulesdaovba)
  • getting returned value from stored procedure
    ... I'm using Command to try to get the returned values of a stored procedure ... How do I set the returned value as a recordset? ... Please make sure the database is available and the connection ...
    (borland.public.delphi.database.ado)
  • Re: Definite difference between using OLEDB and ODBC as the driver
    ... > Recordset code, ... I can reuse the same Command ... > object several times without creating any more than 1 Command Object ... statement involves running a stored procedure) and executing it, ...
    (microsoft.public.inetserver.asp.db)
  • Re: Not Getting results from Stored procedure
    ... message that occur from results of Stored proc processing aren't returned ... calling application as a recordset. ... when calling a stored proc you need ignore the command object ... method on a recordset that isn't result of data from a stored procedure. ...
    (microsoft.public.vb.database)
  • Re: capturing NT user
    ... > Your stored procedure is actually going to return a recordset. ... >> control source of the text box to the relevant field in your log table. ... >> Then enter in the on click event of the command button. ...
    (microsoft.public.access.formscoding)