A newbie attempting to connect to .mdb..help. Please? 2

danielh68 · Jul 10, 2002

Hi

I'm trying to learn how to connect to an Access database. I have created a database in Access, uploaded to the database folder, logged into the site control panal, clicked database tools and created a new ODBC connection.

As a side note, I have been following this tutorial at

http://www.haneng.com/Lessons_17.asp

It's fine, until he gets to the control panel under ODBS Data Source Administrator.

My Question is this:

1. Since he's connecting to PWS do I need to do this? Or, does it generate when I creating a database through my ISP's Database control panel.

Or, can someone point me to a more approprate tutorial?

Thanks,
dan

onpnt · Jul 10, 2002

wow! that's a old tutorial.
Anyway, why are you going through all that. just upload the DB and connect to it. [lol]

I know not that easy.
questions first.
do you have a server supporting asp? I know thats a stupid ? if you're here but I got to ask?

You said you uploaded the DB?
whats the name of a table?
what's the name of the DB for that matter?
after you give that info up we can get going.
[bomb]

I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

nenuknifer · Jul 10, 2002

http://www.w3schools.com

You will get all you wanted.

Feel free to tell me if it annoys you.

danielh68 · Jul 11, 2002

onpnt,

Thanks for your assistance. I will attempt to answer you questions in list form just to speed things up ( I know your time is of value).

1.Yes, server supports asp. In fact, I have a couple of asp forms implemented in the site I've built at

http://www.optistreams.com.

2.Yes. It's an Access database (mydatabase.mbd), exactly like the one in the tutorial. I called my ISP, they said upload it in the "data" folder. Then they instructed me to log in the site control panel, click database tools, click new,type name, then path (DSN connnection)in order to establish an ODBC. Beyond that, they said I was on my own.

3. The table is called "Links"

Note, I bought a book "Sam's 24 ASP" and I plan to read it this weekend. And the site recommended by ccat999 is a great resource. I will investigate it. However, your expertise definetly holds rank.

I'm near completion of this site (graphics, code, everything) for the company and now my boss wants to connect to a database. Which is something I have never done before, so forgive my ignorance.

Sorry, this is longer than I intended.

Thanks a million,
Dan

onpnt · Jul 11, 2002

try this
save this as trial.asp or something upload it and let me know if it finds the path to the folder /data/mydatabase.mbd
it may not. If so you can rewrite it to Server.MapPath("/data/mydatabase.mbd&quot

<%
Set conn = Server.CreateObject("ADODB.Connection&quot

' Create your connection object
connStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("mydatabase.mbd&quot

' Get database path
Conn.Open connStr
' Open the connection to the database

' create your SQL statement
sSql= "SELECT * FROM Links"

Set StrRS = Server.CreateObject("ADODB.Recordset&quot

'Create your recordset object
StrRS.Open sSql,Conn
'Open the recordset
'now lets write some records to the html page
%>
<html>
<head>
<title>Your Title</title>
</head>
<body>


<Table>
<TR>
<TD><B>Database Records</B></TD>

</TR>



<% Do While Not StrRS.EOF %>
<TR>
<TD><%= StrRS("some field&quot

%></TD>
/*****************************************************
here is where you will need to put in the name of the column or in this case recordset.
so if this is the way your table looks in the DB
MyLinks

http://www.blah.com

http://www.moreBlah.com

MyLinks is the name of your recordset.
******************************************************/
</TR>
<% StrRS.MoveNext %>
<% Loop %>
</Table>

<%
StrRS.Close ' Lets close the recordset object
Conn.Close ' Lets close the connection object
' Clear the variables
Set StrRS=Nothing
Set Conn=Nothing

%>
[bomb]

I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

danielh68 · Jul 11, 2002

onpnt,

I copied your asp code and uploaded to a directory called proto. Outside of proto is the folder data. So I tried three variations of the Server.MapPath:

/data/mydatabase.mbd
/mydatabase.mbd
../data/mydatabase.mbd

However eash time I got this error at

http://www.optistreams.com/proto/trial.asp

Sounds like I'm missing a step. Any suggestions?

Thanks,
Dan

onpnt · Jul 11, 2002

what is the error I jsut get 500 server error.
if you have the DB in a folder called data then this should work
"DBQ=" & Server.MapPath("/data/mydatabase.mdb&quot

if this doesn't work move the DB to your root dir and do it the way I originaly state it. I tested that and it works here, if you would like to look at that I can give up the link
[bomb]

I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

onpnt · Jul 11, 2002

I jsut noticed something and it was my fault and I apologize.
look at the extension I wrote in the first script
.mbd

no onpnt it's

.mdb

sorry Dan [bomb]

I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

danielh68 · Jul 11, 2002

onpnt,

I think this is progress because I got a differ kind of error:
---------------------------------------------
Database Records
ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/proto/trial.asp, line 35

-----------------------------------------------------

I see in the code it makes a sql command for Links (name of the table) do I need to request the column (MyLinks) too?

Oh, yeah, can you show me a link to your database?

Thanks,
Dan

onpnt · Jul 11, 2002

you have to reference the column you want to display in the call for it
take a name of a column of records and put it here where it is bold
<TD><%= StrRS("some field&quot

%></TD>
/*****************************************************
here is where you will need to put in the name of the column or in this case recordset.
so if this is the way your table looks in the DB
MyLinks

http://www.blah.com

http://www.moreBlah.com

MyLinks is the name of your recordset.
******************************************************/
</TR>

here is how I tested this code before I gave it to you
I have a DB called Inventory
table called tblInventoryControl
a column or recordset called PARTNUMBER and PARTDESCRIPTION(which are fields or columns) in my table

<%
Set conn = Server.CreateObject("ADODB.Connection&quot

' Create your connection object
connStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Inventory.mdb&quot

' Get database path
Conn.Open connStr
' Open the connection to the database

' create your SQL statement
sSql= "SELECT * FROM tblInventoryControl"

Set StrRS = Server.CreateObject("ADODB.Recordset&quot

'Create your recordset object
StrRS.Open sSql,Conn
'Open the recordset
%>
<html>
<head>
<title>Your Title</title>
</head>
<body>


<Table>
<TR>
<TD><B>Number</B></TD>

<TD><B>Description</B></TD>
</TR>



<% Do While Not StrRS.EOF %>
<TR>
<TD><%= StrRS("PARTNUMBER&quot

%></TD>

<TD><%= StrRS("PARTDESCRIPTION&quot

%></TD>
</TR>
<% StrRS.MoveNext %>
<% Loop %>
</Table>

<%
StrRS.Close ' Lets close the recordset object
Conn.Close ' Lets close the connection object
' Clear the variables
Set StrRS=Nothing
Set Conn=Nothing

%>

[bomb]

I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

onpnt · Jul 11, 2002

here's the link to show the HTML page resulting in the connection

http://onpntwebdesigns.com/inventory.asp

I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

danielh68 · Jul 11, 2002

YES!!!!! Got it! That is cool.

Thanks a bunch for your help. I wonder, though, why we didn't have to do the control settings/ODBC administrator procedure that the tutorial mentioned? It sound like a necessary step. Is it because, my database provides their own online database tool or is it something else you did? I'm just curious --Even in that book I just bought, it mentioned you had to do the same thing. Your insight is welcome.

Anyhow, now I know it can be done. I will redo the steps and expand upon it, etc.

Thanks again. Data is exciting!

--Dan

onpnt · Jul 11, 2002

the ODBC and what I showed you are different ways of connecting to a database
the DSN-Less is the easiest and that is what you did here.
go to this site and read up on the connections. this is a important thing to know when you get into database work and connections etc.

http://www.w3schools.com/ado/ado_connect.asp

I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

danielh68 · Jul 11, 2002

That's a great site. Thanks again, I really appreciate it. I will be visiting that site frequently.

--Dan

danielh68 · Jul 11, 2002

onpnt,

I just had one more question after visiting w3schools.

The opening connection code listed at w3schools differs from the code you implemented:

'Yours
Set conn = Server.CreateObject("ADODB.Connection&quot

connStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Inventory.mdb&quot

Conn.Open connStr

'w3schools
<%
set conn=Server.CreateObject("ADODB.Connection&quot

conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"
%>

I'm just curious to know why, if one has an advantage over the other,etc.

Thanks,
Dan

onpnt · Jul 11, 2002

w3school is using a OLE DB there, as mine is a DSN Less using the Server.MapPath method.
There are 7 ways to connect, here they are
DSN-Less
OLE DB //what you see at w3schools
File DSN
DSN without the use of password/userID
DSN with userID/password
DSN-Less with the server.mapPath //what I showed you
using a path as a reference like //DBQ=C:/folder/folder/DB
was that seven? Yup! [lol]

Note: these examples are MS Access only, SQL has differences
but are very alike.

I believe w3schools has examples of all of these just look around.

if you really want to get into the different ways to connect ot DB's check this site out. I leep it in front of me at all times when doing ASP work in all types of databases

Happy Programming! [bomb]

I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

snowyej · Jul 11, 2002

Ok, I have a similar problem and have tried everything in this post but can't get my page to work. I'm getting a "Too few parameters. Expected 1." error. Here's my code:

<%
Dim album
Dim oConn
Dim sSQL
Dim oRS

album = Request.QueryString("album&quot

Set oConn = Server.CreateObject("ADODB.Connection&quot
oConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; &_
DBQ=" & Server.MapPath("db/photos.mdb&quot)

sSQL = "SELECT * FROM tblAlbum &_
WHERE FolderName = '" & album & "' &_
UNION SELECT * FROM tblPhoto"

Set oRS = Server.CreateObject("ADODB.Recordset&quot
oRS.Open sSQL,oConn

'Next I just tried to test the data...

Response.Write(album & " = " & oRS("FolderName&quot.Value)

oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>

Now, I am pretty new to this, so please be kind. The error I'm getting is on the line oRS.Open sSQL,oConn. Any help is greatly greatly appreciated.

Thanks!

Elizabeth

onpnt · Jul 11, 2002

try this line instead of oRS.Open sSQL,oConn

Set oRS = oConn.Execute(sSQL) [bomb]

I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

onpnt · Jul 11, 2002

you also forgot a / in the source and I would do it like this to simplify things a little
connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("/db/photos.mdb&quot

oConn.Open connStr [bomb]

I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

snowyej · Jul 11, 2002

That works...for the most part (I'll explain below). Can you explain the difference between those statements, if you don't mind? Just trying to grasp all this new .asp coding stuff I've learned in the past couple of days.

Also, something very strange... where I wrote out "album = oRS("FolderName&quot

.Value it did not give me what it should. It's like it pulled data from another field in that same record.

Basically, it should have read:

whistler = whistler

But read this instead:

whistler = 2002.04.20 - Whistler, BC

Where the part after the = sign is the actual AlbumName, not the FolderName. I've checked my database and everything looks as it should. Can you think of any reason why it would do this?

Thanks again for your help. I really appreciate it.

Elizabeth

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

A newbie attempting to connect to .mdb..help. Please? 2

Technical User

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

Programmer

Technical User

Programmer

Programmer

Technical User

Programmer

Technical User

Technical User

Programmer

Technical User

Programmer

Programmer

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor