[thelist] CF / SQL problem

rudy,

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: rudy limeback <r937@interlog.com>
:
: >   <cfset qryString = Insert(" AND mailcodeTB.department LIKE
: >   '#department#%'", qryString, Len(qryString))>
:
: no jeff, that's not an extraneous %, that's the wildcard character
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

um, i guess i musta forget to include my obligatory "i'm just a sql hack"
disclaimer to that response.  thanks for correcting me though cause now i
know what that's all about.

well, kinda.  care to expand on precisely what the wildcard character is
used for?

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: scott, i think what you are trying to do with the insert function is slick
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

i agree.  i've seen dynamic sql statements with the logic embedded in the
cfquery, but never building the sql in a variable then passing that to the
cfquery.  it's genius.

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: i would've done it the old fashioned, dare i say brute force, way, by
: concatenating the to-be-appended bit to the original string, something
like
:
:    <cfset qryString = "#qryString#" + " AND et cetera">
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

actually, you concatenate strings in cf with an ampersand.

<cfset qryString = #qryString# & " AND et cetera">

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: i *think* what's happening is that the blank which is at the front of the
: appended string is overlaying the last character of the original string,
: the D at the end of sacramentoTB.sacID -- hence, syntax error, ain't gots
: no column called sacI
:
: Len(qryString) points to the last character, and that's where you are
: "inserting"
:
: you can fix the problem by adding a blank to the end of the original
string
:
: either that, or specify Len(qryString)+1 for the position where to insert
: the append string
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

i gotta agree with this analysis, although writing the string to the screen
would certainly help debug this.

good luck,

: jeff.howden
: web.development.professional
: evolt.org.member
:
: the.best.looking.developers.on.the.net
:
: http://evolt.org/
: jeff@members.evolt.org