error is search with #PreserveSingleQuotes(WADbSearch1.whereClause)#

i have two table some how if i search a few times from different table it wil give me this error

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] The specified field 'crmStatusID' could refer to more than one table listed in the FROM clause of your SQL statement.

The error occurred in C:\ColdFusion9\wwwroot\webapp\crm\index.cfm: line 55

53 : FROM crm INNER JOIN crmStatus ON crm.crmStatusID = crmStatus.crmstatusID
54 :
55 : #PreserveSingleQuotes(WADbSearch1.whereClause)#
56 :
57 : ORDER BY crmID DESC



--------------------------------------------------------------------------------

VENDORERRORCODE -3007
SQLSTATE HY000
SQL SELECT crm.crmID, crm.crmDate, crm.HotelName, crm.ContactPerson, crm.Mobile, crm.Room, crmStatus.crmStatusID, crmStatus.status FROM crm INNER JOIN crmStatus ON crm.crmStatusID = crmStatus.crmstatusID WHERE ((crmStatusID = 2)) ORDER BY crmID DESC
DATASOURCE webapp2010

Resources:

it is telling me that there are error on lines 55 but it is working a few time and than i give me this error. I can see that on the about "WHERE ((crmStatusID = 2))" ; I think the "crmStatusID = 2" should be "crmStatus.crmStatusID" but it is not working still.

can you tell me where is this from?

#PreserveSingleQuotes(WADbSearch1.whereClause)#

i think it has something to do with session that keep this

#PreserveSingleQuotes(WADbSearch1.whereClause)#

as "WHERE ((crmStatusID = 2))"

how to make this to crmStatus.crmStatusID

please send a copy of your page in a zip archive.

Originally Said By: Jason Byrnes

  please send a copy of your page in a zip archive.  

ok, here i have copy and paste onto 2 words doc file in zip log-index

here is the links
index.cfm

index.cfm

it seems to works at first but when you try to do a search in "crm" section and than click on to "crm logs" links, you wil start to see the error, but it wouldn't happen if you clear the search by clicking it blank and get back to index page. pls help spended 2 long night trying to solve this out.

pleasse dont put the code into word documents, use a copy of the cfm page. only send the one file that has the problem so i can examine the code.

here are the 2 files that is giving me problems, i think something this line of code

#PreserveSingleQuotes(WADbSearch1.whereClause)#

I am not able to reproduce an error using your search page.

I have done about 30 searches on each page, and cannot get the page to error out on me. Perhaps you should empty your browsers cache.

if you following this step you wil discover the error

1) click on this link
index.cfm

2) do a simple search on the "status"

3) now, click on "CRM Logs" under the CRM menu

and you will see the error? I think it has something to do with session in cache, please help me with a solution.

hmmm, maybe in the CRM/index.cfm file,

chaneg{

if (1 EQ 1){
    WADbSearch1_SetSession = "WADbSearch1_index";
  }
}
else     {
  WADbSearch1 = FilterDef("ACCESS");
  if (1 EQ 1){
    if (IsDefined("Session.WADbSearch1_index")){
    //get session query
      WADbSearch1.whereClause = Session.WADbSearch1_index;

to:

if (1 EQ 1){
    WADbSearch1_SetSession = "WADbSearch1_crmindex";
  }
}
else     {
  WADbSearch1 = FilterDef("ACCESS");
  if (1 EQ 1){
    if (IsDefined("Session.WADbSearch1_index")){
    //get session query
      WADbSearch1.whereClause = Session.WADbSearch1_crmindex;

ok, now, we solved half the problem with your suggestion.

if I did a search in crm/logs/index.cfm and than click direct on the top menu "CRM"
it is working well,

1) search crm logs to crm menu is working

but if i m doing it the other way around it wil give me error

meaning if I did a search in crm/index.cfm and than click on the menu "CRM Logs" under the "CRM" menu. it will give me search error.

2) search crm to crm logs menu is not working

I try to change the crmlogs index; it is not working as well.

if (1 EQ 1){
WADbSearch1_SetSession = "WADbSearch1_logsindex";
}

please suggestion a solution, thx

I found the answer to my problems.

solved it with just 3 lines of code

<cfscript>
StructClear(Session);
</cfscript>

I added this to top page of my 2 indexes and it is working now.

thanks for your help anyway.