last ditch attempt to try and get this working

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

Guest

Im still struggling to get this executing a stored proc working .. im
running vb.net (2003) and SQL 2005.. the story thus far is with a lot of
help and a prevailing wind i have come up with the following
( CON_BOSSCONNECTION is already connected )

Dim InputString() As String
Dim ReturnValue As Integer

InputString = Split(InputParm, ";")
' set the query commands
STR_SQLCOMMAND.CommandText = "BossData.dbo.OperatorLogon"
STR_SQLCOMMAND.CommandType = CommandType.StoredProcedure
STR_SQLCOMMAND.CommandTimeout = 30


With STR_SQLCOMMAND
' set the query commands
.CommandText = "BossData.dbo.OperatorLogon"
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 30
' set the connection
.Connection = CON_BOSSCONNECTION
' Set the 1st Parameter
.Parameters.Add("@OperatorName", SqlDbType.VarChar, 20).Value =
InputString(0).ToString
' Set the 2nd Parameter
.Parameters.Add("@OperatorPassword", SqlDbType.VarChar,
20).Value = InputString(1).ToString
' Set the 3rd Parameter
.Parameters.Add("@PasswordLife", SqlDbType.VarChar, 3).Value =
InputString(2).ToString
' Set the returned Paramater
.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction =
ParameterDirection.Output
Try
Dim SP_Result As Integer =
CInt(STR_SQLCOMMAND.ExecuteScalar())


'read the results
Select Case SP_Result
Case 0
'0 = Logon failed - unknown operator name and
password
Case 1
' 1 = logon was a sucscess
Case 2
' 2 = logon failed - User already logged on and is
not a master
Case 3
' 3 = logon failed - User password expired
Case Else
' 4 = Unable to Mark Operator as logged on
End Select

Catch Err As SqlClient.SqlException
Dim errorMessages As String
Dim Counter As Integer

For Counter = 0 To Err.Errors.Count - 1
errorMessages += "Message " &
Err.Errors(Counter).Message & ControlChars.NewLine
Next
Console.WriteLine(errorMessages)
End Try
End With

I have checked the parameter values and ther correct . SP_Result always
returns a 0 .. now i have tried in a query editior to ececute the stored
proc with the same input parms and got a 1 result which is correct

declare @rtn int
exec bossdata.dbo.OperatorLogon 'MyName', 'MyPassword', '999', @ReturnValue
= @rtn output
print @rtn ( @rtn = 1 )

is there any hope at getting this to work, or should i just pack it all in
and give up. I know for certin the info being parmed in should return a 1
from the Stored Proc as a 1 result updates a table which is being done, so im
assuming it the way im reading the returned value ?
 
Peter,

Have you tried setting the to direction for the ReturnValue parameter to
ParameterDirection.ReturnValue?
 
Peter,

Probably are you better of in the newsgroup

microsoft.public.dotnet.adonet

Your problem is in my idea mostly the SQL part.

And this newsgroup is not really related to SQL scripts while the one I
pointed you one is that more.

However there are strange things. I see nowhere in your stored procedure the
parameters.
@Operatorname, @operatorpassword and @PasswordLife

Despite of OleDB are the parameters in SQLClient real names.

In your case I would see first if this credential class would bring a
solution for me.

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.credential.aspx

I think it does not helps anything but you never know.

Cor
 
CT, i have tried that but it returns an exception error

in t-query window its set as a output and it works fine
 
According to the MSDN help, SqlCommand.ExecuteScalar returns "The first
column of the first row in the result set, or a null reference if the result
set is empty." To get the value of an output parameter, you have to check
its .Value property after it has executed the stored procedure.

Tony
 
Hello Peter,

I don't see the guts of the sp listed so I can only assume that you have
defined an output parameter called @ReturnValue. I also assume that there
is no other data being returned... no recordset.

Given these assumptions, you will want to call STR_SQLCOMMAND.ExecuteNonQuery()
(Oh, and by the way, YIKES on your naming convention). You then assign
STR_SQLCOMMAND.Parameters("@ReturnValue").Value to SP_Result. Yer interested
in the parameter value.. what in the world gave you the impression that the
result of .ExecuteScalar would be your out parameter? May want to consider
reading the documentation next time. I'm sure it mentions that .ExecuteScalar
returns the value of the first field of the first row of a recordset given
that the recordset has but a single record with a single field.

-Boo
 
Back
Top