CODEWITHSUNDEEP
mysqlvb.netvisual-studio
Peter James
Peter James

Reputation: 103

Building SQL query for search string

Im having some trouble creating a search query for displaying items in a datagrid view, the error im getting is "Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index"

Below is my code:

Try
    connect()
    Dim sql = "SELECT pcb, component, hour, faultcode, line FROM [sqlcnvfaultentry] WHERE "

    If CheckBox_pcb.Checked Then
        Sql = Sql & " and pcb = @pcb "
        cmd.Parameters.AddWithValue("@pcb", ComboBox_pcb.Text)
    End If

    If CheckBox_part.Checked Then
        Sql = Sql & " and component = @component "
        cmd.Parameters.AddWithValue("@component", ComboBox_part.Text)
    End If

    If CheckBox_hour.Checked Then
        Sql = Sql & " and hour = @hour "
        cmd.Parameters.AddWithValue("@hour", ComboBox_hour.Text)
    End If

    If CheckBox_fault.Checked Then
        Sql = Sql & " and faultcode = @faultcode "
        cmd.Parameters.AddWithValue("@faultcode", ComboBox_fault.Text)
    End If

    If CheckBox_line.Checked Then
        Sql = Sql & " and line = @line "
        cmd.Parameters.AddWithValue("@line", ComboBox_line.Text)
    End If

    Dim adapter = New SqlDataAdapter(cmd.CommandText, con.ConnectionString)
    Dim dt As New DataTable()

    cmd.CommandText = Sql
    adapter.Fill(dt)
    DataGridView_c1.DataSource = dt
    DataGridView_c1.Refresh()

    DataGridView_c1.Columns(0).HeaderText = "PCB:"
    DataGridView_c1.Columns(1).HeaderText = "Component:"
    DataGridView_c1.Columns(2).HeaderText = "Hour:"
    DataGridView_c1.Columns(3).HeaderText = "Fault Code:"
    DataGridView_c1.Columns(4).HeaderText = "Line:"

    disconnect()

Catch exp As Exception
    Throw exp
Finally

End Try

Any help would be great.

Upvotes: 0

Views: 63

Answers (2)

Steve
Steve

Reputation: 216353

Setting the SqlCommand.CommandText after you have passed it to SqlDataAdapter doesn't change the text stored in the adapter, moving the setting of the SqlCommand.Commandtext before the creation of the adapter seems to be a good fix....

cmd.CommandText = Sql
Dim adapter = New SqlDataAdapter(cmd.CommandText, con.ConnectionString)
Dim dt As New DataTable()
adapter.Fill(dt)

but, wait, you still have a problem. When you pass a string (as the CommandText) to the adapter constructor it builds internally another SqlCommand using that string. This internal command has its Parameter collection empty. It doesn't know anything of the parameter collection created externally by your code.
So, the real fix is to create the adapter using directly the SqlCommand prepared by your code.

cmd.CommandText = Sql
cmd.Connection = con

' Pass the SqlCommand, it will be used to be the SelectCommand of the adapter'
Dim adapter = New SqlDataAdapter(cmd)
Dim dt As New DataTable()
' Run the SelectCommand'
adapter.Fill(dt)

Upvotes: 2

LDMJoe
LDMJoe

Reputation: 1589

Well, it looks like your TSQL will never be valid. You start the statement having a value of SELECT... WHERE, then you start appending things to it that all begin with AND. To get around this, you can start your statement as SELECT... WHERE 1=1, then as you start appending AND statements to your WHERE clause, you should be okay.

That said, I'm not a big fan of this approach for building your SQL statement and would much rather see a stored procedure in place.

Now, as to your actual error: since your TSQL statement isn't valid, your result doesn't have any columns, and this part of the code...

DataGridView_c1.Columns(0).HeaderText = "PCB:"
DataGridView_c1.Columns(1).HeaderText = "Component:"
DataGridView_c1.Columns(2).HeaderText = "Hour:"
DataGridView_c1.Columns(3).HeaderText = "Fault Code:"
DataGridView_c1.Columns(4).HeaderText = "Line:"

...is out of range, since you have no recordset due to your invalid SELECT statement.

Upvotes: 2

Related Questions