Code stopped working - URGENT, please!!!!!!!! 1

[siena]

	putor = 0
	strSQL = "SELECT * FROM trafficCountsFiles "
	If advanceSearch = "ON" Then
	strSQL = strSQL & " where FileName <> 'z' "
		If selSiteCode <> "" or selKeyword <> "" Then
			strSQL = strSQL & " and ( "
			If selSiteCode <> "" Then
				strSQL = strSQL & " siteCode LIKE '%" & selSiteCode & "%'   "
				putor = 1
			End If
			If selKeyword <> "" and putor = 1  Then
				strSQL = strSQL & " OR FileName LIKE '%" & selKeyword & "%'   "
			End If
			If selKeyword <> "" and putor = 0  Then
				strSQL = strSQL & " FileName LIKE '%" & selKeyword & "%'   "
			End If
		  	strSQL = strSQL & " )"
		end if
	End If
	strSQL = strSQL & " ORDER BY siteCode ASC;"

The code above was working perfectly with only 2 fields and one table(Simflex and Andrew helped me with part of the code yesterday).
For the last 2 hours since I joined to another table so I could display more fields, it isn't working anymore.
I have only 6 records on one table and 30 on any table.
Now when I run this code, it says 150 records displayed.
I know there is something seriously wrong the code below.
The below code is one with the join to another table.
Any urgent help would be greatly appreciated.

	putor = 0
	strSQL = "SELECT * " & _
	         "From fctrafic,trafficCountsFiles "
	         If advanceSearch = "ON" Then
	strSQL = strSQL & " where FileName <> 'z' "
		If selSiteCode <> "" or selKeyword <> "" Then
			strSQL = strSQL & " and ( "
			If selSiteCode <> "" Then
				strSQL = strSQL & " fctrafic.siteCode = trafficCountsFiles.SiteCode and siteCode LIKE '%" & selSiteCode & "%'   "
				putor = 1
			End If
		    If selSiteCode <>"" and putor = 1 Then
		    strSQL = strSQL & "fctrafic.siteCode = trafficCountsFiles.SiteCode "
		    End If
			If selKeyword <> "" and putor = 1  Then
				strSQL = strSQL & " OR FileName LIKE '%" & selKeyword & "%'   "
			End If
			If selKeyword <> "" and putor = 0  Then
				strSQL = strSQL & " FileName LIKE '%" & selKeyword & "%'   "
			End If
		  	strSQL = strSQL & " fctrafic.siteCode = trafficCountsFiles.SiteCode "
		  	strSQL = strSQL & ")"
		end if
	End If
	strSQL = strSQL & " ORDER BY siteCode ASC;

 

[simonchristieis]

make it easier on everyone.

response.write the code and we can all see whats going on.

 

[siena]

Well,
I am surprised by what I found.

If I do a response.write on the first one (the one that works), I get this:

SELECT * FROM trafficCountsFiles ORDER BY siteCode ASC;

If I do same response.write on the one that doesn't work, I get this
SELECT * From fctrafic,trafficCountsFiles ORDER BY siteCode ASC;

Oh boy, the where clause is not working.
I am bushed now!

 

[vongrunt]

Here is a little bit less jumpy version of code:

putor = 0
strSQL = "SELECT * " & _
         "From fctrafic,trafficCountsFiles "
If advanceSearch = "ON" Then
	strSQL = strSQL & " where FileName <> 'z' "
	If selSiteCode <> "" or selKeyword <> "" Then
		strSQL = strSQL & " and ( "
		If selSiteCode <> "" Then
			strSQL = strSQL & " fctrafic.siteCode = trafficCountsFiles.SiteCode and siteCode LIKE '%" & selSiteCode & "%'   "
			[COLOR=red]strSQL = strSQL & "fctrafic.siteCode = trafficCountsFiles.SiteCode "[/color]
			putor = 1
		End If

		If selKeyword <> "" Then
			If putor = 1  Then strSQL = strSQL & " OR "
			strSQL = strSQL & " FileName LIKE '%" & selKeyword & "%'   "
		End If
		
		strSQL = strSQL & " fctrafic.siteCode = trafficCountsFiles.SiteCode "
		strSQL = strSQL & ")"
    end if
End If
strSQL = strSQL & " ORDER BY siteCode ASC;

It seems like line in red is redundant... and this expression can be probably moved into INNER JOIN between tables.
What do you want to get when advanced search is turned off?

 

[siena]

Thank you vongrunt and I am sorry that I had to leave.
I got very frustrated and had to leave.

To answer you question about what I want to get when the advanced option is turned off, the advanced option is like using a wildcard search.
Once it is on, all records are listed.
When it is turne off, the user is given the ability to search for a specific record based on a given criterium.
For instance, to use basic search (which means advanced is turned off), the user can enter a site code or filename for a specific record.

Thanks again for the help.

I will let you know after testing.

 

[siena]

hi again,
when I do advanced search, it works beautifully but when I turn off the advanced search to perform a basic search, it gives the following error:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'FileName <> 'z' and ( fctrafic.siteCode = trafficCountsFiles.SiteCode and siteCode LIKE '%107231%' fctrafic.siteCode = trafficCountsFiles.SiteCode fctrafic.siteCode = trafficCountsFiles.SiteCode )'.
/TrafficCounts/countSearch.asp, line 355

 

[siena]

Correction!
I mis-interpreted my own code.
If advanced search option is "ON" then a user searches with a filter.
In other words, a user searches by sitecode or filename.
If advanced search option is "OFF", the user performs a wildcard (%) search.
I think I need to rest my brain for the rest of the night.

 

[vongrunt]

Argh... some questions:

- does "Filename <> 'z'" apply to both advanced and normal search?
- read red line again - it is actually redundant two times. Does this condition apply to both searches too?

You also mentioned wildcard search when adv. search is off... this branch of code is completely missing. In that case, field to search with LIKE % is... ?

 

[siena]

Actually, what is working is when advance search is off.
When advance search is off, it is like:

select * from table.

if advanced search is on, it is like select * from table where filename like....
or sitecode like ....

I don't think it will hurt anything whether
Filename <> 'z'" applies to both advanced and normal search.

Well, you know better than I do, obviously.

Ok, I have removed the redundant code but now,
I am getting:
error: Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters

Here is the version I got from you:

   strSQL = "SELECT fctrafic.*,trafficCountsFiles.filename " & _
         "From fctrafic,trafficCountsFiles "
   If advanceSearch = "ON" Then
    strSQL = strSQL & " where FileName <> 'z' "
    If selSiteCode <> "" or selKeyword <> "" Then
        strSQL = strSQL & " and ( "
        If selSiteCode <> "" Then
            strSQL = strSQL & " fctrafic.siteCode = trafficCountsFiles.SiteCode and siteCode LIKE '%" & selSiteCode & "%'   "
            strSQL = strSQL & "fctrafic.siteCode = trafficCountsFiles.SiteCode "
            putor = 1
        End If

        If selKeyword <> "" Then
            If putor = 1  Then strSQL = strSQL & " OR "
            strSQL = strSQL & " FileName LIKE '%" & selKeyword & "%'   "
        End If
        strSQL = strSQL & ")"
    end if
  End If
  strSQL = strSQL & " ORDER BY fctrafic.siteCode ASC; "

I also tried:
select individual records like:

select fctrafic.sitecode,fdate,loc,loc2,trafficcountsfiles.filename but I still get same error.

 

[simonchristieis]

Have you tried response.write ing the advanced code and pasting into query in access?

Also bear in mind that where clauses and inner join have to be delimited by () otherwise access starts crying.

 

[siena]

Let me just give you guys a visual stimulation of what I have been trying to do.

I have done this several times with this approach.
The only difference between what I am doing now and what I have done before is that this is the first time I am using multiple tables.
In the past, it has always been a one-table query.
I honestly didn't think that would have made the difference that it did.
Second, if I took out all the fields with the exception of siteCode and filename, it works really good.
So I am not really sure why additional fields would make the code stop working.

Take a look at this link:

http://www26.brinkster.com/simflex/kenig/nameSearch.asp

I could have used a different screen but this is what management wanted to use.

 

[mbiro]

If you are trying to do the same select statement on two tables, why not just run the sql statement twice (once for one table then again for the other table) and then write out the results of the two recordsets on the same page? Is it imperative that there only be one call to the database?

 

[CassidyHunt]

I know that when I use ORDER BY and two tables, at least in Visual Basic, sometimes it makes me list all the fields and tables in the order by which I want them sorted. IE

Select table1.field1, table2.field2,table3.field3 from table1,table2,table3 order by table2.field2,table1.field1,table3.field3

You can try that and see if it helps.

 

[siena]

well, when I say "the code stopped working",
I mean that when I use " select fctrafic.sitecode, filename from table1, table2 where table1.sitecode = table2.sitecode, it runs good but when I add additional fields, it displays a cartesian product.
Isn't this weird?

 

[mbiro]

SELECT Table1.field1, Table1.field2, table2.field1, table2.field2
FROM Table1 INNER JOIN table2 ON Table1.sitecode = table2.sitecode

 

[vongrunt]

If my babelfish didn't went on vacation... :

putor = 0
strSQL = "SELECT fctrafic.*,trafficCountsFiles.filename " & _
         "From fctrafic,trafficCountsFiles " & _
         "where FileName <> 'z' and fctrafic.siteCode = trafficCountsFiles.SiteCode " 
If advanceSearch = "ON" Then
	If selSiteCode <> "" or selKeyword <> "" Then
		strSQL = strSQL & " and ( "
		If selSiteCode <> "" Then
		    strSQL = strSQL & "siteCode LIKE '%" & selSiteCode & "%' "
		    putor = 1
		End If
		If selKeyword <> "" Then
		    If putor = 1  Then strSQL = strSQL & " OR "
		    strSQL = strSQL & " FileName LIKE '%" & selKeyword & "%'   "
		End If
		strSQL = strSQL & ")"
    End If
End If
strSQL = strSQL & " ORDER BY fctrafic.siteCode ASC; "

 

[siena]

It worked!!!!!!!!!!

Thank you and 2 stars for you and your persistence.
You saved me and I thank you very, very much!!!

I also want to thank everyone who contributed their time and feedback.