ASP and Connection String [Archive]

View Full Version : ASP and Connection String

Thatguy2001au

04-19-2003, 03:53 PM

Hi,

At the moment I have a site which interacts with a database. At the moment, the database I am using is a SQL Server 2000 database. I have a separate asp page with the connection string in it which i just include in any pages where i need to access the database.

The problem is, is that I want to use the same website with a MS Access Database as well. Basically, what i did, was just made a copy of that website and changed the connection string in the separate file to connect to my Access Database. The connection string is ok, but for some reason when a page tries to access the Database it just doesn't work and instead i get an error saying the XML page cannot be viewed even though it ain't an XML page.

So, what i did, i put the actual connection string into the pages which access the database and remove the include file and it works. Any idea what the problem is???

I don't know if it makes a difference, but the connection was created using dreamweaver mx. If anyone has any ideas why the include file isnt working, i wouldn't mind some help.

raf

04-19-2003, 05:13 PM

can you post the code here? code from the include and the code where you include the file.

Thatguy2001au

04-20-2003, 04:33 AM

This is the code from the include file:

<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_imshop_STRING
MM_imshop_STRING = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=e:\websites\impressionaccess\db\impress.mdb"
%>

And here is the code from one of the pages that tries to access that database :

<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_imshop_STRING
Recordset1.Source = "SELECT ALBUM_NAME FROM dbo_ALBUMS"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>

<body>
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<%=(Recordset1.Fields.Item("ALBUM_NAME").Value)%>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>

</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Thatguy2001au

04-20-2003, 04:40 AM

Sorry Man

It looks like it works now. COuld it be that when i was making changes to the file to get it to work that it kept on using a cached file and that's why i always got an error???

Well it seems fine now, all i had to do was change the SQL statements a little from eg "select * from dbo.albums" to "select * from dbo_albums" i just changed the . to an underscore. I did that earlier and was still getting an error. Maybe i was just using cached pages.

How do i make the page expire straight away???

Thanks raf

raf

04-20-2003, 09:40 AM

How do i make the page expire straight away???

Do you mean clientsided? add
Response.Expires = -1000
on top of your asppage

Do you mean serversided? Normally, if you change the code and save it again, the modification date wille be changed and with the next request, the new page will be loaded and cached. So you shouldn't take care of that. However ! If your a bad boy and use Visual Interdev or other codegenerators, then it's possible that some changes can be made without that the modificationdate get's changed.
You can configure your IIS to never cache ASP pages, buth this should only be done on a development machine.
- open IIS
- rightclick the machine where IIS runs on and select Properties
- In the Master Properties dropdown, choose WWW Service
Go to the Home Directory tab
- Click Configutation in the Application Settings frame
- Go to the Process options tab
- Then click the radiobutton in front of "Do not cache ASP files
- Click apply

Thatguy2001au

04-20-2003, 11:08 AM

I don't know if you can help me, but you know the code i posted above which connects to my access database, well, sometimes it works and sometimes it doesn't.

If i try it once it will work, if i try it again straight after, it doesn't and i get an error.

If i leave it for a while and try again, it will work. It's the same with all my other pages.

Is there some sort of settings i should be chaging within ms access for my database???

I know it's kinda strange. But if you know something i don't, then i would love to hear it.

thanks

raf

04-20-2003, 11:36 AM

Well, this is how my connectionstrings for access look like.
(off coarse you need to replace the Name and dbname with the name for your cannection and db-name)

dim conName
set conName=server.CreateObject("adodb.connection")
conName.Open("provider=microsoft.jet.oledb.4.0;data source="&server.MapPath("../db/dbname.mdb"))

to get the values, i use

dim rsRecordsetname 'declare variabele for recordset
set rsRecordsetname = server.CreateObject("adodb.recordset") 'create instance of serverobject for recordset

dim sql 'dim variable to store sql-string in
sql="select name from whatever where number=anumber"
sql=replace(sql,"anumber",session("brand"))
rsRecordsetname.Open sql, conName 'open recordset using connection

'your code that uses the recordsetvalues

rsRecordsetname.Close
set rsRecordsetname=nothing
'your other querys etc
' as soon as possible closign connection --> after all querys were run and recordsets were closed.
conName.Close
set conName = nothing

It's a completely different approach. But i think the code is cleaner and easier to read and debug. Just for inspiration

here's some info on includes that might be usefull
http://www.codingforums.com/showthread.php?s=&threadid=16464&highlight=virtual+include

arnyinc

04-21-2003, 05:56 PM

Originally posted by Thatguy2001au
If i try it once it will work, if i try it again straight after, it doesn't and i get an error.

You have to fix this error and then you won't have your problem any more.

If you need any more detailed help, give some more detail about the error. Make sure you close your connections on each page.

Also, do you know what this line does or why it is included on your page?
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

raf

04-21-2003, 06:39 PM

Also, do you know what this line does or why it is included on your page?Also, do you know what this line does or why it is included on your page?
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

Its a reference to an external DTD. There's no XML in the code so i don't know why he included two top lines.:confused:

ecnarongi

04-21-2003, 06:45 PM

the page was most likely layedout in a WYSIWYG. I think DW puts some garbage at the top of all pages.

raf

04-21-2003, 07:37 PM

I think DW puts some garbage at the top of all pages Thats kinda the only thing it does :D
Seriously, i only use code-generators for multimedia stuff but always use the code view to insert ASP or PHP or whatever.
I've heard quite a few people complaining about unpredictable behaviour of generated code.

Think it's best to clean out the code as far as possible.