Hi there

I'm having some problems with a script that is supposed to search a database with optional variables, and output mean values for the selected dataset.

Users can select any combination of 3 drop down boxes in a form. On submit, the following asp page is supposed to only show results from a query generated form this combination.

The 3 variables transfer ok, so I think there might be something weird with my querystring definitions (whereclause):

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

degree = Request.Querystring("degree")
unit = Request.Querystring("unit")
year = Request.Querystring("year")

' I have seen the error of my ways and will change this to Request.Form in the next version!

whereclause = "WHERE "

If degree >< "" Then

        whereclause = whereclause & "degree = '" & degree & "' AND "

        End if

If unit >< "" Then
        whereclause = whereclause & "unit = '" & unit & "' AND "

        End if

If year >< "" Then
        whereclause = whereclause & "year = '" & year & "' AND "

        end if

If Right(whereclause,4) = "AND " then
whereclause = Left(whereclause, Len(whereclause) -4)
end if

conn.open conn_str
strsql = "SELECT * FROM IPLUeval " & whereclause & " ORDER BY autonumber "

Set RSEVal = conn.Execute(strsql)
RSEval.Open "IPLUeval", conn_str, adOpenKeyset, adLockOptimistic, adCmdtable

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

I get an error saying:

"Syntax error in FROM clause.
/sohpnet/evaluation/ipluget.asp, line 47"

which is basically the conn.execute line.

Can anyone see anything weird here? I'm going codeblind!!

Heidi

Hi Heidi,

Can you Response.Write strsql to the page & post the output, please.

Cheers,

Chris

Hi Chris

Thanks for answering.

On a random configuration of the variables, the response.write of strsql is:

SELECT * FROM IPLUeval WHERE degree = 'BSc Podiatry' AND unit = 'IPLU 2' AND year = '2004/2005' ORDER BY autonumber

Without really having changed anything, I now get the error message

Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.
/sohpnet/evaluation/ipluget.asp, line 47

pointing to the same line as before.
Maybe I should also mention that when I do

'Set RSEVal = conn.Execute(strsql)

The page comes up partially displaying mean values for only the variables that apply to ALL records in the database, but not for the ones that belong to various units alone.

Heidi

Hi Heidi,

I assume all the fields in your where clause are string types in the db. The sql looks ok to me, so it may be a field or reserved word problem - the field name "year" might cuase problems for some dbs.

Try to isolate which part of the query is causing the issue - run it without any where clause, if that works, try it with each part of the where clause individually e.g.:
finally add the order by clause. At one of these points you should find the problem.

HTH,

Chris

I wonder if that works? Do you mean <> there?

Does YEAR column contain data in '2004/2005' format? Can you mention what datatypes are the degree, unit and year in your table?

_________________________
- Vijay G
Strive for Perfection

Hi Vijay

Sorry about the late response, things got a bit mad here!
--------------------
If degree >< "" Then

I wonder if that works? Do you mean <> there?
-----------------------------------
I've worked with >< "" (as in 'anything BUT') many other scrips that run fine, though I suspect it might be tidier with a NOT here? (Learning as I go along!)

-----------------------------------
Does YEAR column contain data in '2004/2005' format? Can you mention what datatypes are the degree, unit and year in your table?
----------------------------------

Yes, Year is '2004/2005' and will eventually be 2005/2006 etc. I've put this in as a string.

Degree and unit are strings as well.

The data for these valuables are entered through another web form, where the variables are filled from a drop down select menu, so all strings should be ok and identical (have checked this btw).

I think I might be better off scrapping the whole things and starting the page again:(

Heidi

Did this get resolved yet?