CODEWITHSUNDEEP
vb.netsql-server-2008visual-studio-2012stored-proceduresparameters
thedevgypsy
thedevgypsy

Reputation: 79

VB.net getting SQL Server stored procedure results

I want to get the result of my stored procedure (from SQL Server 2008) into VB.net 2012.

Here's my stored procedure:

CREATE PROCEDURE [dbo].[csp_LoginAuthentication] 
    @employeeid VARCHAR (20),
    @password VARCHAR (20),
    @accountstatus INT OUT,
    @logincount INT OUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        @accountstatus = account_status,
        @logincount = logincount
    FROM 
        strato_blueapplesmis.dbo.app_login
    WHERE 
        1 = 1
        AND employee_id = @employeeid
        AND password = @password        
END

When I'm executing my stored procedure, I'm getting the results that I want:

enter image description here

EDITED: Here's my current code thanks to Steve's suggestion below:

    Dim sqlConnct As New classSQLConnection

    If sqlConnct.MSSQLConn.State = ConnectionState.Open Then sqlConnct.MSSQLConn.Close()

    With sqlConnct.MSSQLConn

        .Open()

        Dim msSQLComm As SqlCommand = New SqlCommand("csp_LoginAuthentication", sqlConnct.MSSQLConn)
        msSQLComm.CommandType = CommandType.StoredProcedure

        msSQLComm.Parameters.Add("@employeeid", SqlDbType.VarChar).Value = txtEmployeeID.Text
        msSQLComm.Parameters.Add("@password", SqlDbType.VarChar).Value = txtPassword.Text

        Dim accntStat As Integer
        Dim loginCnt As Integer

        accntStat = Convert.ToInt32(msSQLComm.Parameters("@accountstatus").Value)
        loginCnt = Convert.ToInt32(msSQLComm.Parameters("@logincount").Value)

        msSQLComm.ExecuteNonQuery()

        MsgBox(accntStat)

    End With

And I've even tried Joel Coehoorn's suggestion:

    Dim accntStat As Integer
    Dim loginCnt As Integer
    Dim sqlConnct As New classSQLConnection

    If sqlConnct.MSSQLConn.State = ConnectionState.Open Then sqlConnct.MSSQLConn.Close()

    With sqlConnct.MSSQLConn

        Dim msSQLComm As SqlCommand = New SqlCommand("csp_LoginAuthentication", sqlConnct.MSSQLConn)
        msSQLComm.CommandType = CommandType.StoredProcedure

        msSQLComm.Parameters.Add("@employeeid", SqlDbType.NVarChar, 20).Value = txtEmployeeID.Text
        msSQLComm.Parameters.Add("@password", SqlDbType.NVarChar, 20).Value = txtPassword.Text

        msSQLComm.Parameters.Add("@accountstatus", SqlDbType.Int).Direction = ParameterDirection.InputOutput
        msSQLComm.Parameters.Add("@logincount", SqlDbType.Int).Direction = ParameterDirection.InputOutput

        sqlConnct.MSSQLConn.Open()
        msSQLComm.ExecuteNonQuery()

        accntStat = CInt(msSQLComm.Parameters("@accountstatus").Value)
        loginCnt = CInt(msSQLComm.Parameters("@logincount").Value)

    End With

    MsgBox(accntStat)

Both codes are giving me an unhandled error for the msSQLComm.ExecuteNonQuery():

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Procedure or function 'csp_LoginAuthentication' expects parameter '@accountstatus', which was not supplied.

I really do appreciate everyone's help here.

Upvotes: 1

Views: 4454

Answers (3)

thedevgypsy
thedevgypsy

Reputation: 79

Thanks everyone for helping me out with the problem, I'm able to figure out the code's issue. I've checked Procedure or function “” expects parameter “”, which was not supplied and tried replacing 

    msSQLComm.Parameters.Add("@accountstatus", SqlDbType.Int).Direction = ParameterDirection.InputOutput
    msSQLComm.Parameters.Add("@logincount", SqlDbType.Int).Direction = ParameterDirection.InputOutput

TO:

    msSQLComm.Parameters.Add("@accountstatus", SqlDbType.Int).Direction = ParameterDirection.Output
    msSQLComm.Parameters.Add("@logincount", SqlDbType.Int).Direction = ParameterDirection.Output

And now it's giving me the results that I want! Thank you again for helping me, especially to Steve and Joel Coehoorn. Here's the whole code just in case someone might encounter the same problem:

    Dim accntStat As Integer
    Dim loginCnt As Integer
    Dim sqlConnct As New classSQLConnection

    If sqlConnct.MSSQLConn.State = ConnectionState.Open Then sqlConnct.MSSQLConn.Close()

    With sqlConnct.MSSQLConn

        Dim msSQLComm As SqlCommand = New SqlCommand("csp_LoginAuthentication", sqlConnct.MSSQLConn)
        msSQLComm.CommandType = CommandType.StoredProcedure

        msSQLComm.Parameters.Add("@employeeid", SqlDbType.NVarChar).Value = txtEmployeeID.Text
        msSQLComm.Parameters.Add("@password", SqlDbType.NVarChar).Value = txtPassword.Text

        msSQLComm.Parameters.Add("@accountstatus", SqlDbType.Int).Direction = ParameterDirection.Output
        msSQLComm.Parameters.Add("@logincount", SqlDbType.Int).Direction = ParameterDirection.Output

        sqlConnct.MSSQLConn.Open()
        msSQLComm.ExecuteNonQuery()

        accntStat = CInt(msSQLComm.Parameters("@accountstatus").Value)
        loginCnt = CInt(msSQLComm.Parameters("@logincount").Value)

    End With

    MsgBox(loginCnt)

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416111

Dim accntStat As Integer
Dim loginCnt As Integer
Dim sqlConnct As New classSQLConnection
Using sqlConnct.MSSQLConn, _
      cmd As New SqlCommand("csp_LoginAuthentication", sqlConnct.MSSQLConn)         

    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add("@employeeid", SqlDbType.Int).Value = txtEmployeeID.Text
    cmd.Parameters.Add("@password", SqlDbType.NVarChar, 40).Value = txtPassword.Text

    cmd.Parameters.Add("@accountstatus", SqlDbType.Int).Direction = ParameterDirection.InputOutput
    cmd.Parameters.Add("@logincount", SqlDbType.Int).Direction = ParameterDirection.InputOutput

    sqlConnct.MSSQLConn.Open()
    cmd.ExecuteNonQuery()

    accntStat = CInt(cmd.Parameters("@accountstatus").Value)
    loginCnt = CInt(cmd.Parameters("@logincount").Value)

End Using
MsgBox(accntStat)

I think that fixes several issues, including: output parameters and setting their direction, a password parameter set as an integer that's obviously a string of some kind, and failing to close the connection in the case of an exception.

I also have a serious concern about the original code: it sure looks like there's a plain-text password there. You just don't do that. This is not okay, and needs to be fixed immediately.

Upvotes: 2

Steve
Steve

Reputation: 216343

You should add also the Output parameters to the collection of parameters

 msSQLComm.Parameters.Add("@accountstatus", SqlDbType.TinyInt).Direction = ParameterDirection.InputOutput
 msSQLComm.Parameters.Add("@logincount", SqlDbType.Int).Direction = ParameterDirection.InputOutput

I suggest also to change the AddWithValue approach. It is a shortcut with numerous drawbacks as you can read in Can We stop using AddWithValue already?

Looking at your edit, I have noticed that you don't execute the command.
After preparing the Command Text, the parameters and set up the connection you should execute the command with 

 msSqlComm.ExecuteNonQuery()

after that your output parameters should be ready and you can retrieve them with

 accntStat = Convert.ToInt16(msSQLComm.Parameters("@accountstatus").Value)
 loginCnt = Convert.ToInt32(msSQLComm.Parameters("@logincount").Value)

Notice that I put the reading the Value of the parameter inside a Convert.ToXXXX call. This is not required if you compile with Option Strict Off, but nevertheless, if you have this option set to Off, then it is better to set it to ON. It will help a lot to avoid subtle bugs when the compiler emits automatic conversion from a DataType to another DataType

Finally, the SqlCommand need to know what connection use to reach the database. If you build your command without assigning the connection object then there is no way for the command to execute the stored procedure. You could change the line that initialize the command to 

Dim msSQLComm As SqlCommand = New SqlCommand("csp_LoginAuthentication", sqlConnct.MSSQLConn )

Upvotes: 2

Related Questions