How to use OR function for MySQL query VB.NET-CodePudding

I have a problem with the last bit of my code for a school project. I need to select multiple values from column "actuelewerktuigselectie", for the given IDs. Then these values need to be inserted in variables to use further in the project.

I only get the values fot the first ID. See below the code that I've now. What can I change to fix my code to get all of the 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 of this code is what I taught myself in a couple of months because I didn't get the necessary courses to do this project, but I just went along with it and learned myself the very basics of vb.net and MySql.

My column and variables ar in dutch.

CodePudding user response：

Presumably you have an unknown number of results, perhaps with a maximum of 8 results. So, it would be better to use something to store the results in which can expand its storage as needed for as many results as are returned from the query: you can use a List for that, as long as you make something that you can have a list of to contain all the data for each returned row from the query - that something can be a Class with properties to hold each variable. It's probably easier to see my suggested code than 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 can cause unexpected problems. I changed the parameter names to start with a letter to avoid other unexpected problems.

The Using Statement makes sure that things that need to have unmanaged resources disposed of are taken care of.

CodePudding user response：

The problem is that your query only retrieves 2 columns but you try to get 16 values from the reader each time you read. If there will be 8 records then you need to call Read 8 times. If there are 2 columns then you get 2 values from the reader each time you read. If you want to use distinct variables as you are then the code to read the data should be like this:

myReader.Read()
werktuignummer1 = (myReader.GetString(0))
LopendeMeters1 = (myReader.GetDouble(1))

myReader.Read()
werktuignummer2 = (myReader.GetString(0))
LopendeMeters2 = (myReader.GetDouble(1))

myReader.Read()
werktuignummer3 = (myReader.GetString(0))
LopendeMeters3 = (myReader.GetDouble(1))

myReader.Read()
werktuignummer4 = (myReader.GetString(0))
LopendeMeters4 = (myReader.GetDouble(1))

myReader.Read()
werktuignummer5 = (myReader.GetString(0))
LopendeMeters5 = (myReader.GetDouble(1))

myReader.Read()
werktuignummer6 = (myReader.GetString(0))
LopendeMeters6 = (myReader.GetDouble(1))

myReader.Read()
werktuignummer7 = (myReader.GetString(0))
LopendeMeters7 = (myReader.GetDouble(1))

myReader.Read()
werktuignummer8 = (myReader.GetString(0))
LopendeMeters8 = (myReader.GetDouble(1))