Other Software > Developer's Corner
grr SQL and C#
(1/1)
chedabob:
So I have this code
--- ---string scope = null;
try
{
OleDbCommand tableSearch = connection.CreateCommand();
tableSearch.CommandText = "SELECT * FROM coursework WHERE DateDue BETWEEN '" + System.DateTime.Now.Date.ToString() + "' AND '" + scope+ "'";//sql command
//OleDbParameter todaysDate = new OleDbParameter();
//tableSearch.Parameters.Add(todaysDate);
//OleDbParameter dateDue = new OleDbParameter();
//tableSearch.Parameters.Add(dateDue);//add a parameter
//todaysDate.Value = System.DateTime.Now;
if (whatScope == "week")
{
//dateDue.Value = System.DateTime.Now.AddDays(7).ToLongDateString();//give the parameter a value
}
else if (whatScope == "month")
{
//dateDue.Value = System.DateTime.Now.AddMonths(1).ToLongDateString();
}
else if (whatScope == "today")
{
//dateDue.Value = System.DateTime.Now.ToLongDateString();
scope = System.DateTime.Now.Date.ToString();
}
OleDbDataReader reader = tableSearch.ExecuteReader();
assignmentList.Items.Clear();
while (reader.Read())
assignmentList.Items.Add(reader.GetString(0));
reader.Close();
}
catch (OleDbException ex)
{
MessageBox.Show(ex.ToString());
}
Im trying to get it so that it retrieves values from the DB between Today and X. The column DateDue is a Long Date column, but no matter what I try and put in the SQL query, I always get "Data Type Mismatch In Criteria Expression".
Any ideas?
thefritz_j:
maybe I'm mis-reading but:
string scope = null;
try
{
OleDbCommand tableSearch = connection.CreateCommand();
tableSearch.CommandText = "SELECT * FROM coursework WHERE DateDue BETWEEN '" + System.DateTime.Now.Date.ToString() + "' AND '" + scope+ "'";//sql command
will give you:
"SELECT * FROM coursework WHERE DateDue BETWEEN '2/27/2007' AND ''"
and that would give the error you describe. Perhaps you'l wnat to put in a parameter:
tableSearch.CommandText = "SELECT * FROM coursework WHERE DateDue BETWEEN getdate() AND @scope ";//sql command
and then give the parameter in code the same name.
Also FYI: "SELECT *" isn't a good practice, it's easy, but can cause problems in the future.
Ruffnekk:
What the fritz_j said and also look up the Now(), Date_Time() and Sub_Date() functions in SQL. They can make it more easy for you I think.
Example:
SELECT *
FROM table
WHERE date_field <= Date_Format(Now(), "%Y-%m-%d")
AND date_field >= Date_Format(Sub_Date(Now(), INTERVAL 7 DAY), "%Y-%m-%d");
CWuestefeld:
Perhaps you'l wnat to put in a parameter-thefritz_j (February 27, 2007, 12:54 PM)
--- End quote ---
Always, always, always communicate data into the DB via Parameters:
* If you don't, then you're wide open to SQL injection attacks
* Depending on the server you're talking to, it may significantly improve performance by allowing the query plan to be cached
* Depending on the locale(s) that the client and server are in, the ambiguity between date formats may cause errors or incorrect results
* Parameters know how to handle null values correctly, so you don't need to provide special "is null" syntax in the SQL
* Parameters don't require you to escape special characters in the data, like if you're selecting for a person named "O'Neil"
This is one of the few coding guidelines on my team that is non-negotiable. There is simply no reason not to do it correctly.
chedabob:
HAHA I finally did it. It was a combination of me trying to check one field type in the code, with a totally different type in the DB itself. For some reason I was using Long Date Strings, but they are never displayed to the user, so they don't actually provide any visual advantages, and they just become a major pain during the coding side.
Thanks for the help guys.
Navigation
[0] Message Index
Go to full version