Hi guys,

This is my first post and so glad to start sharing the knowledge.

I am using Mysql database with VB.NET and I have this code :

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim str As String = "Server=localhost;Port=3306;Database=testdb;Uid=root;Pwd=password"

        Using con As New MySqlConnection(str)

            Dim query As String = "select * from testdata where rfid_tag='" & TextBox3.Text & "' and Date_serve<= '" & Date.Now.ToString("yyyy-MM-dd ") & "' and Start_Time<= '" & Date.Now.ToString("HH:mm:ss ") & "' and End_Time>= '" & Date.Now.ToString("HH:mm:ss ") & "' or amount_serve='' "
            Dim cm As New MySqlCommand(query, con)

            con.Open()

            Dim rd As MySqlDataReader = cm.ExecuteReader()

            ' Check if any rows exist
            If rd.Read() Then
                If rd.GetString(3) = "small" Then

                    MessageBox.Show("small")

                ElseIf rd.GetString(3) = "Big" Then

                    MessageBox.Show("big")

                ElseIf rd.GetString(3) = "Midium" Then

                    MessageBox.Show("Mid")

                End If

            End If
        End Using

    End Sub

what I want after take action after any of If function like

If rd.GetString(3) = "small" Then

                        MessageBox.Show("small")

need to update End_Time to be 00:00:00 in the element which chose in select with rfid_tag number in the table.

this my database table: for example if in "select" command choose RFID_TAG for John then I need to update the End_Time in table to be 00:00:00 with "update" command!

How can I achieve this?

  ID   First_Name      Date_Serve     Start_Time      End_Time             RFID_Tag           Amount_Serve
---------------------------------------------------------------------------------------------------------------------------------
    1     John           17-11-2018       10:00:00      10:15:00          hdgdYun8JH      Small
    2     George         18-11-2018      11:00:00       11:15:00           kdjfHluhHB        Big

Thanks for all

Use "Update" statement to update database feilds value without "Select" statement with "Where" clause.
Secondly, use parametarised query statement to prevet SQL injections in lieu of direct using of objects.

' Check if any rows exist
            If rd.Read() Then
                If rd.GetString(3) = "small" Then
                    MessageBox.Show("small")
                ElseIf rd.GetString(3) = "Big" Then
                    MessageBox.Show("big")
                ElseIf rd.GetString(3) = "Midium" Then
                    MessageBox.Show("Mid")
                End If
            End If

This is not the proper way to check any record if it exists.
datareader.Read() always produce a single record of a data table. To read multiple record you must do a loop by using datareader.Reaad().

To check a conditional record exists in a table or a datareader you create by using a SQL query statement, you must first do the checking if the datareader holds any record or not by using datareader.HasRows() function, which supplies a boolean value if the datareader holds any record or not. without checking it datareader.Read() can give you an exception/error if it doesn't hold any record.

' Check if any rows exist
    if rd.HasRows() then
        rd.Read()
        If rd.GetString(3) = "small" Then
           MessageBox.Show("small")
        ElseIf rd.GetString(3) = "Big" Then
           MessageBox.Show("big")
        ElseIf rd.GetString(3) = "Midium" Then
           MessageBox.Show("Mid")
        End If
    End If

Hope it can help you to solv your problem

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.