A SQL problem (in my C# program!)

cjmarek · September 20th, 2004, 05:32 PM

I am having a tough time finding a way to pass Checkbox values back to an ACCESS database table(expects yes/no). I get an invalid data type error. Can somebody out there please inform me how to do this procedure?
Here is the code I am using:

                OdbcConnection myConn = new OdbcConnection(@"Provider=MSDASQL;Driver={Microsof t Access Driver (*.mdb)};DBQ=C:\Addresses.mdb");
                string myInsertQuery = "INSERT INTO addresses(Last_Name, First_name, Email_address, Comments, trips, bracket, photos) " +
                    "VALUES ( '" + txtLastName.Text + "', '" + txtFirstName.Text + "', '" + txtEmailAddress.Text + "', '" + txtComments.Text + "', '" + chkTravel.CheckState + "', '" + chkFlashBracket.CheckState + "', '" + chkPhotographs.CheckState + "')";
                OdbcCommand myOdbcCommand = new OdbcCommand(myInsertQuery);
                myOdbcCommand.Connection = myConn;
                myConn.Open();
                myOdbcCommand.ExecuteNonQuery();
                myOdbcCommand.Connection.Close();

There are three checkboxes involved:
chkTravel, chkFlashBracket, chkPhotographs. If I replace these with the values 0 or 1, everthing works great.

Cmarek

bmains · September 20th, 2004, 08:10 PM

Hey,

Try the Checked property, instead of CheckState. That is a zero to one value.

Brian

cjmarek · September 20th, 2004, 10:02 PM

Hey Brian, that was actually the first way i tried it, sure seemed the way to go. But it gave me the following error: ERROR{22018][Microsoft][ODBC Microsoft Access Driver]Data type mismatch in criteria expression.

I have a way I am doing things for now, just to get it to work. Seems like a bit more effort than I should be required to do...I am calling a function - FindBoolValue(bool chkBox), that returns an int 0 or 1. I do this within the SQL building statement:

string myInsertQuery = "INSERT INTO addresses(Last_Name, First_name, Email_address, Comments, trips, bracket, photos) " +
                    "VALUES ( '" + txtLastName.Text + "', '" + txtFirstName.Text + "', '" + txtEmailAddress.Text + "', '" + txtComments.Text + "', '" + FindBoolValue(chkTravel.Checked) + "', '" + FindBoolValue(chkFlashBracket.Checked) + "', '" + FindBoolValue(chkPhotographs.Checked) + "')";

private int FindBoolValue(bool chkBox)
{
    int YesNo = (chkBox == true) ? 1 : 0;
    return YesNo;
}

Cmarek