grr SQL and C#

[chedabob]

So I have this code

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

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.