Expert: Srini Nagarajan - 11/15/2005

Question
-------------------------
Followup To
Question -
Hi Mr.Srini,

I am currently working on our companies website. I have included a form to collect user's data and store it in the database. For connecting I created a folder and stored the database and a file dsn in that folder. Now the code for accessing the database is in another folder. With this scenario its working on my local host. Now when I uploaded this code on to the web server its giving me this following error:

'Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x4a8 Thread 0xb3c DBC 0x20c7e6c Jet'.

/xyz.com/RegistrationForm/Register_Fields.asp, line 17'

I dont understand why is this error coming when the same code i used on my local machine its working fine.

He is the file dsn I am using:

'[ODBC]
DRIVER=Driver do Microsoft Access (*.mdb)
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Access
DriverId=25
DefaultDir=\xyz.com\Databases
DBQ=\xyz.com\Databases\uniplus_pdf.mdb'

And my connection string is something like this:

'Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Mode = 3 ' 3 = adModeReadWrite
MyConn.Open "FILEDSN=\xyz.com\Databases\uniplusdsn.dsn"


And finally the query is like this,

'SQL_query = "INSERT INTO pdfdownload_data"
   SQL_query = SQL_query & "(first_name,last_name,company,email,phone,address,city,state_var,zip,country,"
  SQL_query = SQL_query & "lead_source,others,timeframe,pdf_file,date_field)"
   SQL_query = SQL_query & "VALUES ('"&first_name&"','"&last_name&"','"&company&"','"&email&"','"&phone&"','"&address&"',"
  SQL_query = SQL_query & "'"&city&"','"&state_var&"','"&zip&"','"&country&"','"&lead_source&"','"&others&"',"
   SQL_query = SQL_query & "'"&timeframe&"','"&pdf_file&"',"&CurrentDate&")"
  'Response.write "query" & SQL_query
  MyConn.Execute(SQL_query)'

Please help me where I have gone wrong and what I should be doing to get this working.

Thanks a lot for you time,
Nitu

Answer -
Hi

Don't use ODBC driver... it need right permissions on the files

use JET database here is the sample connection string

------------------------
......
svrpath = server.MapPath("/")
constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & svrpath & "\database\db.mdb;Persist Security Info=False"

Set ConDb1=Server.CreateObject("ADODB.Connection")
ConDb1.Open conStr
......

Happy Programming!!


Thank you Mr.Srini,

I am still using ODBC, and I figured the fault was with the driver that my webserver provides is Microsoft Access Driver (*.mdb) and not 'Driver do Microsoft Access (*.mdb)' as I have given. But this was what the file generated from my odbc file dsn. Anyways now its working.

But now I am worried about the mdb file getting locked when one user is accessinf it. So I am thinking about sql server. I can see that you have expertise in that as well. So can you please give me a sample code for sql file dsn, especially if iam going to upload it on to a webserver.

Thank you for you reply also for your time,
Regards,
Nitu

Answer
Hi


I was busy thats why not able to reply imdtly...

Here is the sample sql connection string without ODBC

Application("DB_ConString") = "DRIVER=SQL Server;SERVER=Server_Name_OR_IP;DATABASE=DatabaseName;UID=UserName;PASSWORD=Password;"

Happy Programming!

-Srini