I like using datarows and datatables as multi-dimensioal array structures. Their most common use seems to be iterating through datatables, but you can also populate them as you would an array but with strongly typed values. This is a demonstration of populating a datarow with a combination of datareader, datarow and direct assignment. I have encapsulated it in a function to avoid using public variables.
Function GetOrderData(ByVal rAccount As DataRow, ByVal rOrder As DataRow) As DataRow
Dim strErrorMessage As String
Dim intExitCode As Integer
Dim StartDate As Date
Dim EndDate As Date
Try
Dim strConnectionString As String = “Server=DBSERVER;Database=DB;User ID=Program;Password=Password;Trusted_Connection=False”
Dim myConnection As SqlConnection = New SqlConnection(strConnectionString)
myConnection.Open()
Dim myCommand As SqlCommand = New SqlCommand(“usp_sel_Query”, myConnection)
Dim mySqlReader As SqlDataReader
‘Get original account info
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add(“@ID”, rAccount.Item(“ID”))
mySqlReader = myCommand.ExecuteReader
Do
While mySqlReader.Read()
If IsDBNull(mySqlReader(“Collected”)) Then rOrder.Item(“Collected”) = 0 Else rOrder.Item(“Collected”) = mySqlReader(“Collected”)
If IsDBNull(mySqlReader(“EndDate”)) Then EndDate = Nothing Else EndDate = mySqlReader(“EndDate”)
rOrder.Item(“CustomerID”) = rAccount.Item(“CustomerID”)
rOrder.Item(“OrderDate”) = rAccount.Item(“OrderDate”)
rOrder.Item(“AccountDate”) = rAccount.Item(“AcctAssign”)
rOrder.Item(“Orders”) = 1 + 5
rOrder.Item(“Package”) = rAccount.Item(“Package”)
End While
Loop While mySqlReader.NextResult()
mySqlReader.Close()
myConnection.Close()
GetOrderData = rOrder
Catch ex As Exception
strErrorMessage = ex.Message
intExitCode = Err.Number()
Finally
If intExitCode <> 0 Then
MsgBox(“Error ” & intExitCode & ” occurred for CustomerID ” & rAccount.Item(“CustomerID”) & “. ” & strErrorMessage, MsgBoxStyle.OKOnly + MsgBoxStyle.Critical)
End If
End Try
End Function