Posted on

VB Access Buttons

Hello,
Can someone assist me how to create here buttons 
- Button7 - First
- Button5 - Next
- Button6 - Previous
- Button8 - Last

To search in database and display the results.

Here is my project until now:
- Also if there are mistakes or suggestions to make it better i'm open for ideas to learn too :)

<pre>
Public Class Form2

    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Using con As New OleDbConnection(ServerStatus)
            Using cmd As New OleDbCommand("SELECT * FROM Table", con)
                cmd.CommandType = CommandType.Text
                Using sda As New OleDbDataAdapter(cmd)
                    Using dt As New DataTable()
                        sda.Fill(dt)

                        'Set AutoGenerateColumns False
                        DataDisplay.AutoGenerateColumns = False
                        'DataDisplay.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
                        DataDisplay.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
                        DataDisplay.AutoResizeColumns()

                        'Set Columns Count
                        DataDisplay.ColumnCount = 6

                        'Add Columns
                        DataDisplay.Columns(0).Name = "ID"
                        DataDisplay.Columns(0).HeaderText = "ID"
                        DataDisplay.Columns(0).DataPropertyName = "ID"

                        DataDisplay.Columns(1).Name = "cName"
                        DataDisplay.Columns(1).HeaderText = "Name"
                        DataDisplay.Columns(1).DataPropertyName = "cName"

                        DataDisplay.Columns(2).Name = "cNumber"
                        DataDisplay.Columns(2).HeaderText = "Number"
                        DataDisplay.Columns(2).DataPropertyName = "cNumber"

                        DataDisplay.Columns(3).Name = "cSupplier"
                        DataDisplay.Columns(3).HeaderText = "Supplier"
                        DataDisplay.Columns(3).DataPropertyName = "cSupplier"

                        DataDisplay.Columns(4).Name = "cStore"
                        DataDisplay.Columns(4).HeaderText = "Store"
                        DataDisplay.Columns(4).DataPropertyName = "cStore"

                        DataDisplay.Columns(5).Name = "cCount"
                        DataDisplay.Columns(5).HeaderText = "Count"
                        DataDisplay.Columns(5).DataPropertyName = "cCount"

                        TextBox1.Text = dt.Rows(0).Item(1)
                        TextBox2.Text = dt.Rows(0).Item(2)
                        TextBox3.Text = dt.Rows(0).Item(3)
                        TextBox4.Text = dt.Rows(0).Item(4)
                        TextBox5.Text = dt.Rows(0).Item(5)
                        TextBox6.Text = dt.Rows(0).Item(0)

                        DataDisplay.DataSource = dt
                    End Using
                End Using
            End Using
        End Using
        Try
            With cmd
                Dim stream As New IO.MemoryStream()
                conn.Open()
                .Connection = conn
                .CommandText = "select cPicture from Table where ID=@uID"
                .Parameters.Add("@uID", OleDbType.Integer, 50).Value = TextBox6.Text
                Dim image As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())
                stream.Write(image, 0, image.Length)
                Dim bitmap As New Bitmap(stream)
                PictureBox1.Image = bitmap '---&gt;I have used another picturebox to display image from database.
                stream.Close()
                .Parameters.Clear()
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cmd.Dispose()
            If conn IsNot Nothing Then
                conn.Close()
            End If
        End Try
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        'Adapter.Update(dt)
        Try
            With cmd
                Dim stream As New IO.MemoryStream()
                conn.Open()
                .Connection = conn
                .CommandText = "select cPicture from Table where ID=@uID"
                .Parameters.Add("@uID", OleDbType.Integer, 50).Value = TextBox6.Text
                Dim image As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())
                stream.Write(image, 0, image.Length)
                Dim bitmap As New Bitmap(stream)
                PictureBox1.Image = bitmap '---&gt;I have used another picturebox to display image from database.
                stream.Close()
                .Parameters.Clear()
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cmd.Dispose()
            If conn IsNot Nothing Then
                conn.Close()
            End If
        End Try
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim dialog As New OpenFileDialog()
        dialog.Title = "Browse Picture"
        dialog.Filter = "Image Files(*.BMP;*.JPG;*.GIF;*.PNG)|*.BMP;*.JPG;*.GIF;*.PNG"
        If dialog.ShowDialog() = Windows.Forms.DialogResult.OK Then
            PictureBox1.Image = Image.FromFile(dialog.FileName)
            'TextBox1.Text = dialog.FileName.ToString
        End If
    End Sub
    Private Sub RefreshData()
        Using con As New OleDbConnection(ServerStatus)
            Using cmd As New OleDbCommand("SELECT * FROM Table", con)
                cmd.CommandType = CommandType.Text
                Using sda As New OleDbDataAdapter(cmd)
                    Using dt As New DataTable()
                        sda.Fill(dt)

                        TextBox1.Text = dt.Rows(0).Item(1)
                        TextBox2.Text = dt.Rows(0).Item(2)
                        TextBox3.Text = dt.Rows(0).Item(3)
                        TextBox4.Text = dt.Rows(0).Item(4)
                        TextBox5.Text = dt.Rows(0).Item(5)
                        TextBox6.Text = dt.Rows(0).Item(0)

                        DataDisplay.DataSource = dt
                    End Using
                End Using
            End Using
        End Using
        Try
            With cmd
                Dim stream As New IO.MemoryStream()
                conn.Open()
                .Connection = conn
                .CommandText = "select cPicture from Table where ID=@uID"
                .Parameters.Add("@uID", OleDbType.Integer, 50).Value = TextBox6.Text
                Dim image As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())
                stream.Write(image, 0, image.Length)
                Dim bitmap As New Bitmap(stream)
                PictureBox1.Image = bitmap '---&gt;I have used another picturebox to display image from database.
                stream.Close()
                .Parameters.Clear()
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cmd.Dispose()
            If conn IsNot Nothing Then
                conn.Close()
            End If
        End Try

    End Sub
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Try
            With cmd
                Dim ms As New IO.MemoryStream()
                PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
                Dim arrimage() As Byte = ms.GetBuffer
                conn.Open()
                .Connection = conn
                .Parameters.Add("@uName", OleDbType.VarChar, 50).Value = TextBox1.Text
                .Parameters.Add("@uNumber", OleDbType.Integer, 50).Value = TextBox2.Text
                .Parameters.Add("@uSupp", OleDbType.VarChar, 50).Value = TextBox3.Text
                .Parameters.Add("@uStore", OleDbType.VarChar, 50).Value = TextBox4.Text
                .Parameters.Add("@uCount", OleDbType.Integer, 50).Value = TextBox5.Text
                .Parameters.Add("@Picture", OleDbType.Binary).Value = arrimage
                .Parameters.AddWithValue("@uID", TextBox6.Text)
                .CommandText = "UPDATE Table SET cName = @uName,cNumber = @uNumber,cSupplier = @uSupp,cStore = @uStore,cCount = @uCount,cPicture = @Picture WHERE ID = @uID"
                .ExecuteNonQuery()
                .Parameters.Clear()
                ms.Close()
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cmd.Dispose()
            If conn IsNot Nothing Then
                conn.Close()
            End If
            RefreshData()
        End Try
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Try
            With cmd
                Dim ms As New IO.MemoryStream()
                PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
                Dim arrimage() As Byte = ms.GetBuffer
                conn.Open()
                .Connection = conn
                .CommandText = "INSERT INTO Table (cName,cNumber,cSupplier,cStore,cCount,cPicture) VALUES (@uName,@uYazaki,@uSupp,@uStore,@uCount,@picture)"
                .Parameters.Add("@uName", OleDbType.VarChar, 50).Value = TextBox1.Text
                .Parameters.Add("@uNumber", OleDbType.Integer, 50).Value = TextBox2.Text
                .Parameters.Add("@uSupp", OleDbType.VarChar, 50).Value = TextBox3.Text
                .Parameters.Add("@uStore", OleDbType.VarChar, 50).Value = TextBox4.Text
                .Parameters.Add("@uCount", OleDbType.Integer, 50).Value = TextBox5.Text
                .Parameters.Add("@Picture", OleDbType.Binary).Value = arrimage
                .ExecuteNonQuery()
                .Parameters.Clear()
                ms.Close()
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cmd.Dispose()
            If conn IsNot Nothing Then
                conn.Close()
            End If
        End Try
    End Sub

End Class</pre>
Posted on

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

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