Edgar Edgar - 2 months ago 6
SQL Question

How to Add Data from SQL to a table and use that table in a listview

I have a class that grabs data from a table on sql server and I assign the data to variables and I then output the data under columns in a listview, problem Is I am only getting the first row in my sql table I need a loop to fill some container full of the data values and pass all rows to listview. How would I accomplish this, my programing experience is very limited. how would I first get the data from sql into a usable container from the code below?

Public Class Inventory
Public mFirstName As String
Public mLastName As String
Public mComputerType As String
Public mAssetTag As String
Public mCheckOutDate As Date
Public mCheckInDate As Date
Public mExpectedReturnDate As Date
Public mUserEmailAddress As String
Public mLoanSubmitter As String
Public mDeployed As Integer

Public Sub New()
LoadData()
End Sub

Public Sub New(dr As DataRow)

End Sub

Private Sub LoadData()
Dim dbConn As HUG.Core.Database.SQLConnection
Dim sql As String = ""
Dim ds As New DataSet

sql = "SELECT * FROM HDData.dbo.TravelLaptopRecords "

dbConn = New HUG.Core.Database.SQLConnection("WorkFiles")
ds = dbConn.FillDataSet(sql)

If Not IsNothing(ds) Then
If ds.Tables(0).Rows.Count > 0 Then
With ds.Tables(0).Rows(0)
mFirstName = CStr(.Item("FirstName"))
mLastName = CStr(.Item("LastName"))
mComputerType = CStr(.Item("ComputerType"))
mAssetTag = CStr(.Item("AssetTag"))
mCheckOutDate = CDate(.Item("CheckOutDate"))
mCheckInDate = CDate(.Item("CheckInDate"))
mExpectedReturnDate = CDate(.Item("ExpectedReturnDate"))
mUserEmailAddress = CStr(.Item("UserEmailAddress"))
mLoanSubmitter = CStr(.Item("LoanSubmitter"))
mDeployed = CInt(.Item("Deployed"))
End With
End If
End If

End Sub
End Class


this is the form page

Public Class Form1
Private mLaptopInventory As Inventory
Private isLoad As Boolean
Private mFirstName As String
Private mLastName As String
Private mComputerType As String
Private mAssetTag As String
Private mCheckOutDate As Date
Private mCheckInDate As Date
Private mExpectedReturnDate As Date
Private mUserEmailAddress As String
Private mLoanSubmitter As String
Private mDeployed As Integer

Public Sub New()

' This call is required by the designer.
InitializeComponent()

' Add any initialization after the InitializeComponent() call.
HUG.Core.Globals.BootStrap("G:\Programs\somefile.ini")

isLoad = True
End Sub

Private Sub Form1_Shown(Sender As Object, e As EventArgs) Handles Me.Shown
mLaptopInventory = New Inventory()
LoadForm()
isLoad = False

End Sub

Private Sub LoadForm()
GroupBox1.Text = "Travel Laptop Inquiry"
InventoryList.View = View.Details

mFirstName = mLaptopInventory.mFirstName
mLastName = mLaptopInventory.mLastName
mComputerType = mLaptopInventory.mComputerType
mAssetTag = mLaptopInventory.mAssetTag
mCheckOutDate = mLaptopInventory.mCheckOutDate
mCheckInDate = mLaptopInventory.mCheckInDate
mExpectedReturnDate = mLaptopInventory.mExpectedReturnDate
mUserEmailAddress = mLaptopInventory.mUserEmailAddress
mLoanSubmitter = mLaptopInventory.mLoanSubmitter
mDeployed = mLaptopInventory.mDeployed


InventoryList.Items.Add(mFirstName)
InventoryList.Items(InventoryList.Items.Count - 1).SubItems.Add(mLastName)
InventoryList.Items(InventoryList.Items.Count - 1).SubItems.Add(mComputerType)
InventoryList.Items(InventoryList.Items.Count - 1).SubItems.Add(mAssetTag)
If mDeployed = -1 Then
InventoryList.Items(InventoryList.Items.Count - 1).SubItems.Add(mCheckOutDate)
InventoryList.Items(InventoryList.Items.Count - 1).SubItems.Add("Item Not Returned").ToString()
InventoryList.Items(InventoryList.Items.Count - 1).SubItems.Add(mExpectedReturnDate)
Else
InventoryList.Items(InventoryList.Items.Count - 1).SubItems.Add("Item is on Hand").ToString()
InventoryList.Items(InventoryList.Items.Count - 1).SubItems.Add(mCheckInDate)
InventoryList.Items(InventoryList.Items.Count - 1).SubItems.Add("").ToString()
End If
InventoryList.Items(InventoryList.Items.Count - 1).SubItems.Add(mUserEmailAddress)
InventoryList.Items(InventoryList.Items.Count - 1).SubItems.Add(mLoanSubmitter)




End Sub
End Class

Answer

You only check the first row with ds.Tables(0).Rows(0). You should loop through all the rows and add the data in a list.

Remove the loading from the New()

Public Sub New()
End Sub

Then change your LoadData to a Public Shared

Public Shared Function LoadData() As List(Of Inventory)
    Dim dbConn As HUG.Core.Database.SQLConnection
    Dim sql As String = ""
    Dim ds As New DataSet
    Dim result As New List(Of Inventory)

    sql = "SELECT * FROM HDData.dbo.TravelLaptopRecords "

    dbConn = New HUG.Core.Database.SQLConnection("WorkFiles")
    ds = dbConn.FillDataSet(sql)

    If Not IsNothing(ds) Then
        For i As Integer = 0 To ds.Tables(0).Rows.Count-1
            Dim newInventory As New Inventory

            newInventory.mFirstName = CStr(ds.Tables(0).Rows(i).Item("FirstName"))
            newInventory.mLastName = CStr(ds.Tables(0).Rows(i).Item("LastName"))
            newInventory.mComputerType = CStr(ds.Tables(0).Rows(i).Item("ComputerType"))
            newInventory.mAssetTag = CStr(ds.Tables(0).Rows(i).Item("AssetTag"))
            newInventory.mCheckOutDate = CDate(ds.Tables(0).Rows(i).Item("CheckOutDate"))
            newInventory.mCheckInDate = CDate(ds.Tables(0).Rows(i).Item("CheckInDate"))
            newInventory.mExpectedReturnDate = CDate(ds.Tables(0).Rows(i).Item("ExpectedReturnDate"))
            newInventory.mUserEmailAddress = CStr(ds.Tables(0).Rows(i).Item("UserEmailAddress"))
            newInventory.mLoanSubmitter = CStr(ds.Tables(0).Rows(i).Item("LoanSubmitter"))
            newInventory.mDeployed = CInt(ds.Tables(0).Rows(i).Item("Deployed"))

            result.Add(newInventory)
        Next
    End If

    Return result
End Function

At least now when you call Inventory.LoadData() you'll get all your data. This isn't the best way of doing things but it should start you on the right direction.