cdemott33
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
there's a great article here on EE that covers this - https://www.experts-exchange.com/articles/3626/ASP-Classic-Using-Parameterized-Queries.html
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.
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
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
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
ASKER
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!
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
%>
ASKER
Good news! I think I figured out why that error is occurring. I hope.... Stand by!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
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 = ?"
Once i did the the code executed perfectly. Thank you!
Big Monty... you were correct. I had to rename the include file to adovbs.asp
Example:
<!--#include file="adovbs.asp" -->
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)
Once i did the the code executed perfectly. Thank you!
ASKER
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.
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.
ASKER
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
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