klog_69
asked on
"Data type mismatch in criteria expression" SQL UPDATE statement fails
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.Tex t);
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.AddWithValu e("@dateti me", datetime.ToString());
cmd.Parameters.AddWithValu e("@title" , eTitle.Text);
cmd.Parameters.AddWithValu e("@audien ce", Audience.Text);
cmd.Parameters.AddWithValu e("@typeGr oup", _Type.SelectedItem.Value);
cmd.Parameters.AddWithValu e("@pRespo nsible", PResponsible.Text);
cmd.Parameters.AddWithValu e("@locati on", location.Text);
cmd.Parameters.AddWithValu e("@descri ption", description.Text);
cmd.Parameters.AddWithValu e("@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
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.Tex
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.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
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
ASKER
DonKyles,
Thanks for the quick response, but this does not help.
Thanks for the quick response, but this does not help.
ASKER
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
Thank you,
Phil
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Phil
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
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
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
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
ASKER
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
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
from
cmd.Parameters.AddWithValu
to
cmd.Parameters.AddWithValu