Solved

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

Posted on 2016-09-15
4
62 Views
Last Modified: 2016-09-16
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

0
Comment
Question by:Declan_Basile
4 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41800850
Try this ..

MessageBox.Show( "The username or password you entered is incorrect", _
        "Login Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Stop)
0
 
LVL 10

Accepted Solution

by:
Duy Pham earned 250 total points
ID: 41800868
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

0
 
LVL 51

Assisted Solution

by:Ryan Chong
Ryan Chong earned 250 total points
ID: 41800900
>>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.
0
 
LVL 1

Author Closing Comment

by:Declan_Basile
ID: 41801962
Thanks.  I ended up converting the code to use SQLDataReader.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question