locked
Data type mismatch in criteria expression RRS feed

  • Question

  • I'm trying to insert data but following error occur.

    Data type mismatch in criteria expression
    Code is like following.
    using (OleDbConnection conn_CDMMU = new OleDbConnection(ConnectionString_CDMMU))
    {
        conn_CDMMU.Open();
        queryString = "INSERT INTO patient ([patient_id],[title],[first_name],[last_name]" +
                      ",[h_phone],[w_phone],[birth_date],[address1],[city],[state],[zip],[sex],[ssnumber]" +
                      ",[medical_alert],[driver_license],[patient_type],[primary_provider]" +
                      ",[status],[mi_name],[alt_chart_no],[cell_phone]) " +
                      "VALUES (@patient_id_value,@title_value,@first_name_time_value,@last_name_value" +
                      ",@h_phone_value,@w_phone_value,@birth_date_value,@address1_value" +
                      ",@city_value,@state_value,@zip_value,@sex_value" +
                      ",@ssnumber_value,@medical_alert_value,@driver_license_value" +
                      ",@patient_type_value,@primary_provider_value,@status_value" +
                      ",@mi_name_value,@alt_chart_no_value,@cell_phone_value)";
        try
        {
            OleDbCommand insertcommand = new OleDbCommand(queryString, conn_CDMMU);
            insertcommand.CommandType = CommandType.Text;
            insertcommand.Parameters.AddWithValue("@patient_id_value", patient.PatientID.ToString());
            insertcommand.Parameters.AddWithValue("@title_value", patient.Title);
            insertcommand.Parameters.AddWithValue("@first_name_time_value", patient.FirstName);
            insertcommand.Parameters.AddWithValue("@last_name_value", patient.LastName);
            insertcommand.Parameters.AddWithValue("@h_phone_value", patient.HomePhone);
            insertcommand.Parameters.AddWithValue("@w_phone_value", patient.WorkPhone);
            insertcommand.Parameters.AddWithValue("@birth_date_value", patient.BirthDate);
            insertcommand.Parameters.AddWithValue("@address1_value", patient.Address1);
            insertcommand.Parameters.AddWithValue("@city_value", patient.City);
            insertcommand.Parameters.AddWithValue("@state_value", patient.State);
            insertcommand.Parameters.AddWithValue("@zip_value", patient.Zip);
            insertcommand.Parameters.AddWithValue("@sex_value", patient.Sex);
            insertcommand.Parameters.AddWithValue("@ssnumber_value", patient.SocSec);
            insertcommand.Parameters.AddWithValue("@medical_alert_value", patient.MedicalAlert);
            insertcommand.Parameters.AddWithValue("@driver_license_value", patient.DriverLicense);
            insertcommand.Parameters.AddWithValue("@patient_type_value", patient.PatientType);
            insertcommand.Parameters.AddWithValue("@primary_provider_value", patient.PrimaryProvider.ToString());
            insertcommand.Parameters.AddWithValue("@status_value", patient.Status);
            insertcommand.Parameters.AddWithValue("@mi_name_value", patient.MIName);
            insertcommand.Parameters.AddWithValue("@alt_chart_no_value", patient.AltChartNo);
            insertcommand.Parameters.AddWithValue("@cell_phone_value", patient.CellPhone);
            insertcommand.ExecuteNonQuery();
            insertcommand.Dispose();
            
        }
        catch (Exception e)
        {
            WriteLog("InsertPatientInfo() failed. " + e.Message);
        }
        conn_CDMMU.Close();
    }
    Can anybody give me some advice?

    Saturday, May 2, 2020 7:59 PM

All replies

  • Hello,

    The error means that one of the values set in the Parameters.AddWithValue does not match the type in the table.

    For instance, and this is picking one at random. If patient.BirthDate was not a valid date you would get the error

    insertcommand.Parameters.AddWithValue("@birth_date_value", patient.BirthDate);

    Another would be if there is a numeric type, I've seen a 32 bit value needing to be passed as a 16 bit value to Microsoft Access.

    So you are for lack of better words in detective mode trying to determine which value is the issue. If this was SQL-Server I would point you to a NuGet package (see project site) I have to assist with this by creating the insert statement with the values that would then be pasted into Access to work through the problem but can not guarantee it with access.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, May 2, 2020 8:57 PM
  • All things are string type including Patient.BirthDate.

    Patient.PatientID and Patient.PrimaryProvider is numeric, so I made it as string.

    If I run program with debugger, the error occur from InsertCommand.ExecuteNonQuery();

    So I can't figure out which value is wrong.

    My DB is MS Access.


    • Edited by Jeff0803 Sunday, May 3, 2020 1:01 AM
    Sunday, May 3, 2020 12:47 AM
  • You figure it out by slimming down your insert statement to one field, run it, if it worked add another field, run it and repeat until it fails then you have the failure point.

    Using this example we add in title_value next and continue until a failure.

    var queryString = "INSERT INTO patient ([patient_id] VALUES (@patient_id_value)";
    
    try
    {
        OleDbCommand insertcommand = new OleDbCommand(queryString, conn_CDMMU);
        insertcommand.Parameters.AddWithValue("@patient_id_value", patient.PatientID.ToString());
        insertcommand.ExecuteNonQuery();
    }
    catch (Exception e)
    {
        WriteLog("InsertPatientInfo() failed. " + e.Message);
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Proposed as answer by BonnieBMVP Monday, May 4, 2020 4:58 AM
    Sunday, May 3, 2020 9:39 AM