Multiple where condition

[Lethugs]

Hi,

Im trying to extract data from sql server where conditions are multiple and variying

I have a datagridview with 2 columns ID and Name

I need to create Select Query to extract data where condition is 'ID should be equal to the value or values in Datagridview ID Column.

im using sql server 2008 r2
vb.net 2010

Please help

Thank you

[hericles]

you can either do multiple conditions in the WHERE statement:
WHERE id = x OR id = y etc
or you can use the in statement if you would rather pass in a group of values:
WHERE id in (x,y,z, etc)
and, of course, you could combine them if that works better:
WHERE id = x OR id in (a,b,c,d)

Obviously, the values from the datagrid would need to be passed in as parameters.

[Lethugs]

That solution I guess is if the number of data is fixed or known. How about if it varies in number. Depending on the number of DGV rows?

[Santanu.Das]

Prepare a string value by concatinating through datagridviewRows. Like

        Dim x As String = "("
        For Each r As DataGridViewRow In DataGridView1.Rows
            x &= r.Cells(0).Value
            If r.Index < DataGridView1.Rows.Count - 1 Then
                x &= ", "
            Else
                x &= ")"
            End If
        Next

Now make the Sql Statement as hericles say

"WHERE id = x OR id in " & x

Suppose it should help you.

[Lethugs]

Do I need to place also the sql query in for loop in DGVrows?

[Santanu.Das]

Not indeed. Construct the SQL Statement after completion of the loop.
The statement is as

Dim sqlstr As String = "Select * From YourTAbleNAme WHERE id = x OR id in " & x

You can also construct SQL Statement through loop like

        Dim sqlStr As String = "Select * From TableName Where id = x OR id in ("

        For Each r As DataGridViewRow In DataGridView1.Rows
            sqlStr &= r.Cells(0).Value
            If r.Index < DataGridView1.Rows.Count - 1 Then
                sqlStr &= ", "
            Else
                sqlStr &= ")"
            End If
        Next

Hope, it can help you.

[Lethugs]

I think I got it

Ill give it a try

report back for update

thanks

[ChrisHunter]

Why don't you create a stored procedure to search for a record based on an ID passed in from the data grid view, instead of doing the query in your VB code?

It means all your stored procedures are in one place and have can be easily identified and modified.

It saves having to search through your VB code to find the string, can save you bulking your VB code out with query strings and is more managable in the long run. Especially when it comes to bigger application.

[Taywin]

Why don't you create a stored procedure to search for a record based on an ID passed in from the data grid view, instead of doing the query in your VB code?

If you look at the OP original post, you could see that the OP quoted a portion of requirement. In other words, this could be an assignment from school and not from the real world practice...

'ID should be equal to the value or values in Datagridview ID Column.

[Lethugs]

Actually I tried creating query in my vb code first before making stored procedure to make sure it's working already.

Thanks for your help