ADO, ASP and Access DataBase

Cannot get the following code to work
I have my database object (CNDB) open but it crashes when attempting to create recordset

Whats wrong with the following??

              
set rsPasswords = cnDB.Execute("select * from Passwords where UserID ='"  & stUserId & "'")

Open in new window


I even simplified it to use hard coded UserID as follows and still crashes
set rsPasswords = cnDB.Execute("select * from Passwords where UserID ='LEE'")

Open in new window


Decided to go further and eliminate the WHERE clause using following and this does work and lists all UserID's including LEE.

set rsPasswords = cnDB.Execute("select * from Passwords")
do while not rspasswords.eof
      response.write rspasswords("UserID")
      Response.write "<br>"
      rspasswords.movenext
loop

Open in new window


This is so simple but I can't get the Where Clause to work -

Need Help...
Thanks
trims30Asked:
Who is Participating?
 
Wayne BarronAuthor, Web DeveloperCommented:
Then that could be your issue.
You need to look at the difference between the 2 versions of IIS.
As there is a LOT of differences between the 2 versions, and a lot of changes that need to take place, and a lot of asp classic codes, no longer work with the new structure, as a lot of people have been finding out.
I however, am not sure if your code is one of them, maybe someone with that type of coding knowledge, with IIS7 can come in and assist you further.

Good Luck
Carrzkiss
0
 
esolveSoftware Development ManagerCommented:
Why don't you use parameters? Your current code allows for sql injection.

http://msdn.microsoft.com/en-us/library/windows/desktop/ms675101(v=vs.85).aspx


In the example above are you sure the UserID is of type string/varchar? If UserID is a number/int it will definitely not work.

Regards
0
 
Dave BaldwinFixer of ProblemsCommented:
That's a good link because it shows all the things that have to be set up in order to work that way.  These pages http://www.w3schools.com/ado/default.asp show a simpler way to go about it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Wayne BarronAuthor, Web DeveloperCommented:
You can check this out.
http://www.cffcs.com/Tutorials/carrzkiss/39/Q_24801116.asp
Code download from here, FREE site, just register and download the code.
http://www.cffcs.com/Main.asp?Entry=39

This project was designed specifically for EE and PSC.

Good Luck
Carrzkiss
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If this is Access, then you don't really need to worry with injection attacks. Access cannot process multiple query statements in the same call like a server database can.

If this works:

"select * from Passwords"

but this does not:

"select * from Passwords where UserID ='LEE'"

then I'd suspect your column names are incorrect.  Can you verify the name of your columns in teh Passwords table?

Also, when you say "crashes" what exactly do you mean?

Can you show how you build the cnDB Object?
0
 
trims30Author Commented:
LSM:

Here's how I create CNDB
'----- Connect to DataBase ----------------
	Dim cnDB       'ADO Connection
	Set cnDB=Server.CreateoBJECT("ADODB.Connection")
	cnDB.Open "c:\custweb\data\CustomerList.MDB"
		

Open in new window


Again, in the example of the select without Where clause I loop through data and display UserID values successfully so we know field name is correct.

Crash is caused by following code since rspasswords object failed to be created.

'----- Close DataBase Files ----------------
	rsPasswords.close
	set rsPasswords = nothing
	cnDB.close
	set cnDB = nothing

Open in new window

0
 
Wayne BarronAuthor, Web DeveloperCommented:
@LSMConsulting
Regardless of rather Access is like Servers or not, using Parameters, and good coding skills is a must. Using the example that I posted the link for, shows just that. Good coding skills.
 
Doing this type of coding
"select * from Passwords where UserID ='LEE'"

Is bad coding skills, and should be written out like so.
Which is the way it should be demonstrated across the board.
It is cleaner, and helps the coder to debug their own code.

<%
getID = protectSQL(request.form("username"))
Set sqlGet = CreateObject("ADODB.Command")
sqlGet.ActiveConnection=objConn
sqlGet.Prepared = true
sqlGet.commandtext = "select UserID from Passwords where UserID =?"
sqlGet.Parameters.Append sqlGet.CreateParameter("@UserID", adVarchar, adParamInput, 50, getID)
set rsGetID = sqlGet.execute
if not rsGetID.eof then
response.Write rsGetID("UserID")
else
response.Write "Sorry, that record does not exist"
end if
%>

Open in new window


Carrzkiss
0
 
trims30Author Commented:
Bad coding or not, this ASP project has been running on a hosted web server for 8 yrs.
I'm just moving it to another server - only change made was from DSN to physical data path for the database.

still doesn't explain why the following fails to create dataset object
set rsPasswords = cnDB.Execute("select * from Passwords where UserID ='LEE'")

Open in new window

0
 
Wayne BarronAuthor, Web DeveloperCommented:
The coding was not targeted to you trims30.

What was the previous Server Version?
What is the newer Server Version?
0
 
trims30Author Commented:
New Server is Windows Server 2008 - Standard SP1
Previous Server - is Windows 2003 Server
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Regardless of rather Access is like Servers or not, using Parameters, and good coding skills is a must. Using the example that I posted the link for, shows just that. Good coding skills.
I agree, but that doesn't negate my comment - Access cannot process multiple commands in the same EXECUTE statement.
0
 
Wayne BarronAuthor, Web DeveloperCommented:
agree
0
 
trims30Author Commented:
moved to a different win2008 server and it's now working.
have no idea as to why.
Thanks for working with me.
0
 
Wayne BarronAuthor, Web DeveloperCommented:
could be the way IIS is setup to run ASP Classic.
I would take a look at both of them side-by-side
To see if there is a different setting somewhere between them.

Good luck
And glad you are back running again.

Carrzkiss
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.