lundi 23 février 2015

INSERT into Access DB using Windows Forms not working but not throwing an error

I'm trying to insert user entered data into a Windows Form into an Access DB. In the form I use a text box, combo boxes that are populated by lookup tables in the db, and 2 dateTimePickers. Through my own debugging attempts, I think I've narrowed it down to the dateTimePickers but I'm not 100% sure. I'm not getting any error, no exceptions are being caught and the "Success Message" is shown after I execute the query but nothing is added to the database.


This is the method call from the form:



try
{
//get values from form
int updatedRow;
int ethnicityID = Convert.ToInt32(comboBoxEthnicity.SelectedValue);
int genderID = Convert.ToInt32(comboBoxGender.SelectedValue);
int raceID = Convert.ToInt32(comboBoxRace.SelectedValue);
DateTime dob = dateTimePickerDoB.Value;
DateTime dateOfConsent = dateTimePickerDateOfConsent.Value;
int hhpid = Convert.ToInt32(textBoxHHPID.Text);

//add new patient to db
updatedRow = TrialDB.writePatient(dataSetTrial, ethnicityID, genderID, raceID, dob, dateOfConsent, hhpid);
//success message, number of new patients added
MessageBox.Show(updatedRow.ToString() + " patient record has been added to the database.");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}


And here is the method from the middle tier:



public static int writePatient(DataSetTrial dataSetTrial, int ethnicityID, int genderID, int raceID, DateTime dob, DateTime dateOfConsent, int hhpid)
{
int addedRow;
OleDbDataAdapter writePatientAdapter;

try
{
//configure adapter
writePatientAdapter = new OleDbDataAdapter();

//insert command
writePatientAdapter.InsertCommand = new OleDbCommand();
writePatientAdapter.InsertCommand.CommandText = "INSERT INTO Patient (EthnicityID,GenderID,RaceID,DateOfBirth, " +
" DateOfConsent,HomeHealthPatientID) " +
" VALUES (?,?,?,?,?,?)";

writePatientAdapter.InsertCommand.Connection = new OleDbConnection(connectString);

//insert command paramaters
writePatientAdapter.InsertCommand.Parameters.Add("@EthnicityID", OleDbType.Integer);
writePatientAdapter.InsertCommand.Parameters["@EthnicityID"].Value = ethnicityID;


writePatientAdapter.InsertCommand.Parameters.Add("@GenderID", OleDbType.Integer);
writePatientAdapter.InsertCommand.Parameters["@GenderID"].Value = genderID;

writePatientAdapter.InsertCommand.Parameters.Add("@RaceID", OleDbType.Integer);
writePatientAdapter.InsertCommand.Parameters["@RaceID"].Value = raceID;

writePatientAdapter.InsertCommand.Parameters.Add("@DateOfBirth", OleDbType.Date);
writePatientAdapter.InsertCommand.Parameters["@DateOfBirth"].Value = dob;

writePatientAdapter.InsertCommand.Parameters.Add("@DateOfConsent", OleDbType.Date);
writePatientAdapter.InsertCommand.Parameters["@DateOfConsent"].Value = dateOfConsent;

writePatientAdapter.InsertCommand.Parameters.Add("@HomeHealthPatientID", OleDbType.Integer);
writePatientAdapter.InsertCommand.Parameters["@HomeHealthPatientID"].Value = hhpid;

writePatientAdapter.InsertCommand.Connection.Open();

addedRow = writePatientAdapter.Update(dataSetTrial, "Patient");

}
catch (Exception)
{
throw new ApplicationException(dbOrDeErrorMsg);
}

writePatientAdapter.InsertCommand.Connection.Close();

return addedRow;
}


When I add the DateTime variables to my watch list in debug mode it has trouble evaluating the leading { in the format from the dateTimePicker. That's why I'm thinking it's the DateTime causing me trouble. I tried the INSERT statement in the DB directly and it worked, but I can't account for the date formatting when I'm typing it in by hand. The web shows a lot of trouble with doing this but none of the fixes I've found have helped. Any help would be appreciated.


Aucun commentaire:

Enregistrer un commentaire