dcsimg
Results 1 to 5 of 5
  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    22

    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
    Code:
        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
    Code:
        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
    Code:
        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.

  2. #2
    Hyperactive Member
    Join Date
    Sep 2017
    Posts
    429

    Re: Checking if database values already exist in mysql

    My suggestion is to use a bidningsource and read the BindingSource.Find method documentation

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    22

    Re: Checking if database values already exist in mysql

    Thanks kpmc Do I have to use if statement ?

  4. #4
    Hyperactive Member
    Join Date
    Sep 2017
    Posts
    429

    Re: Checking if database values already exist in mysql

    Yes. The BindingSource.Find method returns an Interger if the value is -1 it will not contain values you searched for
    Last edited by kpmc; Feb 22nd, 2018 at 01:05 PM.

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,628

    Re: Checking if database values already exist in mysql

    The problem here,
    Code:
    table.Rows(0).Item(17)
    Is that will return an Object, not a String value

    This would be how to test for '1'
    Code:
    If table.Rows(0).Item(17).ToString = "1" Then
    because this code
    Code:
     query = "update dat.students set studonsite = '1' where studtags='" & studtag.Text & "' "
    indicates that the "studonsite" field has a Data Type of String.
    Last edited by wes4dbt; Feb 22nd, 2018 at 04:43 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.