Member Avatar for kazekagerandy

i am working on a POS as a school project. im having a problem in getting the stocks, which is a record in my database.

this is what i need to do, i need to populate the combobox with drug names with suggests. when i choose a certain drug, its stocks will be displayed on a textbox as a reference.

this is what ive done so far:

Private Sub posform_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        accountlbl.Text = mainmenu.namelbl.Text

        dbconnect()
        Dim suggest As New SqlCommand("select prodname from productinfotbl ", connect)
        suggest.CommandType = CommandType.Text

        adapt.SelectCommand = suggest
        adapt.SelectCommand.ExecuteNonQuery()
        adapt.Fill(dset, "suggests")

          ComboBox1.DataSource = dset.Tables("suggests")
        ComboBox1.DisplayMember = "prodname"
        ComboBox1.ValueMember = "prodname"
        ComboBox1.AutoCompleteSource = AutoCompleteSource.ListItems
        ComboBox1.AutoCompleteMode = AutoCompleteMode.Suggest

i was able to populate the combobox with this but the error strikes when i put this code so i can fetch other data when i change the value of the combobox:

 dbconnect()

        Dim sql As New SqlCommand("select * from  productinfotbl where prodname='" & ComboBox1.Text & "'", connect)
        sql.CommandType = CommandType.Text
        adapt.SelectCommand = sql
        adapt.SelectCommand.ExecuteNonQuery()
        adapt.Fill(dset, "prods")
        dc()

        stocktbx.Text = dset.Tables("prods").Rows(0).Item("stocks")

it shows the ff error message:

An exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll but was not handled in user code

  • 2 Contributors
  • 4 Replies
  • 289 Views
  • 45 Minutes Discussion Span
  • Latest Post Latest Post by kazekagerandy

Dani AI

Generated

The IndexOutOfRangeException means the code tried to read row 0 from a table that had no rows. Typical causes are: the SELECT returned nothing (mismatched product name), the DataTable/Table wasn’t actually filled, or a shared Dataset was reused without clearing. As and already touched on, the simplest, most reliable approach is to bind a DataTable that contains both the product name and its stocks, then read the stocks from the bound item instead of issuing a fragile string-built SELECT on every change.

Load once (Form Load) and bind both columns so SelectedIndexChanged can show stocks immediately:

Private Sub LoadProducts()
    Using conn As New SqlConnection(connString)
        Dim sql As String = "SELECT prodid, prodname, stocks FROM productinfotbl ORDER BY prodname"
        Using da As New SqlDataAdapter(sql, conn)
            Dim dt As New DataTable()
            da.Fill(dt)
            ComboBox1.DataSource = dt
            ComboBox1.DisplayMember = "prodname"
            ComboBox1.ValueMember = "stocks"
            ComboBox1.AutoCompleteMode = AutoCompleteMode.SuggestAppend
            ComboBox1.AutoCompleteSource = AutoCompleteSource.ListItems
        End Using
    End Using
End Sub

Then update the textbox from SelectedIndexChanged (as advised), with null/DB checks:

Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
    If ComboBox1.SelectedIndex < 0 Then
        stocktbx.Clear()
        Return
    End If
    Dim v = ComboBox1.SelectedValue
    If v IsNot Nothing AndAlso Not Convert.IsDBNull(v) Then
        stocktbx.Text = v.ToString()
    Else
        stocktbx.Clear()
    End If
End Sub

If you must query per selection, use a parameterized SELECT and check DataTable.Rows.Count > 0 before accessing Rows(0). Never use ExecuteNonQuery for SELECTs; prefer SqlDataAdapter.Fill or ExecuteReader. Always Dispose/Using connections, handle exceptions, and clear or use separate DataTables when reusing a global DataSet. These practices avoid the IndexOutOfRange error and prevent SQL injection and resource leaks.

Recommended Answers

All 4 Replies

Member Avatar for maurice91

If I understand you correctly, when the combobox is set to a certain drug, you want the stocks corresponding to that drug to be stored in a textbox.

Here is a solution:

'Create a dataview
dim dv as new dataview

'Associate the dataview to dst (Dataset table)
dv.Table = dst.Tables("Drugs")

'Data Row View object to query DataView object
Dim drv As DataRowView 

'Filter based on a combo box value selected
dv.RowFilter = "[Drug ID] = " & CInt(cboComboBox.SelectedValue)


'Retrieve my values returned in the result
For Each drv In dv
     Stock_Level = drv("Stocks") 
Next
Member Avatar for kazekagerandy

thanks for the suggestion maurice, but my question is this: where should i put that code? i mean, which event?

Member Avatar for maurice91

In the combobox SelectedIndexChanged event, also include:

Stocks.Textbox.Text = Stock_Level

Member Avatar for kazekagerandy

oh thanks maurice! that really saved me! :))

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.