?
Solved

Calling SQL Server Stored Procedures - something's odd here

Posted on 2005-05-09
2
Medium Priority
?
303 Views
Last Modified: 2010-04-23
Hi,

     I have the following code which is supposed to call two stored procedures.  The first one takes an input and gives an output - this one is working just fine.  The second one calls a stored procedure that changes the status of a row in the database.  This one is for some reason ineffective.  It works from sql query analyzer, so it's not the procedure.  Here's the code:

Private Sub cmdStart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdStart.Click

        If txtContID.Text = "" Then Exit Sub

        Dim SQL_CONNECTION_STRING As String = "Server=ga1cdc02;DataBase=Production1;Integrated Security=SSPI"

        Dim conn As New SqlClient.SqlConnection(SQL_CONNECTION_STRING)
        Dim cmd As New SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader
        Dim returnVal As Integer

        cmd.Connection = conn
        cmd.CommandText = "PTS_ChkStatus"
        cmd.CommandType = CommandType.StoredProcedure
        'the first parameter is always @RETURN_VALUE
        cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4)
        cmd.Parameters(0).Direction = ParameterDirection.ReturnValue
        cmd.Parameters.Add("@ip_ContID", SqlDbType.VarChar).Value = txtContID.Text
        cmd.Parameters.Add("@op_Status", SqlDbType.Int, 4)
        cmd.Parameters(2).Direction = ParameterDirection.Output


        Try
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            returnVal = DirectCast(cmd.Parameters("@op_status").Value, Integer)

        Catch ex As SqlClient.SqlException
            lblInfo.Text = "SQL Error 101 - Contact IT"
            'MessageBox.Show(ex.Message, "SQL Error")
        Finally
            cmd.Connection.Close()
        End Try

        If returnVal = 1 Then
            lblInfo.Text = "Process is already started."
            Exit Sub
        End If

        ' Tell it to start item - call PTS_StatusStart
        cmd.Connection = conn
        cmd.CommandText = "PTS_StatusStarted"
        cmd.CommandType = CommandType.StoredProcedure

        'Send in parameters
        cmd.Parameters.Add("@ip_ContID", SqlDbType.VarChar).Value = txtContID.Text

        Try
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()

            cmd.Connection.Close()
        Catch ex As SqlClient.SqlException
            lblInfo.Text = "SQL Error 102 - Contact IT"
            'MessageBox.Show(ex.Message, "SQL Error")
        End Try

        lblInfo.Text = "Item Started"
    End Sub


Is it because I'm trying to use the same connection stuff?

Thanks,

JP
0
Comment
Question by:gleznov
2 Comments
 
LVL 14

Accepted Solution

by:
ptakja earned 1000 total points
ID: 13958868
I think that the problem is that you are passing in a bunch of parameters from the 1st SP into the 2nd. Try clearing the Parameters collection prior to adding @ip_ContID as:

     ' Tell it to start item - call PTS_StatusStart
        cmd.Connection = conn
        cmd.CommandText = "PTS_StatusStarted"
        cmd.CommandType = CommandType.StoredProcedure

        'Send in parameters
        Call cmd.Parameters.Clear()
        cmd.Parameters.Add("@ip_ContID", SqlDbType.VarChar).Value = txtContID.Text

        Try
           


Also, there is no need to close the database connection after the 1st SP execution since you are rolling right into another one. The performance of your app may suffer if you open / close connections frequently. Try opening  at the start and closing in a Finally block of your Try Catch.
0
 
LVL 9

Assisted Solution

by:p_sie
p_sie earned 1000 total points
ID: 13958876
It is because you are using the cmd again; and you are adding parameters to it, first 2 and then 1 more, so the second time you call the execute the
cmd command has 3 parameters.
Either remove the first two parameters before you add a new one, or use a new command (e.g. cmdTwo)

Good luck,

P_sie
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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Screencast - Getting to Know the Pipeline

864 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