Problem with return value.

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

Guest

Hello All!

I have a value in a textbox(txbTableIDm.Text ) that I would like to use in a
paremiter in a SP I wrote, and then have the select statement work off that
parememter, retireive a diffrent value in another(txbCredits) texbox. Heres
the code:
Public Sub GetCredit()
Dim ConCred As SqlConnection
Dim strCred As String
Dim cmdCred As SqlCommand
Dim daCred As SqlDataAdapter

ConCred = New
SqlConnection("Server=localhost;UID=xxxx;PWD=xxxxxx;database=dbSTMBL")
cmdCred = New SqlCommand("GetCredit", ConCred)
cmdCred.CommandType = CommandType.StoredProcedure

Dim prmUser_To As New SqlParameter
prmUser_To.ParameterName = "@UserID"
prmUser_To.SqlDbType = SqlDbType.VarChar
prmUser_To.Size = 50
If txbTableIDm.Text <> "" Then
prmUser_To.Value = (txbTableIDm.Text)
End If

ConCred.Open()
prmUser_To.Direction = ParameterDirection.Output
cmdCred.Parameters.Add(prmUser_To)

<ERROR>Dim reader As SqlDataReader = cmdCred.ExecuteReader
txbCredits.Text = Convert.ToString(prmUser_To)
ConCred.Close()

End Sub

Just in case, here is the SP it's calling
CREATE PROCEDURE dbo.GetCredit @UserID varchar(50), @Return_Credit
Nvarchar(50) OUT
AS
SELECT @Return_Credit = Credits FROM Bankroll WHERE UserID= @UserID
return (@Return_Credit)
GO

Any thoughts?

TIA!!

Rudy
 
Standard question #2:
What error message do you get?

You have two parameters in the stored procedure, but you are only adding one
parameter to the command object. You have to add two parameters.
 
Hi Guffa!
My Bad. I was doing this late last night, first attempt.
This is what I have changed
ConCred.Open()
Dim Returned As New SqlParameter("@Returned_Credit",
SqlDbType.NVarChar, 50)

Returned.Direction = ParameterDirection.Output
cmdCred.Parameters.Add(Returned)

<ERROR>Dim reader As SqlDataReader = cmdCred.ExecuteReader<ERROR>
txbCredits.Text = Convert.ToString(Returned.Value)

ConCred.Close()
I'm getting an error of "An unhandled exception of type
'System.Data.SqlClient.SqlException' occurred in system.data.dll" at the
above line.
I'm pretty sure my SP is correct, but I'll double check that.

Thanks for your help on this!!!

Rudy
 
Why do call cmdCred.ExecuteReader? You only need a single value returned.
By getting a returned DataReader, you need to call DataRead.Read() to get
the first row of data, it there is any.

In your case, you simply:

cmdCred.ExecuteNonQuery()
txbCredits.Text = Convert.ToString(prmUser_To.Value)

And more important, I think, is that your SP is not correct, That is why the
error on command execution line.

SP's return type is INTEGER, not other type (you used nvarchar). Have you
actually tested your SP? Since @Return_Cred is OUTPUT parameter, in SP, you
only need to assign a value to it. You do not need to use REUTRN keyword in
SP to return value of output parameter. RETURN is mainly used in SP to
indicate executing status, such as if the SP succeeded or not.

Oddly enough, when you save a SP with this kind of error (Return
NonIntegerValue), the syntext checker did not identify it, At run time, SQL
Server trys to convert whatever value type to INTEGER type. If converting
fails, you get runtime error.

Also, in your SP, @Return_Cred should be declared as OUTPUT, not OUT (it
might be a typo when you did the post, though).

Anyway, test your SP before call it from somewhere outside SQL Server.
 
Hello All!!

OK, I'm begining to understand how this works, with the help from people on
this forum. But, I'm still hving some problems. I'm going to repost my
code, and the SP, along with how I tested it. My sp was wrong before, but
now it's correct. I changed a couple of things in my code, but I'm still
getting a system error.

Public Sub GetCredit()
Dim ConCred As SqlConnection
Dim strCred As String
Dim cmdCred As SqlCommand

Dim daCred As SqlDataAdapter

ConCred = New
SqlConnection("Server=localhost;UID=xxxx;PWD=xxxxxx;database=xxxxx")
cmdCred = New SqlCommand("GetCredit", ConCred)
cmdCred.CommandType = CommandType.StoredProcedure

Dim prmUser_To As New SqlParameter
prmUser_To.ParameterName = "@UserID"
prmUser_To.SqlDbType = SqlDbType.VarChar
prmUser_To.Size = 50
If txbTableIDm.Text <> "" Then
prmUser_To.Value = (txbTableIDm.Text)
End If
Dim CredRet As New SqlParameter
CredRet.ParameterName = "@Credits"
CredRet.SqlDbType = SqlDbType.NVarChar
CredRet.Size = 50
CredRet.Direction = ParameterDirection.Output
cmdCred.Parameters.Add(CredRet)
txbCredits.Text = Convert.ToString(CredRet.Value)

ConCred.Open()

cmdCred.ExecuteNonQuery()<<ERRORS HERE>>
'txbCredits.Text = Convert.ToString(Returned.Value)
ConCred.Close()
End Sub

MY Sp
CREATE PROCEDURE dbo.GetCredit @UserID varchar(50), @Credits nvarchar(50)
OUTPUT
AS
SELECT @Credits = Credits FROM Bankroll WHERE UserID= @UserID
RETURN @@ERROR

My test in QA
ECLARE @RC int
DECLARE @UserID varchar(50)
DECLARE @Credits nvarchar(50)

SET @UserID = '78c9c996-6a5c-41e0-b4c1-2d1926bd5075'
EXECUTE @RC = footbet.dbo.GetCredit
@UserID, @Credits OUTPUT

SELECT @RC AS ReturnCode, @Credits AS Credits
This worked just fine.

And the only error I get is system error. I made sure there is a value in
the table to pass ot the textbox. The texbox name is correct. Any ideas
where else I might look that could be causing?

As always, TIA!!!


A confused Rudy
 
If you code is directly copied from your code, then you have an obvious
error: you did not add SQLParameter "pmtUser" to the SqlCommand "cmdCred",
hence the error when callthe cmdCred.ExecuteNonQuery(), because you did not
spply a required parameter.
 
Thanks Norman! I did catch that this afternoon, and it fixed my problem.
Thanks to everyone for helping me out!!

Rudy
 
Back
Top