Solved

ADO, ASP and Access DataBase

Posted on 2013-01-30
14
285 Views
Last Modified: 2013-02-02
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
0
Comment
Question by:trims30
  • 6
  • 4
  • 2
  • +2
14 Comments
 
LVL 6

Expert Comment

by:esolve
ID: 38838479
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 38838626
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
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 38838802
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
 
LVL 84
ID: 38838839
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
 

Author Comment

by:trims30
ID: 38839295
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
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 38839870
@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
 

Author Comment

by:trims30
ID: 38840041
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 30

Expert Comment

by:Wayne Barron
ID: 38840072
The coding was not targeted to you trims30.

What was the previous Server Version?
What is the newer Server Version?
0
 

Author Comment

by:trims30
ID: 38840214
New Server is Windows Server 2008 - Standard SP1
Previous Server - is Windows 2003 Server
0
 
LVL 30

Accepted Solution

by:
Wayne Barron earned 150 total points
ID: 38840467
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
 
LVL 84
ID: 38840835
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
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 38841303
agree
0
 

Author Closing Comment

by:trims30
ID: 38846654
moved to a different win2008 server and it's now working.
have no idea as to why.
Thanks for working with me.
0
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 38846922
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now