使用NextRecordset通过一个Connection输出多个select查询

 1   
 2dim conn   
 3dim strconn   
 4dim rs   
 5dim strsql   
 6dim strsql2   
 7dim strsql3   
 8dim strsql4   
 9dim strsql5   
10dim strsql6   
11dim strsql7   
12dim strsql8   
13  
14'strconn = Driver={SQL Server};Description=example;SERVER=222.222.1.2;UID=webexample;PWD=;DATABASE=webexample"   
15  
16  
17'Format Declare & EXEC statements that will be passed   
18'to the database with the output parameters   
19strsql = "DECLARE " & CHR(10) & "@Id_Req " & "INT" & CHR(10)   
20strsql2 ="exec " & "sp_EmpInfo" & " '" & request("txtFirstName") & "'," & "'" & request("txtLastName") & "', " & "'" & request("txtaddress") & "', " & "'" & request("txtcity") & "', "& "@Id_Req " & "OUTPUT" & chr(10)   
21  
22'Formats one or more sql statements that will be passed to the   
23'database In this examples I use six different ways.   
24strsql3 ="SELECT * FROM AllData WHERE RecordId = @Id_Req" & Chr(10)   
25strsql4 ="SELECT AllData.fname, AllData.lname FROM Alldata WHERE RecordId = @Id_Req" & Chr(10)   
26strsql5 ="SELECT AllData.fname FROM AllData WHERE RecordId = @Id_Req" & Chr(10)   
27strsql6 ="SELECT AllData.lname FROM AllData WHERE RecordId = @Id_Req" & Chr(10)   
28strsql7 ="SELECT AllData.Address FROM AllData WHERE RecordId = @Id_Req" & Chr(10)   
29strsql8 ="SELECT AllData.city FROM AllData WHERE RecordId = @Id_Req" & Chr(10)   
30  
31'Puts together all of the local variables into one variable   
32'That will be used by the recordset object   
33strsql = strsql & strsql2 & strsql3 & strsql4 & strsql5 & strsql6 & strsql7 & strsql8   
34  
35'This is optional this writes out the strsql local variable   
36'that will be passed to the database   
37response.write "

<b>" &amp; "Sql Statement that is passed to the database" &amp; "</b>

1" & "

<br/>

1"   
2response.write strsql & "

<br/>

1" & "

<br/>

 1"   
 2  
 3'sets a connection & recordset objects and executes the strsql local variable   
 4set conn = server.createobject("adodb.connection")   
 5conn.open strconn   
 6set rs = server.createobject("adodb.recordset")   
 7rs.open strsql, conn   
 8  
 9'Parses out the individual recordsets and places them   
10'into individual table rows   
11intcount = 1   
12Do Until rs Is Nothing   
13response.write "

<table border="1" width="25%">"

response.write "<b> Contents of recordset #" &amp; intCount &amp; "</b><br/>"
'Parses out the individual recordsets and places them into table rows
Do While Not rs.EOF
response.write "<tr>"
For Each oField In RS.Fields
response.write "<th>" &amp; oField.Name &amp; "</th>"
Next
Response.write "</tr>" &amp; "<tr>"
For Each oField In RS.Fields
response.write "<td align="center">"
If IsNull(oField) Then
Response.Write " "
Else
Response.Write oField.Value
End If
response.write "</td>"
Next
rs.MoveNext
Loop
'Uses the NEXTRECORDSET Method
Set rs = rs.NextRecordset
intCount = intCount + 1
response.write "</tr></table>

1"   
2Loop   
Published At
Categories with Web编程
Tagged with
comments powered by Disqus