• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

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

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
jpetter
Asked:
jpetter
3 Solutions
 
Bob LearnedCommented:
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
 
jpetterAuthor Commented:
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
 
Bob LearnedCommented:
ExecuteNonQuery does not have any return values.  What value are you trying to get back from Stored Procedure?

Bob
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
jpetterAuthor Commented:
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
 
Bob LearnedCommented:
Look at ExecuteScalar.

Bob
0
 
jpetterAuthor Commented:
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
 
Javert93Commented:
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
 
jpetterAuthor Commented:
Javert93,

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

Thanks,
Jeff
0
 
Snarf0001Commented:
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
 
jpetterAuthor Commented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now