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

Question

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.

Philip Stuyck · Accepted Answer · 2015-02-24 06:55:26Z

You are mixing 2 different ways of doing database access. One one hand you are working with an adapter which is intended with working with a dataset. You would do the changes on a table in a dataset and then call update on the adapter. But you are taking control of the commmand objects inside the adapter passing parameter values yourself, which you would only do if you use the commands right from the beginning without the adapter.

I suggest you google a bit around to find the proper use of commands

You are not far away from a soluton just instantiate the insert command, you are doing that already, but just create your own variable for it, don't use the adapter , and do your updating via the insertcommand yourself.

You need this api on the insertcommand :

https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.executenonquery(v=vs.110).aspx

just lose the adapter, you don't need it in this example.

Now the reason why you don't see any errors is because not any updates are happening either. You did not do any changes on the dataset, and a such the update on the adapter is not going to do anything.

public static int writePatient(DataSetTrial dataSetTrial, int ethnicityID, int genderID, int raceID, DateTime dob, DateTime dateOfConsent, int hhpid)
{
    int addedRow;
    //OleDbDataAdapter writePatientAdapter; don't need this

    try
    {
    //configure adapter
    //writePatientAdapter = new OleDbDataAdapter(); no, you don't need it

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

    InsertCommand.Connection = new     OleDbConnection(connectString);

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


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

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

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

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

    InsertCommand.Parameters.Add("@HomeHealthPatientID", OleDbType.Integer);

    InsertCommand.Parameters["@HomeHealthPatientID"].Value = hhpid; 

    InsertCommand.Connection.Open();

    addedRow = InsertCommand.executeNonQuery();

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

         InsertCommand.Connection.Close();

         return addedRow;
}

This is a bit what I think should get you going, didn't test it, you might run into different issues now.

Thank you!! This worked!! I'm still pretty new to development. This is the first Windows Form application I've written outside of the classroom. I think I just got wires crossed on what I learned in school. The only thing I changed from your code above was to move the var InsertCommand declaration out of the try block so that I could close it in a finally block. Thank you much for your help. It's greatly appreciated after two days of researching and banging my head against the keyboard.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related