ASP newbie question

yeraulone · 25-11-2005 2:12pm #1

Hello All,

I was wondering if it is possible to take information from a database and store it in ASP files (I'm sure it is). I have been asked to create a system, that takes information from a database and stores in asp pages, so that the pages will be indexed by search engine spiders.

Does anyone know of any tutorials online or any good resources I can read?

Thanks for your help

J

damalo · 25-11-2005 3:37pm

Easily done - I presume you want to pull some info from the DB and then show it to the visitor in HTML

1- Connect to Database
2- Run SQL query
3- Recordset gets returned
4- Empty/print contents of recordset into a paragraph/whatever

In VB script here is the connection: http://www.w3schools.com/ado/ado_connect.asp

Here's an example of a query and printing it out:
http://www.w3schools.com/ado/ado_recordset.asp

HTH

DJB · 25-11-2005 3:41pm

/Edit/ - Maybe my answer was a bit too advanced. If you are only getting started, use damalo's advice.

You can use the File System Object to create html files that are created on the fly. You can then link to these from one of your pages so the search engines index them. Although, I don't usually have problems with a page like this:

default.asp?content=1

The content=1 part pulls the specific details for that page and displays it. Same with content=2, etc. Google picks these up fine but I have heard of issues with other search engines.

You can create an asp page that creates a html file for each record in your database, e.g.

'This is my own function, you would use your own method for opening a recordset
sSQL = "SELECT * FROM tblPages"
CALL OpenRS(rsPages,sSQL,sDatabase) 

'create an instance of the FileSystemObject
SET sFSO = Server.CreateObject("Scripting.FileSystemObject")

'Loop through all your records in the database
DO WHILE NOT rsPages.EOF

'Map a path to you unique file name based on the id in the database
sThisPage = Server.Mappath("../htmlpages/"& rsPages("pgID") &".htm")

'Check if the file exists already and delete it
TempPath=sThisPage 
IF sFSO.FileExists(TempPath) = TRUE THEN
sFSO.DeleteFile(TempPath)
END IF

'Create a new file with the same name and fill it with content
Set f = sFSO.CreateTextFile(sThisPage, true)
f.write "<html>"& vbcrlf
f.write "<body>"& vbcrlf
f.write rsPages("pgContent") & vbcrlf
f.write "</body>"& vbcrlf
f.write "</html>"& vbcrlf

'Begin the loop
rsPages.MoveNext
LOOP

You'd obviously have to amend that to suit yourself - it's not a plug and play script. You can add more info into f.write section for different head tags, such as description, keywords, title, etc. and they can all be pulled from the database based on the current file being created.

Don't know how much of a newbie you are so that might sound too techie for you but it's a good way of creating search engine friendly html files from a database. Don't forget to link to them from the home page so that spiders can find them.

Some good sites I've used in the past:
www.dmxzone.com
www.asp101.com
www.4guysfromrolla.com
www.sloppycode.net (great code examples on this site)

Hope that helps!

Rgds,

Dave

yeraulone · 25-11-2005 4:01pm

cheers damalo - I'm ok with database connections, thanks for your answer.

DJB - Excellent mate. Thats what I'm after. Thanks. Quick question:

How would this page be initiated? What i mean is, how does it know when to run to check the databsae for new records?

As for linking them to the home page - I was hoping to build these files into an archive, with a link to the archive from the home page.

DJB · 25-11-2005 4:21pm

yeraulone wrote:

cheers damalo - I'm ok with database connections, thanks for your answer.

DJB - Excellent mate. Thats what I'm after. Thanks. Quick question:

How would this page be initiated? What i mean is, how does it know when to run to check the databsae for new records?

As for linking them to the home page - I was hoping to build these files into an archive, with a link to the archive from the home page.

Phew! I tought I might get slated for over complicating things!

You can initiate this in a couple of ways. You can have it run every day at a particular time using a windows scheduled task. But I think the best way to do it is to create this as a little function. Do you add/update your database by a asp web page or is it by direct input or another method? If you do it on an asp page, you can call the function after an add/update has been made. The script above would only need to be run the first time to create all the pages from current records. When records are updated, you can run the script again but don't do the loop, just do it for the one record. Same goes for new records. Insert the record and after it is inserted run the function again. Your code would look something like this:

sSQL = "UPDATE tblPages SET......."
CALL UpdateRec(sName,sSQL,sDatabase)

CALL UpdateHtmlFile(sRecord)

Response.Redirect("complete.asp")

So, your basically adding a step between your update/insert script and your redirect script. This way, if any update is done to the database, your html files get updated automatically.

For your archive page. That's a simple recordset page, call it archive.asp. The link you would create to the traditional "detail" page would be:

<%
DO WHILE NOT rsPages.EOF
%>
<a href="../htmlpages/<% = rsPages("pgID") %>.html"><% = rsPages("pgName") %></a><br />
<%
rsPages.MoveNext
LOOP
%>

Search engines would have no problem picking any of this up. You are not using querystrings anywhere in it.

Cheeurs,

Dave

yeraulone · 25-11-2005 4:32pm

You're a legend.

I reckon that a windows scheduled task will be the way to go. The database is updated from a xml page with is updated via a .vbs script. Its set to run every 15 mins or so. So for this script, as its a monthly archive, it will be cool to run it once a month I guess.

Man thanks for the feedback - really helpfull

J

yeraulone · 25-11-2005 5:08pm

Hello again

I've taken the code and modified it slightly. It gets to line 43 and then gives me this error message:

Error Type:
Microsoft VBScript runtime (0x800A0046)
Permission denied
/newsfeed/index.asp, line 43

which is this line:

Set f = sFSO.CreateTextFile(sThisPage, true)

And ideas as to what might be the problem here? i take it it may have something to do with my IIS settings?

Thanks

J

Heres the rest of my code:

<%

Dim Connection, Recordset
Dim sSQL, sConnString
Dim sFSO
Dim sThisPage
Dim TempPath
Dim f

'declare SQL statement that will query the database
sSQL = "SELECT * FROM tblPages"

'define the connection string, specify database 
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
"Data Source=" & Server.MapPath("news.mdb") 


'create an ADO connection and recordset
Set connection = Server.CreateObject("ADODB.Connection")
Set recordset = Server.CreateObject("ADODB.Recordset")

'Open the connection to the database
connection.Open sConnString

'Open the recordset object, execute the SQL statement
recordset.Open sSQL, connection

'create an instance of the FileSystemObject
SET sFSO = Server.CreateObject("Scripting.FileSystemObject")

'Loop through all your records in the database
DO WHILE NOT recordset.EOF

'Map a path to you unique file name based on the id in the database
sThisPage = Server.Mappath("htmlpages/"& recordset("pgID") &".htm")

'Check if the file exists already and delete it
TempPath=sThisPage 
IF sFSO.FileExists(TempPath) = TRUE THEN
sFSO.DeleteFile(TempPath)
END IF

'Create a new file with the same name and fill it with content
Set f = sFSO.CreateTextFile(sThisPage, true)
f.write "<html>"& vbcrlf
f.write "<body>"& vbcrlf
f.write recordset("pgContent") & vbcrlf
f.write "</body>"& vbcrlf
f.write "</html>"& vbcrlf

'Begin the loop
recordset.MoveNext
LOOP

%>

DJB · 25-11-2005 6:42pm

I've gotten this myself when testing this script initially. If you're file structure is like this:

/pages/thisscript.asp
/pages/htmlpages/

Change this line:
sThisPage = Server.Mappath("htmlpages/"& recordset("pgID") &".htm")

to:

sThisPage = Server.Mappath("../pages/htmlpages/"& recordset("pgID") &".htm")

Make sure there is a folder called htmlpages already created. This can also cause this error because if the folder isn't created first manually, you're trying to create a folder and a file. That was my interpretation of it and it worked once done.

Try that first. If still same problem make sure you have read/write and maybe script source access permissions set in IIS.

Rgds,

Dave

yeraulone · 28-11-2005 12:51pm

thanks again Dave.

DJB · 28-11-2005 1:57pm

No problem. Did that work as a matter of interest (the last bit)?

Dave

ASP newbie question

Comments