Solved

"Data type mismatch in criteria expression" SQL UPDATE statement fails

Posted on 2006-11-15
8
1,337 Views
Last Modified: 2011-09-20
I am using an access database for a very small C# ASP.NET site.  I am trying to perform an update on a record using a parameterized UPDATE statement, but get an error when it is executed. The code is listed below:

        string ID = Request.QueryString["ID"];

        DateTime tempdate = _Date.SelectedDate;
        int hr = Convert.ToInt32(hour.Text);
        if (ampm.SelectedValue == "PM")
            hr += 12;
        int min = Convert.ToInt32(minute.Text);
        DateTime datetime = new DateTime(tempdate.Year, tempdate.Month, tempdate.Day, hr, min, 0);

        if (!Page.IsValid)
            return;

        OleDbCommand cmd = new OleDbCommand("UPDATE tbl_calendar "
            + "SET _Date = @datetime, Title = @title, Type = @typeGroup, Audience = @audience, "
            + "PersonResponsible = @pResponsible, Location = @location, Description = @description "
            + "WHERE ID = @ID", conn);
           
        cmd.Parameters.AddWithValue("@datetime", datetime.ToString());
        cmd.Parameters.AddWithValue("@title", eTitle.Text);
        cmd.Parameters.AddWithValue("@audience", Audience.Text);
        cmd.Parameters.AddWithValue("@typeGroup", _Type.SelectedItem.Value);
        cmd.Parameters.AddWithValue("@pResponsible", PResponsible.Text);
        cmd.Parameters.AddWithValue("@location", location.Text);
        cmd.Parameters.AddWithValue("@description", description.Text);
        cmd.Parameters.AddWithValue("@ID", ID);

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

Also if I remove the parameter for _Date so it looks like "...SET _Date = '" + datetime + "',..."  The code executes, but then the changes aren't reflected.

On another note, I have an INSERT statement that formats 'datetime' the exact same way, but with a parameter, and it works.  Your help is very much appreciated.

Thank you,

Phil
0
Comment
Question by:klog_69
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 6

Expert Comment

by:DonKyles
ID: 17953071
try this

from
cmd.Parameters.AddWithValue("@datetime", datetime.ToString());
to
cmd.Parameters.AddWithValue("@datetime", datetime.ToShortDateString());
0
 

Author Comment

by:klog_69
ID: 17953243
DonKyles,

Thanks for the quick response, but this does not help.
0
 

Author Comment

by:klog_69
ID: 17953310
I'm not exactly sure what I did, but I removed the Audience field because I don't actually need it.  The Audience part was giving be the same error.  My only thought is that with parameters, you have to keep your items in the same order that they are in in the table.  So now everything works, however if I move the _Date field after the title field I get the mismatch error error again. Can anyone confirm that this is indeed what is happening?

Thank you,

Phil
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 6

Accepted Solution

by:
DonKyles earned 125 total points
ID: 17958887
By doing your way, you have to make the parameter in the same order that you declare in the SQL statement not the database table

OleDbCommand cmd = new OleDbCommand("UPDATE tbl_calendar "
            + "SET _Date = @datetime, Title = @title, Type = @typeGroup, Audience = @audience, "
            + "PersonResponsible = @pResponsible, Location = @location, Description = @description "
            + "WHERE ID = @ID", conn);
           
        cmd.Parameters.AddWithValue("@datetime", datetime.ToString());
        cmd.Parameters.AddWithValue("@title", eTitle.Text);
        cmd.Parameters.AddWithValue("@audience", Audience.Text);
        cmd.Parameters.AddWithValue("@typeGroup", _Type.SelectedItem.Value);
        cmd.Parameters.AddWithValue("@pResponsible", PResponsible.Text);
        cmd.Parameters.AddWithValue("@location", location.Text);
        cmd.Parameters.AddWithValue("@description", description.Text);
        cmd.Parameters.AddWithValue("@ID", ID);

there is another way to do this by writeing the SQL frist
qry = "UPDATE tbl_calendar " +
         "SET _Date = '" + datetime.ToString() + "',  " +
         "       Title = '" + eTitle.Text + "', " +
         "       Type = '" + _Type.SelectedValue+ "', " +
         "       Audience = '" + Audience.Text.Replace("'", "''") + "', " +
         "       PersonResponsible = '" + PResponsible.Text.Replace("'", "''") + "', " +
         "       Location = '" + location.Text.Replace("'", "''") + "', " +
         "       Description = '" + description.Text.Replace("'', "''") + "  " +
         "WHERE ID = '" + ID + "'";

// you can print out the qry frist to see the complete query before you execute it
//                             lblSql.Text = qry;

OleDbCommand cmd = new OleDbCommand(qry, conn);   ..... and so on
0
 

Author Comment

by:klog_69
ID: 17962364
Thanks for the clarification.  I had essentially what you are talking about with the alternative you presented, but I ran into the problem of single apostrophes.  So in researching this problem I saw the replace method first, and then stumbled onto my current solution, which I like better.  I feel it is far easier to determine what is going on in the sql statement.  Thank you for your input though.

Phil
0
 
LVL 6

Expert Comment

by:DonKyles
ID: 17962666
Different programmers use different way to write/run the code.

This another way to do it

string[] credentials =  { datetime.ToString(), eTitle.Text, _Type.SelectedValue,  Audience.Text, PResponsible.Text,  location.Text, description.Text, ID  };

string cmdText = "UPDATE tbl_calendar " +
                          "SET _Date = '" + credentials[0]  + "',  " +
                          "       Title = '" + credentials[1] + "', " +
                          "       Type = '" + credentials[2] + "', " +
                          "       Audience = '" + credentials[3]  + "', " +
                          "       PersonResponsible = '" + credentials[4] + "', " +
                          "       Location = '" + credentials[5]  + "', " +
                          "       Description = '" + credentials[6]  + "  " +
                          "WHERE ID = '" + credentials[7] + "'";

SqlConnection connection = new SqlConnection(_connStr);
SqlCommand cmd = new SqlCommand(cmdText, conn);
try
{
       conn.Open();
       cmd.ExecuteNonQuery();
       conn.Close();
}
catch (Exception ex)
{
       lblErrorMessage.Text = ex.message;
}

Just stick into the way you like and easy for you to debug and find the problem.

My style would be..
- Create SQL statement and print out to the screen (I can see the actual value from the web from)
- Run the SQL Stagement in Query Analysis (try different input from the web form)
- SQL statement execute successfully then I go to the code part  and write the execute command

Hope this is helpful
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 17974359
The order of parameters is of no importance.
I think your problem is the syntax for datetime string values in JET SQL:

  "SET _Date = '" + datetime.ToString() + "',  " +

It should be:

  "SET _Date = #" + datetime.ToString() + "#,  " +

and the string must be in US format, m/d/yyyy, or ISO format, yyyy-mm-dd.

/gustav
0
 

Author Comment

by:klog_69
ID: 17982265
gustav,

I am aware of what you are saying with the #'s for representing dates, but I am avoiding the string concatenations and the trouble involved with preventing SQL injection with this approach.  So I am sticking with the parameter approach.

On a note, although the actual order of the database fields may be unimportant, I will stick with what DonKyles wrote regarding parameter order:  when assigning the values to the parameters, they should be in same order as when they appeared in the SQL statement.  I have swapped the order around and sure enough I received the error again.  

I will go ahead an award DonKyles the points for the explaination.

Thanks for the help.

Phil
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Delete old Sharepoint backups 2 54
T-sql question 13 31
emailing registered email addresses in phpmyadmin 5 67
Access VBA for Search Engine 7 6
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question