The # sign is part of db field names

Too bad he has this:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0206 - Column ISREC not in specified tables.

I can confirm that enclosing the field name in brackets [] and using two
pound signs # as philh suggested works with an MS Access database.

Carl

philh wrote:
> Have you tried square brackets:
>
> select * from istatic
> where [ISrec##] = #recnum#
>

Have you tried square brackets:

select * from istatic
where [ISrec##] = #recnum#

Use three # signs....

SELECT ISREC### AS ISREC

The ### tells CF to really use a # in that place. At least it will print the # sign if you use ### on a webpage.

The following seems to get around the field name problem, but does not return the data.

<cfparam name="lastname" type="string">
<cfparam name="recnum" type="numeric">
<cfquery name="GetIndiv" datasource="AS400" username="quser" password="quser" dbtype="odbc">
select * from istatic
where ISrec## = #recnum#

</cfquery>
<cfif GetIndiv.recordcount is 0>

<cfoutput> <br> "Invalid user ID or password" #lastname# #GetIndiv.islnam# #recnum#<br> </cfoutput>


<cfelse>


<cfoutput query="GetIndiv"> #GetIndiv.RecordCount# </cfoutput>
Total records <br>

</cfif>

You can see that various combinations don't work. I can't change the field names. At least, not now.

Invalid CFML construct found on line 6 at column 18.
ColdFusion was looking at the following text:

isrec

The CFML compiler was processing:

* An expression that began on line 6, column 13.
Your expression might be missing an ending "#" (it might look like #expr ).
* The body of a cfquery tag beginning on line 5, column 2.


The error occurred in D:\usersites\taxsearch\htdocs\update2\submit.cfm: line 6

4 : <cfparam name="recnum" type="numeric">
5 : <cfquery name="GetIndiv" datasource="AS400" username="quser" password="quser" dbtype="odbc">
6 : select isrec# as isrec,ISLNAM,isfnam,iscity from istatic
7 : where ISrec = recnum
8 :

rror Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0206 - Column ISREC not in specified tables.

The error occurred in D:\usersites\taxsearch\htdocs\update2\submit.cfm: line 5

3 : <cfparam name="lastname" type="string">
4 : <cfparam name="recnum" type="numeric">
5 : <cfquery name="GetIndiv" datasource="AS400" username="quser" password="quser" dbtype="odbc">
6 : select isrec## as isrec,ISLNAM,isfnam,iscity from istatic
7 : where ISrec = recnum

SQL select isrec# as isrec,ISLNAM,isfnam,iscity from istatic where ISrec = recnum
DATASOURCE AS400
VENDORERRORCODE -206
SQLSTATE 42S22

I get the following error;

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0206 - Column "isrec#" not in specified tables.

The error occurred in D:\usersites\taxsearch\htdocs\update2\submit.cfm: line 5

3 : <cfparam name="lastname" type="string">
4 : <cfparam name="recnum" type="numeric">
5 : <cfquery name="GetIndiv" datasource="AS400" username="quser" password="quser" dbtype="odbc">
6 : select "isrec##" as isrec,ISLNAM,isfnam,iscity from istatic
7 : where ISrec = recnum

SQL select "isrec#" as isrec,ISLNAM,isfnam,iscity from istatic where ISrec = recnum
DATASOURCE AS400
VENDORERRORCODE -206
SQLSTATE 42S22

I have never had this problem but an idea might be to us the "AS" command in the SQL.... Another use for this is if you have two tables in a JOIN with the same name...

SELECT ISREC# AS ISREC

You may have to use "SELECT ISREC### AS ISREC" so that CF knows that the single # is not starting a var...

Then you should be able to access the var as:

#query.isrec#

I am not sure if this will work or not, but you can give it a try.