Still Having SQL Stored Proc probs

14 Jun 2006 10:36 AM

Peter Newman

ok this is getting far to technical for me now   .. lol

thank you all for the help yesterday, however im still stumbling on one bit
... heres my code so far

        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)
            ' Set the 2nd Parameter
            .Parameters.Add("@OperatorPassword", SqlDbType.VarChar,
20).Value = InputString(1)
            ' Set the 3rd Parameter
            .Parameters.Add("@PasswordLife", SqlDbType.VarChar, 3).Value =
InputString(2)
            Try
                ' execute the query Use ExecuteScalar as only returning a
single value
                STR_SQLREADER = .ExecuteReader()
            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
        'read the results
        While STR_SQLREADER.Read()
            'Obtain values from each row here...
            Console.WriteLine(CStr(STR_SQLREADER(0)))
            ' set the function return
            LogonUsers = CInt(STR_SQLREADER(0))
        End While
        ' clearup
        STR_SQLREADER.Close()

Im getting this error message,

Message Procedure or Function 'OperatorLogon' expects parameter
'@ReturnValue', which was not supplied.

so my next question is in this case how do i
1. add the parameter @returnValue which is a type Integer and direction
Output from the stored proc
2. how do i read it ?

thanks again for your contuined help

14 Jun 2006 11:24 AM

Cor Ligthert [MVP]

Peter,

I don't see it in your code, therefore you have to look in your SP what for
'@ReturnValue' is expected.

I assume that the SP is just not right in this case.

Cor

Show quoteHide quote

"Peter Newman" <PeterNew***@discussions.microsoft.com> schreef in bericht
news:1B7AA267-3D07-4FD2-89DE-572942E65869@microsoft.com...
> ok this is getting far to technical for me now   .. lol
>
> thank you all for the help yesterday, however im still stumbling on one
> bit
> .. heres my code so far
>
>        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)
>            ' Set the 2nd Parameter
>            .Parameters.Add("@OperatorPassword", SqlDbType.VarChar,
> 20).Value = InputString(1)
>            ' Set the 3rd Parameter
>            .Parameters.Add("@PasswordLife", SqlDbType.VarChar, 3).Value =
> InputString(2)
>            Try
>                ' execute the query Use ExecuteScalar as only returning a
> single value
>                STR_SQLREADER = .ExecuteReader()
>            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
>        'read the results
>        While STR_SQLREADER.Read()
>            'Obtain values from each row here...
>            Console.WriteLine(CStr(STR_SQLREADER(0)))
>            ' set the function return
>            LogonUsers = CInt(STR_SQLREADER(0))
>        End While
>        ' clearup
>        STR_SQLREADER.Close()
>
>
> Im getting this error message,
>
> Message Procedure or Function 'OperatorLogon' expects parameter
> '@ReturnValue', which was not supplied.
>
> so my next question is in this case how do i
> 1. add the parameter @returnValue which is a type Integer and direction
> Output from the stored proc
> 2. how do i read it ?
>
> thanks again for your contuined help
>