Updating a record in ms access db where there is a checkbox

simon taylor

Hi,
i have created a class called QalificationOnEntry and another class called dataConnection which is used to connect to my ms access db. In my QalificationOnEntry class, one of my data members is of type int called 'check' which is used to insert a value of 1 or 0 into my ms access table called QualificationOnEntry where one of my fields is a checkbox. When i test my QalificationOnEntry class by making an object of this class and setting values for the data members and inserting the data into my ms access table, QualificationOnEntry , i am able to see the record. The problem i am having is trying to update the same record based on an 'entryId' as i get an error saying "Input string was not in a correct format." on this line,
set_checked(Convert.ToInt32(data[current_index, 7])); // error pointing here
Can somebody tell me why i get this error? The above line of code is the checkbox, but if i can insert a value why can't i update 1 of the columns for this same record?

QalificationOnEntry qu = new QalificationOnEntry();
      /*
      * insert statment works fine
      * qu.set_studentId(2);
      qu.set_levelOfQualification("MY LEVEL");
      qu.set_subject("A+ SUBJECT");
      qu.set_examBoard("SOME EXAM BOARD");
      qu.set_grade("FAILE!!!");
      qu.set_dateTaken(d1);
      qu.set_checked(1);
      qu.set_checkedBy("checked by me");
      qu.set_checkedDate(d2);

      qu.insertRecord();*/

      // update same record does not work fine
      qu.loadByEntryId(30);
      MessageBox.Show("subject is " + qu.get_subject());

      qu.set_subject("N+ SUBJECT");
      qu.set_checked(1);
      qu.updateRecord(30);

public void updateRecord(int id)
    {
      dataConnection ds = new dataConnection();
      ds.executeCommand("UPDATE QalificationOnEntry SET entryId='" + entryId + "',studentId=" + "'" + studentId + "'" + ",levelOfQualification=" + "'" + levelOfQualification + "'" + ",subject=" + "'" + subject + "'" + ",examBoard=" + "'" + examBoard + "'" + ",grade=" + "'" + grade + "'" + ",dateTaken=" + "'" + dateTaken + "'" + ",checked=" + "'" + @check + "'" + ",checkedBy=" + "'" + checkedBy + "'" + ",checkedDate=" + "'" + checkedDate + "'" + " WHERE entryId = " + id);
    }

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

theLizard15y ago
The question is how does access store checked states is it 1, 0 or True, False?

checked="'" + @check + "'" try getting rid of the single quote's see what happens
Sam Hobbs15y ago
Note that your code is doing three or more things in one line; it is first getting the data, then calling Convert then calling set_checked. I suggest making that one line into three or more lines, and then you will get a better idea of the problem. Do you know how to use the debugger to single-step? That could help, but if you do less in each line instead of doing so much in one line, then when you get an error, you will see better what the data is that is a problem.
Kevin Aung15y ago
Hard to tell because I can't see where you declared your variables and not sure if the error code you provided was just that line or if there's more. But from looking at the UPDATE command, the only difference with checked field from the other fields is the variable for "checked" is "@check".

ds.executeCommand("UPDATE QalificationOnEntry SET entryId='" + entryId + "',studentId=" + "'" + studentId + "'" + ",levelOfQualification=" + "'" + levelOfQualification + "'" + ",subject=" + "'" + subject + "'" + ",examBoard=" + "'" + examBoard + "'" + ",grade=" + "'" + grade + "'" + ",dateTaken=" + "'" + dateTaken + "'" + ",checked=" + "'" + @check + "'" + ",checkedBy=" + "'" + checkedBy + "'" + ",checkedDate=" + "'" + checkedDate + "'" + " WHERE entryId = " + id);