topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Thursday March 28, 2024, 6:16 pm
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: grr SQL and C#  (Read 5669 times)

chedabob

  • Participant
  • Joined in 2006
  • *
  • Posts: 34
  • C# Master (if only!!!)
    • View Profile
    • Donate to Member
grr SQL and C#
« on: February 27, 2007, 12:10 PM »
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

  • Charter Honorary Member
  • Joined in 2005
  • ***
  • Posts: 75
    • View Profile
    • Donate to Member
Re: grr SQL and C#
« Reply #1 on: February 27, 2007, 12:54 PM »
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

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 332
  • Uhm yeah...
    • View Profile
    • RuffNekk's Crypto Pages
    • Donate to Member
Re: grr SQL and C#
« Reply #2 on: February 27, 2007, 12:59 PM »
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");
Regards,
RuffNekk

Programming is an art form that fights back.

CWuestefeld

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 1,009
    • View Profile
    • Donate to Member
Re: grr SQL and C#
« Reply #3 on: February 27, 2007, 06:36 PM »
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

  • Participant
  • Joined in 2006
  • *
  • Posts: 34
  • C# Master (if only!!!)
    • View Profile
    • Donate to Member
Re: grr SQL and C#
« Reply #4 on: February 28, 2007, 03:21 AM »
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.