Variable MySql Where query from VB.net

Question

I have a this query like this:

SELECT movimiento_almacen WHERE re_boleta_idre_boleta= XXX

I would like to know if there is a way to consult multiple re_boleta_idre_boleta to sort in just one query like this:

SELECT movimiento_almacen WHERE re_boleta_idre_boleta= XXX,XXX,XXX

But the XXX are variable, sometimes could be only 1 WHERE in other query could be 4 WHERE...

I was thinking on a procedure that receive an array but I never did and trying to figure how to do it

AND isnt funtional because its variable the query...

Use an if statement? if this then use 1 element, else add to array and use the array. — DeveloperLV
– DeveloperLV, Commented Jan 27, 2021 at 20:22
Thing you want to use the 'in' criteria WHERE re_boleta_idre_boleta IN (XXX,XXX,XXX) guru99.com/where-clause.html — Hursey
– Hursey, Commented Jan 27, 2021 at 21:00

jmcilhinney · Accepted Answer · 2021-01-28 02:07:53Z

1

Here's an example I wrote some time ago for querying SQL Server based on selections in a ListBox:

Dim connection As New SqlConnection("connection string here")
Dim command As New SqlCommand
Dim query As New StringBuilder("SELECT * FROM MyTable")
 
Select Case Me.ListBox1.SelectedItems.Count
    Case 1
        query.Append(" WHERE MyColumn = @MyColumn")
        command.Parameters.AddWithValue("@MyColumn", Me.ListBox1.SelectedItem)
    Case Is > 1
        query.Append(" WHERE MyColumn IN (")
 
        Dim paramName As String
 
        For index As Integer = 0 To Me.ListBox1.SelectedItems.Count - 1 Step 1
            paramName = "@MyColumn" & index
 
            If index > 0 Then
                query.Append(", ")
            End If
 
            query.Append(paramName)
            command.Parameters.AddWithValue(paramName, Me.ListBox1.SelectedItems(index))
        Next index
 
        query.Append(")")
End Select
 
command.CommandText = query.ToString()
command.Connection = connection

In that case, you can end up with three different types of queries:

No items selected: SELECT * FROM MyTable
One item selected: SELECT * FROM MyTable WHERE MyColumn = @MyColumn
Multiple items selected: SELECT * FROM MyTable WHERE MyColumn IN (@MyColumn0, @MyColumn1)

You can use the same code structure with the MySQL ADO.NET provider. You say that you have discrete variables but, however you identify which ones you need to include, I suggest that you place them in an array or collection and then you can use a loop in the same way I have in that code, looping over your array/collection instead of the SelectedItems of the ListBox.

answered Jan 28, 2021 at 2:07

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

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

2 Comments

user3128539 Over a year ago

Thanks a Lot, this help me a lot! I was doing as you said, just the IN clause I didnt know but help me just a LOT!

user3128539 Over a year ago

This help me with the variable part where I was stuck, and the case in VB... I was thinking to do with store procedures but After this I will do as you say, this is a fully answer! Thanks again!

nbk · Accepted Answer · 2021-01-28 08:49:59Z

1

To use more than one value you need the INclause

Like so

cmd.CommandText = "SELECT movimiento_almacen WHERE re_boleta_idre_boleta IN (@val1,@val2,@val3)"
cmd.Parameters.AddWithValue("@val1", val1.Text )
cmd.Parameters.AddWithValue("@val2", val2.Text )
cmd.Parameters.AddWithValue("@val4", val3.Text )
Dim reader  = cmd.ExecuteReader()
while reader.Read()
   yourarray = reader(0).ToString()
End While

This is a static with three values only, But the commandtext string can be soved in al loop

edited Jan 28, 2021 at 8:49

answered Jan 27, 2021 at 21:37

nbk

49.6k8 gold badges32 silver badges53 bronze badges

2 Comments

jmcilhinney Over a year ago

That doesn't answer the question as asked because it doesn't allow the number of values to be varied.

user3128539 Over a year ago

Thanks a Lot, IN clause was the solution, Regards and thanks for your time

Collectives™ on Stack Overflow

Variable MySql Where query from VB.net

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related