SELECT (If Statement)

[KuyaRomeo]

Hi

I know some basic SQL, but beyond adding some AND and WHERE conditions . . I get lost.

I have a SQL Datasource in asp.net that I want to bind to a datagrid. This usually is a snap. But I have multiple text boxes on the form that I want to use in the WHERE conditions. The SQLdata wizard allows you to point to the control and pick it in your WHERE statement. But here is where I need help.

I want to do a Select * Where field1 = textbox1

But only if the textbox1 is not blank. If it is blank, it will skew my search results. How do I do this?

I don't really want to manually bind my datagrid because then it is a headache (and beyond my expertise) to get the edit, sort, etc working.

I can see the SQL that the datasource wizard generates, I just need to know how to edit that sql so that I can make it SELECT WHERE field1 = textbox1 . . . only when textbox one has data in it . . otherwise, don't use it in the search criteria.

?????

[gk53]

you can use something like
Select * from table WHERE field1 = COALESCE(textbox1,field1)

[bigmike1212]

I just need to know how to edit that sql so that I can make it SELECT WHERE field1 = textbox1 . . . only when textbox one has data in it . . otherwise, don't use it in the search criteria.

In Classic it is just

userinput=request.form("textbox_one") & ""
sql="Select * from table"
if len(userinput)>0 then
sql= sql & " where field1=" & userinput
end if

Surely NET can do that in code?

[jassi.singh]

Hello,

Try this

if (textbox.text != string.empty)
{
sqlquery = "select * from table where id = " + textbox.text
}
else
{
sqlquery = "select * from table "
}

[KuyaRomeo]

Hello,

Try this

if (textbox.text != string.empty)
{
sqlquery = "select * from table where id = " + textbox.text
}
else
{
sqlquery = "select * from table "
}

Yes, I ended up doing it very similar to this . . . . building the query on the fly. Was hoping to find something easier within SQL commands to do this . . but alas . . went old school.

[bigmike1212]

Yes, I ended up doing it very similar to this . . . . building the query on the fly. Was hoping to find something easier within SQL commands to do this . . but alas . . went old school.

What was wrong with GK's answer if you want just SQL?

That's what COALESCE does...

[KuyaRomeo]

Well, I will fully admit that I may have been doing it wrong . . when trying to implement GK's solution. I am learning as I go and certainly may have missed something .

That solution worked great, if I was only using one text box, but when I was building my sql based on 7 text boxes that were all possibly null or filled and only wanting to all the criteria from the boxes if there was text, but skipping it if it was null . . . COALESCE was bringing back no results at all.

Again . . . could be that COALESCE was something I have never used before and I was doing it wrong. Embarrassingly not an expert here !

What was wrong with GK's answer if you want just SQL?

That's what COALESCE does...