Home / code / asp

Data-access,

 

Reading of ASCII text-files with #include

You can read and write to a ASCII text-file. You can use it for many different applications.
An example is: The weather prediction. The weather predictions are written in weather.txt. You only have to update the TXT file not the ASP or HTML-files. This is dynamic content.

The include statement

Include - insert ASCII-code

<!-- #include file="path/text.txt" -->
<!-- #include virtual="path/text.txt" -->

This file can contain text, but also HTML-statements and ASP-instructions. You use file if the path is physical, and virtual if the path is virtual.

The entire file is read and assigned to the variable, this can be useful if you have to search for a text-string.

Example:

text.txt 

line 1
line2
last line

The asp page 

<HTML>
<HEAD><TITLE>Reading text with include</TITLE><HEAD>
<BODY>
<H2>Reading from a file.</H2>
<br>

<!-- #include file="text.txt" -->

</BODY>
</HTML>

The result will then be: 

<HTML>
<HEAD><TITLE>Reading text with include</TITLE><HEAD>
<BODY>
<H2>Reading from a file.</H2>
<br>

line 1
line2
last line

</BODY>
</HTML>

 

Using Access-databases with ODBC through the component ADO

To communicate with a database you have possibilities, through ODBC or OLE. Here we explain ODBC.

Checking a DSN-connection.
To connect to a database, ASP can use drivers in the Open DataBase Connection system.
When, on the computer where PWS or IIS is installed, office/lotus notes or any other database program is installed, there is a new icon in the control panelODBC Data Sources (32bit).

There are 2 ways to make the connection, through system or file. I use system.
Click add.
I use Microsoft access (office), so we use Microsoft Access Driver [*.mdb]

Type the data source name in. (usually the same as the database-file name, without .mdb)
Click on select to select the database.
When you finished this you'll see that the database is entered in the System DSN.

SQL
Structured Query Language is the language used to select the data from the database.

You are supposed to know the tables in the database and the structure of the tables.

For more information on SQL check this page

In ASP

First you need to make a connection: 

set conn=server.createobject("ADODB.connection")
set rs=server.createobject("ADODB.recordset")
conn.open "database_name"

The you make a selection with a SQL instruction 

set rs=conn.execute("select * from table")

The you print the selection, usually with a loop. 

response.write("<table>")
do while not rs.eof
  response.write("<tr>")
  for i=0 to rs.fields.count - 1
    response.write("<td>"& rs(i) &"</td>")
  next
response.write("</tr>")
rs.movenext
loop
response.write("</table>")

Finally you close the connection: 

rs.close
conn.close
set rs=Nothing
set conn=Nothing

 

Using Access-databases with OLE through the component ADO

ODBC is really easy, but you cannot define DSN-connections from the asp-page itself. With OLE you can.

Again we need to open the connection: 

set conn =Server.CreateObject("ADODB.Connection")
conn.provider = "Microsoft.Jet.OLEDB.4.0"
Conn.ConnectionString="Data Source="& Server.MapPath("../database/database.mdb")
conn.open

The you make a selection with a SQL instruction 

set rs=conn.execute("select * from table")

The you print the selection, usually with a loop. 

response.write("<table>")
do while not rs.eof
  reponse.write("<tr>")
  for i=0 to rs.fields.count - 1
    response.write("<td>"& rs(i) &"</td>")
  next
response.write("</tr>")
rs.movenext
loop
response.write("</table>")

As you can see we specify a connection provider (Microsoft.Jet.OLEDB.4.0). This database engine is in PWS,MS SQL,IIS 4.0. You can download extra software from the option packs from Microsoft.

GetString
This recordset has some special methods, one is GetString.This allows you to write the entire table at once, without the while loop. 

rs.GetString(w,x,y,z)
  w=sorting
  x=number of fields
  y=separation between fields(column)
  z=separation between records(rows)
  there can be a 5Th element, for a empty field.

Example: 

<HTML>
<HEAD><TITLE>OLE connection with string</TITLE></HEAD>
<BODY>
<%
set con =Server.CreateObject("ADODB.Connection")
conn.provider = "Microsoft.Jet.OLEDB.4.0"
Conn.ConnectionString="Data Source="& Server.MapPath("../database/database.mdb")
conn.open
msql="select * from table"
set rs=conn.execute(msql)
response.write("<table><tr><td>")
response.write(rs.getstring(,,"</td><td>","</td></tr><tr><td>","&nbsp;"))
response.write("</table>")
%>
</BODY>
</HTML>

 

Connecting to SQL Server databases through OLE with the component ADO

To make a connection to SQL server we use the following code: 

sConnect="DRIVER={SQL Server};SERVER=SQLINF;DATABASE=databasename;UID=userid;PWD=password"
set conn = Server.CreateObject ("ADODB.Connection")
Conn.Open sConnect
The driver DRIVER={SQL Server}
The server SERVER=SQLINF
The database DATABASE=databasename
The username you want to use UID=userid
The corresponding password PWD=password

 

Requesting information on the connection.
In the next program you can display all information on the connection by using properties. 

<HTML>
<HEAD><TITLE>Display info on connection</TITLE></HEAD>
<BODY>

<%
dim conn
dim objprop
set conn = server.CreateObject ("ADODB.Connection")
conn.open "database"

response.write("<table>")
for each objprop in conn.properties
%>
  <tr><td><% =objprop.name %></td>
  <td><% =objprop.value %> </td></tr>
<%
next
conn.close
set conn=Nothing
%>

</TABLE>
</BODY>
</HTML>

Track down errors
If you want to track down errors you can use the following code to see what went wrong with SQL-instruction 

<HTML>
<HEAD><TITLE>Track down errors</TITLE></HEAD>
<BODY>

<%
on error resume next
dim conn, rs
dim objerr
set conn=server.CreateObject ("ADODB.Connection")
conn.open "database"
set rs=conn.execute("select nocolumn from notable")
if conn.errors.count > 0 then
    response.write("<table>")
    for each objerr in conn.errors
    %>
	<tr><td>Number</td>
	<td><% =objerr.number %></td></tr>
	<tr><td>NativeError</td>
	<td><% =objerr.nativeerror %gt;</td></tr>
	<tr><td>SQLState</td>
	<td><% =objerr.sqlstate %gt;</td></tr>
	<tr><td>Cource</td>
	<td><% =objerr.source %gt;</td></tr>
	<tr><td>Description</td>
	<td><% =objerr.description %gt;</td></tr>
    <%
    next
else
    response.write("Everything is ok")
    rs.close
    set rs=nothing
end if
conn.close
set conn=Nothing
%>

</TABLE>
</BODY>
</HTML>

 

TOP

Latest script:

 

Books: