Incorrect syntax near 's'. Unclosed quotation mark after the character string

Question

I'm using a query to pull data from an SQL database, at times the last dropdown im using to get the record i'm looking for has a single quote, when it does I get the following error: Incorrect syntax near 's'. Unclosed quotation mark after the character string

This is the code I have:

Using objcommand As New SqlCommand("", G3SqlConnection)
        Dim DS01 As String = DDLDS01.SelectedItem.Text
        Dim State As String = DDLState.SelectedItem.Text
        Dim Council As String = DDLCouncil.SelectedItem.Text
        Dim Local As String = DDLLocal.SelectedItem.Text

        Dim objParam As SqlParameter
        Dim objDataReader As SqlDataReader
        Dim strSelect As String = "SELECT * " & _
            "FROM ConstitutionsDAT " & _
            "WHERE DS01 = '" & DS01 & "' AND STATE = '" & State & "' AND COUNCIL = '" & Council & "' AND LOCAL = '" & Local & "' AND JURISDICTION = '" & DDLJurisdiction.SelectedItem.Text & "' "

        strSelect.ToString.Replace("'", "''")
        objcommand.CommandType = CommandType.Text
        objcommand.CommandText = strSelect


        Try

            objDataReader = objcommand.ExecuteReader
            DDLJurisdiction.Items.Add("")
            While objDataReader.Read()
                If Not IsDBNull(objDataReader("SUBUNIT")) Then
                    txtSubUnit.Text = (objDataReader("SUBUNIT"))
                End If

                If Not IsDBNull(objDataReader("DS02")) Then
                    lblDS02.Text = (objDataReader("DS02"))
                End If
                If Not IsDBNull(objDataReader("LEGISLATIVE_DISTRICT")) Then
                    txtALD.Text = (objDataReader("LEGISLATIVE_DISTRICT"))
                End If
                If Not IsDBNull(objDataReader("REGION")) Then
                    txtRegion.Text = (objDataReader("REGION"))
                End If
                If DDLState.SelectedItem.Text <> "OTHER" Then
                    If Not IsDBNull(objDataReader("UNIT_CODE")) Then
                        txtUnitCode.Text = (objDataReader("UNIT_CODE"))
                    End If
                End If                   
            End While
            objDataReader.Close()
        Catch objError As Exception
            OutError.Text = "Error: " & objError.Message & objError.Source
            Exit Sub
        End Try
    End Using

Not all records contain a single quote, only some, so i'd need something that would work if a single quote is present or not. Thanks.

score 5 · Accepted Answer · edited May 23 '17 at 12:27

5

Your problem is this line here:

strSelect.ToString.Replace("'", "''")

This is changing your WHERE clause from something like

WHERE DS01 = 'asdf' AND ...

To:

WHERE DS01 = ''asdf'' AND ...

You need to do the replace on the individual values in the where clause, not on the whole select statement.

What you should really be doing is using a parameterized query instead.

Update: added same link as aquinas because it's a good link

edited May 23 '17 at 12:27

Community

1
1

answered Aug 29 '14 at 17:49

Michael Dunlap

4,300
25
36

1

I feel like even telling someone how to "fix" a query by escaping characters is like someone coming to you and saying: "I have a headache. I'm looking for the hammer to bash my skull in, have you seen it." "Oh yeah! It's right over there." Instead of just saying: "No! Stop! Get some Tylenol." :) – aquinas Aug 29 '14 at 17:52
@aquinas Fair enough. But I'd still like to point out how to fix what they do have, then point out that there's a better way, given that I want to answer the question they asked as well. – Michael Dunlap Aug 29 '14 at 17:53
Even after I've removed the Replace String, i'm getting this error. I only included it after searching around the web for answers. If I go directly to the SQL table and remove the single quote from the record that has it the query works fine, it's only when the text contains an 's... – user3991041 Aug 29 '14 at 18:00
@user3991041 You have do do the replace on each variable, `DS01`, `State`, `Council`, etc. But, as has been suggested, you should use a parameterized query instead so that you don't have to do these kinds of replacements. – Michael Dunlap Aug 29 '14 at 18:03
Based on your answer above I've figured out the solution, thanks. Rather then putting the replace on the entire line I only added it to the field that has the single quote. I will look into using a parameterized query once I have this project complete, it's kept me busy all summer long but am almost done. It seems very important... Thanks again for your help. – user3991041 Aug 29 '14 at 18:10

score 3 · Answer 2 · edited May 23 '17 at 12:17

3

Use parameterized queries, and only EVER use parameterized queries. See: How do I create a parameterized SQL query? Why Should I?

edited May 23 '17 at 12:17

Community

1
1

answered Aug 29 '14 at 17:48

aquinas

23,318
5
58
81

Incorrect syntax near 's'. Unclosed quotation mark after the character string

2 Answers2