Query, DateTime
-
I'm posting this with the risk of Heath getting angry at me because for this one instance I'm not using the parameters in the dataAdapter...not because I like the old way of constructing a long select command, but because I don't know how to use the parameters in this circumstance. I am querying a database, no big deal. But, there are like 7 or 8 different "filter" parameters the user may choose from...and they can also choose to not use any of them. I didn't know how to tell the adapter to ignore that particular parameter, so I resorted to constructing my own select command. However (and Heath will probably say I told you so ;) ) two of these parameters are dates...I am trying to search BETWEEN Date1 AND Date2. This is giving me some really strange results though. First of all, I have to set Date1 all the way to the day before if I hope to get the correct results. Also, sometimes a perfectly good date range will return nothing, whereas a ridiculous range that hasn't even happened yet, will return rows with dates that don't fall anywhere in between my specified range. I'm sort of confused by it, so if anybody has faced this issue before, I'd like to know how it was solved....or, if I can somehow "turn-off" parameters at run time, or if I can see a small example, one or two lines is all, of how to create a parameter to the adapter at run time (peferrably a BETWEEN AND parameter), I'd be much obliged. And Heath, I know you get really frustrated when people don't use the adapter parameters, cause that's what they are there for, sorry about that.
-
I'm posting this with the risk of Heath getting angry at me because for this one instance I'm not using the parameters in the dataAdapter...not because I like the old way of constructing a long select command, but because I don't know how to use the parameters in this circumstance. I am querying a database, no big deal. But, there are like 7 or 8 different "filter" parameters the user may choose from...and they can also choose to not use any of them. I didn't know how to tell the adapter to ignore that particular parameter, so I resorted to constructing my own select command. However (and Heath will probably say I told you so ;) ) two of these parameters are dates...I am trying to search BETWEEN Date1 AND Date2. This is giving me some really strange results though. First of all, I have to set Date1 all the way to the day before if I hope to get the correct results. Also, sometimes a perfectly good date range will return nothing, whereas a ridiculous range that hasn't even happened yet, will return rows with dates that don't fall anywhere in between my specified range. I'm sort of confused by it, so if anybody has faced this issue before, I'd like to know how it was solved....or, if I can somehow "turn-off" parameters at run time, or if I can see a small example, one or two lines is all, of how to create a parameter to the adapter at run time (peferrably a BETWEEN AND parameter), I'd be much obliged. And Heath, I know you get really frustrated when people don't use the adapter parameters, cause that's what they are there for, sorry about that.
-
I'm posting this with the risk of Heath getting angry at me because for this one instance I'm not using the parameters in the dataAdapter...not because I like the old way of constructing a long select command, but because I don't know how to use the parameters in this circumstance. I am querying a database, no big deal. But, there are like 7 or 8 different "filter" parameters the user may choose from...and they can also choose to not use any of them. I didn't know how to tell the adapter to ignore that particular parameter, so I resorted to constructing my own select command. However (and Heath will probably say I told you so ;) ) two of these parameters are dates...I am trying to search BETWEEN Date1 AND Date2. This is giving me some really strange results though. First of all, I have to set Date1 all the way to the day before if I hope to get the correct results. Also, sometimes a perfectly good date range will return nothing, whereas a ridiculous range that hasn't even happened yet, will return rows with dates that don't fall anywhere in between my specified range. I'm sort of confused by it, so if anybody has faced this issue before, I'd like to know how it was solved....or, if I can somehow "turn-off" parameters at run time, or if I can see a small example, one or two lines is all, of how to create a parameter to the adapter at run time (peferrably a BETWEEN AND parameter), I'd be much obliged. And Heath, I know you get really frustrated when people don't use the adapter parameters, cause that's what they are there for, sorry about that.
I won't badger you this time, but I still suggest you try it. Once you do, I suspect you'll never turn back. :) Dates in SQL Server are single-quoted like strings. When referring to Date fields, SQL Server knows what to do. For Access (and some other's I've seen, but don't remember) you encase them in pounds (or hashes - #). If you don't, they will be treated like strings (IIRC) so you won't get the result you'd expect.
Microsoft MVP, Visual C# My Articles
-
Putting pound signs around the Date like Heath suggested made my query a little better...I no longer have to subtract a day from Time1 to get the correct results. But here is the strange result I'm getting: A row with a date column of 5/4/2004 is being selected when the date value of Time2 is 5/11/2004 and Time1 is anything from 5/4/2004 to 5/10/2004, but it's not selecting my row when Time1 is before 5/4/2004, and it should. Here is the statement I'm using to select my rows. I hope you can find something, because I sure can't:
string MySQLText = "SELECT * FROM Invoices"; DateTime Time1 = new DateTime(dateTimePicker1.Value.Year, dateTimePicker1.Value.Month, dateTimePicker1.Value.Day, 0, 0, 0); DateTime Time2 = new DateTime(dateTimePicker2.Value.Year, dateTimePicker2.Value.Month, dateTimePicker2.Value.Day, 23, 59, 59); MySQLText += " WHERE (OrderDate BETWEEN #" + Time1.ToString() + "# AND #" + Time2.ToString() + "#)"; oleDbSelectCommand1.CommandText = MySQLText; oleDbDataAdapter11.Fill( MyDataset ); -
I won't badger you this time, but I still suggest you try it. Once you do, I suspect you'll never turn back. :) Dates in SQL Server are single-quoted like strings. When referring to Date fields, SQL Server knows what to do. For Access (and some other's I've seen, but don't remember) you encase them in pounds (or hashes - #). If you don't, they will be treated like strings (IIRC) so you won't get the result you'd expect.
Microsoft MVP, Visual C# My Articles
Even with the pound signs, the adapter is treating the dates as strings. It is acting like 5/11/04 should go BEFORE 5/4/04. If using the parameters will fix this, I'll change to them as long as I can add them to the adapter programmatically, because in truth I would prefer to use the parameters...I just didn't know how in this situation. What would you suggest? Thank :)
-
Even with the pound signs, the adapter is treating the dates as strings. It is acting like 5/11/04 should go BEFORE 5/4/04. If using the parameters will fix this, I'll change to them as long as I can add them to the adapter programmatically, because in truth I would prefer to use the parameters...I just didn't know how in this situation. What would you suggest? Thank :)
Trust me - using parameters is so much better. You can add them programmatically, as there is no other way! :) For example (and I'm assuming you're using an
OleDbCommandsince you're using # signs):OleDbCommand cmd = oleDbConnection.CreateCommand();
cmd.CommandText = "SELECT * FROM MyTable WHERE DateCol BETWEEN ? AND ?";
cmd.Parameters.Add("StartDate", OleDbType.DBDate).Value = DateTime.Parse("5/4/04");
cmd.Parameters.Add("EndDate", OleDbType.DBDate).Value = DateTime.Parse("5/11/04");
OleDbDataReader reader = null;
try
{
oleDbConnection.Open();
reader = cmd.ExecuteReader();
// ...
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
}
finally
{
if (reader != null) reader.Close();
oleDbConnection.Close();
}Of course, you could use this command with an
OleDbDataAdapteras well - this is just an example. Still, though it is unusual that it's treating it as a string. You're not using quotes and # signs, are you?Microsoft MVP, Visual C# My Articles
-
Trust me - using parameters is so much better. You can add them programmatically, as there is no other way! :) For example (and I'm assuming you're using an
OleDbCommandsince you're using # signs):OleDbCommand cmd = oleDbConnection.CreateCommand();
cmd.CommandText = "SELECT * FROM MyTable WHERE DateCol BETWEEN ? AND ?";
cmd.Parameters.Add("StartDate", OleDbType.DBDate).Value = DateTime.Parse("5/4/04");
cmd.Parameters.Add("EndDate", OleDbType.DBDate).Value = DateTime.Parse("5/11/04");
OleDbDataReader reader = null;
try
{
oleDbConnection.Open();
reader = cmd.ExecuteReader();
// ...
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
}
finally
{
if (reader != null) reader.Close();
oleDbConnection.Close();
}Of course, you could use this command with an
OleDbDataAdapteras well - this is just an example. Still, though it is unusual that it's treating it as a string. You're not using quotes and # signs, are you?Microsoft MVP, Visual C# My Articles
-
Trust me - using parameters is so much better. You can add them programmatically, as there is no other way! :) For example (and I'm assuming you're using an
OleDbCommandsince you're using # signs):OleDbCommand cmd = oleDbConnection.CreateCommand();
cmd.CommandText = "SELECT * FROM MyTable WHERE DateCol BETWEEN ? AND ?";
cmd.Parameters.Add("StartDate", OleDbType.DBDate).Value = DateTime.Parse("5/4/04");
cmd.Parameters.Add("EndDate", OleDbType.DBDate).Value = DateTime.Parse("5/11/04");
OleDbDataReader reader = null;
try
{
oleDbConnection.Open();
reader = cmd.ExecuteReader();
// ...
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
}
finally
{
if (reader != null) reader.Close();
oleDbConnection.Close();
}Of course, you could use this command with an
OleDbDataAdapteras well - this is just an example. Still, though it is unusual that it's treating it as a string. You're not using quotes and # signs, are you?Microsoft MVP, Visual C# My Articles