|
Page 1 of 2
Before Asp.net invasion there were many other programming languages and technologies on which the dynamic pages were made. In this tutorial I will explain how we can migrate our classic asp application to the modern asp.net application. Most of the components that were written was in visual basic 6 which communicated with the asp application using the COM components.
Using COM Objects in Asp.net
The Asp.net processor understands nearly all the syntax and all the objects that ASP itself supported. Let's see how you can create a simple ADO Connection object in either an ASP page or an ASP.NET page with this line of code:
cnn = Server.CreateObj("ADODB.Connection");
Not all COM Components can be instantiated in ASP.NET this way. In particular, components that use the Single-Threaded Apartment (STA) threading model will not function properly in ASP.NET pages unless you add a compatibility directive to the page.
@Page aspcompat=true
Let's see how we can convert an Asp page to an Asp.net page. The is a simple Asp page that communicates with the database and pulls the result and displays it on the screen.
strConn = Provider=SQLOLEDB;Data Source=(local);"&"uid=sa;password=Database=Northwind;" Set cnn = Server.CreateObject("ADODB.Connection");
cnn.Open strConn
strQuery = "SELECT CompanyName FROM Customers"
SET rstCust = cnn.Execute(strQuery)
Now Lets see that what we have to do to convert the Classic Page to Modern Asp.net page:
@Page aspcompat=true
object cnn;
object rstCust;
object temp;
string strConn;
string companyName;
strCnn = "Provider=SQLOLEDB;Data Source=(localhost);"
cnn = Server.CreateObject("ADODB.Connection");
cnn.GetType().InvokeMember("Open",BindingFlags.InvokeMethod,null,cnn,new object[]
{ strCnn,Type.Missing,Type.Missing,Type.Missing});
strQuery = "SELECT CompanyName FROM Customers";
while
(!(bool) rstCust.GetType().InvokeMember("EOF",BindingFlags.GetProperty,null,rstCust,new object[] {"CompanyName"});
@Page aspcompat=true object cnn; object rstCust; object temp; string strConn; string companyName; strCnn = "Provider=SQLOLEDB;Data Source=(localhost);" cnn = Server.CreateObject("ADODB.Connection"); cnn.GetType().InvokeMember("Open",BindingFlags.InvokeMethod,null,cnn,new object[] { strCnn,Type.Missing,Type.Missing,Type.Missing}); strQuery = "SELECT CompanyName FROM Customers"; while (!(bool) rstCust.GetType().InvokeMember("EOF",BindingFlags.GetProperty,null,rstCust,new object[] {"CompanyName"});
Working with ADO Recodsets
Recordsets are like dataset with less capability and features. Let's see some examples of using RecordSets.
To create a recordset containing all of the records in one table of the database, without sorting them:
strSQLQuery = "SELECT * FROM tablename"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
To create a recordset containing only those records in one table where the field 'Name' consists of only the word 'Fred':
strSQLQuery = "SELECT * FROM tablename WHERE Name = 'Fred'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
To create a recordset containing all of the records in one table and sort them alphabetically based on the field Name starting at 'a':
strSQLQuery = "SELECT * FROM tablename ORDER BY Name ASC"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
To create a recordset containing all of the records in one table and sort them alphabetically based on the field 'Name' starting at 'z':
strSQLQuery = "SELECT * FROM tablename ORDER BY Name DESC"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
If you want to be able to be sure of selecting only one particular record from a database, each table should have a 'Unique Key' field - usually a simple auto-incrementing number field. You can then select, update or delete records in the database using this field as the criteria.
strSQLQuery = "SELECT * FROM tablename WHERE RecordID = 15"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
To retrieve the values of various fields in a Recordset use:
strValue = rs("FieldName")
strValue can of course be any variable name, FieldName is the field name in the table that makes up the recordset. Multiple recordsets can be opened for the same database and/or table - just change the 'rs' to whatever name you want to give to each recordset.
Standard SQL queries can be used to update, add or delete records, or create recordsets based on conditions. For example:
strSQLQuery = "DELETE * FROM tablename WHERE Name = 'Fred'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
|
