ASP.Net single quotes embedded SQL multiple conditions

Discussion in 'ASP .Net' started by JB, Apr 23, 2010.

  1. JB

    JB Guest

    Hello Community

    I am using ASP.Net with C# with embedded SQL query using single quotes.
    I am passing mulitple parameters with multiple conditions such that the "AND"
    and the "OR" conditions need to be on one line or atleast be considered as
    one statement:

    strSql = " Select * " ;
    strSql += " From table1 " ;
    strSql += " Where qty = " + amt ;
    strSql += " AND color = ' " + color1 + " ' " ;
    strSql += " OR material = ' " + silk + " ' " ;

    In the past I could satisfy the condition in the ASP.Net C# portion
    using codebehind but in this case I don't have that option.

    Can anyone tell me how I can put these 2 conditions on one line or use
    parenthesis around them the same as you would in a mathematical expression
    that and make the "AND" and "OR" (2 conditions ) considered as one condition
    or statement ?

    Thanks
    Jeff

    JB
    JB, Apr 23, 2010
    #1
    1. Advertising

  2. JB wrote:
    > Hello Community
    >
    > I am using ASP.Net with C# with embedded SQL query using single quotes.
    > I am passing mulitple parameters with multiple conditions such that the "AND"
    > and the "OR" conditions need to be on one line or atleast be considered as
    > one statement:
    >
    > strSql = " Select * " ;
    > strSql += " From table1 " ;
    > strSql += " Where qty = " + amt ;
    > strSql += " AND color = ' " + color1 + " ' " ;
    > strSql += " OR material = ' " + silk + " ' " ;
    >
    > In the past I could satisfy the condition in the ASP.Net C# portion
    > using codebehind but in this case I don't have that option.
    >
    > Can anyone tell me how I can put these 2 conditions on one line or use
    > parenthesis around them the same as you would in a mathematical expression
    > that and make the "AND" and "OR" (2 conditions ) considered as one condition
    > or statement ?


    You seem to be under the impression that it matters in the slightest how
    many lines you use in building the string. There is no significance to
    this at all. You can just as well have

    strSql = "Select * from table1 where ";
    strSql += " qty = amt AND color = " ;
    strSql += "'" + color1 + "' OR material = 'silk'";

    You can put parentheses for grouping in the WHERE clause anywhere in the
    string they belong.
    Harlan Messinger, Apr 23, 2010
    #2
    1. Advertising

  3. JB

    JB Guest

    Hello Harlan

    Thanks! Your solution pointed out something that I didn't understand
    when using this method of embedded sql.

    Jeff
    --
    JB


    "Harlan Messinger" wrote:

    > JB wrote:
    > > Hello Community
    > >
    > > I am using ASP.Net with C# with embedded SQL query using single quotes.
    > > I am passing mulitple parameters with multiple conditions such that the "AND"
    > > and the "OR" conditions need to be on one line or atleast be considered as
    > > one statement:
    > >
    > > strSql = " Select * " ;
    > > strSql += " From table1 " ;
    > > strSql += " Where qty = " + amt ;
    > > strSql += " AND color = ' " + color1 + " ' " ;
    > > strSql += " OR material = ' " + silk + " ' " ;
    > >
    > > In the past I could satisfy the condition in the ASP.Net C# portion
    > > using codebehind but in this case I don't have that option.
    > >
    > > Can anyone tell me how I can put these 2 conditions on one line or use
    > > parenthesis around them the same as you would in a mathematical expression
    > > that and make the "AND" and "OR" (2 conditions ) considered as one condition
    > > or statement ?

    >
    > You seem to be under the impression that it matters in the slightest how
    > many lines you use in building the string. There is no significance to
    > this at all. You can just as well have
    >
    > strSql = "Select * from table1 where ";
    > strSql += " qty = amt AND color = " ;
    > strSql += "'" + color1 + "' OR material = 'silk'";
    >
    > You can put parentheses for grouping in the WHERE clause anywhere in the
    > string they belong.
    > .
    >
    JB, Apr 26, 2010
    #3
  4. On Apr 27, 12:31 am, JB <> wrote:
    > Hello Harlan
    >
    >     Thanks!  Your solution pointed out something that I didn't understand
    > when using this method of embedded sql.
    >
    >     Jeff
    > --
    > JB
    >
    >
    >
    > "Harlan Messinger" wrote:
    > > JB wrote:
    > > > Hello Community

    >
    > > >     I am using ASP.Net with C# with embedded SQL query using single quotes.  
    > > > I am passing mulitple parameters with multiple conditions such that the "AND"
    > > > and the "OR" conditions need to be on one line or atleast be considered as
    > > > one statement:

    >
    > > >     strSql =    "  Select *  " ;
    > > >     strSql +=  "  From table1 " ;
    > > >     strSql +=  "  Where qty    = " +  amt ;
    > > >     strSql +=  "  AND color     =  ' " + color1 +  " ' " ;
    > > >     strSql +=  "  OR  material =  ' " + silk     +  " ' " ;

    >
    > > >     In the past I could satisfy the condition in the ASP.Net C# portion
    > > > using codebehind but in this case I don't have that option.

    >
    > > >     Can anyone tell me how I can put these 2 conditions on one line or use
    > > > parenthesis around them the same as you would in a mathematical expression
    > > > that and make the "AND" and "OR"  (2 conditions ) considered as one condition
    > > > or statement ?

    >
    > > You seem to be under the impression that it matters in the slightest how
    > > many lines you use in building the string. There is no significance to
    > > this at all. You can just as well have

    >
    > > strSql = "Select *  from table1 where ";
    > > strSql += " qty = amt AND color = " ;
    > > strSql += "'" + color1 + "' OR material = 'silk'";

    >
    > > You can put parentheses for grouping in the WHERE clause anywhere in the
    > > string they belong.
    > > .


    Most likely you will love

    strSql = @"Select * from table1 where
    qty ='" + amt + "' AND color =
    '" + color1 + "' OR material = '"silk"'";

    or

    strSql = string.Format(@"Select * from table1 where
    qty = '{0}' AND color =
    '{1}' OR material = '{2}' ",
    amt,
    color,
    silk);

    Hope this helps
    Alexey Smirnov, Apr 27, 2010
    #4
  5. JB

    Patrice Guest

    Hello,

    >> > strSql = " Select * " ;
    >> > strSql += " From table1 " ;
    >> > strSql += " Where qty = " + amt ;
    >> > strSql += " AND color = ' " + color1 + " ' " ;
    >> > strSql += " OR material = ' " + silk + " ' " ;


    Unrelated to this specific issue, but you may want to consider using
    parameters :
    http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

    Not listed but IMO one of the key benefit is that values embedded in the SQL
    string could cause problems if not well done i..e :
    - you have to replace ' with '' in strings
    - you have to use a date format that match your server language (or better
    use a format such as YYYMMDD that works regardless of the server settings)
    - to take extra care if your code runs in a country that doesn't use . as a
    decimal separator (else you'll get 2,5 rather than 2.5 in your SQL
    statement).

    With parameters you'll just work with the actual data type...

    --
    Patrice
    Patrice, Apr 27, 2010
    #5
  6. On Apr 27, 11:32 am, "Patrice" <http://scribe-en.blogspot.com/> wrote:
    > Hello,
    >
    > >> >     strSql =    "  Select *  " ;
    > >> >     strSql +=  "  From table1 " ;
    > >> >     strSql +=  "  Where qty    = " +  amt ;
    > >> >     strSql +=  "  AND color     =  ' " + color1 +  " ' " ;
    > >> >     strSql +=  "  OR  material =  ' " + silk     +  " ' " ;

    >
    > Unrelated to this specific issue, but you may want to consider using
    > parameters :http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx
    >
    > Not listed but IMO one of the key benefit is that values embedded in the SQL
    > string could cause problems if not well done i..e :
    > - you have to replace ' with '' in strings
    > - you have to use a date format that match your server language (or better
    > use a format such as YYYMMDD that works regardless of the server settings)
    > - to take extra care if your code runs in a country that doesn't use . as a
    > decimal separator (else you'll get 2,5 rather than 2.5 in your SQL
    > statement).
    >
    > With parameters you'll just work with the actual data type...
    >
    > --
    > Patrice


    I agree with suggestion of Patrice.
    Alexey Smirnov, Apr 27, 2010
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. SStory
    Replies:
    5
    Views:
    5,726
    SStory
    Jun 7, 2004
  2. Chris
    Replies:
    1
    Views:
    13,611
    Oisin
    Mar 24, 2006
  3. Elmo
    Replies:
    11
    Views:
    645
    Mark Rae
    Dec 18, 2006
  4. Replies:
    10
    Views:
    1,179
    Anno Siegel
    Apr 17, 2006
  5. Replies:
    2
    Views:
    119
    slebetman
    Jul 7, 2008
Loading...

Share This Page