What is wrong with this like query

[ADQUSIT]

HI. I have a helping form, which appears on the leave event of the textbox of main form (SaleInfo). When this form is loaded so the Prod_Name from ProdInfo table is loaded into Combobox (where, combobox is set to AutoComplete).

On the form, I have DGV, Combobox and a Button, on which i have the following Query:

Dim selProd As String = "select * from ProdInfo where Prod_Name like '%" & ComboBox1.Text & "%'"

I want to load only that product into a DataGridView, which is selected in Combobox. But when i press the button so it displays the entire table values. Please tell me that what is wrong with this like query.

[Shaggy Hiker]

There may be nothing wrong with it. What you need to do is put a breakpoint on the line right after that one and see what selProd ended up as after it was completely built. I suspect that you will find that the WHERE clause ends up as: WHERE Prod_Name like '%%', in which case you should get the whole table. ComboBox1.Text may prove to be unreliable. The SelectedItem should be more reliable, but that is making a few assumptions about how you use the combobox. Another alternative is that what you end up with is so simple that every product matches it, but either way it would help for you to take a look at what the SQL string actually ends up as.

[ADQUSIT]

This is my code Shagy:

 Dim selProd As String = "select * from ProdInfo where Prod_Name like '%" & ComboBox1.Text & "%'"
                Dim cmdSql As New SqlCommand(selProd, cnSql)
                cmdSql.CommandType = CommandType.Text
                daSql.SelectCommand = cmdSql

                daSql.Fill(dsSql, "ProdInfo")

                dgvSalesPicker.DataSource = dsSql
                dgvSalesPicker.DataMember = "ProdInfo"

and

ComboBox1.Text may prove to be unreliable. The SelectedItem should be more reliable

So should i go with combobox1.selecteditem?

[ADQUSIT]

yes i put a breakpoint on my query. When the break point occurs, so its equal to nothing. But then i put the breakpoint on the next line, right after the query, i.e.

 Dim selProd As String = "select * from ProdInfo where Prod_Name like '%" & ComboBox1.Text & "%'"
                Dim cmdSql As New SqlCommand(selProd, cnSql)
                cmdSql.CommandType = CommandType.Text
                daSql.SelectCommand = cmdSql

                daSql.Fill(dsSql, "ProdInfo")

                dgvSalesPicker.DataSource = dsSql
                dgvSalesPicker.DataMember = "ProdInfo"

This is what it appears in breakpoint.
selProd = "select * from ProdInfo where Prod_Name like '%Keyboard%'"

[Shaggy Hiker]

And that returns ALL the records? It shouldn't, unless all the product names have keyboard in them somewhere. One thing I don't see in your code is where you clear the datatable prior to filling it with the new data. You may be doing that and didn't show it, but you may want to confirm that you have an empty datatable at the time you fill it, or else it will append to the existing rather than replacing.

[ADQUSIT]

I am clearing DataSet like this:

This is button click event code:

Try
            If ComboBox1.Text = "" Then
                MessageBox.Show("At least one value must be mention in textbox", "SellingMS", MessageBoxButtons.OK)
                ComboBox1.Focus()
                Exit Sub
            Else

dsSql2.Clear()
                Dim selProd As String = "select * from ProdInfo where Prod_Name like '%" & ComboBox1.Text & "%'"
                Dim cmdSql As New SqlCommand(selProd, cnSql)
                cmdSql.CommandType = CommandType.Text
                daSql.SelectCommand = cmdSql

                daSql.Fill(dsSql2, "ProdInfo")
                DataGridView1.DataSource = dsSql2
                DataGridView1.DataMember = "ProdInfo"

  End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

The main problem i had is solved. Its showing only exact record. Bundles of thanks Shaggy.

[ADQUSIT]

I got an error message that:

Object Reference is not set to an instance of object

When i click on the product in DGV, which has to be loaded into the textbox on the main form, from where this helping form is called. So during this loading it gives me this error message. Please assist me that what is wrong here?

[Shaggy Hiker]

Whenever you get that error message, the first thing you should do is this:

Examine the line that is throwing the exception. There are some situations where the exception will appear to be arising from the wrong line, which makes it annoying, but those are rare, and they usually take you to a .Show or .ShowDialog method when they happen. In those cases, you have more work to do to find the real line that is causing the exception, but in most cases the compiler takes you to the real line, and that's where you have to look for the problem.

The thing you are looking for in the line is the object that is Nothing. One of them will be. If you don't find it, show us the line. The object that is Nothing IS the problem you are having, so you have to fix that to solve anything, but finding it is the first step. Often enough, once you find the object that is Nothing you will know right away what is wrong. Sometimes it isn't so clear, but we need to know which object is Nothing to be able to suggest anything meaningful, too, so that's still the first step.

[ADQUSIT]

very nice explanation SH.

This is the code which has the exception. When i click the product in DGV, so after clicking on that product the following code gets exception.

Private Sub DataGridView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.Click

        Dim AffectedRow_id As Integer = Me.DataGridView1.CurrentRow.Index
        With Me.DataGridView1

            If DataGridView1.Rows(AffectedRow_id).IsNewRow Then
                Exit Sub
            End If
            frmMyPur.txtMPPID.Text = .Rows(AffectedRow_id).Cells(0).Value
            frmMyPur.txtMPPN.text = .Rows(AffectedRow_id).Cells(1).Value
        End With
        frmMyPur.ShowDialog()
        Me.Hide()
    End Sub

The bold line has exception. While the same code on other form, doesn't gets exception.