CODEWITHSUNDEEP
sqlvb.net
PurchaseWinRAR
PurchaseWinRAR

Reputation: 35

'No value given for one or more required parameters.' Error, Can't get over it

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()

Upvotes: 0

Views: 351

Answers (2)

jmcilhinney
jmcilhinney

Reputation: 54417

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

Upvotes: 0

Kate
Kate

Reputation: 1836

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.

Upvotes: 1

Related Questions