[Immortalarena]

I am working on a site where I would like to be able to sort the records by letter. I have the code able to access the database and return the results of 1 letter in specific. What I need help with is how to set up a link from a letter that would pass into the code. Here is the code I have:

[codebox]strSQL = "SELECT users.fname, users.lname, users.email, users.phone, users.cellphone, users.street, users.city, users.state, users.zip FROM users WHERE (((users.lname) Like 'M%'));"[/codebox]

The current code will send back the names with the last name starting with M, but I need that to be able to change as needed.

Any help is greatly appreciated. Thanks,

[Advertisements]

How do want to get the variable? With a form? If so try this:

strInput = Trim(Request.Form("lastname"))
strSQL = "SELECT users.fname, users.lname, users.email, users.phone, users.cellphone, users.street, users.city, users.state, users.zip FROM users WHERE (((users.lname) Like '"&strInput&"%'));"

This is very basic and doesn't take into consideration things like sql injection.

Also if the form input is a last name you would need a function to get the first letter from it otherwise it will try and find all name like the last name

IE: if the last name input in the form is "Smith" it will look for all names beginning with "Smith" like - Smith, Smiths, Smithson...

Let me know if I can help further.

[gunner12]

How do want to get the variable? With a form? If so try this:

strInput = Trim(Request.Form("lastname"))
strSQL = "SELECT users.fname, users.lname, users.email, users.phone, users.cellphone, users.street, users.city, users.state, users.zip FROM users WHERE (((users.lname) Like '"&strInput&"%'));"

This is very basic and doesn't take into consideration things like sql injection.

Also if the form input is a last name you would need a function to get the first letter from it otherwise it will try and find all name like the last name

IE: if the last name input in the form is "Smith" it will look for all names beginning with "Smith" like - Smith, Smiths, Smithson...

Let me know if I can help further.

[Immortalarena]

I was looking to have just A-Z at the top, each as a link, and then when you clicked on a letter it would return everyone in the database with the last name starting with the letter chosen. Do you know of a way to pass a letter through a link?

Also would strInput be considered a variable or is it a built in function? Thanks,


-Matt

[gunner12]

Sure.

<a href="index.asp?find=A">A</a> <a href="index.asp?find=B">B</a>  <a href="index.asp?find=C">C</a>... 
<%
strInput = Trim(Request.Querystring("find"))
strSQL = "SELECT users.fname, users.lname, users.email, users.phone, users.cellphone, users.street, users.city, users.state, users.zip FROM users WHERE (((LCASE(users.lname)) Like '"&Lcase(strInput)&"%'));"
%>

Something like that will work. Notice I converted everythng to lowercase also otherwise it would only find things in uppercase

[Immortalarena]

That's PERFECT!!!! Thanks, that helps out a lot!

Thanks,

-Matt

[gunner12]

My pleasure!

[Immortalarena]

Similar situation, only this time I am trying to return an entry that is equal to the ID Number that will be passed. I used the same setup, but I feel I have done something wrong. Any help is appreciated:

strInput = Trim(Request.Querystring("food"))
strSQL = "SELECT stock.stock_ID FROM stock WHERE (((stock.stock_ID) Like '"&strInput)&"%'));"

error received:

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/stockedit.asp, line 24, column 84
strSQL = "SELECT stock.stock_ID FROM stock WHERE (((stock.stock_ID) Like '"&strInput)&"%'));"

It may be some of my other code too, but if there is a better way to do this let me know Thanks,
-Matt

[Ax238]

Matt,

You are correct, there is something wrong with the SQL. While you correctly took out the Lcase(, you didn't take out the end parentheses for the function. Furthermore, since you are looking for an ID (I'll take a guess that it's a number), you won't need to use a LIKE predicate.

This might work better for you:

strInput = Trim(Request.Querystring("food"))
strSQL = "SELECT stock.stock_ID FROM stock WHERE stock.stock_ID = " & strInput & ";"

If it's not a number, just put single quotes around the value:
= '" & strInput & "';"

To reiterate gunner12, no security measures have been put in to eliminate the potential for SQL injection.

Regards,

Ax

[Immortalarena]

Thanks, that works a lot better!

[Immortalarena]

Ok I have another ASP Code issue, I am trying to pass a User ID Variable through the sessions management, but the variable is staying empty. Do I need to clarify some code information here?

SQL = "Select * From users Where email = '" & email & "' And password = '" & password & "'" 
Session("uid") = userid

I am trying to get the session variable "uid" to be equal to the users.userid number
users is the Access Table, and userid is the numeric value in that field.

Any help is appreciated! Thanks,

[Ax238]

I don't see where you are getting the recordset for the SQL statement or setting userid to a field in the recordset. Just setting the SQL statement isn't going to automatically fill the userid variable.

[Immortalarena]

yeah, I'm not sure either, but I'm working on some new code, thanks for the response!

[Ax238]

You'll probably be doing something like the following:

Set objCon = Server.CreateObject("ADODB.Connection")
Set objRec = Server.CreateObject("ADODB.Recordset")
objCon.Open "YourOwnConnection", "", ""
SQL = "SELECT * FROM users WHERE email = '" & email & "' AND password = '" & password & "'"
objRec.Open SQL, objCon
If Not objRec.EOF Then
	Session("uid") = objRec("userid")
	' do whatever else you need to here
End If
objRec.Close
Set objRec = Nothing
objCon.Close
Set objCon = Nothing

This might help with the connection:
ASP Access Database Connection

Ax

[Immortalarena]

Thanks, that helped a lot!

[Ax238]

No problem, glad I could be of assistance.