Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index -6

Question

I have SQL database table with following data:

BillNo    Particular     Price     Unit       Amount     Taxamount     Tax
2905      Airfreight     100.000    100      10000.000     0.000      0.000
2905      Customs        4500.00    1         0.000       4500.000    675.000 
2906        THC          250.000    1         0.000       250.000     38.000
2906        XYZ          5000.00    1        5000.000       0.0000    0.0000

In a window form I have a textbox named Tbblbillto.Text for searching by bill number, and a Datagrid. When I type the bill number in the textbox, how can I make the data from the SQL table be filtered against the bill number and then put it in the datagrid?

*Data Grid Table*
**Particular     Price            Unit         Amount      Taxamount        Tax**

Private Sub Tbblbillto_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Tbblbillto.TextChanged
       Dim Cmd As New SqlClient.SqlCommand
       Dim Con As New SqlClient.SqlConnection
       Dim Rd As SqlDataReader
       Con.ConnectionString = "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=dbase;Integrated Security=True;Pooling=False"
       Cmd.Connection = Con
       Con.Open()
       Dim Row As New DataGridViewRow
       Dim Int As Integer
       Row = Dgvbillsa.Rows(Int)
       Cmd.CommandText = "Select * from BillDetails Where BillNo = '" & Tbblbillto.Text & "'"
       Rd = Cmd.ExecuteReader
       Rd.Read()
       If Rd.HasRows Then
           Row.Cells(0).Value = Rd.Item("Particular")
           Row.Cells(1).Value = Rd.Item("Price")
           Row.Cells(2).Value = Rd.Item("Unit")
           Row.Cells(3).Value = Rd.Item("Amount")
           Row.Cells(4).Value = Rd.Item("TaxAmount")
           Row.Cells(5).Value = Rd.Item("Tax")
       End If
   End Sub

What would happen if the user entered `'; Drop Table BillDetails --` into the text box? — Chris Dunaway, Jun 01 '17 at 15:56

score 1 · Answer 1 · answered May 31 '17 at 16:33

You can load the data straight into a DataGridView using a DataTable, or at least this is how I would do it.

You should however be using parameters when executing SQL commands. This is to reduce syntax issues but more importantly stops SQL injection. See Bobby Tables for more details on this.

I would also consider implementing Using:

Sometimes your code requires an unmanaged resource, such as a file handle, a COM wrapper, or a SQL connection. A Using block guarantees the disposal of one or more such resources when your code is finished with them. This makes them available for other code to use.

With the changes your code would look something similar to this:

Dim dt As New DataTable
Using con As New SqlConnection("Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=dbase;Integrated Security=True;Pooling=False"),
      cmd As New SqlCommand("SELECT * FROM BillDetails WHERE BillNo = @BillNo", con)

    con.Open()

    cmd.Parameters.Add("@BillNo", SqlDbType.[Type]).Value = Tbblbillto.Text

    dt.Load(cmd.ExecuteReader())

End Using

Dgvbillsa.DataSource = dt

Note that I have used SqlDbType.[Type]. You will want to replace [Type] with the data type you've used on your database.

This would load the data straight into your DataGridView. Depending on the setup of the DataGridView you may need to make a few changes for your columns.

score 0 · Answer 2 · edited Jun 01 '17 at 14:24

Here is the answer for my question:

Cmd.CommandText = "Select Particular, Price, Unit, Amount, TaxAmount, Tax from BillDetails Where BillNo = '" & Tbblbillto.Text & "' GROUP BY [Particular], [Price], [Unit], [Amount], [TaxAmount], [Tax]"
Rd.Close()
Rd = Cmd.ExecuteReader
While Rd.Read() OrElse (Rd.NextResult())
    If Rd.HasRows Then
        Dgvbillsa.Rows.Add(Rd.Item("Particular"), Rd.Item("Price"), Rd.Item("Unit"), Rd.Item("Amount"), Rd.Item("TaxAmount"), Rd.Item("Tax"))

Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index -6

2 Answers2