0

I'm trying to take a Yes/No value from my database on Access and make it so if the Yes/No is checked on Access it will check it on the form. Although I keep getting

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

On the line Dim rs As OleDbDataReader = SQLCmd.ExecuteReader()

Sorry if it's a really easy and stupid mistake, I'm a college student and googling isn't helping me figure this one out.

cn.Open()

Dim SQLCmd As New OleDbCommand
SQLCmd.Connection = cn
SQLCmd.CommandText = "SELECT *, staffIn FROM Staff WHERE staffName = DarrenSloan"
Dim rs As OleDbDataReader = SQLCmd.ExecuteReader()

While rs.Read

    Dim DisplayValue As String = rs("staffIn")
    SQLCmd.Parameters.AddWithValue("@inorout", inOrOut.Checked)
    SQLCmd.ExecuteNonQuery()

End While

cn.Close()
Stefan Drissen
  • 3,266
  • 1
  • 13
  • 21
PurchaseWinRAR
  • 35
  • 3
  • What is the point of this line `SQLCmd.Parameters.AddWithValue("@inorout", inOrOut.Checked)`? – djv Mar 16 '20 at 19:54
  • It was my understanding that that it would take the value of the checkbox in Access and apply its true/false state to the checkbox on the form? – PurchaseWinRAR Mar 16 '20 at 19:57
  • Are you trying to update the database in the same loop? You should make a new command object, not use the same one you're querying the database with... – djv Mar 16 '20 at 20:06

2 Answers2

1

I know this is an old post but I seem to remember that OleDb does not support named parameters.

Also, pretty sure that DarrenSloan should be surrounded by single quotes, like any string value. And indeed, reusing the SQL command like this is not the way to do it.

The CommandText:

SQLCmd.CommandText = "SELECT *, staffIn FROM Staff WHERE staffName = DarrenSloan"

does not contain any parameter. Thus, the parameter inorout has no effect:

SQLCmd.Parameters.AddWithValue("@inorout", inOrOut.Checked)

Either use two statements, one SELECT and one UPDATE. Or use a different mechanism like a databound grid. Maybe you are using a datagridview control to display the data. Then there are different techniques to keep the data in sync. It depends on how you choose to render the data on your form.

Kate
  • 1,809
  • 1
  • 8
  • 7
0

Firstly, get rid of the loop. You would only use a loop if you were expecting more than one record. By the looks of it, you are expecting only one record, so no loop.

Secondly, stop calling ExecuteNonQuery. That is for making changes to the database, which you're obviously not trying to do. You obviously know how to get data from the query because you're doing it here:

Dim DisplayValue As String = rs("staffIn")

If you want to get data from another field, do the same thing. You can then use that data in whatever way you like, e.g.

Using connection As New OleDbConnection("connection string here"),
      command As New OleDbCommand("SELECT * FROM Staff WHERE staffName = 'DarrenSloan'", connection)
    connection.Open()

    Using reader = command.ExecuteReader()
        If reader.Read() Then
            Dim inOrOut = reader.GetBoolean(reader.GetOrdinal("inorout"))

            inOrOutCheckBox.Checked = inOrOut
        End If
    End Using
End Using

Notice that I have wrapped the text literal in the SQL in single-quotes? I would expect that you would normally not want to hard-code a name there, but use input from the user instead, In that case, you would use a parameter, e.g.

Using connection As New OleDbConnection("connection string here"),
      command As New OleDbCommand("SELECT * FROM Staff WHERE staffName = @staffName", connection)
    command.Parameters.Add("@staffName", OleDbType.VarChar, 50).Value = staffNameTextBox.Text
    connection.Open()

    Using reader = command.ExecuteReader()
        If reader.Read() Then
            Dim inOrOut = reader.GetBoolean(reader.GetOrdinal("inorout"))

            inOrOutCheckBox.Checked = inOrOut
        End If
    End Using
End Using
jmcilhinney
  • 50,448
  • 5
  • 26
  • 46