Getting entries from an SQL database with datetime parameter

[Nick]

I have created a simple aspx page with a datagrid, a textbox and a button on
it.
I want the user to enter a date in the textbox press the button and return a
list of items to the datagrid from an SQL database but I am having problems
with the date selection.

The field in the database is datetime format (dd-MM-yyyy hh:mm:ss)
I expect the user to type in a specific date e.g. 10-05-2005 or 10/05/2005
and click the button.

I have tested all the elements of the page and they all work. e.g. I've hard
coded a query to populate the datagrid, tested that the button raises the
event etc.

But when I attempt to pass the date to the query via a parameter I either
get an error or no data (even though I know it exists).


The DateFrom variable is a String passed to the function. It is the value
typed in to the textbox

I've tried passing the value straight to the parameter as a string.

dbParam_datefrom.ParameterName = "@datefrom"
dbParam_datefrom.Value = DateFrom
dbParam_datefrom.DbType = DbType.stringfixedlength
dbCommand.Parameters.Add(dbParam_datefrom)


And I've tried converting DateFrom to a DateTime object

dbParam_datefrom.ParameterName = "@datefrom"
dbParam_datefrom.Value = Convert.ToDateTime(DateFrom)
dbParam_datefrom.DbType = DbType.DateTime
dbCommand.Parameters.Add(dbParam_datefrom)



My SQL query contains the code

Dated>=@datefrom

Dated is the database field with datetime format that I have in the WHERE
clause

Am I making a glaring novice mistake as I'm very new to .NET.

Any help would be greatly appreciated as I'm really stuck.

Thanks
Nick

 

[Rogas69]

Hi Nick,

If you want to pass date to the function you have to verify if the parsed
format is what you want. You can use profiler to see exact form of the date
that is passed to database. On the other hand, you could use T-SQL CONVERT
funtion to convert date string into date time accordig to your needs. By the
way if there is a chance that someone will change default date format in
your database, you will run into troubles comparing date to string.

Peter

 

[Nick]

Thanks for that.
I'll give it a try and see what happens

Nick