Re: SQL string problem
From: Jake Marx (msnews_at_longhead.com)
Date: 05/13/04
- Next message: Minna: "Excel - VBA guru needed URGENTLY!"
- Previous message: Jim Rech: "Re: Window Resize"
- In reply to: TLowe: "Re: SQL string problem"
- Next in thread: onedaywhen: "Re: SQL string problem"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 13 May 2004 09:53:52 -0700
Hi TLowe,
If this section of your code isn't too long, would you mind posting it here?
What you have posted so far looks OK, but you don't show all the steps, so
maybe there is an issue elsewhere in your code. AFAIK, there is no
practical limit on the length of the Source argument to the Open method, so
I don't think you're running into a limitation that would be cutting off
your statement.
As onedaywhen pointed out, my reply to your original post was flat out
wrong. So please disregard it. But it is a good function to use to clean
up particular data items when it's possible they may contain apostrophes. I
don't use it often, however, because I typically use stored procedures
instead of ad-hoc SQL statements. With stored procedures, you don't have to
clean up your parameters before sending them to SQL.
-- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] TLowe wrote: > 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. > > > 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"
- Next message: Minna: "Excel - VBA guru needed URGENTLY!"
- Previous message: Jim Rech: "Re: Window Resize"
- In reply to: TLowe: "Re: SQL string problem"
- Next in thread: onedaywhen: "Re: SQL string problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
