How to use the OR function for MySQL queries VB.NET

Question

There is a problem with the last piece of code in my school project. I need to select multiple values ​​for a given ID from the "actuelewerktuigselectie" column. These values ​​then need to be inserted into variables for further use in the project.

I only get the value of the first ID. See below my current code. What can I change to fix my code to get all values?

Dim sCnnStr As String = My.Settings.sCnnStr
        Dim oCnn As New MySqlConnection(sCnnStr)
        Dim sSql As String = "select pfProfielfreesnummer, pfLopendemeters from actuelewerktuigselectie where (awsId=@1 OR awsId=@2 OR awsId=@3 OR awsId=@4 OR awsId=@5 OR awsId=@6 OR awsId=@7 OR awsId=@8)"
        Dim oCmd As MySqlCommand = New MySqlCommand(sSql, oCnn)

        oCmd.Parameters.AddWithValue("@1", 5)
        oCmd.Parameters.AddWithValue("@2", 6)
        oCmd.Parameters.AddWithValue("@3", 7)
        oCmd.Parameters.AddWithValue("@4", 8)
        oCmd.Parameters.AddWithValue("@5", 17)
        oCmd.Parameters.AddWithValue("@6", 18)
        oCmd.Parameters.AddWithValue("@7", 19)
        oCmd.Parameters.AddWithValue("@8", 20)

        oCnn.Open()
        Dim myReader As MySqlDataReader
        myReader = oCmd.ExecuteReader()
        While (myReader.Read())
            werktuignummer1 = (myReader.GetString(0))
            LopendeMeters1 = (myReader.GetDouble(1))

            werktuignummer2 = (myReader.GetString(2))
            LopendeMeters2 = (myReader.GetDouble(3))

            werktuignummer3 = (myReader.GetString(4))
            LopendeMeters3 = (myReader.GetDouble(5))

            werktuignummer4 = (myReader.GetString(6))
            LopendeMeters4 = (myReader.GetDouble(7))

            werktuignummer5 = (myReader.GetString(8))
            LopendeMeters5 = (myReader.GetDouble(9))

            werktuignummer6 = (myReader.GetString(10))
            LopendeMeters6 = (myReader.GetDouble(11))

            werktuignummer7 = (myReader.GetString(12))
            LopendeMeters7 = (myReader.GetDouble(13))

            werktuignummer8 = (myReader.GetString(14))
            LopendeMeters8 = (myReader.GetDouble(15))
        End While
        myReader.Close()
        oCnn.Close()

All this code I taught myself in a few months because I didn't get the courses I needed to complete this project, but I just followed it and learned the basics of vb.net and MySql.

My columns and variables ar are in Dutch.

Answer 1

Presumably you have an unknown number of results, possibly up to 8 results. So it's better to use something to store the results, the storage of which can be expanded as needed to accommodate as many results returned from the query: you can use List for this, as long as you create some A list containing all the data returned for each row in a query - the content can be a class with a property that holds each variable. It's probably easier to look at the code I suggest than to explain it:

Public Class ToolDatum
    Public Property ToolID As String
    Public Property LopendeMeter As Double

    Public Sub New()
        ' Empty constructor
    End Sub

    Public Sub New(werktuignummer As String, LopendeMeter As Double)
        Me.ToolID = werktuignummer
        Me.LopendeMeter = LopendeMeter
    End Sub

End Class

Public Function GetData() As List(Of ToolDatum)
    Dim toolData As New List(Of ToolDatum)

    Dim connStr As String = My.Settings.sCnnStr
    Dim sql As String = "SELECT pfProfielfreesnummer, pfLopendemeters
                         FROM actuelewerktuigselectie
                         WHERE (awsId = @P1 OR awsId = @P2 OR awsId = @P3 OR awsId = @P4 OR awsId = @P5 OR awsId = @P6 OR awsId = @P7 OR awsId = @P8)"

    Using conn = New MySqlConnection(connStr),
           cmd = New MySqlCommand(sql, conn)

        cmd.Parameters.Add("@P1", MySqlDbType.Int32).Value = 5
        cmd.Parameters.Add("@P2", MySqlDbType.Int32).Value = 6
        cmd.Parameters.Add("@P3", MySqlDbType.Int32).Value = 7
        cmd.Parameters.Add("@P4", MySqlDbType.Int32).Value = 8
        cmd.Parameters.Add("@P5", MySqlDbType.Int32).Value = 17
        cmd.Parameters.Add("@P6", MySqlDbType.Int32).Value = 18
        cmd.Parameters.Add("@P7", MySqlDbType.Int32).Value = 19
        cmd.Parameters.Add("@P8", MySqlDbType.Int32).Value = 20

        conn.Open()

        Using myReader = cmd.ExecuteReader()
            While (myReader.Read())
                toolData.Add(New ToolDatum(myReader.GetString(0), myReader.GetDouble(1)))
            End While

        End Using

    End Using

    Return toolData

End Function

The function GetData (you should give it a descriptive name) will return a list that you can iterate over to get each individual result, e.g.

Dim q = GetData()
For Each r In q
    Console.WriteLine(r.ToolID & " " & r.LopendeMeter)
Next

---

I changed AddWithValue to Add because the former may cause unexpected problems. I changed the parameter names to start with a letter to avoid other unexpected problems.

Use declarations Ensure that things that need to deal with unmanaged resources are handled.