Re: SQL string problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Jake Marx (msnews_at_longhead.com)
Date: 05/12/04 

Date: Wed, 12 May 2004 12:49:02 -0700

Hi TLowe,

You must "escape" any single quotes when sending SQL statements directly to
SQL Server via ADO. By that, I mean that you must add another single quote
immediately after any existing single quotes in your SQL statement.
Typically, I use a simple function to accomplish this:

    Public Function gsMakeStringSQLSafe(rsSQL As String) As String
        gsMakeStringSQLSafe=Replace$(rsSQL, "'", "''")
    End Function

Just pass your SQL statement through this function when opening your
recordset:

    rs.Open gsMakeStringSQLSafe(strSQL), cnnStoredProc, _
     adUseClient, adLockReadOnly

Hopefully, this will fix your problem. 

-- 
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]
TLowe wrote:
> Hello all!
> I am using an ADO recordset to import data into Excel 2000 from a Win
> 2003/SQL Server2000 setup.  My problem is the SQL string is getting
> truncated and is not passed to SQL server 2000 in a complete form.
> Its cut off.  It appears to cutoff around 185 characters.  Is this a
> setting that can be reset?  Is this an ADO Property that should be
> set to allow a bigger SQL statement to be passed to SQL server.  Its
> really weird that it only takes so many characters, truncates the
> balance of the statement and then still tacks the double quotes at
> the end of the statement?   The
>
> Here is the ADO recordset :
> rs.Open strSQL, cnnStoredProc, adUseClient, adLockReadOnly
>
> Here is the full SQL statement that is assigned to the strSQL
> variable that is initially stored in the variable strSQL.  This
> statement runs in the SQL analyzer with no problems (variables x, and
> y with actual values).
>
> SELECT [description],count(t2.Result) as total,t2.Result,t2.Vendor,
> t1.[date]from Tblquestions t1 inner join TblResultsQuestion t2 on
> T1.[id] = T2.QuestionID where T1.type = 4 and vendor like '" & x & "'
> and [description] like '" & y & "' group by [description],Vendor,
> [date], result order by [description],Result desc
>
> Here is the actual statement that gets put into the strSQL variable
> when passed via the above rs.open statement.
>
> strSQL : "SELECT [description],count(t2.Result) as total,t2.Result,
> t2.Vendor, t1.[date] from Tblquestions t1 inner join
> TblResultsQuestion t2 on T1.[id] = T2.QuestionID where t1.type = 4
> and ve"

Relevant Pages

  • Re: Escape html tags and other dangerous input
    ... The & affects the querystring. ... Single quotes can end the SQL statement allowing for a SQL ... >> injection, but that doesn't help with the ampersand, and the search page ...
    (microsoft.public.dotnet.security)
  • Re: Handling single quotes " " in data
    ... There's no reason not to store the single quotes. ... Presumably your SQL statement is something like ... (remove the extra spaces in the parameters in the Replace statement before ... > I have been storing file locations in a table in access. ...
    (microsoft.public.access.modulesdaovba)
  • executing an sql statement in perl
    ... and thetime> sysdate - 90 ... however i don't think it likes the 2 sets of single quotes. ... The below sql statement works fine directly in my sql program ...
    (comp.lang.perl.misc)
  • Re: SQL and RPG
    ... the SQL statement is being called ... I don't need the semicolon token, ... ITMI in this example is 6 characters and ITP# is 18 ... If your fields are defined as character you have to put single quotes ...
    (comp.sys.ibm.as400.misc)
  • Re: SQL string problem
    ... clean up your parameters before sending them to SQL. ... > You must "escape" any single quotes when sending SQL statements ... > Just pass your SQL statement through this function when opening your ... >> Here is the full SQL statement that is assigned to the strSQL ...
    (microsoft.public.excel.programming)