#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Apr 2011
    Posts
    13
    Rep Power
    0

    SELECT (If Statement)


    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.

    ?????
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,608
    Rep Power
    278
    you can use something like
    Select * from table WHERE field1 = COALESCE(textbox1,field1)
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
  4. #3
  5. 0x800A0C93
    ASP Super Hero (2500 - 2999 posts)

    Join Date
    Jan 2009
    Location
    Ridin' through this world...
    Posts
    2,975
    Rep Power
    1310
    Originally Posted by KuyaRomeo
    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

    Code:
    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?
    Brinkster- free development account. Not affiliated, but I sure like free. Brinkster.com
  6. #4
  7. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Sep 2011
    Posts
    105
    Rep Power
    4
    Hello,

    Try this

    if (textbox.text != string.empty)
    {
    sqlquery = "select * from table where id = " + textbox.text
    }
    else
    {
    sqlquery = "select * from table "
    }
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Apr 2011
    Posts
    13
    Rep Power
    0
    Originally Posted by jassi.singh
    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.
  10. #6
  11. 0x800A0C93
    ASP Super Hero (2500 - 2999 posts)

    Join Date
    Jan 2009
    Location
    Ridin' through this world...
    Posts
    2,975
    Rep Power
    1310
    Originally Posted by KuyaRomeo
    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...
  12. #7
  13. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Apr 2011
    Posts
    13
    Rep Power
    0
    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 !


    Originally Posted by bigmike1212
    What was wrong with GK's answer if you want just SQL?

    That's what COALESCE does...

Similar Threads

  1. select statement help
    By webb in forum ASP Development
    Replies: 15
    Last Post: August 30th, 2005, 12:29 AM
  2. Help with SELECT Statement
    By paperduck in forum Microsoft Access Help
    Replies: 1
    Last Post: January 15th, 2005, 05:18 AM
  3. Loop problem or Select statement problem?
    By ap390 in forum ASP Development
    Replies: 3
    Last Post: October 20th, 2004, 10:09 AM
  4. SELECT statement and variables
    By DieselPower in forum ASP Development
    Replies: 4
    Last Post: March 15th, 2004, 02:47 PM
  5. Trying to make a select statement conditional
    By mbrodt in forum SQL Development
    Replies: 0
    Last Post: November 12th, 2003, 07:28 PM

IMN logo majestic logo threadwatch logo seochat tools logo