Post reply

VB Recordset closes even though sp returns Values

  • 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

  • December 2, 2003 at 10:37 am

    #484321

    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

  • December 2, 2003 at 10:45 am

    #484323

    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

  • December 2, 2003 at 11:09 am

    #484325

    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?

  • December 2, 2003 at 11:16 am

    #484326

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

  • December 2, 2003 at 11:45 am

    #484335

    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.

  • December 2, 2003 at 11:53 am

    #484336

    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

  • December 2, 2003 at 12:09 pm

    #484346

    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.

  • December 2, 2003 at 12:46 pm

    #484350

    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?

  • December 2, 2003 at 1:24 pm

    #484356

    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

  • December 7, 2003 at 8:22 am

    #485020

    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

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

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