...

View Full Version : Problem with WHERE Clause



Jacob-Bushnell
11-19-2003, 07:32 PM
Hi all, I have a rather strange problem with the WHERE clause of my SQL statement.
This is what I have right now:

sqlstmt = "SELECT * FROM TblItems WHERE MedTyp='Book'"

For some reason it returns ALL the records in that table.

TblItems of course is the name of the table and MedTyp is the name of the field.
For the record, I DO have records matching the query :)

(Unfourtantly) Right now I am using an Access DB. I created a query within the DB that worked just fine. I tried using that SQL statement insted of what I have now but it did the same thing.

I response.writed the SQL statement to my page and it seemed to look fine.

After all this it still returns ALL the records!

Any one know what I am doing wrong?
I have attached a copy of my page.
Thanks a lot, Jacob

Roy Sinclair
11-19-2003, 07:59 PM
Your attachment went awol.

Jacob-Bushnell
11-19-2003, 08:40 PM
Woops sorry about that- my sattalit got too much rain on it just as I sent it!
Here you are

A1ien51
11-19-2003, 08:53 PM
try

sqlstmt = "SELECT * FROM TblItems WHERE MedTyp like 'Book'"

Jacob-Bushnell
11-20-2003, 01:45 AM
Hmm- that was a good idea but unfournatly it does the same thing.
All the records are still returned. :confused:

glenngv
11-20-2003, 06:08 AM
Are you really sure some records have MedTyp other than 'Book'?

Jacob-Bushnell
11-20-2003, 05:43 PM
<VERY POSITIVE AWNSER> I AM SURE </VERY PSITIVE AWNSER>
:D

I have attached a copy of my DB if any one wanted to see it. (it's pretty small)

A1ien51
11-20-2003, 07:22 PM
I ran the query and I only get the records with book in it.

Eric

angiras
11-20-2003, 07:49 PM
SELECT TblItems.*, TblItems.MedTyp
FROM TblItems
WHERE (((TblItems.MedTyp)="book"));


I get only the book


SELECT TblItems.*, TblItems.MedTyp
FROM TblItems
WHERE (((TblItems.MedTyp) Like "book"));


I get only the books

I get only the books ... I get only the books ... I get only the books ... I get only the books ... I get only the books ... I get only the books ... I get only the books ... I get only the books ... I get only the books ... I get only the books ... I get only the books ... I get only the books ... I get only the books ... I get only the books

:-))

Jacob-Bushnell
11-21-2003, 01:01 AM
Very interesting! Could this be a problem with my PWS?

angiras
11-21-2003, 07:14 AM
I cannot omagine rgat you don't get the same... then really I have no idea


have you tried to run this command directly into your database ?

or try it as a storedProcedure


SELECT TblItems.*, TblItems.MedTyp
FROM TblItems
WHERE (((TblItems.MedTyp)=[@item]));

Jacob-Bushnell
11-21-2003, 05:38 PM
Yes, I ran a query on my DB in access and it worked fine. I ran a sub procedure (I think that the same thing :) ) and it returned ALL the records.

This seems strange. I tried to put it on some shard server space to test it there but I havent been able to get it to read the DB path yet :(

I will keep trying that and also try to make some other querys for different DB. I am not very experenced with this so it may take me some time!

Jacob-Bushnell
11-22-2003, 12:31 AM
Well I ran a copy on my Shared Server and it did the same thing... Looks like I have problem somwhere :rolleyes:

I apreiciate you guys sticking with me.
I attached a copy of my included file that ha all my stored procedures on it for any one to take a look at if the want.

Thanks,

M@rco
11-22-2003, 04:06 AM
To be honest, this is such a *basic* query that the problem must either be something very freaky, or something rather obvious.

Given that you are using Access, I suspect that replacing the single quotes with double quotes (the correct syntax for a string in JET SQL) will solve it:
sqlstmt = "SELECT * FROM TblItems WHERE MedTyp=""Book"""I'd like to add that using SELECT * is considered *very* bad practice, even if you DO want all the fields returned. I once wrote a rather lengthy explanation on why not, but can't find the post, so read this instead (which is probably better anyway):
http://www.adopenstatic.com/faq/selectstarisbad.asp

Jacob-Bushnell
11-24-2003, 06:50 PM
I agree with you about it being eithier freaky or basic and judging by my VERY limited experence it is probably the latter!

I tried changing the single quotes to doubles but got an error message saying:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

I am rather young and inexperienced as you can probably tell by my programing! I have posted a new copy of my code with the other lines I have tried commented out.

I hope somone can spot this "invisble" error somwhere!
Thanks, Jake

Jacob-Bushnell
11-24-2003, 07:10 PM
Oops here's the atachment.

M@rco
11-24-2003, 07:24 PM
Are you sure there really is a filed called Medtyp?

Ignore ASP for the moment and create a new query in Access itself, using the query diagram thingy so you don't get anything wrong or do anything invalid. Once that works, then switch to SQL view stick it in your ASP.

Jacob-Bushnell
11-24-2003, 07:35 PM
Ok: Yes, there is a field called MedTyp (The T is in caps).

The attachment I posted is from the actual SQL builder in Access.

Just adding it in after sqlstmt= did not work.
I had to add quotes and change the doubbles to singles... which might be my problem. But it wont work by itself for some reason.

Oh, and it did work in access it self by the way :)

Here is what it looked like:

sqlstmt = "SELECT TblItems.ID, TblItems.TITLE, TblItems.CONDITION, TblItems.PRICESELL, TblItems.DESCRIPTION, TblItems.MedTyp, TblItems.STATUS, TblItems.DATELIST, TblItems.QUANTITY
FROM TblItems
WHERE (((TblItems.MedTyp)="Book"));"

Which gave me:

Expected end of statement
/nathhan.org/resource_room/Usedbooks/searchbook.asp, line 19, column 221

sqlstmt = "SELECT TblItems.ID, TblItems.TITLE, TblItems.CONDITION, TblItems.PRICESELL, TblItems.DESCRIPTION, TblItems.MedTyp, TblItems.STATUS, TblItems.DATELIST, TblItems.QUANTITY FROM TblItems WHERE (((TblItems.MedTyp)="Book"));"

Thanks

M@rco
11-24-2003, 08:14 PM
You forgot to double the quotes inside the string...

sqlstmt = "SELECT TblItems.ID, TblItems.TITLE, TblItems.CONDITION, TblItems.PRICESELL, TblItems.DESCRIPTION, TblItems.MedTyp, TblItems.STATUS, TblItems.DATELIST, TblItems.QUANTITY FROM TblItems WHERE (((TblItems.MedTyp)=""Book""));"

Jacob-Bushnell
11-24-2003, 08:22 PM
Ok, I put that in and it gave me this error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/nathhan.org/resource_room/Usedbooks/searchbook.asp, line 21

:(

M@rco
11-24-2003, 11:44 PM
Hmmm....

And this doesn't work either?:
sqlstmt = "SELECT TblItems.ID, TblItems.TITLE, TblItems.CONDITION, TblItems.PRICESELL, TblItems.DESCRIPTION, TblItems.MedTyp, TblItems.STATUS, TblItems.DATELIST, TblItems.QUANTITY FROM TblItems WHERE (((TblItems.MedTyp)='Book'));"

I have noticed that you're using the old-fashioned OLEDB for ODBC driver... you should really be using the JET4 OLEDB driver... but let's not worry about that for the moment...

Jacob-Bushnell
11-25-2003, 04:41 PM
Yes and no, It runs just fine but again returns all the records.

M@rco
11-25-2003, 05:00 PM
So you're saying that there are records in the resulting recordset with a MedTyp value of something other than Book? That's just plain impossible!!

Jacob-Bushnell
11-25-2003, 05:43 PM
I Know! Thats's why I am here. I canot figure it out. I have other SQL statments that work just fine. It seems it should be somthing glareingly obvious but I am new to programing and just can't see it.

I tried changing the field I am querying and it did the same thing. Now I am making a new DB and am going to try and see if I can get a SQL statment to work with it.

Any more Ideas? :confused:

Jacob-Bushnell
11-25-2003, 06:29 PM
Ok, I just finshed creating a test DB and another page with an SQL statement. I woks perfectly so that elimanates any thing mechanicly wrong with my PWS.
Now the only thing left is my poor coding practice! :D
I think I will try to rewrite the whole thing or somthing like that.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum