Link to home
Start Free TrialLog in
Avatar of cdemott33
cdemott33Flag for United States of America

asked on

Help with Classic ASP - Parameterizing Query

I have a classic ASP web app and need some help.  How do I change my code so I can use Parameters?

	' Get Request.Form values
	strLogin = Trim(Request.Form("txtLogin"))
	strPassword = Trim(Request.Form("txtPassword"))
	
	
	' Build SQL query
	strSQLStatement = "SELECT name, eMailAddress, rights  "
	strSQLStatement = strSQLStatement & "FROM Login "
	strSQLStatement = strSQLStatement & "WHERE userName = '" & strLogin & "' "
	strSQLStatement = strSQLStatement & "AND password = '" & strPassword & "' "

	' Connection to client DB
	Set conn = Server.CreateObject("ADODB.Connection")
	conn.open connectionStr

	' Create our recordset
	set strFilename=Server.CreateObject("ADODB.recordset")
	strFilename.Open strSQLStatement, conn

	If Not strFilename.EOF Then
		strFilename.MoveFirst

		strUserFound = "True"

		strName = strFilename.Fields("name").value
		strEmail = strFilename.Fields("eMailAddress").value
		strRights = strFilename.Fields("rights").value
	Else
		strUserFound = "False"
	End If 

	' Close our connection
	strFilename.Close
	Set strFilename = Nothing

Open in new window

Avatar of Big Monty
Big Monty
Flag of United States of America image

Avatar of cdemott33

ASKER

I've read that article and unfortunately, I'm not able to implement the code examples and get them to work.
@Big Monty
Thanks for pointing to my article.

@cdemott33
What do you need help with understanding?
If you can let us know exactly what you need help with, we can assist you better.
It's a good article that explains the concept clearly :)

Show what you've attempted and we can show you where you may have gone wrong. We're here to help you, not write name new code for you
I have a mockup for you, but like Big Monty pointed out.
You need to at least show us what you have tried.
And TRUST me.
The is never bad code, there are only people that do not want to learn the right way of doing things.
You are here because you want to learn.
So, show us what you have.
And we will go from there.

Wayne
Sorry.  I should have posted this with my original request.  

So i took my code above and tried to rewrite it based on the EE article you posted above.  When I try and run the code I get this error:

"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.  postlogin.asp, line 82"

Line 82 point to cmd.CommandType = adCmdText

I thinking I have something in the wrong place perhaps.  You're help is appreciated.  Thanks!

' Get Request.Form values
	strLogin = Trim(Request.Form("txtLogin"))
	strPassword = Trim(Request.Form("txtPassword"))


set strFilename=Server.CreateObject("ADODB.recordset")
	Set cmd = Server.CreateObject("ADODB.Command")
	
	Set conn = Server.CreateObject("ADODB.Connection")
	conn.open connectionStr
	
	cmd.ActiveConnection = conn
	
	' Build SQL query
	cmd.CommandText = 	"SELECT ID, name, eMailAddress, rights, webadmin, updatedPassword " & _
						"FROM Login " & _
						"WHERE userName = @userName " & _
						"AND password = @password " & _
						"AND active = 'yes' "
	cmd.CommandType = adCmdText
	cmd.CommandTimeout = 900
	
	cmd.Parameters.Append cmd.CreateParameter("@userName", adVarChar, adParamInput, 50, strLogin)
	cmd.Parameters.Append cmd.CreateParameter("@password", adVarChar, adParamInput, 50, strPassword)	
	
	strFilename.CursorLocation = adUseClient
	strFilename.Open cmd, adOpenForwardOnly, adLockReadOnly
	
	If Not strFilename.EOF Then
		strFilename.MoveFirst
		
		strUserFound = "True"
		
		strID = strFilename.Fields("ID").value
		strName = strFilename.Fields("name").value
		strEmail = strFilename.Fields("eMailAddress").value
		strRights = strFilename.Fields("rights").value	
		strWebAdmin = strFilename.Fields("webadmin").value	
		strUpdatedPassword = strFilename.Fields("updatedPassword").value		
	Else
		strUserFound = "False"
	End If 
		
	' Close our connection
	strFilename.Close
	Set strFilename = Nothing

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Wayne Barron
Wayne Barron
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for your help Wayne!  My updated code is below.  When I run this code I get the following error:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.  postlogin.asp, line 30

Line 30 point here:

set rsUser = sqlUser.execute

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="ssi/x_str_inc/openConn.asp" -->
<%
		

' I would put the connection string in its own file an call it with an <!--#INCLUDE FILE="ACN.asp"-->
' If you are using ACCESS database, just use it, with the CONN name.

Set Conn = CreateObject("ADODB.Connection")
Conn.ConnectionString = connectionStr
Conn.Open


strLogin = Trim(Request.Form("txtLogin"))
strPassword = Trim(Request.Form("txtPassword"))


'Get the ADOVBS.INC file, to make this easier to work with.
'200 = adVarChar
'1 = adParamInput

<!--#include file="adovbs.inc" -->

Set sqlUser = Server.CreateObject("ADODB.Command")
sqlUser.ActiveConnection=Conn
sqlUser.Prepared = true
sqlUser.commandtext="Select username, eMailAddress, rights FROM Login where userName =? and password "
sqlUser.Parameters.Append sqlUser.CreateParameter("@Username", 200, 1, 25, strLogin) 
sqlUser.Parameters.Append sqlUser.CreateParameter("@Password", 200, 1, 25, strPassword) 
set rsUser = sqlUser.execute

if rsUser.eof then
response.write "sorry, the information you provided, does not match a user in our database!"
else
Username = rsUser("username")
email = rsUser("eMailAddress")
theRights = rsUser("rights")
end if

%>

<table>
<tr><td>Usename</td><td>email</td><td>rights</td></tr>
<tr><td><%=Username%></td><td><%=email%></td><td><%=theRights%></td></tr>
</table>

<%
rsUser.close
set rsUser = Nothing
Conn.close
set Conn = Nothing
%>

Open in new window

Good news!   I think I figured out why that error is occurring.  I hope....   Stand by!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Big Monty and Wayne, Thank you both for your help this evening.  

Big Monty... you were correct.  I had to rename the include file to adovbs.asp

Example:

<!--#include file="adovbs.asp" -->

Open in new window


I also had to make on minor edit to the SELECT statement and add the "= ?" to the password column

"...FROM Login where userName =? and password = ?"

sqlUser.commandtext="Select username, eMailAddress, rights FROM Login where userName =? and password = ? "
sqlUser.Parameters.Append sqlUser.CreateParameter("@Username", 200, 1, 50, strLogin) 
sqlUser.Parameters.Append sqlUser.CreateParameter("@Password", 200, 1, 50, strPassword) 

Open in new window


Once i did the the code executed perfectly.  Thank you!
Once again the help on EE is wonderful.  I really thank you both for your help this evening.
Opps.
That was my mistake.
But glad that you was able to troubleshoot it, and resolve the? mark.

If interested in learning more in ASP Classic. Let me know.
I have a website dedicated to ASP classic and other scripting languages.
Hey Wayne - I'd love to check out your site.  Please send a link when you can.
Hey, cdemott33.
Sure. It is http://www.cffcs.com
I am in the process of planning a total site redesign.
Looking at late May for that to be released.

Have a good one.
Wayne