Problem with running a SP that has input & a output parameter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi folks

I have a SQL Server SP that is eventually called from a command button on a
form.

The SP has one input parameter and one output parameter.

The problem is that when I call the SP from the command button click event
and assign it to a variable, it returns the value "Nothing".

The SP code is as follows

======================================
CREATE PROCEDURE [DBO].[sp_GetStoredPwd]
@UserName varchar(15),
@Result char(10) OUTPUT
AS

DECLARE @StoredPwd char(10)
PRINT 'Msg from DB - @UserName = ' + @UserName

SET @Result = (SELECT chPWD FROM Clients WHERE vchClientUserName = @UserName)
PRINT 'Msg from DB - @UserName = ' + @UserName + ' - @Result = ' + @Result

RETURN
GO
============================================

The code for the command button click event is as follows:

===========================================
Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnCommit.Click

Dim strInfo As String
Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim chrPwd As Char

If Len(txtUserName.Text) = 0 Or Len(txtPassword.Text) = 0 Then
MessageBox.Show("Please enter both a UserName AND Password",
"Missing Required Info", MessageBoxButtons.OKCancel, MessageBoxIcon.Error)
Exit Sub
End If

Try
strInfo =
"server=USER-LMZWW8DZJO\DSK_TOP_ENG;Trusted_Connection=true;database=Int_Expl_Proj"
cn = New SqlConnection
cn.ConnectionString = strInfo
cn.Open()

cmd = New SqlCommand
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_GetStoredPwd"
.Connection = cn
End With

Dim pmTemp As SqlParameter

pmTemp = cmd.Parameters.Add(New SqlParameter("@UserName",
SqlDbType.VarChar, 15))
pmTemp.Direction = ParameterDirection.Input
pmTemp.Value = txtUserName.Text

pmTemp = cmd.Parameters.Add(New SqlParameter("@Result",
SqlDbType.Char, 10))
pmTemp.Direction = ParameterDirection.Output

chrPwd = cmd.ExecuteScalar

If chrPwd = txtPassword.Text Then
Process.Start("IEXPLORE.EXE")
'Me.Visible = False

Else

MessageBox.Show("Incorrect UserName or Password")

End If

Catch InvalidOpEx As InvalidOperationException
If cn.State = ConnectionState.Open Then
cn.Close()
End If

Catch SQLEx As SqlException
Dim errorMessages As String
Dim i As Integer

For i = 0 To SQLEx.Errors.Count - 1
errorMessages += "Index #" & i.ToString() &
ControlChars.NewLine _
& "Message: " & SQLEx.Errors(i).Message &
ControlChars.NewLine _
& "LineNumber: " & SQLEx.Errors(i).LineNumber
& ControlChars.NewLine _
& "Source: " & SQLEx.Errors(i).Source &
ControlChars.NewLine _
& "Procedure: " & SQLEx.Errors(i).Procedure &
ControlChars.NewLine
Next i

Dim log As System.Diagnostics.EventLog = New
System.Diagnostics.EventLog
log.Source = "Int Expl"
log.WriteEntry(errorMessages)
Debug.Write(errorMessages)
Console.WriteLine("An exception occurred. Please contact your
system administrator.")

Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try

End Sub
===========================================

I hope somebody can help

Kind regards

Ross Petersen
 
Ross said:
Hi folks

I have a SQL Server SP that is eventually called from a command button on
a
form.

The SP has one input parameter and one output parameter.

The problem is that when I call the SP from the command button click event
and assign it to a variable, it returns the value "Nothing".

The SP code is as follows

======================================
CREATE PROCEDURE [DBO].[sp_GetStoredPwd]
@UserName varchar(15),
@Result char(10) OUTPUT
AS

DECLARE @StoredPwd char(10)
PRINT 'Msg from DB - @UserName = ' + @UserName

SET @Result = (SELECT chPWD FROM Clients WHERE vchClientUserName =
@UserName)
PRINT 'Msg from DB - @UserName = ' + @UserName + ' - @Result = ' + @Result

RETURN
GO
============================================

The code for the command button click event is as follows:

===========================================
Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnCommit.Click

Dim strInfo As String
Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim chrPwd As Char

If Len(txtUserName.Text) = 0 Or Len(txtPassword.Text) = 0 Then
MessageBox.Show("Please enter both a UserName AND Password",
"Missing Required Info", MessageBoxButtons.OKCancel, MessageBoxIcon.Error)
Exit Sub
End If

Try
strInfo =
"server=USER-LMZWW8DZJO\DSK_TOP_ENG;Trusted_Connection=true;database=Int_Expl_Proj"
cn = New SqlConnection
cn.ConnectionString = strInfo
cn.Open()

cmd = New SqlCommand
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_GetStoredPwd"
.Connection = cn
End With

Dim pmTemp As SqlParameter

pmTemp = cmd.Parameters.Add(New SqlParameter("@UserName",
SqlDbType.VarChar, 15))
pmTemp.Direction = ParameterDirection.Input
pmTemp.Value = txtUserName.Text

pmTemp = cmd.Parameters.Add(New SqlParameter("@Result",
SqlDbType.Char, 10))
pmTemp.Direction = ParameterDirection.Output
Click to expand...

ExecuteScalar is for queries which return result sets, not output parameter.

Should be:
cmd.ExecuteNonQuery()
chrPwd = pmTemp.Value()

David
 
Back
Top