The first one in the following example Code Block for a certain type of ASP Application Program This type of application uses ADO to read and operate records returned from a single SQL query. It uses the ADO recordset object to read data records returned from the northwind sample database provided by Microsoft Access. The code will be included in files with. asp file extensions.
[Visual Basic]
<% @ Language = VBScript %>
<!
This ASP example uses ADO to read records from a database and print two
Fields from all returned records to an ASP page. Connection to the northwind database is through an ODBC system data source (DSN.
>
<HTML>
<Body>
<%
Dim adoconn, adors, sqlstr
Sqlstr = "select * from employees ;"
Set adoconn = server. Createobject ("ADODB. Connection ")
Adoconn. Open "DSN = test"
Set adors = adoconn.exe cute (sqlstr)
If adors. bof and adors. EOF then 'query didn' t return any records.
Response. Write ("no records .")
Else
Adors. movefirst
Do while not adors. EOF
Response. Write (adors ("firstname ")&""_
& Adors ("lastname") & "<br> ")
Adors. movenext
Loop
Response. Write ("<p> end of data .")
End if
Adors. Close
Set adors = nothing
%>
</Body>
</Html> The following example illustrates the minimum changes required to convert the previous example to an ASP. NET application. Most changes are necessary to conform to the new visual basic syntax. This file can be renamed with the. aspx file extension and will run with ASP. NET. The modified code lines are displayed in bold. Note: The <% @ page> command with the aspcompat = true attribute is added on the first line. [Visual Basic]
<% @ page aspcompat = true language = VB %>
This example uses ADO to read records from a database and print two
fields from all records in the database to an ASP. NET page.
the database is located on the server and connection is through an ODBC system data source (DSN.
<%
dim objconn, RS, sqlstr
sqlstr = "select * from employees;"
objconn = server. createobject ("ADODB. connection ") 'set removed.
objconn. open ("DSN = test") 'parentheses added.
rs = objconn.exe cute (sqlstr) 'set statement removed.
response. write (" ADO test ") If Rs. bof and Rs. EOF then 'query didn't return any records.
Response. Write ("no records ")
Else
Rs. movefirst
Do while not Rs. EOF
'Specify value property.
Response. Write (RS ("firstname"). Value _
& "" & RS ("lastname"). Value & "<br> ")
Rs. movenext
Loop
Response. Write ("<p> end of Data ")
End if
Rs. Close
Rs = nothing 'set statement removed.
%> The next example is an ASP. NET application that uses ADO. Net to read records from the same northwind database as in the previous example. The output generated by these codes is equivalent to the output in the previous example and has been modified to comply with ASP. NET code block conventions. This example creates an ADO. Net DataSet object. In this case, this object contains a data table, which is used in almost the same way as the ADO record set. Note that a dataset can be composed of one or more Ables, datarelations, and constraints that constitute a memory-resident database? The ADO record set is much more flexible. To use ADO. net, you need to import the system. Data and system. Data. oledb namespaces. If the data source is an SQL Server database, import the system. Data. sqlclient namespace instead of system. Data. oledb. For more information about connecting objects using ADO and SQL. NET data providers, see manage connections. [Visual Basic]
<% @ Import namespace = "system. Data" %>
<% @ Import namespace = "system. Data. oledb" %>
<!
This example uses ADO. Net to read records from a database and print two
Fields from all returned records to an ASP. NET page. The database
Is located on the local server.
>
<HTML>
<Script language = VB runat = Server>
Sub page_load (sender as object, e as eventargs)
Dim myconnection as oledbconnection
Dim mycommand as oledbdataadapter
Dim mydataset as Dataset
Dim mytable as datatable
Dim loop1, numrows as integer
Dim sqlstr as string
Sqlstr = "select * from employees ;"
'Create a connection to the data source.
Myconnection = new oledbconnection ("provider = sqloledb ;"_
& "Server = localhost;" Integrated Security = sspi ;"_
& "Initial catalog = northwind ") 'Create a command object with the SQL statement.
Mycommand = new oledbdataadapter (sqlstr, myconnection) 'Fill a dataset with data returned from the database.
Mydataset = new dataset
Mycommand. Fill (mydataset)
'Create a new able object and assign to it
'The new table in the tables collection.
Mytable = new datatable
Mytable = mydataset. Tables (0)
'Find how many rows are in the rows collection
'Of the new datatable object.
Numrows = mytable. Rows. Count
If numrows = 0 then
Response. Write ("<p> no records. </P> ")
Else
Response. Write ("<p>" & CSTR (numrows) & "records found. </P> ")
For loop1 = 0 to numrows-1
'Print the values of the two columns in the columns
'Collection for each row.
Response. Write (mytable. Rows (loop1). Item ("firstname ")_
& "" & Mytable. Rows (loop1). Item ("lastname") & "<br> ")
Next loop1
End if
Response. Write ("<p> end of data. </P> ")
End sub
</SCRIPT>
</Html> When a single record set is returned for database queries (or even multi-table join queries, you can use a single able (mytable in this example) in almost the same way as using the ADO record set ). Refer to Net Framework SDK document |
