[thelist] CF / SQL problem

Thanks for all the responses.  Instead fo cluttering up everyone's inbox with a
response to each one, here are my replies to the very issues:

1)  Concatenating Strings:  It seems like I've had problems using string
concatentation in CF in the past, so I went with the Insert function instead.  I
don't know that concatenating the string is "brute force", becuase I'm
essentially doing the same thing, but with more typing and possibly unnecessary
complexity.
2) Creation of the string itself [ Len function and debugging ].  Len points to
the last character, yes.  But, insert starts inserting the string after that
character.  So, if you want to insert a string to the beginning of another
string, you would start at 0, and not 1.  Also, I have been outputting the
generated string to the screen, but neglected to include that code in my
question. The outputted string looks correct.
3) The use of the wildcard:  I've tried it without the wildcard and testing for
equality (=) instead of comparing with LIKE.  Same error either way.
4) The #department# field:  It is from a form, and I probably should have used
#Form.department#, but all of my other queries (zip code, state, etc.) have
worked without using "Form." in it.  I will try it that way, though, just to
make sure.  Nope, didn't change any results.
4)  The weirdest part:  If I take the string generated (copied and pasted from
the debug output) and go back and put it into the the query instead, it works
fine.  Here's what I mean:

-- I run the app putting in "Internal Audit" into the Department field.
-- The query string is outputted at the top of the screen (before the error is
generated) [formatted here for readability]:
     SELECT * FROM mailcodeTB, divisionTB, stateTB, sacramentoTB
     WHERE mailcodeTB.divisionID = divisionTB.divisionID AND mailcodeTB.stateID
= stateTB.stateID
     AND mailcodeTB.sacID = sacramentoTB.sacID
     AND mailcodeTB.department LIKE 'Internal Audit%' ORDER BY mailcode
-- I copy and pasted that into my code as the query (instead of using the
qryString variable).
-- I re-run the application and it works just fine.

So, now you see why I'm stumped.  I may just have to end up providing a
drop-down list of departments based on our company directory.   However, that
list of
departments may not be up-to-date as departments get created and mailcodes are
generated.


If there are any other ideas, I'd be extremely grateful.

Scott





:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: 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,