Solved

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

Posted on 2004-09-02
10
339 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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 23

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 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