Need Help with WHERE and ORDER BY in code. New to ASP

[alloyd1]

I keep erroring out and do not know enough. A buddy helped me with the majority of code and it works perfect if I keep the statements like this:

RS.Open "Select * From "&tableName&"" &"", DB, 1, 3

I made some changes and below trying to use a where and order by and pooooof... does not work and I need someone to give me a hand please.

<%
		Dim tableName, linkTo, imgSize2W, imgSize2H
			tableName = "tbl_deluz"
			imgSize2H = "110"
			imgSize2W = "150"
			
	
		Dim strconn
			strconn="PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & server.MapPath("data/data.mdb")
		
		Dim db
			set db = Server.CreateObject("ADODB.Connection")
			db.open strconn
		
		Dim strOrderBy
		strOrderBy = "MLSNum"
		
		Dim strCriteriaBy
		strCriteriaBy = "Area"
	
			Set RS = server.CreateObject("ADODB.Recordset")
					RS.Open "Select * From "&tableName&"" &"", DB, Where ""&strCriteriaBy&" = 202"  OREDER BY "&StrOrderBy&" ASC, 1, 3				
%>

Respectfully,
Arlan

[ilearneditonline]

[CODE=asp]"Select * From "&tableName &"" &"", DB, Where ""&strCriteriaB y&" = 202" OREDER BY "&StrOrderB y&" ASC, 1, 3 [/CODE]

Microsoft Link
The main problem i see is that you were adding your WHERE and ORDER BY clauses after the ActiveConnectio n oject. Plus you spelled ORDER incorrectly.

[CODE=asp]
SELECT * FROM "&tableName &" WHERE "&strCriteriaBy &"=202 ORDER BY "&StrOrderB y, DB, 1, 3
[/CODE]

[jhardman]

Arlan,

Correct syntax looks like this:

RS.Open "Select * From " & tableName & " Where "&strCriteriaBy&" = 202 ORDER BY "&StrOrderBy&" ASC",DB, 1, 3

The reason this is confusing is that we query databases with a different language (SQL) which is supposed to be understood by just about every db. When you write this line you are saying
1- open a recordset

RS.open

2- these are the parameters I want

"SELECT * FROM mydbTable WHERE userID = 202 ORDER BY lastName ASC"

3- This is the db I want

DB

4- Use a particular type of cursor and locktype so that two people can't try to update the db at the same time

1,3

Notice the second part which is called a database query. This is just a long string written in almost passable English which the db should be able to understand. Sometimes it is useful to save this string to a different variable like this:
[code=asp]query = "Select * From " & tableName
query = query & " Where "&strCriteriaBy &" = 202"
query = query & " ORDER BY "&StrOrderB y&" ASC"
response.write query 'write out the query for troubleshooting

RS.open query, db, 1, 3
[/code]The troubleshooting line is useful because you can test it in some database managers or at least read it through to see if it makes sense.

Now look back at your original query. This part of it makes sense:

"Select * From "&tableName

but after this you add to it two blank strings:

&"" &""

This won't make the query break, but it might confuse you as you try to count quote marks to see if you are within a string or not, and it definitely confused you a bit as you tried to figure out how the query is supposed to be worded. Anyway, Let me know if this helps.

Jared

[jhardman]

Microsoft Link
The main problem i see is that you were adding your WHERE and ORDER BY clauses after the ActiveConnectio n oject. Plus you spelled ORDER incorrectly.

[CODE=asp]
SELECT * FROM "&tableName &" WHERE "&strCriteriaBy &"=202 ORDER BY "&StrOrderB y, DB, 1, 3
[/CODE]

Jinx!
Jared

[alloyd1]

Jinx!
Jared

Thank you Jared for Spelling it out... that made complete sense and now it does exactly what I wanted. I do have one question.

My field I used for my where statement.

query = query & " Where "&strCriteriaBy&" = 202"

I had to change the field type from Text to Single to get this to work. Some of those are suppose to be numeric and others are text. Can this be corrected?

Again.... thank you for helping me out.

[jhardman]

Thank you Jared for Spelling it out... that made complete sense and now it does exactly what I wanted. I do have one question.

My field I used for my where statement.

query = query & " Where "&strCriteriaBy&" = 202"

I had to change the field type from Text to Single to get this to work. Some of those are suppose to be numeric and others are text. Can this be corrected?

Again.... thank you for helping me out.

right, for numeric values the value is not supposed to be in quotes, for strings the value is supposed to be in single quotes: "WHERE lastName = 'Hardman'" and for dates the value is supposed to be in hash marks: "date = #3/11/07#". Does this answer your question? You can look up SQL syntax on google for more info, I've mostly just picked it up as I go.

Jared

[alloyd1]

Totally answered my question. You are awesome and thank you for helping me out.

Have a wonderful day and I hope somehow I can return the favor one day.

Arlan

[ilearneditonline]

You don't really need to add the ASC as that is the default. Unless you were doing something like ORDER BY date DESC, id ASC