Connecting to Excel Database with ASP

thehaz · March 24th, 2004, 02:53 PM

I've spent hours on this and I'm still clueless, but have learned the jargon off by heart even though it still doesn't work.

It's probably something really trivial.

I have a free account on websamba which is ASP enabled and this all works fine, I have some online chat and active pages doing various things like storing cookies on the client side and displaying everyone's messages in a refreshing window.

I want to be able to store data, e.g. I would like to open a text file and dump the chat into this file. I've tried some commands, but the server says it can't do that, I think because the server won't allow me to create / open text files.

The service *does* allow me to have an excel database. I've created a little database and uploaded it to the server, also I've requested it be set up and the service sent me an email telling that the database had been approved and was now:

The DSN you requested has been created
DSN Name : DSN_thehaz
DataBase File Name : database\thehaz.xls
DateBase Type : Microsoft Excel

The file is there, and resides on the space under my

My files are literally here, according to mappath
E:\Freehost20\thehaz\findserverpath.asp

and it would literally be
E:\Freehost20\thehaz\database\thehaz.xls
on the server, or www.websamba.com\thehaz\database\thehaz.xls via the web, not that that resolves as a URL and just hangs, probably quite correctly as I don't want my database being available for browsing

All this seems straightforward to me.

Now on to the problem. How do I get data in and out of the database from an ASP page?

I can attempt to open the database, with this code:
<html><head></head><body>Starting the test<br><br>
<%
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DSN_thehaz"
%>
<br><br>
Test ended.
</body>
</html>

result is this:

Starting the test (so it is running),
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Excel Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
/thehaz/textfile.asp, line 5

Same thing happens when I replace line 5 with conn.Open "DSN=DSN_thehaz" which I think is probably correct, or with conn.Open "database\thehaz.xls" the error changes into:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

and I get the same error with
conn.Open "database\DNS_thehaz"
conn.Open "DNS=database\DNS_thehaz"

Ok, what am I doing wrong here? The code seems to be something similar to what various websites say to do.

Thanks in advance. :)

(edit: - p.s. noticed a typo in the cut and pasting, yes line 5 should be conn.Open "DSN=DSN_thehaz" in various forms. The error isn't about the spelling of DSN.)

acdsky · March 24th, 2004, 04:49 PM

Hi

Try this:
<%
Dim Conn, RS
Set Conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")

Conn.ConnectionString = "DBQ=c:\Book2.xls;DRIVER=Microsoft Excel Driver (*.xls);UID=admin;"
Conn.Open
rs.cursortype = 3
SQL = "Select * From [Sheet1$]"
rs.open SQL, conn

while not rs.eof
response.write RS("name")
rs.movenext
wend
%>

You could replace SQL with an Update query to write data to the sheet. Personaly I never use excel cause it causes to many issues... Make sure your web service user has got access to the file/directory.

thehaz · March 24th, 2004, 05:00 PM

" Item cannot be found in the collection corresponding to the requested name or ordinal. ", but it is on a different line, and when I remove the while loop (error is about that rs.eof I think) then the page completes without an error, so I think that must be working, thanks. Also of course, the path name needed changing, the file isn't on the C drive. Left "admin" where it was, probably doesn't matter in excel.

Now to find out what data queries do actually work through this link ;) thanks again.