Opening an ADO Recordset from a SQL Server Stored Procedure using VB.NET

The posted code in VB.net doesn't display a MsgBox.  Typing "EXEC procUserOperations 154" from a query windows in SQL Server works.  The VB.net program doesn't output an error or "bomb out".  It just continues on with its execution.  I can even purposefully type a stored procedure name that doesn't exist for the CommandText and the program still doesn't "bomb out" or give an error message.  What's wrong with this code, and how can I debug this program?  The stored procedure returns a recordset and its declaration is :

ALTER PROCEDURE [dbo].[procUserOperations]
      @ParentOperationId As Int
As
BEGIN

       Dim cn As New ADODB.Connection
        Dim cmd As New ADODB.Command
        Dim Param As New ADODB.Parameter
        Dim rs As New ADODB.Recordset

        cn.ConnectionString = strConn2
        cn.Open()
        With cmd
            .CommandText = "procUserOperations"
            .CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
            .ActiveConnection = cn
            Param = .CreateParameter("ParentOperationId", ADODB.DataTypeEnum.adInteger,
                                     ADODB.ParameterDirectionEnum.adParamInput,, 154)
            .Parameters.Append(Param)
            rs = .Execute
        End With
        MsgBox(rs.Fields("UserId").Value & "1")

Open in new window

LVL 1
Declan_BasileITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Try this ..

MessageBox.Show( "The username or password you entered is incorrect", _
        "Login Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Stop)
Duy PhamFreelance IT ConsultantCommented:
I don't see any error with your code. Just some suggestions:
1. Make sure that your code is running within an Windows Application, otherwise MsgBox won't work
2. Check if the caller of your function catches and swallows the exception. Or wrap your code into a Try Catch then show the message as below
        Dim cn As New ADODB.Connection
        Dim cmd As New ADODB.Command
        Dim Param As New ADODB.Parameter
        Dim rs As New ADODB.Recordset

        Try
                cn.ConnectionString = strConn2
                cn.Open()
                With cmd
                    .CommandText = "procUserOperations"
                    .CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
                    .ActiveConnection = cn
                    Param = .CreateParameter("ParentOperationId", ADODB.DataTypeEnum.adInteger,
                                     ADODB.ParameterDirectionEnum.adParamInput,, 154)
                    .Parameters.Append(Param)
                    rs = .Execute
                End With
                MsgBox(rs.Fields("UserId").Value & "1")
        Catch ex As Exception
                MsgBox(ex.Message)
        End Try

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>I can even purposefully type a stored procedure name that doesn't exist for the CommandText and the program still doesn't "bomb out" or give an error message.

as a start for debugging, try make some break points in your codes and debug it with Watches. make sure your program runs through the codes where you put in the break points.
Declan_BasileITAuthor Commented:
Thanks.  I ended up converting the code to use SQLDataReader.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.