SQL Statement error

Hi,

I have a gridview that displays the result of a table, and I have 2 DropDownList and 2 BasicDatePicker: (The SQL statement work fine but when loading the page I get an SQL depassement Time )

When I try to use:

SELECT * FROM Missions WHERE
(
(@Division = 'NULL' OR Division = @Division) AND
(@Lieu = 'NULL' OR Lieu = @Lieu) AND
(@Date1 IS NULL OR Date1>= @Date1) AND
(@Date2 IS NULL OR Date2 <= @Date2)
)

I get an SQL dépassement time, even If set the default value to something not null

Any help plz?

Re: SQL Statement error

What types are Division etc? Why are you using ="null" sometimes and IS NULL others? IS NULL is the correct way. What does "SQL dépassement time" mean?

Re: SQL Statement error

AidyF
What types are Division etc? Why are you using ="null" sometimes and IS NULL others? IS NULL is the correct way. What does "SQL dépassement time" mean?

Division: string

Lieu: string

Date1: Datetime

Date2: Datetime

SQL Dépassement time: means that the time is less than 1/1/1750 or more than 31/12/9999

Re: SQL Statement error

Try change to:

SELECT * FROM Missions WHERE
(
(@Division IS NULL OR ( @Division IS NOT NULL AND Division = @Division)) AND
(@Lieu IS NULL OR (@Lieu IS NOT NULL AND Lieu = @Lieu)) AND
(@Date1 IS NULL OR (@Date1 IS NOT NULL AND Date1>= @Date1)) AND
(@Date2 IS NULL OR (@Date2 IS NOT NULL AND Date2 <= @Date2))
)

Case you pass the value 'NULL' in your variables @Division and @Lieu disconsider my changes.

I hope this help.

Re: SQL Statement error

This is the problem :

pierrefrc

(@Date1 IS NULL OR (@Date1 IS NOT NULL AND
Date1>=
@Date1)) AND


(@Date2 IS NULL OR (@Date2 IS NOT NULL AND
Date2 <=
@Date2))
)

Because when the page load, the datepicker doesn't choose any value

For division or lieu, I just pass the default value to 'NULL' and it works,

For DateTime I can't pass the default value to 'NULL' because it's not a string, so I used the " IS NULL": It's working on Test but not working when loading the page

Re: SQL Statement error

Do you can to post your code to execute this sql ?

Re: SQL Statement error

Hi,

In the datepick control in GridView when you bind the field to the property of control. You can make a checking in markup, something like. I suppose it is selecteddate property:

selecteddate='<%# (Eval("fieldname") == DBNull.Value) ? "***" : Eval("fieldname") %>'

Thanks,