none
Access Database update- error no value given to one or more required parameter RRS feed

  • Question

  • Dear all

    I am getting above error in sql statement my codes are as below

     Dim cmd2 As New OleDbCommand("UPDATE `abc` SET `Standard` = ?, `AcademicYear` = ?, `StudentName` = ? WHERE ((`ID` = ?) AND ((?=1 AND `Standard` IS NULL) OR (`Standard` = ?))  AND ((? = 1 AND `AcademicYear` IS NULL) OR (`AcademicYear` = ?)) AND ((? = 1 AND `StudentName` IS NULL) OR (`StudentName` = ?)))", RegDataConn)
            RegDataConn.Open()
            'UPDATE `Registration` SET `AdmDate` = ? WHERE ((`AdmissionID` = ?) AND ((? = 1 AND `AdmDate` IS NULL) OR (`AdmDate` = ?)) '
    
            Try
    
                cmd2.Parameters.AddWithValue("ID", TextBox1.Text)
                cmd2.Parameters.AddWithValue("Standard", TextBox2.Text)
                cmd2.Parameters.AddWithValue("AcademicYear", TextBox3.Text)
                cmd2.Parameters.AddWithValue("StudentName", TextBox4.Text)
    
    
                cmd2.ExecuteNonQuery()
                MsgBox("Data Inseted successfully")

    sunil

    Sunday, October 25, 2015 6:17 PM

Answers

  • Based upon your parameters this was what made sense to me:

    Dim cmd2 As New OleDbCommand("UPDATE Registration SET StudentName = ?, Standard = ?, AcademicYear = ? WHERE AdmissionID = ?", RegDataConn)
            RegDataConn.Open()
    
         cmd2.Parameters.AddWithValue("StudentName", TextBox4.Text)
         cmd2.Parameters.AddWithValue("Standard", TextBox2.Text)
         cmd2.Parameters.AddWithValue("AcademicYear", CDate(TextBox3.Text))
         cmd2.Parameters.AddWithValue("ID", TextBox1.Text)

    Note that parameters must be added to the Parameters collection based upon their ordinal position in the SQL statement when using OLEDB. If AcademicYear is a Date data type in the Access table then the parameter value must be a Date. You can remove the function call to CDate if this is not the case.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, October 27, 2015 12:36 PM

All replies

  • I am getting above error in sql statement my codes are as below

     Dim cmd2 As New OleDbCommand("UPDATE `abc` SET `Standard` = ?, `AcademicYear` = ?, `StudentName` = ? WHERE ((`ID` = ?) AND ((?=1 AND `Standard` IS NULL) OR (`Standard` = ?))  AND ((? = 1 AND `AcademicYear` IS NULL) OR (`AcademicYear` = ?)) AND ((? = 1 AND `StudentName` IS NULL) OR (`StudentName` = ?)))", RegDataConn)
    

    The UPDATE statement has 10 parameter markers but only 4 parameters are added to the command object.  Since the some of the same columns are specified both the SET and WHERE clause, perhaps your intent is to supply the new values in the SET clause and the original values in the WHERE clause as a optimistic concurrency check?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, October 25, 2015 6:26 PM
  • Dear Dan Guzman

    they are only 4 ID, Standard, Academicyear and studentname parameters

    sunil

    Sunday, October 25, 2015 6:33 PM
  • they are only 4 ID, Standard, Academicyear and studentname parameters

    Each '?' parameter marker in the query is a different parameter.  You need to add separate parameters for each marker, each with a different parameter name, even when the values being passed is the same.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Monday, October 26, 2015 1:14 PM grammar
    Monday, October 26, 2015 1:13 PM
  • Based upon your parameters this was what made sense to me:

    Dim cmd2 As New OleDbCommand("UPDATE Registration SET StudentName = ?, Standard = ?, AcademicYear = ? WHERE AdmissionID = ?", RegDataConn)
            RegDataConn.Open()
    
         cmd2.Parameters.AddWithValue("StudentName", TextBox4.Text)
         cmd2.Parameters.AddWithValue("Standard", TextBox2.Text)
         cmd2.Parameters.AddWithValue("AcademicYear", CDate(TextBox3.Text))
         cmd2.Parameters.AddWithValue("ID", TextBox1.Text)

    Note that parameters must be added to the Parameters collection based upon their ordinal position in the SQL statement when using OLEDB. If AcademicYear is a Date data type in the Access table then the parameter value must be a Date. You can remove the function call to CDate if this is not the case.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, October 27, 2015 12:36 PM