select multiple records from sql table

[rizillion]

I want to select multiple records from sql table and insert it to a dataset when the selected index is changed from a combo box. for example if i select "1" from the combo box then, only values with "1" in the sql table's mentioned column should be inserted into a dataset.

Given below is the code that i used to select from the sql table

Public Function findInvoiceItems(ByVal invNumber As Integer) As SalesInvoiceItems
        Dim cmd As SqlCommand
        Dim reader As SqlDataReader
        Dim inv As New SalesInvoiceItems

        cmd = New SqlCommand("select * from sales_invoice_items where invoice_number= @invnumber", cn)
        cmd.Parameters.AddWithValue("@invnumber", invNumber)
        Try
            cn.Open()
            reader = cmd.ExecuteReader()
            If reader.Read Then
                inv.salesInvoiceNumber = CInt(reader.Item("invoice_number"))
                inv.salesProductCode = CInt(reader.Item("product_code"))
                inv.salesProduct = reader.Item("product")
                inv.salesQuantity = reader.Item("quantity")
                inv.salesPrice = reader.Item("price")
                inv.salesGross = reader.Item("gross_amount")
                inv.salesItemDiscount = reader.Item("discount")
                inv.salesNet = reader.Item("net_amount")
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            cmd.Dispose()
            cn.Close()
        End Try
        Return inv
    End Function

and given below is the code that i used to insert values from sql table when the selected index is changed from the combo box.

Private Sub cmbInvoiceNumber_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbInvoiceNumber.SelectedIndexChanged
'dsQtyupdate is a dataset 
'tblQtyUpdate is a table from the dsQtyUpdate dataset
'objInvoiceItems is a class which refers the sql table fields 
'invoiceList is an arraylist

    dsQtyUpdate.Clear()
    For Each objInvoiceItems In invoiceList
        Dim drStkUpdate As DataRow
        drStkUpdate = tblQtyUpdate.NewRow
        objInvoiceItemsDB = New InvoiceItemsDB
        objInvoiceItems = objInvoiceItemsDB.findInvoiceItems(CInt(cmbInvoiceNumber.SelectedItem))
        drStkUpdate(0) = CInt(objInvoiceItems.salesQuantity)
        drStkUpdate(1) = objInvoiceItems.salesProductCode
        drStkUpdate(2) = objInvoiceItems.salesInvoiceNumber
        tblQtyUpdate.Rows.Add(drStkUpdate)
    Next

    Try
        Dim drStkUpdate1 As DataRow
        drStkUpdate1 = tblQtyUpdate.NewRow
        For Each drStkUpdate1 In tblQtyUpdate.Rows
            Dim objStocksDB As StocksDB
            objStocksDB = New StocksDB
            Dim stk As Stocks
            stk = objStocksDB.findStock(CInt(drStkUpdate1(1)))
            drStkUpdate1(0) = CInt(stk.stkStockInHand) + CInt(drStkUpdate1(0))
            tblQtyUpdate.AcceptChanges()
        Next
    Catch ex As Exception
    End Try
End Sub

but what happens when the selected index is changed from the combo box is that only the first record is displayed in a loop.

please help me with this code. I've tried this in so many different ways since last two days and i'm unable to find a solution.

[TinaRosenqvist]

Where do you build your InvoiceList...

[rizillion]

Where do you build your InvoiceList...

given below is the code:

Imports BusinessObjects 'is a dll file added as a reference for business objects
Imports DatabaseConnectivity 'is a dll file added as a reference for database connection with sql

Imports System.Data
Imports System.Data.SqlClient

Public Class DeleteSalesInvoiceUI
    Dim objSalesInvoiceDB As New SalesInvoiceDB
    Dim objSales As New SalesInvoice
    Dim salesList As New ArrayList

    Dim objInvoiceItemsDB As New InvoiceItemsDB
    Dim objInvoiceItems As New SalesInvoiceItems
    Dim invoiceList As New ArrayList

    Dim objStocksDB As New StocksDB
    Dim objStocks As New Stocks
    Dim stockList As New ArrayList


    Private Sub DeleteSalesInvoice_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        LoadItems()
    End Sub

    Private Sub LoadItems()
        'getting the invoice number from the sales invoice database
        objSalesInvoiceDB = New SalesInvoiceDB
        salesList.Clear()
        salesList = objSalesInvoiceDB.GetInvoiceID()

        'getting the invoice items from the sales invoice items database
        objInvoiceItemsDB = New InvoiceItemsDB
        invoiceList.Clear()
        invoiceList = objInvoiceItemsDB.GetInvoiceID()

        'getting the stocks from the stocks database
        objStocksDB = New StocksDB
        stockList.Clear()
        stockList = objStocksDB.GetStock()


        'loading the list of invoice numbers into combo box
        salesList = objSalesInvoiceDB.GetInvoiceID()
        cmbInvoiceNumber.Items.Clear()
        For Each objSales In salesList
            cmbInvoiceNumber.Items.Add(objSales.salesNumber)
        Next
    End Sub