ASP Database access basics.

Matt Massie · Matt Massie

I am attempting to write my first ASP code, and I'm having a rather tough
time. My desired end product is this:
A client clicks the link on my webpage that allows him to a logon and
password. (There will only be 3-5 users) Immediately upon successful
authentication, a query will access a database, and write the results to a
..CSV text file, and immediately send that to the client's browser. After the
download ends, an update query will run which adds a time stamp to a
previously null field. With this program I want my client to login, and have
no other interface than accepting the file download. I have several problems
to tackle. First I don't know where the best place to house the database. Am
I required to put it somewhere? And must I let IIS 5.0 know that it's there.
Next, I can't get the connection to work. I'm beginning to think that's a
result of improper preparation of the database. Third, I obviously iron out
the ASP file until I can see it in action. So. No problem, right. I've been
at it for 3 12 hour days now, and I've got to call for help. Below is the
code I've prepared. The databases the ASP calls for are in the same
directory as the code. Any help would be nice, even if only a clue on which
direction to focus my attention. Can you even see my logic and is it
inefficient?
Thank you all very much!
M. Massie - I think in copying and pasting I lost a little of the
tabbing

<html>

<head>
<meta HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252">
</head>

<body>

<%
Username = Request.querystring("username")
Password = Request.querystring("password")

Set lConn = Server.CreateObject("ADODB.Connection")
lConn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.Mappath("Users.mdb")

lSQL = "SELECT First_Name, Last_Name From Customer Where Username = '" &
Username & "' AND Password = '" & Password & "'"

Set lRS = lConn.Execute(lSQL)

On Error Resume Next
lRS.MoveFirst

firstName = lRS.fields("First_Name").value
lastName = lRS.fields("Last_Name").value

If firstname = "" Or lastname = "" Then
%>

Invalid Username or Password please <a href = "Login.asp">try again.</a><br
/><br />

<%
Else
%>

Hello <%=firstName%>&nbsp<%=lastName%>,<br /><br />

<%
//Initialize Variables
Dim adoCon
Dim rsDefender
Dim StrUserID
Dim strSQL
Dim objFSO, objTextFile
Dim StartDate

//Establish Connection to the Database @ "DefenderSecurity.mdb"

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("DefenderSecurity.mdb")
Set rsDefender = Server.CreateObject("ADODB.Recordset")

//Query Initialization

If Username="xxxxxxx" and Password="******" Then
strSQL = "SELECT * from DefenderMaster where FOOTPRINT in
('DA','DC','DD') and [DATE DOWNLOADED] is null;"
Else If Username="yyyyyyy" and Password="******" Then
strSQL = "SELECT * FROM DefenderMaster WHERE FOOTPRINT in
('DB','DE','DF','DG') and [DATE DOWNLOADED] is null;"
End If

If Username="xxxxxxx" and Password="*******" Then
SQL1 = "UPDATE [DATE DOWNLOADED] FROM DefenderMaster set
[DATE DOWNLOADED] =now() WHERE FOOTPRINT in
('DA','DC','DD') and [DATE DOWNLOADED] is null;"
Else If Username="yyyyyyy" and Password="*******" Then
SQL1 = "UPDATE [DATE DOWNLOADED] FROM DefenderMaster set
[DATE DOWNLOADED] =now() WHERE FOOTPRINT
in ('DB','DE','DF','DG') and [DATE DOWNLOADED] is
null;"
End If

rsDefender.open strSQL, adoCon
rsDefender.MoveFirst
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile =
objFSO.CreateTextFile("w:\wwwroot\textexports\resu lts.csv", True)

Do While rsDefender.EOF=False
If rsDefender.EOF=True Then
Exit Do
Else
objTextFile.WriteLine (rsDefender("ID") & "," &
rsDefender("SOURCE") & "," & rsDefender("TITLE") & "," & rsDefender("FIRST
NAME") & "," & rsDefender("MIDDLE NAME") & "," &
rsDefender("LAST NAME") & "," & rsDefender("SUFFIX") & "," &
rsDefender("PHONE 1") & "," &_
rsDefender("PHONE 2") & "," & rsDefender("ADDRESS 1") &
"," & rsDefender("ADDRESS 2") & "," & rsDefender("CITY") & "," &_
rsDefender("STATE") & "," & rsDefender("ZIP") & "," &
rsDefender("ZIP +4") & "," & rsDefender("ESTIMATED INCOME") & "," &_
rsDefender("WEALTH RATING") & "," & rsDefender("HOME OWNER
PROBABILITY") & "," & rsDefender
( ("ESTIMATED CURRENT HOME
VALUE") & "," & rsDefender("MEDIAN HOUSING VALUE") & "," & rsDefender("TOTAL
ENHANCEMENT MATCH TYPE") & "," & rsDefender("REGION") & "," &
rsDefender("FOOTPRINT") & "," & rsDefender("REGION CODE")
& "," & rsDefender("MASSIE CODE") & "," & rsDefender("CAMPAIGN CODE") & ","
& rsDefender("LEAD TYPE") & "," & rsDefender ("DATE
DOWNLOADED"))
End If
rsDefender.MoveNext
Loop

//Release references and Objects
objTextFile.Close
rsDefender.close
Set objTextFile = Nothing
Set objFSO = Nothing
rsDefender.close
set rsDefender = nothing
set strSQL = nothing
set adoCon = nothing
Response.Redirect("../textexports/results.txt")
%>
<a href="./results.txt" target="main">Click here to download now</A>

<%
lconn.close
set lConn = nothing
%>

</body>
</html>

Bob Barrows · Bob Barrows

I'm having trouble finding a question in all that narrative. Are you having
a specific problem? error messages? Something not working the way you want
it to work? You may want to browse the Databases section at [url]www.aspfaq.com[/url]
if you need general information on working with databases.

I notice that you are using ODBC to connect to the database. ODBC is
obsolete. You should use the more robust OLEDB provider for Jet databases.
See [url]www.able-consulting.com/ado_conn.htm[/url] for help with creating a connection
string using the Jet OLEDB provider.

The only specific question you ask is where to put the database. While you
can put it in the same folder that contains your asp files, it is not
recommended. Here's why: fire up your browser and enter the url to the
database file: [url]http://your_website/DefenderSecurity.mdb[/url]. See what happpens?
You're allowed to download the file to your pc. Not good, given that it
contains sensitive information. All a hacker would need to do is either
guess the name of your mdb file to obtain its contents. One way to prevent
this is to change the file extension of the file from "mdb" to "asp". It
will still work fine as a database, but it will not give the user the
opportunity to download it if they browse to it. The most secure place to
store your database is in a folder that is outside of your website. That way
nobody can ever browse to it. Keep in mind that if you take this route, you
must grant the IUSR_machine account Change permissions to the folder
containing the database file. Depending on the security mode being used in
your IIS application, you may need to grant the same rights to the
IWAM_machine account.

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I don't
check it very often. If you must reply off-line, then remove the "NO SPAM"

"Matt Massie" <rmmassie@comcast.net> wrote in message
news:ALSdnQAOjukvYQSiRVn-tw@comcast.com...

>
>
> I am attempting to write my first ASP code, and I'm having a rather tough
> time. My desired end product is this:
> A client clicks the link on my webpage that allows him to a logon and
> password. (There will only be 3-5 users) Immediately upon successful
> authentication, a query will access a database, and write the results to a
> .CSV text file, and immediately send that to the client's browser. After

the

> download ends, an update query will run which adds a time stamp to a
> previously null field. With this program I want my client to login, and

have

> no other interface than accepting the file download. I have several

problems

> to tackle. First I don't know where the best place to house the database.

Am

> I required to put it somewhere? And must I let IIS 5.0 know that it's

there.

> Next, I can't get the connection to work. I'm beginning to think that's a
> result of improper preparation of the database. Third, I obviously iron

out

> the ASP file until I can see it in action. So. No problem, right. I've

been

> at it for 3 12 hour days now, and I've got to call for help. Below is the
> code I've prepared. The databases the ASP calls for are in the same
> directory as the code. Any help would be nice, even if only a clue on

which

> direction to focus my attention. Can you even see my logic and is it
> inefficient?

Matt Massie · Matt Massie

My lack of clarity is an indication of the hole I'm in.
1. Can I call on the database immediately after copying it to the server HD?
Or do I have to Create a connection somehow OUTSIDE the ASP file?

2. How do I setup the OLE DB Jet connection?

I've been scouring these ASP development web pages and everything seems very
clean and easy. However, It never works for me. I want to know if I'm
missing a step BEFORE the code happens that the developers assume I've
already completed. I'm building this on my XP Home laptop, then moving it
over to my webserver. All I do is copy it to the wwwroot dir and let it
re-write the files I changed. Could I create a problem for myself there?

Thanks
M. Massie

Matt Massie · Matt Massie

On XP Home, Can I run an ASP page locally that refers to a .mdb in the same
directory? I only want to test to see if my connection strings are correct
before I put things on the server.

Bob Barrows · Bob Barrows

Matt Massie wrote:

> My lack of clarity is an indication of the hole I'm in.
> 1. Can I call on the database immediately after copying it to the
> server HD?

Why not? As long as the IUSR_machine account has the necessary NTFS
permissions (at least Change) on the folder containing the database file,
and the connection string being used to connect to it is correct, there
should not be any problem.

> Or do I have to Create a connection somehow OUTSIDE the
> ASP file?

No. all connections are created and opened within the ASP pages. The only
possible problem is if the web server's MDAC installation is corrupt, which
is rare.

The exception, which is not recommended, is if you wish to use an ODBC DSN
to establish the connection. You would do this using the ODBC DSN Manager
(sometimes known as the Data Source Manager) to create a System DSN, to
which you could refer in your connection strings. Again, this is not
recommended: Microsoft has deprecated the OLEDB for ODBC Drivers provider -
it is obsolete: use the native database providers instead.

>
> 2. How do I setup the OLE DB Jet connection?
>

?
The website I directed you to ([url]www.able-consulting.com/ado_conn.htm[/url] ) has
very explicit examples of valid OLEDB connection strings. Just scroll down
to the OLEDB Provider For Microsoft Jet samples and use the appropriate one
(I suspect it will be the first example you see: unsecured database). Just
replace the string you are using ("{Driver= ...") with the example shown on
the website, replacing the data source name with the name of the database
file you are using.

> I've been scouring these ASP development web pages and everything
> seems very clean and easy. However, It never works for me.

What does that mean? You need to give us specific problems (error messages,
etc.) if you expect us to provide any help.

I STRONGLY suggest you browse the database-related questions on the
aspfaq.com site. :-)
Since you are using Access, you may want to use the site's search engine to
find Access-related questions and answers.

> I want to
> know if I'm missing a step BEFORE the code happens that the
> developers assume I've already completed. I'm building this on my XP
> Home laptop, then moving it over to my webserver. All I do is copy it
> to the wwwroot dir and let it re-write the files I changed. Could I
> create a problem for myself there?
>

None that I can think of off-hand.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Bob Barrows · Bob Barrows

Matt Massie wrote:

> On XP Home, Can I run an ASP page locally that refers to a .mdb in
> the same directory? I only want to test to see if my connection
> strings are correct before I put things on the server.

Yes. That's what Server.MapPath does for you: it returns the filesystem path
to the file specified in the url you supply in the argument. Look at the
result of

Response.Write Server.MapPath("DefenderSecurity.mdb")

to see what I mean.

Wait a minute: XP Home? I don't think you can run a web server on an XP Home
machine. It needs to be XP Professional, IIRC. Let's see ... yep, right
here: [url]http://www.aspfaq.com/show.asp?id=2079[/url]. Read what it says.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

ASP Database access basics.

Thread Tools

Display

ASP Database access basics.

Similar Questions and Discussions

Access Database

access to database

Web access to database

Write Access to Access DataBase

Access vs DBF database??

Re: ASP Database access basics.

Re: ASP Database access basics.

Re: ASP Database access basics.

Re: ASP Database access basics.

Re: ASP Database access basics.

Posting Permissions