CODEWITHSUNDEEP
sqlsql-servervb.net
Edgar
Edgar

Reputation: 503

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

Upvotes: 0

Views: 81

Answers (2)

MyNameHere
MyNameHere

Reputation: 21

You are choosing Row zero (With ds.Tables(0).Rows(0)) and it needs to be in a loop like:

For i As Integer = 0 To ds.Tables(0).Rows.Count-1
    ' Get the strings here
    ' ...
    mFirstName = ds.Tables(0).Rows(i).Item("FirstName").ToString
Next

Upvotes: 0

the_lotus
the_lotus

Reputation: 12748

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.

Upvotes: 1

Related Questions