Re: SQL string problem

From: TLowe (noelbisspam_at_nomindspringspam.com)
Date: 05/12/04 

Date: Wed, 12 May 2004 17:06:15 -0500

Thanks Jake
That is an excellent function to use to clean up data. I was looking for
something like that to clean up some of the data passed to SQL. I dont
think that the source of this problem is the quotes. This is just cutting
off data like their is some kind of limit to the size. I will keep looking
and see if I can find anything that might shed some light on the issue.

"Jake Marx" <msnews@longhead.com> wrote in message
news:OXwEuoFOEHA.2480@tk2msftngp13.phx.gbl...
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: Run-time error 3709
    ... Hi Fox ... dim strSQL as string ... CTRL-G to Goto the debuG window -- look at the SQL statement ...
    (microsoft.public.access.modulesdaovba)
  • 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)
  • Re: SQL Statement or Cursor
    ... > Your post seems to ask whether you're better off using a cursor or a sql ... > you can achieve this in a SQL Statement, but I'll offer a caution to you ... SQL Server's tsql doesn't have a rownum ... >> Initial Result Set but lacking Incrementing number. ...
    (microsoft.public.sqlserver.programming)
  • Re: Populating a list -- table structure?
    ... this clears up a lot of issues and jargon with SQL. ... I think I'll have to try to get a query that will take the BKitIDs and the KitIDs and bring that information together. ... Queries (just shows the QBE grid for convenience -- ... and It really helps to use Aliases for tablenames as it makes the SQL statement shorter. ...
    (microsoft.public.access.forms)
  • Re: Emailing a Report
    ... CTRL-G to Goto the debuG window -- look at the SQL statement ... The Sub LoopAgmtsSendEmail is highlighted by the Debugger. ...
    (microsoft.public.access.modulesdaovba)