How to update one element in column chose with Select in VB.NET

[mksaeed]

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

[Shark1]

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.

[Santanu.Das]

' 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