CODEWITHSUNDEEP
vb.netms-access
bob smith
bob smith

Reputation: 1

Visual basic System.Data.OleDb.OleDbException: 'No value given for one or more required parameters.'

I'm trying to make a login form and keep getting this error:

Visual basic System.Data.OleDb.OleDbException: 'No value given for one or more required parameters.'

I'm using an access database with vb and everything is spelled correctly but I keep getting this error?

Private Sub Btn_Login_Click(sender As Object, e As EventArgs) Handles Btn_Login.Click
    If DbConnect() Then
        Dim SQLCmd As New OleDbCommand
        With SQLCmd
            .Connection = cn
            .CommandText = "Select * From Tbl_Staff Where (Username = @sUsername) and (Password = @sPassword)"
            .Parameters.AddWithValue("@sUsername", Txt_Username.Text)
            .Parameters.AddWithValue("@sPassword", Txt_Password.Text)

            Dim rs As OleDbDataReader = .ExecuteReader()

            If rs.Read Then
                PublicFirstname = rs("Firstname")
                PublicStaffID = rs("StaffID")
                'PublicAccessLevel = rs("AccessLevel")
                frmMainMenu.Show()
            Else
                MsgBox("Sorry, incorrect log in details entered")
            End If
            rs.Close()

EDIT:
Changed my code but still getting the same error:

If DbConnect() Then
    Using SQLCmd As New OleDbCommand
        With SQLCmd
            .Connection = cn
            .CommandText = "Select * From Tbl_Staff Where (Username = ?) and ([Password] = ?)"
            .Parameters.Add("@sUsername", OleDbType.VarChar).Value = Txt_Username.Text
            .Parameters.Add("@sPassword", OleDbType.VarChar).Value = Txt_Password.Text

            Using rs As OleDbDataReader = .ExecuteReader()
                If rs.Read Then
                Else
                    MsgBox("Sorry, incorrect log in details entered")
                End If
                rs.Close()
            End Using
        End With
    End Using
End If

Upvotes: 0

Views: 328

Answers (1)

MatSnow
MatSnow

Reputation: 7537

I see two problems here.

  1. OleDbCommand does not support named parameters with CommandType set to Text.
    The order they are supplied is substantial.
  2. PASSWORD is a reserved word in ms-access.
    Put it in square brackets.

Other things to enhance.

  • Use Using for the OleDbCommand and the OleDbDataReader.
  • Don't store passwords in clear-text.

So your code should look like following:

Using SQLCmd As New OleDbCommand
    With SQLCmd
        .Connection = cn
        .CommandText = "Select * From Tbl_Staff Where (Username = ?) and ([Password] = ?)"
        .Parameters.Add("@sUsername", OleDbType.VarChar).Value = Txt_Username.Text
        .Parameters.Add("@sPassword", OleDbType.VarChar).Value = Txt_Password.Text

        Using rs As OleDbDataReader = .ExecuteReader()
            If rs.Read Then
            Else
                MsgBox("Sorry, incorrect log in details entered")
            End If
            rs.Close()
        End Using
    End With
End Using

EDIT:
As the column names are not Username and Password but sUsername and sPassword, you have to change the CommandText as follows:

.CommandText = "Select * From Tbl_Staff Where (sUsername = ?) and (sPassword = ?)"

Upvotes: 1

Related Questions