Home / code / asp

More on databases,

 

Recordset object

A recordset holds the result of the SQL-instruction SELECT.

To open a recordset we can add more parameters then the table name and the SQL-instruction. With these parameters we can add the cursor type, locking type and the options.

To make it all a lot simpler, Microsoft has given the numbers a name and put them all in 1 file. (adovbs.inc)

Example: 

<!-- #include file="adovbs.inc" -->
<%
dim conn, rs
set conn=server.createobject("")
set rs=server.createobject("ADODB.connection")
conn.open "table"
rs.open "table", conn, adopenForwardOnly, adLockReadOnly, adCmdTable
%>

The code of the method open:
recordset.open"tablename", connection, curortype, lockingtype, options

Recordset types:

updateable and non-updateable recordset The data of the recordset can or can not be changed.
scrollable and non scrollable recordsets With scrollable recordsets you can move both forwards and backwards
keyset and non-keyset recordsets With most tables there is a key-kolumn (or columns). With these tables it is interesting to work with keyset recordsets. Only the key-column is send and only when you want to see the record the other data is send. This works faster then non-keyset recordsets.
dynamic and static recordsets When you have defined a recordset, you can view the data. If in the mean time another user change records, these changes are automatic visible with dynamic recordset and not with static recordsets.

Properties of the recordset object

SOURCE
This is the table, SQL-instruction, the view or stored procedure where the recordset is based on.

Example: 

dim rs
set rs=server.createobject("ADODB.Recordset")
rs.source "select * from table"
or 
dim rs
set rs=server.createobject("ADODB.Recordset")
rs.source "table"

ACTIVECONNECTION
In ActiveConnection you decide which connection is used. Infact you don't have to create a connection-object.

Example:
With connection object: 

dim rs, conn
Set Conn = Server.CreateObject ("ADODB.Connection")
set rs = server.createobject("ADODB.Connection")
'for SQL server
' conn.open "Driver={SQL Server};Server=SQLINF;Database=databasename;UID=userid;PWD=password"
'for access
conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source="& server.MapPath("../databases/databasename.mdb") &";USERID=userid;Password=password;"
rs.activeconnection=conn
Without connection object: 
dim rs
set rs = server.createobject("ADODB.Connection")
'for SQL server
' conn.open "Driver={SQL Server};Server=SQLINF;Database=databasename;UID=userid;PWD=password"
'for access
rs.activeconnection="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source="& server.MapPath("../databases/databasename.mdb") &";USERID=userid;Password=password;"

CURSORTYPE

Forward Only non-scrolable, static (default)
Static Scrollable, static
Dynamic Dynamic, scrollable
Keyset Changes are visible, addings are not.
The constants are: 
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

Example: 

rs.cursortype = adOpenForwardOnly

LOCKTYPE
How do we lock the recordset for the others?

Read-only No locking. (default)
Pessimistic The record is locked as soon as you use it. No one else has then access.
Optimistic The record is locked when you change it. No one else has then access.
Optimistic Batch Let you change more records at once. The records are locked one by one when they get changed and then released.
The constants are: 
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

Example: 

rs.locktype = adLockReadOnly

OPTIONS
With the parameter options you specify which info the property source holds. Option is not a property of the recordset.

adCmdText The source holds a SQL-instruction.
adCmdTable The source holds a table
adCmdStoredProc The source holds the name of a stored procedure or a query.
adCmdUnknown The source holds a unknown type.

Searching and showing data

OPEN
The recordset is opened with the method open.
recordset.open source, activeconnection, cursortype, locktyp, options

Example: 

rs.open "table", conn, adForwardOnly, adLockReadOnly, adCmdTable

CLOSE
If you don't need the recordset anymore, you need to close it so you can release the locked records.
recordset.Close

EOF and BOF
If you open the recordset, the pointer of the recordset is pointed on the first record. With the methods MoveNext and MovePrev you can move the pointer forward and backward.

The EOF becomes true when the pointer is after the last record. The BOF becomes true when the pointer is before the first record. 

dim rs, conn
set conn = server.createobject("ADODB.connection")
set rs = server.createobject("ADODB.connection")
conn.open "Driver={SQL server}, Server=SQLINf, Database=tablename, UID=userid, PWD=password"
rs.open "table", conn,adForwardOnly,adLockReadOnly,adCMdTable
do while not rs.eof
    ...
    rs.MoveNext
loop

BOOKMARKS
A bookmark is a position in the recordset you want to remember. Bookmarks can only be used with keysets and static records. 

dim varBookmark
varBookmark=rs.bookmark

MOVENEXT, MOVEPREVIOUS, MOVEFIRST, MOVELAST, MOVE

rs.movenext Go to the next line.
test on rs.eof
rs.moveprevious Go to the previous line.
test on rs.bof
rs.movefirst Go to the first line
rs.movelast Go to the last line
rs.move, numberrecs, startpos Go to a specific position. numberrecs from the startpos
startpos can be :
adBookmarkCurrent the current position
adBookmarkFirst the fisr line
adBookmarkLast the last line

Example:
rs.move 3, adBookmarkCurrent
3 records forward from the current position.

FIND RECORDS
With Find and Filter you can search 1 or more records from a opened recordset.
With find you search for the first record the complies with the conditions. With filter all the records that complies with conditions.

Find
recordset.find criterium, skiprecords, searchdirections, startpos

criterium Holds the condition
The allowed comparisons:
<, >, =, like;
rs.find "name like '*white*'"
skiprecords The move from the startpositions from where the search should start.
searchdirections forward : adSearchForward
backward = adSearchBackward
startpos The bookmark that holds the start positions.

Filter
recordset.filter = "condition"

Example 

recordset.filter = "city = 'New York'""
recordset.filter = adFilterNone
With adFilterNone you remove the filter.

Fields collection
A row exists of a number of columns that we can address with recordset("columnname"). Then you need to know the column names to show the data. The fields collection holds the data of the row. Column name and the value of the current row.

We can make a function that makes a table on basis of the data of the recordset. 

<%
function RecToTable (objRec)
  dim strT		'table html-string
  dim fldF		'current field object
  'the table header
  strT = "<TABLE BORDER=1><TR ALIGN=CENTER>"
  'show the column name of every field
  For each fldF in objRec.Fields
	strT = strT &"<TD>"&fldF.name&"</TD>"
  Next
  strT = strT &"</tr>"
  'the rows
  whileNot objRec.EOF
	strT =strT &"<TR ALIGN=CENTER>"
	'column
	For each fldF in objRec.Fields
		strT=strT &"<TD>"& fldF.value &"</TD>"
	next
	strT = strT &"</TR>"
	objRec.MoveNext
  Wend
  strT = strT &"</TR>"
  'returning the table to the script
  RecToTable =strT
End function
%>
Then you can call the function with:
response.write rectotable(objrec)

GetRows
With this method the requested rows are put in a 2 dimensional table.
table = recordset.GetRows (rows, start, colomns)

rows The numbers of rows you want to get. With the constant adGetRowsRest you get all the rows from the current position to the end of the recordset.
Start The bookmark that is the start position
columns column name or number

Example: 

<HTML>
<HEAD><TITLE>GetRows</TITLE></HEAD>
<BODY>

<!-- #include file="../databases/adocbs.inc" -->
<%
dim rs, conn,i,x,table,mrow,mcolumn
set conn = server.createobject("ADODB.connection")
set rs = server.createobject("ADODB.Recordset")
'for SQL server
' conn.open "Driver={SQL Server};Server=SQLINF;database=databasename;UID=userid,PWS=password"
' for access
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.ConnectionString="Data Source="&server.MapPath("../databases/database.mdb")
conn.open
msql="select * from table"
rs.open msql,conn,adOpenStatic, adLockReadOnly, adCmdText
table = rs.GetRows()
mrow = ubound(table,2)
mcolumn = ubound(table,1)
rs.close
conn.close
set rs=nothing
set conn=nothing
%>
<H3>Showing all data in the table</H3>
The table contains <% =ubound(table,2) %> rows and <% =ubound(table,1) %> columns
<br><table border=1>
<% for i=1 to mrow %>
  <tr>
	<% for j=1 to mcolumn %>
	  <td><%
	  if isnull(table(j,i)) then
		response.write(" ")
	  else
		response.write(" ")
	  end if
	  %></td>
	<% next %>
  </tr>
<% next %>
</table>

</BODY>
</HTML>

 

Modify data

The command object
This command can, like the connection object, make a connection to a database, returning a recordset but also using a SQL-instruction that doesn't return a value, like insert update and delete.

set objcommand = server.createobject("ADODB.Command")
objcommand.ActiveConnection = strconnect
objcommand.commandtext = "SQLInstr." or "table" or "stor. proc"
objcommand.commandtype = commandtype
objcommand.execute

Example: 

dim rs, objcomm, strconn
set objcomm = server.createobject("ADODB.command")
set rs = server.createobject("ADODB.command")
strconn = "Driver={SQL server};sever=SQLINF;database=databasename;UID=userid;PWD=password"
objcomm.ActiveConnection = strconn
objcomm.commandtext = "table"
objcomm.commandtype = adCmdTable
set rs = objComm.Execute
The last instruction can be replaced by:
rs.open objcomm

Example 2 (update.asp) 

<HTML>
<HEAD><TITLE>Update</TITLE></HEAD>
<BODY>

<!-- #include file="adocbs.inc" -->
<%
dim rs, strconn, number, msql, comm
set comm = server.createobject("ADODB.command")
' for SQL server
'strconn = "Driver={SQL server};sever=SQLINF;database=databasename;UID=userid;PWD=password"
' access
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.ConnectionString="Data Source="&server.MapPath("../databases/database.mdb")
comm.activeconnection = strconn
msql = "update table set columnname='new value' where columnname=value"
comm.commandtext=msql
comm.commandtype=adCmdText
comm.Execute number
response.write ( number &" rows are modified")
%>

</BODY>
</HTML>

Modify data with connection.execute
connection.execute commandtext, recordsaffected, options

Add data with a recordset

ADDING A ROW 

<HTML>
<HEAD><TITLE>Adding a row mith a recordset</TITLE></HEAD>
<BODY>

<!-- #include file="adocbs.inc" -->
<%
dim rs, strconn
' first open a recordset
set rs=Server.CreateObject("ADODB.Rcordset")
' for SQL server
' strconn = "Driver={SQL Server};server=SQLINF;Database=database;UID=userid;PWD=password"
' for access
strconn="Provider=Microsoft.Jet.OLEDB.4.0;Data source="&server.MapPath("../databases/database.mdb")
rs.open "table", strconn, adOpenStatic, adLockOptimistic, adCmdTable
' add a empty row
rs.AddNew
'Fill the row with data
rs("columnname1")=value1
rs("columnname2")="value 2"
rs("columnname3")=value3
rs("columnname4")=value4
'writing the data(saving)
rs.update
response.write ("there is 1 row added.")
%>
</BODY>
</HTML>
If you don't want to save the new data: 
' add a empty row
rs.AddNew
'Fill the row with data
rs("columnname1")=value1
rs("columnname2")="value 2"
rs("columnname3")=value3
rs("columnname4")=value4
' DON't write the data (not saving)
rs.CancelUpdate

CHANGING DATA 

<HTML>
<HEAD><TITLE>Adding a row mith a recordset</TITLE></HEAD>
<BODY>

<!-- #include file="adocbs.inc" -->
<%
dim rs, strconn
' first open a recordset
set rs=Server.CreateObject("ADODB.Rcordset")
' for SQL server
' strconn = "Driver={SQL Server};server=SQLINF;Database=database;UID=userid;PWD=password"
' for access
strconn="Provider=Microsoft.Jet.OLEDB.4.0;Data source="&server.MapPath("../databases/database.mdb")
rs.open "table", strconn, adOpenStatic, adLockOptimistic, adCmdTable
' searching for the to change record
rs.filter="columnname=value"
rs("columnname2")="value 2"
' Saving
rs.updatebatch adAffectCurrent
response.write ("The row is changed")
%>

</BODY>
</HTML>
The method updatebatch can have different parameters.
adAffectCurrent Only the changes of the current records are saved.
AdAffectAll You can first change different records and then save them all at once.
adAffectGroup Writes only the changes of the records that complies with the present filter.

UNDO THE CHANGES
rs.cancelbatch adAffectAll

REMOVE RECORDS
to remove the present record:
rs.delete

With parameters:
rs.Delete parameters

adAffectCurrent Only the current record.
adAffectGroup All records that correspond with the current filter.

Example: 

<HTML>
<HEAD><TITLE>removing a row(record)</TITLE></HEAD>
<BODY>

<!-- #include file="adocbs.inc" -->
<%
dim rs, strconn
' first open a recordset
set rs=Server.CreateObject("ADODB.Rcordset")
' for SQL server
' strconn = "Driver={SQL Server};server=SQLINF;Database=database;UID=userid;PWD=password"
' for access
strconn="Provider=Microsoft.Jet.OLEDB.4.0;Data source="&server.MapPath("../databases/database.mdb")
rs.open "table", strconn, adOpenStatic, adLockOptimistic, adCmdTable
' searching for the record
rs.filter="columnname=value"
' removing the record
rs.delete
response.write ("The row is history")
%>

</BODY>
</HTML>
The method delete works immediately, except when you work with the cursortype adLockBatchOptimistic. Then you need to use the method Updatebatch.

 

Making connection for more pages

It is easy to use a connection for other pages, there is the danger to leave this connection open to long. If many users have to make a connection to the same asp-database application, it is better to use the connection on several webpages by putting the connection-code in the Application-object or the session-object of the ASP. This doesn't work for ASP-pages.

Session_OnStart of the file Global.asa, like in the vollowing script: 

set session("conntemp")=server.createobject("ADODB.Connection")
set session("rstemp")=server.createobject("ADODB.recordset")

You use the next sript to open a connection: 

session("conntemp").open"Driver={SQL server};Server=SQLINF;Database=database;UID=userid;PWD=password"
session("rstemp").activeconnection=session("conntemp")
session("rstemp").cursortype=adOpenStatic
session("rstemp").LockType=adLockOptimistic
session("rstemp").open "database"
At the end you close the connection with: 
session("rstemp").close
session("conntemp").close

For a separated user who need to use a connection for several pages it is better to use the session-object then the application-object.

 

TOP

Latest script:

 

Books: