ADO Code Snippets

1. SQLMultiQuery Routine
2. Get SQL column names from record set
3. Get Record Count of Record Set
4. Version of ADO running on Server
5. Return RecordSet as HTML Table
6. Filter a RecordSet
7. Data Shaping Example

1. SQLMultiQuery Routine
   
   

   Private Sub SQLMultiQuery(queryStr, RecSet, Conn)
   
   	Set Conn = Server.CreateObject("ADODB.Connection")
   	'*************************
   	'* Set client side cursor
   	'*************************
   	Conn.cursorLocation = 3
   	Conn.Open "DSN_Name", "SQL_User", "SQL_Password"
   
   	Set Cmd = Server.CreateObject("ADODB.Command")
   	Cmd.CommandText = queryStr
   	Set Cmd.ActiveConnection = Conn
   	Set RecSet = Server.CreateObject("ADODB.Recordset")
   	Set RecSet = Cmd.Execute
   
   End Sub
   
   

2. Get SQL column names from record set
   
   

   CALL SQLMultiQuery (strSQL, RecSet, conn ) 
   
   For Each thing In RecSet.Fields 
     response.write vbcrlf & "
    name = " & thing.Name 
   Next 
   
   

3. Get Record Count of Record Set
   
   

   RecSet.RecordCount
   

4. Version of ADO running on Server
   
   Set Conn = Server.CreateObject("ADODB.Connection") Response.Write "ADO Version = " & Conn.version Set Conn = Nothing
5. Transform RecordSet into HTML Table String - ADO GetString
   
   Dim strTable strTable = RecSet.GetString (,,"</td><td>","</td></tr><tr><td>","&nbsp;") %> <TABLE> <TR> <TD> <% Response.Write(strTable) %> </TD> </TR> </TABLE>
6. Filter a RecordSet
   
   strSQL = "SELECT pub_id, title_id, title FROM titles ORDER BY pub_id, title" Call SQLMultiQuery (strSQL, RecSet, Conn ) you will have a recordset with lots of pub_id RecSet.Filter = "pub_id = '0736'" now the recordset will just have pub_id's with the above value. RecSet.Filter = adFilterNone ' constant from adovbs.inc ( = 0) now we have the full recordset back again
7. Data Shaping
   
   strConnect = "Provider=MSDataShape;data provider=msdasql;Data Source=DSN_NAME;uid=USER;pwd=PASSWD;" Set rsEvents = Server.CreateObject("ADODB.RecordSet") strSQL1 = "SELECT a,b,m FROM tblNameOne" strSQL2 = "SELECT b,x,y FROM tblNameTwo" strSQL3 = "SELECT c,y,z FROM tblNameThree" queryStr = "SHAPE {" & strSQL1 & "} " & _ "APPEND(( SHAPE {" & strSQL2 & "} " & _ "APPEND ( {" & strSQL3 & "} " & _ "RELATE y To y) As rsY)" & _ "RELATE b To b ) As rsB" rsEvents.Source = queryStr rsEvents.ActiveConnection = strConnect rsEvents.Open While Not rsEvents.EOF a = rsEvents.Fields.Item("appearanceOrder").Value Set rsChild = rsEvents.Fields.Item("rsB").Value While NOT rsChild.EOF b = Trim(rsChild.Fields.Item("b").Value) Set rsBaby = rsChild.Fields.Item("rsText").Value While NOT rsBaby.EOF c = rsBaby.Fields.Item("c").Value rsBaby.MoveNext Wend rsChild.MoveNext Wend rsEvents.MoveNext Wend rsEvents.Close
8. 
   
   
9.