ODBC DSN-Less string

**Lol999** · 05-13-2006, 08:08 AM

Hi all, apologies upfront for the total noob question but:

my webhost supports ODBC DSN and DSN-less connections. At the moment I have my database driven gallery running using their DSN created online through Helm. I have tried using a DSN-less connection but it was the OLEDB syntax. Is there a problem trying to use the OLEDB connection string in this instance with my host implicitly referring to them as ODBC? If so, what's a good ODBC string to connect with? I asked tech support if my string looked okay, and it was of the OLEDB variety, and they confirmed it was. Now if I needed to used an ODBC string instead of an OLEDB one surely they would have said so?
Just getting a bit confused and trying like hell to to make sense of it.

Cheers, Lol

**chrishirst** · 05-13-2006, 12:52 PM

a "good" ODBC string would really depend on

a/ The database server (the string for Access wouldn't work for Oracle)
b/ What driver version the host has installed. (the MySQL 2.5 string won't work if the 3.51 driver is installed)
c/ and to some extent what language you are working in.

**Lol999** · 05-13-2006, 12:57 PM

Hi Chris, forgot to add I'm using an Access 2000 database stored in the proper db folder

I take it from your reply that there is a difference between ODBC and OLEDB?

Cheers, Lol

**Lol999** · 05-13-2006, 12:58 PM

Forgot to add VBScript

**chrishirst** · 05-13-2006, 01:34 PM

ok

OLEDB

Code:

Dim vPath, pPath, ConString
vPath = "/path/dbname.mdb"
 pPath = Server.MapPath(vPath)

ConString = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & pPath & ";"

ODBC

Code:

Dim vPath, pPath, ConString
vPath = "/path/dbname.mdb"
 pPath = Server.MapPath(vPath)

ConString  = "Driver= {Microsoft Access Driver (*.mdb)};DBQ="  & pPath & ";"

**Lol999** · 05-13-2006, 01:42 PM

Cheers Chris!

**MyOpinion** · 05-13-2006, 08:40 PM

If performance is one of your concerns, you should use the OLEDB connection. It runs faster than the ODBC connection.

**Lol999** · 05-14-2006, 09:03 AM

Well I've tried the OLEDB connection and it just keeps spitting errors out so that's whi I asked about the ODBC connection. It specifically refers to ODBC DSNs's in my control panel so I assume that is what's required. I'll have to have a further play and see what appertains.

Lol

**chrishirst** · 05-14-2006, 09:44 AM

What errors do you get with OLEDB?

**Lol999** · 05-14-2006, 10:00 AM

I just keep getting "there is a problem with the page you are trying to display" or "there is an error in your insert statement". This is the frustrating part - I'm doing these pages through Dreamweaver and have tried both the Insert Record Entry wizard and the manual approach usinf server behaviours. I have managed to get only 1 to work and that was just playing. i can't even alter the damned thing to include extra fields as I just keep getting the "problem with page" coming up again. I don't know if it's my Dreamweaver acting up or the DSN on the host. That';s anothert reasoin I was trying to delineate between ODBC and OLEDB, so then I can go DSN-less and cut that out of the loop. My next step is to create pages in Dreamweaver and then handcode the asp part. It's not a field type error or mismatch on the database as to be honest I'm a bit good with Access. I could bite the bullet and download a slot in script but it's become a matter of principle now and I'm going to beat the swine

Cheers, Lol

**chrishirst** · 05-14-2006, 12:00 PM

Use

Tools -> Internet Options... -> Advanced tab -> Uncheck "Show friendly HTTP error messages" (in the Browsing section)

so you can see the exact error message

**Lol999** · 05-14-2006, 03:40 PM

Hmm, this is what I get:

Microsoft OLE DB Provider for ODBC Driverserror '80004005'
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x11c Thread 0x166c DBC 0x169d004 Jet'. /Guest_confirm.asp, line 3

**Lol999** · 05-14-2006, 03:46 PM

The connection string is fine because I'm using it on an asp page to view the database contents and it runs a treat.

What I have done is create a HTML page with a form on it called Guest_entry.htm. The action on the form is /Guest_confirm.asp and the method is post. Guest_confirm.asp has a simple message on it and this code behind it to write to the database details collected from the form on Guest_entry.htm:

<<A href="mailto:%@LANGUAGE="VBSCRIPT">%@LANGUAGE="VBS CRIPT" CODEPAGE="1252"%>
<%Set DBConnection=Server.Createobject("ADODB.Connection ")
DBConnection.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db/Source.mdb") & ";"
varname = Request.QueryString("Name")
varemail = Request.QueryString("E-mail")
varmessage = Request.QueryString("Message")
SQLQuery="INSERT INTO Source (Name,E-mail,Message) VALUES"
SQLQuery=SQLQuery & "('" & varname & "', '" & varemail & "', '" & varmessage & "')"
set recsetado=DBConnection.Execute(SQLQuery)
dbconnection.close
set dbconnection=nothing %>

To be honest I'm at a bit of a loss

**chrishirst** · 05-14-2006, 04:57 PM

first off get rid of this gibberish line completely

<<A href="mailto:%@LANGUAGE="VBSCRIPT">%@LANGUAGE="VBS CRIPT" CODEPAGE="1252"%>

vbscript is the default and as that line is completely messed up it may be causing problems.

the 80004005 error code is fairly generic BUT

This is often caused by no read/write permissions to the folder or .mdb file for the IUSR_nnn account. However the times I have come across this has been down to brain fade and mis-spelling the folder or DB name.
Also is the db folder in the site root? If so use "/db/filename" especially if you are using an include for the connection data.

**Lol999** · 05-14-2006, 05:02 PM

Hi Chris, the db is in a separate folder called "db". There are 2 other folders, logs and wwwroot. I've just mailed my host asking if write permissions are automatically set. am awaiting reply ;-). I don't know what else it could be but I'm a noob at this. Give me ADO in VB and I'm a happy bunny, but get me anywhere near web stuff and I get brain lock.

Cheers, Lol

**Lol999** · 05-14-2006, 05:07 PM

I've just connected to my site through Smart FTP and done a right-click on my database folder. I've got a properties/CHMOD dialogue come up with nothing set. is this where I need to make alterations?

**chrishirst** · 05-14-2006, 05:40 PM

Nope, there's no CHMOD for windows servers. Your host should have set it up so that the user account for your site has permission to those folders (well that's how we do it anyway)

your database folder is above the webroot. This is done so nobody can download the database.

so your conn string needs to be

Code:

server.mappath("../db/filename.mdb")

**Lol999** · 05-14-2006, 05:56 PM

I've raised a ticket asking if permissions are set but not had any response yet. probably no-one there. It's definitely got me puzzled. I'll take the files to college this week and run them on our dedicated server and if they work on there it's a host thing. Glad I only signed on a monthly contract

Thanks for your help Chris, great as ever.

Cheers, Lol