Data mismatch Error

Question

I have a query that is run to update values changed during editing of controls on a form. When the query is run I get the error message stating, "Data type mismatch in criteria expression.". This only occurs if one of the text boxes with dates are left empty and I run the update.

Since the parameters are all set to be objects, then it appears to me that my problem might well be with setting my parameters. It is my guess that the parameters for the five textboxes that are updating date columns are not correct. But I have no idea how to correct that. Nor do I know if I am even close to the cause.

The Update query:

        MasterBase.ChangeMasterQuery("UPDATE sitChangeMaster " &  
                                     "SET ChangeID=@recno,ChangeName=@name,ChangeManagerID=@managerid, " &  
                                     "ChangeManager=@manager,ChangeType=@type,ChangeOwner=@owner, " &  
                                     "WhereUsed=@where,ChangeMade=@made,ChangeReason=@reason,ChangeResult=@result, " &  
                                     "filePath=@path,DateOpen=@open,DateSubmit=@submit,DateApprove=@approve, " &  
                                     "DateTrain=@train,DateEffective=@effective,Active=@active,Obsolete=@obsolete " &  
                                     "WHERE ChangeID=@recno")  

The parameters are are set in the Sub like this:

    Private Sub AddParams()  
        'ChangeMaster parameters  
        MasterBase.AddParam("@recno", lblChangeID.Text)  
        MasterBase.AddParam("@name", txtName.Text)  
        MasterBase.AddParam("@managerid", EmployeeID.ToString)  
        MasterBase.AddParam("@manager", cboManager.Text)  
        MasterBase.AddParam("@type", cboType.Text)  
        MasterBase.AddParam("@owner", cboOwner.Text)  
        MasterBase.AddParam("@where", cboWhere.Text)  
        MasterBase.AddParam("@made", txtMade.Text)  
        MasterBase.AddParam("@reason", txtReason.Text)  
        MasterBase.AddParam("@result", txtResult.Text)  
        MasterBase.AddParam("@path", lblLink.Text)  
        MasterBase.AddParam("@open", txtOpen.Text)  
        MasterBase.AddParam("@submit", txtSubmit.Text)  
        MasterBase.AddParam("@approve", txtApprove.Text)  
        MasterBase.AddParam("@train", txtTrain.Text)  
        MasterBase.AddParam("@effective", txtEffective.Text)  
        MasterBase.AddParam("@active", chkActive.Checked)  
        MasterBase.AddParam("@obsolete", chkObsolete.Checked)  
    End Sub  

Answer 1

I tried to work with the information provided, but was unable to resolve my problem. So here is what I finally did for resolution. I do not believe it to be the best way to fix the problem , but it was what I could come up with.

So what I to resolve the issue in this manner

    Private Sub CheckDate()
        BldCmd = ""
        If Not String.IsNullOrWhiteSpace(txtOpen.Text) Then BldCmd = "DateOpen=@Open,"
        If Not String.IsNullOrWhiteSpace(txtSubmit.Text) Then BldCmd += ("DateSubmit=@submit,")
        If Not String.IsNullOrWhiteSpace(txtApprove.Text) Then BldCmd += ("DateApprove=@approve")
        If Not String.IsNullOrWhiteSpace(txtTrain.Text) Then BldCmd += ("DateTrain=@submit")
        If Not String.IsNullOrWhiteSpace(txtEffective.Text) Then BldCmd += ("DateEffective=@effective")
    End Sub

Then the query is run as such:

    Private Sub UpdateRecord()
        CheckDate()
#Region "Establish Connection and execute query"
        MasterBase.ChangeMasterQuery("UPDATE sitChangeMaster " &
                                     "SET ChangeID=@recno,ChangeName=@name,ChangeManagerID=@managerid, " &
                                     "ChangeManager=@manager,ChangeType=@type,ChangeOwner=@owner, " &
                                     "WhereUsed=@where,ChangeMade=@made,ChangeReason=@reason,ChangeResult=@result, " &
                                     "filePath=@path," & BldCmd & ",Active=@active,Obsolete=@obsolete " &
                                     "WHERE ChangeID=@recno")
#End Region
    End Sub

Answer 2

If the effective date can be null then in the table definition (in this case SQL-Server) check allow nulls. Other databases will be the same but of course configured according to the database being used.

If that does not work use DateTime.TryParse, if null then pass DBNull.Value else pass the date in the TextBox. If neither help I need more details.

Answer 3

Hi GlennWalker-1236,
As karenpayneoregon said, you can use DateTime.TryParse method to check if the corresponding textbox is null.
If textbox text is null, you can pass DBNull.Value.
Here is my text code example and it worked fine in Access database.

private void update_Click(object sender, EventArgs e)  
{  
    string connectionString ="your connectionString";  
    using (OdbcConnection Cn = new OdbcConnection(connectionString)) //Access database  
    {  
        Cn.Open();  
        string updateSql = "UPDATE Table1 SET Date = ? WHERE ID = ?";  
        OdbcCommand cmd = Cn.CreateCommand();  
        cmd.CommandText = updateSql;  
        DateTime dateValue;  
        if (DateTime.TryParse(textBox1.Text, out dateValue))  
            cmd.Parameters.AddWithValue("@Date",textBox1.Text);  
        else  
            cmd.Parameters.AddWithValue("@Date", DBNull.Value);  
        cmd.Parameters.AddWithValue("@ID", textBox2.Text);  
        cmd.ExecuteNonQuery();  
    }  
}  

>>Byf the way, when did the forum get changed?
What do you mean by forum changes?
Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentationto enable e-mail notifications if you want to receive the related email notification for this thread.