VB Recordset closes even though sp returns Values

Question

VB Recordset closes even though sp returns Values

cfeisel

SSC Veteran

Points: 206
More actions
December 2, 2003 at 9:44 am

#83497

I have a VB application as an interface to my SQL database. For a report, I'm running a stored procedure which uses a selectinto statement and then a select statement to gather the data.
Select X
INTO ##T2
FROM T1
Select agg(X)
FROM ##T2
RETURN
If I run this in Query Analyzer -- No problems, I get the data as expected.
If I run this from my VB app, rs.Open "Exec sp ", the recordset is closed even though I know data is there.
Any thoughts?
Thanks,
Chris

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

cwitucki Old Hand Points: 307 More actions · Answer 1

Do you specify your connection string for your ADO object? In your post, you do not specify it when you are opening your recordset (rs.open [SQL statement], [connection]) so do you do so prior?

Also, what are you using the "RETURN" for?

Edited by - cwitucki on 12/02/2003 10:37:23 AM

Edited by - cwitucki on 12/02/2003 10:37:36 AM

cfeisel SSC Veteran Points: 206 More actions · Answer 2

The command to open the recordset should have read:

rs.Open "Exec sp ", cnDataTable

where cnDataTable was defined and opened earlier in the app.

The RETURN was in the sp from an earlier attempt of grabbing straws to get it to work.

Chris

cwitucki Old Hand Points: 307 More actions · Answer 3

Since your SP runs fine in QA, I would check your connection. Are you using direct connection string or ODBC? What is your cursor type? I've had issues with ODBC drivers. What OS and SQL version?

cwitucki Old Hand Points: 307 More actions · Answer 4

Can you provide more details regarding your VB code? The Dim, Set, and command lines for the RS?

cfeisel SSC Veteran Points: 206 More actions · Answer 5

For the connection:

Set cn = New ADODB.Connection

cn.ConnectionString = "User ID = USER; Password = PASS;" & _

"Data Source = SERVER; Initial Catalog = DATABASE;"

cn.Open

For the recordset:

(At the top of the module)

Public rs As ADODB.Recordset

(Report Generation Button)

Set rs = New ADODB.Recordset

rs.Open "Exec sp ", cn

Set report.DataSource = rs

report.Show vbModal

Thanks For the help.

ThomasH SSCertifiable Points: 7437 More actions · Answer 6

At the start of your SP, add..

SET NOCOUNT ON

VB has a hard time dealing with

"(nnn row(s) affected)" output.

Once you understand the BITs, all the pieces come together

Antares686 SSC Guru Points: 125444 More actions · Answer 7

Make sure your ADO connection is defined with Client Side Cursor, this copies it into a local object in drivers address space and it should retain.

cwitucki Old Hand Points: 307 More actions · Answer 8

If you create an ODBC connection, test that it works correctly, and specify it as your "Data Source" in your connection string, do you see the same behavior?

cfeisel SSC Veteran Points: 206 More actions · Answer 9

Thanks Everyone... it works now.

It was a combination of the SET NOCOUNT ON and cn.CursorLocation = adUseClient. When I added these to my code and it worked.

I did try to use the SET NOCOUNT ON statement earlier but it didn't help with out the Client side cursor.

Chris

r_achar SSC-Addicted Points: 432 More actions · Answer 10

cfeisel,

Thanks for updating us on your solution.

More often than not, the members ignore to inform the group after they find a solution to their problem. I appreciate your post.

Just thought I should acknowledge.

Ram Achar