Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

ASP Coding help


  • Please log in to reply

#1
Immortalarena

Immortalarena

    Member

  • Member
  • PipPipPip
  • 211 posts
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, :)
  • 0

Advertisements


#2
gunner12

gunner12

    Member

  • Member
  • PipPip
  • 46 posts
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.
  • 0

#3
Immortalarena

Immortalarena

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 211 posts
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
  • 0

#4
gunner12

gunner12

    Member

  • Member
  • PipPip
  • 46 posts
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
  • 0

#5
Immortalarena

Immortalarena

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 211 posts
That's PERFECT!!!! Thanks, that helps out a lot! :)

Thanks, :)

-Matt
  • 0

#6
gunner12

gunner12

    Member

  • Member
  • PipPip
  • 46 posts
My pleasure! :)
  • 0

#7
Immortalarena

Immortalarena

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 211 posts
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
  • 0

#8
Ax238

Ax238

    Tech Staff

  • Technician
  • 1,323 posts
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
  • 0

#9
Immortalarena

Immortalarena

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 211 posts
Thanks, that works a lot better!

:)
  • 0

#10
Immortalarena

Immortalarena

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 211 posts
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,
  • 0

#11
Ax238

Ax238

    Tech Staff

  • Technician
  • 1,323 posts
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.
  • 0

#12
Immortalarena

Immortalarena

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 211 posts
yeah, I'm not sure either, but I'm working on some new code, thanks for the response!
  • 0

#13
Ax238

Ax238

    Tech Staff

  • Technician
  • 1,323 posts
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
  • 0

#14
Immortalarena

Immortalarena

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 211 posts
Thanks, that helped a lot!

:)
  • 0

#15
Ax238

Ax238

    Tech Staff

  • Technician
  • 1,323 posts
:) No problem, glad I could be of assistance.
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP