jeudi 22 février 2018

Checking if database values already exist in mysql

Hello Good Day this time my only problem is how to check if the specific data value from database is already exist. I have a system which is student attendance and I also have a database table for students that contains a values studonsite, This studonsite value will be updated every student scanned depends on the student id tag, if one student tag is scanned the studonsite table will turn to 1 represented as the Student is inside the campus and 0 is OUT. The problem is If the student scanned his ID tag twice in the IN which is '1' without scanning in the out (0) the warning will pop up that the student is already login, and he must log out first.

Here is my code for update

Public Sub updatein()
    con = New MySqlConnection
    con.ConnectionString = "server=localhost;userid=root;password=1234;database=dat"
    Dim reader As MySqlDataReader

    Try
        con.Open()
        Dim query As String

        query = "update dat.students set studonsite = '1' where studtags='" & studtag.Text & "' "
        cmd = New MySqlCommand(query, con)
        reader = cmd.ExecuteReader


        con.Close()
    Catch ex As MySqlException
        MessageBox.Show(ex.Message)
    Finally
        con.Dispose()
    End Try



End Sub

And for the RFID tag Textbox event

Private Sub studtag_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles studtag.TextChanged

    Notenrolled.Close()
    greet.Text = ""
    PictureBox4.Visible = False

    If studtag.Text = "44F2F38B" Then

        Endday()

    End If



    If studtag.Text = recents.Text Then

        alreadylogin.Show()
    ElseIf studtag.TextLength = 8 Then

        DataGridView1.Sort(DataGridView1.Columns(6), System.ComponentModel.ListSortDirection.Descending)

        Dim table As New DataTable

        Try
            con.Open()
            'Gets or sets an SQL statement or stored procedure used to select records in the database.
            With cmd
                .Connection = con
                .CommandText = "SELECT * from students where `studtags`='" & studtag.Text & "';"
            End With
            da.SelectCommand = cmd
            da.Fill(table)



            idno.Text = table.Rows(0).Item(1)
            lastxt.Text = table.Rows(0).Item(2)
            firstxt.Text = table.Rows(0).Item(3)
            middletxt.Text = table.Rows(0).Item(4)
            dob.Text = table.Rows(0).Item(6)
            year.Text = table.Rows(0).Item(7)
            crsetxt.Text = table.Rows(0).Item(10)

            tagtxt.Text = studtag.Text
            timein.Text = times.Text

            If studtag.Text = table.Rows(0).Item(17) Then
                greets.Text = "You already login"
            End If


            dr = cmd.ExecuteReader()
            dr.Read()

            If dob.Text = bday.Text Then
                greet.Text = firstxt.Text + " Today is your Birthday. Greetings :D."
                PictureBox4.Visible = True

            End If

            Dim img() As Byte = CType(dr("studpic"), Byte())


            Using ms As New IO.MemoryStream(img)
                PictureBox1.Image = Image.FromStream(ms)

            End Using
            '--> uses relative path here
            My.Computer.Audio.Play("C:\Users\BOR\Desktop\Parsu Gate\Parsu\audio\scanned.wav")
            insert()
            loadtable()
            studdailyhistory()
            updatein()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Notenrolled.Show()
            DataGridView1.Sort(DataGridView1.Columns(5), System.ComponentModel.ListSortDirection.Descending)
            If studtag.Text = "44F2F38B" Then
                Notenrolled.Close()
            End If


        Finally

            con.Dispose()
            con.Close()

        End Try

    End If
    recents.Text = tagtxt.Text



End Sub

I wonder that I can use If statement but I failed Here is my start

If studtag.Text = table.Rows(0).Item(17) = 1 Then
                greets.Text = "You already login"
            End If

My goal in this function is : If the studtag.text(where the RFID tag displayed) which has studonsite values '1' then the message displayed

I will do any suggestions, Thank you so much.

Aucun commentaire:

Enregistrer un commentaire