Building SQL query for search string

Question

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.

All of your string concatenations will produce WHERE and blah = @blah at the end of the the command text. That syntax is invalid between the "where" and the "and". Also, you are setting the commandText of your cmd object after you have already created the SqlDataAdapter from it. — вʀaᴎᴅᴏƞ вєнᴎєƞ
– вʀaᴎᴅᴏƞ вєнᴎєƞ, Commented Feb 1, 2016 at 15:03
I've changed the code for "Where 1=1" - but the row that throws the exception is - DataGridView_c1.Columns(1).HeaderText = "Component:" — Peter James
– Peter James, Commented Feb 1, 2016 at 15:13
Move the cmd.CommandText = Sql line to a line above the Dim adapter = bit. — вʀaᴎᴅᴏƞ вєнᴎєƞ
– вʀaᴎᴅᴏƞ вєнᴎєƞ, Commented Feb 1, 2016 at 15:14
String concatenation exposes you to SQL injection and unexpected errors whether it contains parameter placeholders or not. This is not a parameterized query. If you really need such an open-ended query, use an ORM that supports LINQ (eg EF, NH) and add .Where(...) clauses dynamically — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 1, 2016 at 15:28

LDMJoe · Accepted Answer · 2016-02-01 15:06:27Z

2

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.

answered Feb 1, 2016 at 15:06

LDMJoe

1,58913 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Peter James Over a year ago

Ah right ok i see, so i've changed it to WHERE 1=1 but still have the problem.

LDMJoe Over a year ago

Output your SELECT statement before you run it, then run it against your database outside of your vb.net code. My hunch is your TSQL is still invalid.

вʀaᴎᴅᴏƞ вєнᴎєƞ Over a year ago

@LDMJoe, While you are correct, you missed something. Even if the statement is corrected to have a valid syntax, there is still the issue of setting the commandText of the cmd object after having already created the SqlDataAdapter from it. Basically the query text will never get executed.

LDMJoe Over a year ago

@вʀaᴎᴅᴏƞвєнᴎєƞ Thanks - my vb.net isn't as sharp as my SQL, so once I found an SQL problem, I assumed it was the only problem!

Steve · Accepted Answer · 2016-02-06 08:00:33Z

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)

Collectives™ on Stack Overflow

Building SQL query for search string

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related