Query interpreting single quote as

[jmikow]

I have been using a variable to create my select statement in ASP.Net using VB.Net and recently it started adding an additional "'" after each "'" that I had in my code causing it to send a "''" when it executes the code.

Below is the code I use to generate my sql statement:

   stmt = "select invoice_no," & _
                    "dateentered as [Date Entered]," & _
                    "date_billed as [Date Billed]," & _
                    "claim_no as [Claim Number]," & _
                    "records_re as [Subject Name]," & _
                    "ltrim(rtrim(ltrim(rtrim(s.first_name)) + ' ' + ltrim(rtrim(s.last_name)))) as [Source]," & _
                    "ltrim(rtrim(ltrim(rtrim(s.city)) + ', ' + ltrim(rtrim(s.state)))) as [Source City]," & _
                    "ltrim(rtrim(ltrim(rtrim(w.plaintiff)) + ' vs. ' + ltrim(rtrim(w.defendant)))) as [Case Title]," & _
                    "f.f_DocumentID as [Doc ID]," & _
                    "left(w.subpoena_type,1) as [SubType]," & _
                    "w.date_due as [DateDue]" & _
                " from worksheets w," & _
                    "sources s," & _
                    "clients c," & _
                    "FortisApplebyRecordsRep.sysadm.DailyWork f" & _
                " where w.client_id = @ClientID" & _
                    " and w.dateentered >= DateAdd(day,-@SearchFor,GetDate())" & _
                    " and w.bill_id = c.client_id" & _
                    " and (left(rtrim(ltrim(c.client_last_name)),4) = 'CSAA'" & _
                        " or left(rtrim(ltrim(c.client_last_name)),21) = 'CALIFORNIA STATE AUTO')" & _
                    " and s.source_no = w.source_id" & _
                    " and w.invoice_no = f.invoiceno"

        Select Case ddlSortBy.SelectedValue
            Case "DateEntered"
                stmt = stmt & " order by [Date Entered]"
            Case "DateBilled"
                stmt = stmt & " order by [Date Billed]"
            Case "SubjectName"
                stmt = stmt & " order by [Subject Name]"
            Case "ClaimNumber"
                stmt = stmt & " order by [Claim Number]"
            Case "Source"
                stmt = stmt & " order by [Source]"
        End Select

        Select Case ddlSortDir.SelectedValue
            Case "a"
                stmt = stmt & " asc"
            Case "d"
                stmt = stmt & " desc"
        End Select

        ' Call ExecuteReader static method of SqlHelper class that returns a SqlDataReader
        ' We pass in database connection string, stored procedure name and value of categoryID parameterand, and a "1" for CategoryID value
        da = New SqlDataAdapter(stmt, connection)
        da.SelectCommand.Parameters.Add("@SearchFor", CType(txtSearchFor.Text, Integer))
        da.SelectCommand.Parameters.Add("@ContactID", CType(txtContactID.Text, Integer))
        da.SelectCommand.Parameters.Add("@ClientID", CType(txtClientID.Text, Integer))

        ds = New DataSet
        da.Fill(ds, "w")

Below is how the code is being sent to the server to be executed:

select invoice_no,dateentered as [Date Entered],date_billed as [Date Billed],claim_no as [Claim Number],records_re as [Subject Name],ltrim(rtrim(ltrim(rtrim(s.first_name)) + '' '' + ltrim(rtrim(s.last_name)))) as [Source],ltrim(rtrim(ltrim(rtrim(s.city)) + '', '' + ltrim(rtrim(s.state)))) as [Source City],ltrim(rtrim(ltrim(rtrim(w.plaintiff)) + '' vs. '' + ltrim(rtrim(w.defendant)))) as [Case Title],f.f_DocumentID as [Doc ID],left(w.subpoena_type,1) as [SubType],w.date_due as [DateDue] from worksheets w,sources s,clients c,FortisApplebyRecordsRep.sysadm.DailyWork f where w.client_id = @ClientID and w.dateentered >= DateAdd(day,-@SearchFor,GetDate()) and w.bill_id = c.client_id and (left(rtrim(ltrim(c.client_last_name)),4) = ''CSAA'' or left(rtrim(ltrim(c.client_last_name)),21) = ''CALIFORNIA STATE AUTO'') and s.source_no = w.source_id and w.invoice_no = f.invoiceno order by [Date Entered] asc

Any assistance to help figure out why this is happening would be extremely helpful.

Thanks,

Josh

 

[adamroof]

ltrim(rtrim(ltrim(rtrim(w.plaintiff)) + ' vs. ' + ltrim(rtrim(w.defendant)))) as [Case Title]," & _

=

"ltrim(rtrim(ltrim(rtrim(w.plaintiff)) '" + vs. + "' ltrim(rtrim(w.defendant)))) as [Case Title]," & _

 

[adamroof]

to clarify, single - double - + vs. + double - single

 

[adamroof]

um...
its early...

"ltrim(rtrim(ltrim(rtrim(w.plaintiff))" + " vs. " + "ltrim(rtrim(w.defendant)))) as [Case Title]," & _

should produce

adamroof vs. jmikow

 

[talon121]

You can always just change any statements within the quotes that need quotes to a single ' .

That should fix any errors, the replies above also look like good answers for that. --

---
"Thought Id throw in my two pennies"
Jason


---
LexiMedia, LLC Development Group

http://www.leximedia.net