Solved

ADO, ASP and Access DataBase

Posted on 2013-01-30
14
282 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 82

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

744 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

13 Experts available now in Live!

Get 1:1 Help Now