How to Handle a SQL Statement with Quotes

Discussion in 'ASP .Net' started by swep, Jan 10, 2007.

  1. swep

    swep Guest

    To any who might be able to help this is the situation.I have a
    dropdown list that gets populated from SQL. It contains both single and
    double quotes. It populates the dropdown list just fine. I create a
    variable and assign its value to whatever is the selected value of the
    dropdown list control. (Line 1) Based on that variable I use it to
    search for another item in the table, then populate a Gridview (Lines
    19-26). The problem (I think) is that since the variable
    (sProductDescripton) has single and double quotes, when it is used in
    the query statement the quotes are not being 'seen'. (Line 12) Below is
    my code:

    1 Dim sProductDescripton As String =
    Me.ddlProduct.SelectedValue
    2 Dim sDistrict As String = Me.ddlDivision.SelectedValue
    3 Dim sProductType As String = Me.ddlProdType.SelectedValue
    4 Dim sPart As String = "Panel"
    5
    6 Dim myConn As New SqlConnection
    7 Dim myCmd As New SqlCommand
    8 Dim sSQL As String
    9 Dim sConn As String
    12 sSQL = "select NAME, PART from POP_PRODUCT where TYPE=
    '" & sProductType & "' and PART= '" & sPart & "' and DISTRICT= '" &
    sDistrict & "' and NAME='" & sProductDescripton & "'"
    13 sConn = "Server=USFBGDXPP196\SQLEXPRESS;Initial
    Catalog=APPS;Integrated Security=True"
    14
    15 myConn = New SqlConnection(sConn)
    16 myConn.Open()
    17 myCmd = New SqlCommand(sSQL, myConn)
    18
    19 Dim dSet As New Data.DataSet
    20 Dim dView As New Data.DataView
    21 Dim myDataAdapter As SqlDataAdapter
    22 myDataAdapter = New SqlDataAdapter(sSQL, sConn)
    23 myDataAdapter.Fill(dSet)
    24
    25 Me.gvPanel.DataSource = dSet
    26 Me.gvPanel.DataBind()
    27
    28 myCmd.Dispose()
    29 myConn.Close()

    Could anyone offer any suggestions?
    swep, Jan 10, 2007
    #1
    1. Advertising

  2. swep

    David Wier Guest

    There are two ways to handle this - #1 is much more preferable for more
    reasons than 1:
    1. Parameterized Queries (2 part tutorial:
    http://aspnet101.com/aspnet101/tutorials.aspx?id=1)
    2. http://aspnet101.com/aspnet101/tutorials.aspx?id=2 - remember
    concatenation of queries is not recommended at all.

    --
    David Wier
    MVP/ASPInsider
    http://aspnet101.com
    http://aspexpress.com


    "swep" <> wrote in message
    news:...
    > To any who might be able to help this is the situation.I have a
    > dropdown list that gets populated from SQL. It contains both single and
    > double quotes. It populates the dropdown list just fine. I create a
    > variable and assign its value to whatever is the selected value of the
    > dropdown list control. (Line 1) Based on that variable I use it to
    > search for another item in the table, then populate a Gridview (Lines
    > 19-26). The problem (I think) is that since the variable
    > (sProductDescripton) has single and double quotes, when it is used in
    > the query statement the quotes are not being 'seen'. (Line 12) Below is
    > my code:
    >
    > 1 Dim sProductDescripton As String =
    > Me.ddlProduct.SelectedValue
    > 2 Dim sDistrict As String = Me.ddlDivision.SelectedValue
    > 3 Dim sProductType As String = Me.ddlProdType.SelectedValue
    > 4 Dim sPart As String = "Panel"
    > 5
    > 6 Dim myConn As New SqlConnection
    > 7 Dim myCmd As New SqlCommand
    > 8 Dim sSQL As String
    > 9 Dim sConn As String
    > 12 sSQL = "select NAME, PART from POP_PRODUCT where TYPE=
    > '" & sProductType & "' and PART= '" & sPart & "' and DISTRICT= '" &
    > sDistrict & "' and NAME='" & sProductDescripton & "'"
    > 13 sConn = "Server=USFBGDXPP196\SQLEXPRESS;Initial
    > Catalog=APPS;Integrated Security=True"
    > 14
    > 15 myConn = New SqlConnection(sConn)
    > 16 myConn.Open()
    > 17 myCmd = New SqlCommand(sSQL, myConn)
    > 18
    > 19 Dim dSet As New Data.DataSet
    > 20 Dim dView As New Data.DataView
    > 21 Dim myDataAdapter As SqlDataAdapter
    > 22 myDataAdapter = New SqlDataAdapter(sSQL, sConn)
    > 23 myDataAdapter.Fill(dSet)
    > 24
    > 25 Me.gvPanel.DataSource = dSet
    > 26 Me.gvPanel.DataBind()
    > 27
    > 28 myCmd.Dispose()
    > 29 myConn.Close()
    >
    > Could anyone offer any suggestions?
    >
    David Wier, Jan 10, 2007
    #2
    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,720
    SStory
    Jun 7, 2004
  2. Chris White

    Quotes/Double Quotes in Image Control

    Chris White, Sep 22, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    4,825
    Hermit Dave
    Sep 22, 2004
  3. Chris
    Replies:
    1
    Views:
    13,591
    Oisin
    Mar 24, 2006
  4. Lawrence Tierney

    Multiline quotes - escaping quotes - et al

    Lawrence Tierney, Dec 24, 2003, in forum: Java
    Replies:
    3
    Views:
    4,476
    Andrew Thompson
    Dec 24, 2003
  5. Elmo
    Replies:
    11
    Views:
    635
    Mark Rae
    Dec 18, 2006
Loading...

Share This Page