Private Sub GetSalesOrderData(ByVal salesOrderNumber As String, _
ByRef dsSalesOrder As DataSet)
Dim sqlSalesOrder As String = _
"SELECT SOH.SalesOrderNumber, S.Name AS Store, " & _
" SOH.OrderDate, SP.FirstName AS SalesFirstName, " & _
" SP.LastName AS SalesLastName, E.JobTitle AS " & _
" SalesTitle, SOH.PurchaseOrderNumber, " & _
" SM.Name AS ShipMethod, BA.AddressLine1 " & _
" AS BillAddress1, BA.AddressLine2 AS " & _
" BillAddress2, BA.City AS BillCity, " & _
" BA.PostalCode AS BillPostalCode, BSP.Name " & _
" AS BillStateProvince, BCR.Name AS " & _
" BillCountryRegion, SA.AddressLine1 AS " & _
" ShipAddress1, SA.AddressLine2 AS " & _
" ShipAddress2, SA.City AS ShipCity, " & _
" SA.PostalCode AS ShipPostalCode, SSP.Name " & _
" AS ShipStateProvince, SCR.Name AS " & _
" ShipCountryRegion, PP.PhoneNumber AS CustPhone, " & _
" CC.FirstName AS CustFirstName, CC.LastName " & _
" AS CustLastName " & _
"FROM Sales.Customer C INNER JOIN " & _
" Sales.SalesOrderHeader SOH ON " & _
" SOH.CustomerID = C.CustomerID " & _
" LEFT OUTER JOIN Sales.Store S ON " & _
" S.BusinessEntityID = C.StoreID " & _
" INNER JOIN Person.Address AS BA ON " & _
" BA.AddressID = SOH.BillToAddressID " & _
" INNER JOIN Person.StateProvince BSP ON " & _
" BSP.StateProvinceID = BA.StateProvinceID " & _
" INNER JOIN Person.CountryRegion BCR ON " & _
" BCR.CountryRegionCode = BSP.CountryRegionCode " & _
" INNER JOIN Person.Address SA ON " & _
" SA.AddressID = SOH.ShipToAddressID " & _
" INNER JOIN Person.StateProvince SSP ON " & _
" SSP.StateProvinceID = SA.StateProvinceID " & _
" INNER JOIN Person.CountryRegion SCR ON" & _
" SCR.CountryRegionCode = SSP.CountryRegionCode " & _
" INNER JOIN Purchasing.ShipMethod SM ON " & _
" SOH.ShipMethodID = SM.ShipMethodID " & _
" LEFT OUTER JOIN Person.Person CC ON " & _
" CC.BusinessEntityID = C.PersonID " & _
" LEFT OUTER JOIN Person.PersonPhone PP ON " & _
" CC.BusinessEntityID = PP.BusinessEntityID " & _
" LEFT OUTER JOIN Person.Person SP " & _
" RIGHT OUTER JOIN HumanResources.Employee E ON " & _
" SP.BusinessEntityID = E.BusinessEntityID ON " & _
" SOH.SalesPersonID = E.BusinessEntityID " & _
"WHERE (SOH.SalesOrderNumber = @SalesOrderNumber)"
Using connection As New SqlConnection( _
"Data Source=(local); " & _
"Initial Catalog=AdventureWorks2008R2; " & _
"Integrated Security=SSPI")
Dim command As New SqlCommand(sqlSalesOrder, connection)
Dim parameter As New SqlParameter("SalesOrderNumber", _
salesOrderNumber)
command.Parameters.Add(parameter)
Dim salesOrderAdapter As New SqlDataAdapter(command)
salesOrderAdapter.Fill(dsSalesOrder, "SalesOrder")
End Using
End Sub