Data mismatch Error

Glenn Walker 251 Reputation points
2021-01-27T01:40:18.163+00:00

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.

60781-screenshot.jpg

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  
Windows Forms
Windows Forms
A set of .NET Framework managed libraries for developing graphical user interfaces.
1,906 questions
{count} votes

Accepted answer
  1. Glenn Walker 251 Reputation points
    2021-02-14T04:23:02.34+00:00

    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
    
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Karen Payne MVP 35,456 Reputation points
    2021-01-27T11:55:38.71+00:00

    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.

    61011-11111111111.png

    0 comments No comments

  2. Daniel Zhang-MSFT 9,626 Reputation points
    2021-01-28T02:34:53.283+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.