Solved

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

Posted on 2004-09-02
10
328 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

12 Experts available now in Live!

Get 1:1 Help Now