Solved

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

Posted on 2004-09-02
10
346 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
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…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

688 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