VB.net Search Query using two parameters

Question

I have created a search query in VB.net using datasource which has two parameters.

SELECT        [Product Code], Description, Input_Date, Price, Unit, Quantity, Markup, Total
FROM            Inventory_Table
WHERE        (? = ?)

I made two parameters because I want to search by specific columns, this is how i used the query:

  Inventory_TableTableAdapter.SearchQuery(DBDataSet1.Inventory_Table, InvSearchCombo.Text, InvSearchTxt.Text)

First parameter would be a dropdown combobox containing all columns from the table, the second parameter would be an input textbox.

But whenever i try searching nothing would appear. What seems to be the problem? I really want to implement this kind of search feature. Thanks in advance.

You can't set the field name as parameter. You should have to create a dinamic sql instead. Parsing the string — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Sep 18, 2015 at 17:04
does it mean i need to make separate queries per field name? — Marc Intes
– Marc Intes, Commented Sep 18, 2015 at 17:06
Your tittle is not according with your explanation, I mean, your tittle, could be VB.net Search Query using dynamic attritube in where clause or something like this. — Juan Ruiz de Castilla
– Juan Ruiz de Castilla, Commented Sep 18, 2015 at 17:21

Juan Ruiz de Castilla · Accepted Answer · 2015-09-18 20:56:01Z

2

In this you can use a dynamic code

Dim columnQuery As String = "Description"

Using command As New SqlCommand( "select Description,Input_Date from dep where " &  columnQuery  & " = @par1", connection)

command.Parameters.AddWithValue("@par1", "descripcion")

End using

EDIT

A better form could be this:

First, create store procedure:

CREATE PROCEDURE SP_LIST_TABLA_BY_DYNAMIC_COLUMN
@PAR_COLUMN VARCHAR(20),
@PAR_VALUE VARCHAR(20)
AS

DECLARE @STRSQL NVARCHAR(MAX)

SET @STRSQL  = 'SELECT PRODUCT_CODE,DESCRIP,INPUT_DATE FROM INVENTORY_TABLE WHERE ' + @PAR_COLUMN + ' = ' + @PAR_VALUE

EXEC sp_executesql @STRSQL

Then invoke it:

Using command As New SqlCommand( "SP_LIST_TABLA_BY_DYNAMIC_COLUMN", connection)
    command.CommandType = CommandType.StoredProcedure
    command.Parameters.AddWithValue("@PAR_COLUMN", "product_code")
    command.Parameters.AddWithValue("@PAR_VALUE", "1")

    Using reader As SqlDataReader = command.ExecuteReader()
        While reader.Read() 


    End While
    End using

End using

But like user @Basic says: if the column name is coming from user input (even if via a database) then you're going to be vulnerable to SQL injection attacks

One suggestion could be evaluate that par_column name exists and par_value don't have some special characters.

edited Sep 18, 2015 at 20:56

answered Sep 18, 2015 at 17:42

Juan Ruiz de Castilla

99215 silver badges25 bronze badges

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

1 Comment

Basic Over a year ago

But be aware that if the column name is coming from user input (even if via a database) then you're going to be vulnerable to SQL injection attacks. Eg Imagine if the column name entered was 1=1; DROP TABLE dep;-- (Obviously don't do this)

Juan Carlos Oropeza · Accepted Answer · 2015-09-18 17:39:18Z

0

Imagine you have a combo filter and save the ID as @par1 and text field save as @par2

Combo have this values:

none (id: -999)
field1 (id: 0)
field2 (id: 1)

Not sure about how you set your parameter so I will use some pseudo code.

You can make a trick to dynamic set what filter to use.

SELECT  *
FROM    Inventory_Table
WHERE        
    (Field1 = @par2 and 0 = @par1)
OR  (Field2 = @par2 and 1 = @par1)
OR  (-999 = @par1)

So if you select Field1 then 0 = @par1 will be true and first filter will be active

if you select Field2 then 1 = @par1 will be true and second filter will be active

if none is select all rows are return.

edited Sep 18, 2015 at 17:39

answered Sep 18, 2015 at 17:11

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

10 Comments

Juan Ruiz de Castilla Over a year ago

His question is more about dynamic attribute as parameter, his tittle is not according with his explanation.

Juan Carlos Oropeza Over a year ago

Hola @JuanRuizdeCastilla, Not sure if doesnt match the title, but match the requirement on the text. He want choose the fieldName from a combobox, and filter on that fieldName

Juan Ruiz de Castilla Over a year ago

Hi namesake :), Are you sure?, he talk about dynamic attribute in where clause independently if are two parameters nested.

Juan Carlos Oropeza Over a year ago

Tocayo Juan Im 95% sure. Not see any mention on OP question to dynamic attribute. I have see this kind of question before. Is just a search in the table. The problem is the field is choose from the combobox. I think my solution will help.

Josh Part Over a year ago

@JuanCarlosOropeza OP doesn't mention dynamic column names because he doesn't know that's what he needs (apparently). According to the question and his sample code, he needs the first parameter to be the column where to search and the second parameter the value to search for.

|

Collectives™ on Stack Overflow

VB.net Search Query using two parameters

2 Answers 2

1 Comment

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related