Return Multiple Resultsets with DataReader
1: Dim objConn As SqlConnection
2: Dim objCmd As SqlCommand
3: Dim dataReader As SqlDataReader
4: Dim strSql As String
5: Dim txtCarrier as String
6: Dim txtFromPort as String
7: Dim txtToPort as String
8: Dim txtContainer as String
9: Dim txtProduct as String
10:
11: Sub Page_Load(Source As Object, E As EventArgs)
12:
13: 'SQL connection.
14: objConn = New SqlConnection("Data Source=xxxxxxx;" _
15: & "Initial Catalog=xxxxxxxx;User Id=sa;Password=xxxxxx;" _
16: & "Connect Timeout=15;Network Library=dbmssocn;")
17:
18: If Not Page.IsPostBack Then
19: LoadCombos
20: End If
21:
22: End Sub
23:
24: Sub LoadCombos()
25:
26: strSql = "SELECT SP_Key, SP_Name FROM tbl_Service_Provider_Codes WHERE SP_Type = 'C' ORDER BY SP_Name;" _
27: & "SELECT tbl_Port_Codes.Port_Key, tbl_Country_Codes.Country_Name + '-' + tbl_Port_Codes.Port_Name as Port FROM tbl_Port_Codes INNER JOIN tbl_Country_Codes ON tbl_Port_Codes.Port_Country_Key = tbl_Country_Codes.Country_Key Where tbl_Port_Codes.Port_Mode = 'O' ORDER BY Port;" _
28: & "SELECT tbl_Port_Codes.Port_Key, tbl_Country_Codes.Country_Name + '-' + tbl_Port_Codes.Port_Name as Port FROM tbl_Port_Codes INNER JOIN tbl_Country_Codes ON tbl_Port_Codes.Port_Country_Key = tbl_Country_Codes.Country_Key Where tbl_Port_Codes.Port_Mode = 'O' ORDER BY Port;" _
29: & "SELECT Product_Key, Product_Name FROM tbl_Product_Codes ORDER BY Product_Name;" _
30: & "SELECT Container_Key, Container_Name FROM tbl_Container_Codes ORDER BY Container_Name;"
31:
32:
33: objCmd = New SqlCommand(strSql, objConn)
34: Try
35: objConn.Open()
36: dataReader = objCmd.ExecuteReader()
37: 'Carriers
38: With ddlCarriers
39: .DataSource = dataReader
40: .DataTextField = "SP_Name"
41: .DataValueField = "SP_Key"
42: .DataBind()
43: End With
44: dataReader.NextResult()
45: 'From PortCodes
46: With ddlFromPorts
47: .DataSource = dataReader
48: .DataTextField = "Port"
49: .DataValueField = "Port_Key"
50: .DataBind()
51: End With
52: 'To PortCodes
53: dataReader.NextResult()
54: With ddlToPorts
55: .DataSource = dataReader
56: .DataTextField = "Port"
57: .DataValueField = "Port_Key"
58: .DataBind()
59: End With
60: 'Products
61: dataReader.NextResult()
62: With ddlProducts
63: .DataSource = dataReader
64: .DataTextField = "Product_Name"
65: .DataValueField = "Product_Key"
66: .DataBind()
67: End With
68: 'Container
69: dataReader.NextResult()
70: With ddlContainers
71: .DataSource = dataReader
72: .DataTextField = "Container_Name"
73: .DataValueField = "Container_Key"
74: .DataBind()
75: End With
76: Catch exc As Exception
77: Response.Write(exc)
78: Finally
79: If Not dataReader Is Nothing Then
80: dataReader.Close()
81: End If
82: objCmd = Nothing
83: If objConn.State = ConnectionState.Open Then
84: objConn.Close()
85: End If
86: objConn.Dispose()
87: End Try
88:
89: End Sub
Posted by sachauncey at March 19, 2003 08:01 PM