Trouble populating recordset from stored procedure in VB 
Author Message
 Trouble populating recordset from stored procedure in VB

Hi,

I am having trouble with assigning the results of a sproc to a recordset and
wondered if anyone could spot any obvious mistakes. I haven't included any
SQL scripts as the sproc works exactly as expected in query analyser,
returning the correct dataset. The code doesn't error, it just doesn't
assign anything to the recordset. Any help would be gratefully received.

Many thanks
TimTom xx :o) xx

VB calling code
---------------
Dim objActiveConnection As ADODB.Connection
Dim objSPCommand As ADODB.Command

Set objActiveConnection = New ADODB.Connection
Set objSPCommand = New ADODB.Command

objActiveConnection.ConnectionString = oIMM.Settings.ConnectionString
objActiveConnection.Open
objSPCommand.ActiveConnection = objActiveConnection
objSPCommand.CommandType = adCmdStoredProc
objSPCommand.CommandText = "spListsQuoteOrderDetails"

Dim rsListData As New ADODB.Recordset
Set rsListData = objSPCommand.Execute(, 11) 'Where 11 = quote number

objActiveConnection.Close
Set objActiveConnection = Nothing

Stored procedure
----------------
CREATE PROCEDURE spListsQuoteOrderDetails
(

)
AS
SELECT     dbo.tblQuoteItems.qiID, dbo.tblQuoteItems.qiItem# AS CItem,
dbo.tblQuoteItems.qiQty AS CQty,
      CASE WHEN len(convert(varchar(1000),sDescription)) = null THEN qiNotes
                ELSE tblStock.sDescription
             END  AS LProduct,
                      dbo.tblQuoteItems.qiPrice AS RPrice,
dbo.tblQuoteItems.qiQuoteID, dbo.tblQuoteItems.qiNotes,
len(convert(varchar(1000),sDescription)) as tim
FROM         dbo.tblQuoteItems LEFT OUTER JOIN
                      dbo.tblStock ON dbo.tblQuoteItems.qiProductID =
dbo.tblStock.sID

GO



Sun, 09 Nov 2003 20:12:41 GMT  
 Trouble populating recordset from stored procedure in VB
  Dim cmd As ADODB.Command
  Dim rsTemp As ADODB.Recordset

Set cmd = New ADODB.Command
  cmd.ActiveConnection = cn
  cmd.CommandText = "CompanySelect"
  cmd.CommandType = adCmdStoredProc

  Set rsTemp = New ADODB.Recordset
  rsTemp.CursorLocation = adUseClient
  rsTemp.Open cmd, , adOpenForwardOnly, adLockReadOnly

Regards

VOrtex


Quote:
> Hi,

> I am having trouble with assigning the results of a sproc to a recordset
and
> wondered if anyone could spot any obvious mistakes. I haven't included any
> SQL scripts as the sproc works exactly as expected in query analyser,
> returning the correct dataset. The code doesn't error, it just doesn't
> assign anything to the recordset. Any help would be gratefully received.

> Many thanks
> TimTom xx :o) xx

> VB calling code
> ---------------
> Dim objActiveConnection As ADODB.Connection
> Dim objSPCommand As ADODB.Command

> Set objActiveConnection = New ADODB.Connection
> Set objSPCommand = New ADODB.Command

> objActiveConnection.ConnectionString = oIMM.Settings.ConnectionString
> objActiveConnection.Open
> objSPCommand.ActiveConnection = objActiveConnection
> objSPCommand.CommandType = adCmdStoredProc
> objSPCommand.CommandText = "spListsQuoteOrderDetails"

> Dim rsListData As New ADODB.Recordset
> Set rsListData = objSPCommand.Execute(, 11) 'Where 11 = quote number

> objActiveConnection.Close
> Set objActiveConnection = Nothing

Stored procedure
> ----------------
> CREATE PROCEDURE spListsQuoteOrderDetails
> (

> )
> AS
> SELECT     dbo.tblQuoteItems.qiID, dbo.tblQuoteItems.qiItem# AS CItem,
> dbo.tblQuoteItems.qiQty AS CQty,
>       CASE WHEN len(convert(varchar(1000),sDescription)) = null THEN
qiNotes
>                 ELSE tblStock.sDescription
>              END  AS LProduct,
>                       dbo.tblQuoteItems.qiPrice AS RPrice,
> dbo.tblQuoteItems.qiQuoteID, dbo.tblQuoteItems.qiNotes,
> len(convert(varchar(1000),sDescription)) as tim
> FROM         dbo.tblQuoteItems LEFT OUTER JOIN
>                       dbo.tblStock ON dbo.tblQuoteItems.qiProductID =
> dbo.tblStock.sID

> GO



Sun, 09 Nov 2003 22:44:11 GMT  
 Trouble populating recordset from stored procedure in VB
Thanks :o)

Tim xx



Sun, 09 Nov 2003 23:07:08 GMT  
 Trouble populating recordset from stored procedure in VB
You should add the line
    SET NOCOUNT ON as the first line of the stored procedure,
        otherwise the results will not be populated to the recordset

    Alternatively, you can use rs.NextRecordset Method until you get to the
valid recordset


Quote:
> Hi,

> I am having trouble with assigning the results of a sproc to a recordset
and
> wondered if anyone could spot any obvious mistakes. I haven't included any
> SQL scripts as the sproc works exactly as expected in query analyser,
> returning the correct dataset. The code doesn't error, it just doesn't
> assign anything to the recordset. Any help would be gratefully received.

> Many thanks
> TimTom xx :o) xx

> VB calling code
> ---------------
> Dim objActiveConnection As ADODB.Connection
> Dim objSPCommand As ADODB.Command

> Set objActiveConnection = New ADODB.Connection
> Set objSPCommand = New ADODB.Command

> objActiveConnection.ConnectionString = oIMM.Settings.ConnectionString
> objActiveConnection.Open
> objSPCommand.ActiveConnection = objActiveConnection
> objSPCommand.CommandType = adCmdStoredProc
> objSPCommand.CommandText = "spListsQuoteOrderDetails"

> Dim rsListData As New ADODB.Recordset
> Set rsListData = objSPCommand.Execute(, 11) 'Where 11 = quote number

> objActiveConnection.Close
> Set objActiveConnection = Nothing

> Stored procedure
> ----------------
> CREATE PROCEDURE spListsQuoteOrderDetails
> (

> )
> AS
> SELECT     dbo.tblQuoteItems.qiID, dbo.tblQuoteItems.qiItem# AS CItem,
> dbo.tblQuoteItems.qiQty AS CQty,
>       CASE WHEN len(convert(varchar(1000),sDescription)) = null THEN
qiNotes
>                 ELSE tblStock.sDescription
>              END  AS LProduct,
>                       dbo.tblQuoteItems.qiPrice AS RPrice,
> dbo.tblQuoteItems.qiQuoteID, dbo.tblQuoteItems.qiNotes,
> len(convert(varchar(1000),sDescription)) as tim
> FROM         dbo.tblQuoteItems LEFT OUTER JOIN
>                       dbo.tblStock ON dbo.tblQuoteItems.qiProductID =
> dbo.tblStock.sID

> GO

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----==  Over 80,000 Newsgroups - 16 Different Servers! =-----


Sun, 09 Nov 2003 23:43:15 GMT  
 Trouble populating recordset from stored procedure in VB
I don't think so !

Regards

VOrtex


Quote:
> You should add the line
>     SET NOCOUNT ON as the first line of the stored procedure,
>         otherwise the results will not be populated to the recordset

>     Alternatively, you can use rs.NextRecordset Method until you get to
the
> valid recordset



> > Hi,

> > I am having trouble with assigning the results of a sproc to a recordset
> and
> > wondered if anyone could spot any obvious mistakes. I haven't included
any
> > SQL scripts as the sproc works exactly as expected in query analyser,
> > returning the correct dataset. The code doesn't error, it just doesn't
> > assign anything to the recordset. Any help would be gratefully received.

> > Many thanks
> > TimTom xx :o) xx

> > VB calling code
> > ---------------
> > Dim objActiveConnection As ADODB.Connection
> > Dim objSPCommand As ADODB.Command

> > Set objActiveConnection = New ADODB.Connection
> > Set objSPCommand = New ADODB.Command

> > objActiveConnection.ConnectionString = oIMM.Settings.ConnectionString
> > objActiveConnection.Open
> > objSPCommand.ActiveConnection = objActiveConnection
> > objSPCommand.CommandType = adCmdStoredProc
> > objSPCommand.CommandText = "spListsQuoteOrderDetails"

> > Dim rsListData As New ADODB.Recordset
> > Set rsListData = objSPCommand.Execute(, 11) 'Where 11 = quote number

> > objActiveConnection.Close
> > Set objActiveConnection = Nothing

> > Stored procedure
> > ----------------
> > CREATE PROCEDURE spListsQuoteOrderDetails
> > (

> > )
> > AS
> > SELECT     dbo.tblQuoteItems.qiID, dbo.tblQuoteItems.qiItem# AS CItem,
> > dbo.tblQuoteItems.qiQty AS CQty,
> >       CASE WHEN len(convert(varchar(1000),sDescription)) = null THEN
> qiNotes
> >                 ELSE tblStock.sDescription
> >              END  AS LProduct,
> >                       dbo.tblQuoteItems.qiPrice AS RPrice,
> > dbo.tblQuoteItems.qiQuoteID, dbo.tblQuoteItems.qiNotes,
> > len(convert(varchar(1000),sDescription)) as tim
> > FROM         dbo.tblQuoteItems LEFT OUTER JOIN
> >                       dbo.tblStock ON dbo.tblQuoteItems.qiProductID =
> > dbo.tblStock.sID

> > GO

> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----==  Over 80,000 Newsgroups - 16 Different Servers! =-----



Fri, 14 Nov 2003 14:14:24 GMT  
 Trouble populating recordset from stored procedure in VB
Vortex,

What is it with Mike's answer that you don't like? Mike provided a technically correct
description of what is a strange behavior in ADO.

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com

Quote:

> I don't think so !

> Regards

> VOrtex



> > You should add the line
> >     SET NOCOUNT ON as the first line of the stored procedure,
> >         otherwise the results will not be populated to the recordset

> >     Alternatively, you can use rs.NextRecordset Method until you get to
> the
> > valid recordset



Fri, 14 Nov 2003 14:52:12 GMT  
 Trouble populating recordset from stored procedure in VB
    But that doesn't unfortunately address the problem posted by TimTom. In
his original code, he was not passing the parameter value for the stored
procedure at all. He was instead using the EXECUTE method with wrong values.
This was pointed out by Vortex. I think his disagreement is with the fact
that "SET NOCOUNT ON" doesn't really do anything & his original code would
still not work.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )



Sat, 15 Nov 2003 00:16:30 GMT  
 Trouble populating recordset from stored procedure in VB
OK, I checked the original message and I see what you mean. Still, I'd say that Vortex
could be a bit more elaborate in his answer.
"I don't think so !" not only has a bad sound to it, it is also not very helpful!

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com


Quote:
>     But that doesn't unfortunately address the problem posted by TimTom. In
> his original code, he was not passing the parameter value for the stored
> procedure at all. He was instead using the EXECUTE method with wrong values.
> This was pointed out by Vortex. I think his disagreement is with the fact
> that "SET NOCOUNT ON" doesn't really do anything & his original code would
> still not work.

> --
> Umachandar Jayachandran
> SQL Resources at http://www.umachandar.com/resources.htm
> ( Please reply only to newsgroup. )



Sat, 15 Nov 2003 14:38:46 GMT  
 Trouble populating recordset from stored procedure in VB
Ah well !!

But I did solve the problem !!!

SET NOCOUNT ON makes the execution of the stored procedure
faster .... I believe this is from memory : )

Regards

Vortex



Quote:
> OK, I checked the original message and I see what you mean. Still, I'd say
that Vortex
> could be a bit more elaborate in his answer.
> "I don't think so !" not only has a bad sound to it, it is also not very
helpful!

> --
> Tibor Karaszi, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com



> >     But that doesn't unfortunately address the problem posted by TimTom.
In
> > his original code, he was not passing the parameter value for the stored
> > procedure at all. He was instead using the EXECUTE method with wrong
values.
> > This was pointed out by Vortex. I think his disagreement is with the
fact
> > that "SET NOCOUNT ON" doesn't really do anything & his original code
would
> > still not work.

> > --
> > Umachandar Jayachandran
> > SQL Resources at http://www.umachandar.com/resources.htm
> > ( Please reply only to newsgroup. )



Sun, 16 Nov 2003 23:13:31 GMT  
 Trouble populating recordset from stored procedure in VB
Hmm, you are making no sense to me, I'm afraid.

First, you day "I solved the problem". But you never posted any suggestion!
Reading prior posts, Mike posted suggestion to add NOCOUNT, and you responded "I don't
think so !".

Then you say that NOCOUNT makes the proc executes faster. But this isn't TimTom's
problem. His problem is that he can't get the result!
--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com

Quote:

> Ah well !!

> But I did solve the problem !!!

> SET NOCOUNT ON makes the execution of the stored procedure
> faster .... I believe this is from memory : )

> Regards

> Vortex



> > OK, I checked the original message and I see what you mean. Still, I'd say
> that Vortex
> > could be a bit more elaborate in his answer.
> > "I don't think so !" not only has a bad sound to it, it is also not very
> helpful!

> > --
> > Tibor Karaszi, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com



> > >     But that doesn't unfortunately address the problem posted by TimTom.
> In
> > > his original code, he was not passing the parameter value for the stored
> > > procedure at all. He was instead using the EXECUTE method with wrong
> values.
> > > This was pointed out by Vortex. I think his disagreement is with the
> fact
> > > that "SET NOCOUNT ON" doesn't really do anything & his original code
> would
> > > still not work.

> > > --
> > > Umachandar Jayachandran
> > > SQL Resources at http://www.umachandar.com/resources.htm
> > > ( Please reply only to newsgroup. )



Tue, 18 Nov 2003 21:03:35 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. Populating a rdo recordset in VB from an Oracle stored procedure

2. ADO populating DataGrid from Recordset(data retrieved from stored procedure using 3 parameters)

3. ado recordsets from stored procedures, stored procedures have input parameters

4. Return recordset from Oracle Stored Procedure to VB.

5. Getting a recordset from a DB2 Stored Procedure with ADO in VB 6

6. HELP!: RecordSets, return values, Stored Procedures, in VB, SQL6.5

7. Return Recordset From Stored Procedure to VB

8. Binding VB recordset to SQL Stored Procedure

9. Need VB example of call DB2 stored procedure without recordset

10. How to get a recordset in VB from Oracle Stored Procedure

11. Code/Stored Procedure to auto populate tables from main table

12. Using Stored Procedures to populate Datasets.

 

 
Powered by phpBB® Forum Software