Solved

Using ADO.NET/VB.NET, how to return boolean value from parameter query.

Posted on 2004-09-02
10
327 Views
Last Modified: 2010-04-23
Yup, it's me again, and this is kind of a continuation of my earlier app question.

I don't know how much background is necessary to clarify this, but the strict "nitty-gritty" is:
I have an ASP.NET web form applet that will be more of a utility tool for a group of project managers. Basically there will be one web form page, which will have 3 multiline textboxes and one button. The way this should function is that a project manager will paste a list of user IDs into one of the multiline textboxes and click the button. When the button is clicked, an ADO.NET connection is established to one of our SQL servers, and then each ID read, one at a time (one ID per line). A parameter query is then executed using the current ID as input, and the query should return a boolean value depending upon whether the ID is found in a particular table. If the ID is found in the table, the ID should be written to one textbox, and if not, the ID is then written to the other. The way I have it setup now, I am using a stored procedure that I call from VB. My relevant VB code and the stored procedure are pasted below.

When I run this below, I receive an error that "Procedure or function procIsIDExec has too many arguments specified".

As always, I appreciate any and all assistance.

Thanks,
Jeff

***************  stored procedure ***********************************
CREATE PROCEDURE [procIsIDExec] (
      @userID varchar(50)
)
 AS
return case when exists (select 1 from tblExecSupt where UserID = @userID) then 1 else 0 end
GO
*******************************************************************

********************* VB Code ***************************************
    Private Sub btnCheckIDs_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCheckIDs.Click
        Dim i As Integer
        Dim iReturn As Integer
        Dim sLine As String
        Dim s() As String = txtInput.Text.Split()

        'setup the connection to the sql server and check the ID
        Dim connString As String = "Server=usnymen11wxx01;Database=US1Migration;User ID=sa;Password=critters;Trusted_Connection=False"
        Dim conn As SqlConnection = New SqlConnection(connString)

        Dim cmd As SqlCommand = New SqlCommand("procIsIDExec", conn)

        cmd.CommandType = CommandType.StoredProcedure

        'Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        'Dim data As DataSet = New DataSet()

        Dim split() As String
        split = txtInput.Text.Split(vbCrLf)
        For i = 0 To split.Length - 1
            If Not split(i) = "" AndAlso split(i).Chars(0) = vbLf Then
                split(i) = split(i).Substring(1)
            End If

            cmd.Parameters.Add("@userID", SqlDbType.VarChar).Value = split(i)
            conn.Open()
            iReturn = cmd.ExecuteNonQuery()
            conn.Close()

            If iReturn = 0 Then
                txtNonExec.Text = (split(i))
            Else
                txtNonExec.Text = txtNonExec.Text & vbCrLf & split(i)
            End If
        Next


    End Sub

0
Comment
Question by:jpetter
10 Comments
 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 225 total points
ID: 11964815
You keep adding more an more parameters.  You only need to add the parameter once, and then set the value within the For...Next loop:

Dim paramUserID As SqlParameter = cmd.Parameters.Add("@userID", SqlDbType.VarChar)

...
paramUserID.Value = Split(i)

Bob
0
 

Author Comment

by:jpetter
ID: 11965138
Bob,

Thanks for the quick response. Implementing your process I can run the app, though I can't get the return value on the query to work. All values return as true regardless of what is really going on.

I'll play with it a little and get back. Maybe there's a better method that using a stored procedure....something along the lines of passing the value through a SqlCommand query??

Thanks,
Jeff
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 11965225
ExecuteNonQuery does not have any return values.  What value are you trying to get back from Stored Procedure?

Bob
0
 

Author Comment

by:jpetter
ID: 11965530
Bob, Oh wow. I've stared at the MSDN documentation until my eyes watered and I didn't pick that up.

I'm sending the Stored Procedure a user ID as an input parameter, and if the SP finds the ID in the ExecSupt table, the SP should return a '1' otherwise it should return a '0'.

Thanks,
Jeff
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 11965540
Look at ExecuteScalar.

Bob
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:jpetter
ID: 11965659
Ok, I tried it and received no results. However, it states that you can return a value from a database, so maybe I'll work on re-writing the SP to return the ID if found and a null string if not, and then make modify my decision statement to reflect that.

Thanks again, and I'll let you know how I make out,
Jeff
0
 
LVL 4

Assisted Solution

by:Javert93
Javert93 earned 225 total points
ID: 11966811
The return value from a stored procedure is actually a parameter unto itself. The following code is what you need:

    Private Sub btnCheckIDs_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCheckIDs.Click
        'setup the connection to the sql server and check the ID
        Dim connString As String = "Server=usnymen11wxx01;Database=US1Migration;User ID=sa;Password=critters;Trusted_Connection=False"
        Dim conn As SqlConnection = New SqlConnection(connString)

        ' Setup the SqlCommand object and set up its parameters
        Dim param As SqlParameter
        Dim cmd As SqlCommand = New SqlCommand("procIsIDExec", conn)
        cmd.CommandType = CommandType.StoredProcedure

        ' The return value parameter must be added first
        param = New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
        param.Direction = ParameterDirection.ReturnValue
        cmd.Parameters.Add(param)

        param = New SqlParameter("@userID", SqlDbType.VarChar, 50)
        param.Direction = ParameterDirection.Input
        cmd.Parameters.Add(param)

        ' Start processing the ID's
        Dim i, iReturn As Integer
        Dim split() As String = txtInput.Text.Split(vbCrLf)
        conn.Open()   ' Open the connection here for performance

        For i = 0 To split.Length - 1
            If Not split(i) = "" AndAlso split(i).Chars(0) = vbLf Then
                split(i) = split(i).Substring(1)
            End If


            ' Execure the query and get the return value
            cmd.Parameters("@userID").Value = split(i)
            cmd.ExecuteNonQuery()
            iReturn = Convert.ToInt32(cmd.Parameters("@RETURN_VALUE").Value)

            ' Process the return value
            If iReturn = 0 Then
                txtNonExec.Text = (split(i))
            Else
                txtNonExec.Text = txtNonExec.Text & vbCrLf & split(i)
            End If
        Next

        ' Close the connection to the database
        conn.Close()
    End Sub
0
 

Author Comment

by:jpetter
ID: 11967211
Javert93,

Thanks! I've been struggling with a similar approach (unsucessfully too), and will give your suggestion a try very soon.

Thanks,
Jeff
0
 
LVL 22

Accepted Solution

by:
Snarf0001 earned 50 total points
ID: 11968998
use the executescalar as mentioned above, the other problem, is that you're adding another @userID parameter through every pass of the loop

        For i = 0 To split.Length - 1
            If Not split(i) = "" AndAlso split(i).Chars(0) = vbLf Then
                split(i) = split(i).Substring(1)
            End If

            cmd.Parameters.Add("@userID", SqlDbType.VarChar).Value = split(i)
            conn.Open()
            iReturn = cmd.ExecuteNonQuery()
            conn.Close()

            If iReturn = 0 Then
                txtNonExec.Text = (split(i))
            Else
                txtNonExec.Text = txtNonExec.Text & vbCrLf & split(i)
            End If
        Next

change it to the following:


       cmd.Parameters.Add("@userID", SqlDbType.VarChar)

       For i = 0 To split.Length - 1
            If Not split(i) = "" AndAlso split(i).Chars(0) = vbLf Then
                split(i) = split(i).Substring(1)
            End If

            cmd.Parameters("@userID").Value = split(i)
            conn.Open()
            iReturn = cmd.ExecuteNonQuery()
            conn.Close()

            If iReturn = 0 Then
                txtNonExec.Text = (split(i))
            Else
                txtNonExec.Text = txtNonExec.Text & vbCrLf & split(i)
            End If
        Next

This way you'll only be updating the value, not adding the parameter again.
0
 

Author Comment

by:jpetter
ID: 11972655
Many thanks to both of you. I worked well into the night, and picked it up again first thing this morning, and with your collective help, was able to get it working, and 15 minutes before the conference call to discuss it. Perfect!

Thanks,
Jeff
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now