Member Avatar for Xiao_1

if i insert the new studadmin and the admincard and it Successfully Register but when i look at the access it not go the correct row is go to the timein and timeout

i not sure which part wrong:

Private Sub btnRegister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRegister.Click
        If String.IsNullOrWhiteSpace(txtcard.Text) Or String.IsNullOrWhiteSpace(txtadmno.Text) Then
            MessageBox.Show("Please complete the on the box.", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End If

        Dim Conn As System.Data.OleDb.OleDbConnection
        Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\Database1.accdb"
        Conn = New System.Data.OleDb.OleDbConnection(ConnectionString)


        Try
            If Conn.State = ConnectionState.Open Then Conn.Close()
            Conn.Open()

            Dim CMD As New System.Data.OleDb.OleDbCommand
            CMD.CommandText = "Select FROM tbl_studentadm WHERE [Stud Admin] ='" & txtadmno.Text & "' AND [Admin Card] = '" & txtcard.Text & "'"
            Dim sql As String = "insert into tbl_studentadm ([Stud Admin], [Admin Card]) values('" & txtadmno.Text & "', '" & txtcard.Text & "')"
            Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql, Conn)
            sqlCom.Connection = Conn

            Dim result As Integer = sqlCom.ExecuteNonQuery()

            sqlCom.Dispose()
            Conn.Close()

            If result > 0 Then
                MessageBox.Show("Successfully Register.")
            Else
                MessageBox.Show("Failure to Register.")

            End If
            txtadmno.Text = ""
            txtcard.Text = ""
            txtadmno.Focus()

            Lecturer_Form.Show()
        Catch ex As Exception
            MessageBox.Show("Failed to connect to Database..", "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub


    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

        lbltime.Text = "Time:" + TimeString

        If String.IsNullOrWhiteSpace(txtadmno.Text) Or String.IsNullOrWhiteSpace(txtcard.Text) Then
            'MessageBox.Show("Please complete the on the box.", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End If

        Using conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\Database1.accdb")


            lbltime.Text = TimeOfDay.Hour & ":" & TimeOfDay.Minute & ":" & TimeOfDay.Second
            If TimeOfDay.Second < 10 Then
                Dim fitsecond As String
                fitsecond = TimeOfDay.Second
                lbltime.Text = TimeOfDay.Hour & ":" & TimeOfDay.Minute & ":" & "0" & fitsecond
            End If
            If TimeOfDay.Minute < 10 Then
                Dim fitminute As String
                fitminute = TimeOfDay.Minute
                lbltime.Text = TimeOfDay.Hour & ":" & "0" & fitminute & ":" & TimeOfDay.Second
            End If
            If TimeOfDay.Hour < 10 Then
                Dim fithour As String
                fithour = TimeOfDay.Hour
                lbltime.Text = "0" & fithour & ":" & TimeOfDay.Minute & ":" & TimeOfDay.Second
            End If

            Dim sql As String = "INSERT INTO tbl_studentadm (TimeIn, TimeOut) VALUES ('" & txtadmno.Text & "', '" & txtcard.Text & "')"
            Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql, conn)
            sqlCom.Connection = conn
            conn.Open()

            Dim CMD As New System.Data.OleDb.OleDbCommand
            CMD.CommandText = "UPDATE tbl_StudentAdm SET [Stud Admin] = @StudAdmin WHERE TimeIn = @TimeIn"

            CMD.Parameters.AddWithValue("@StudAdmin", "Values")
            CMD.Parameters.AddWithValue("@TimeIn", "TimeOutValues")


            Dim result As Integer = sqlCom.ExecuteNonQuery()

            sqlCom.Dispose()
            conn.Close()

            If result > 0 Then
                ' MessageBox.Show("Insert time in.")
            Else
                MessageBox.Show("Failure to time in.")

            End If
        End Using

        'Catch ex As Exception
        'MessageBox.Show("Failed to connect to Database..", "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        'End Try
    End Sub
  • 2 Contributors
  • 1 Reply
  • 221 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by rubberman

Dani AI

Generated

— quick diagnosis and a compact plan.

Why the wrong columns get populated

  • There are two separate places writing to the same table: the Register button and the Timer tick. The Register code inserts the admin number and card; the Timer handler then inserts again but targets the TimeIn/TimeOut columns, so the admin/card strings end up in time fields. Also the SELECT in your Register routine is syntactically invalid (no column list after SELECT), and the UPDATE you build in the Timer handler is never executed and uses literal placeholder strings instead of real values.

What to change, step by step

  • Decide the intended behavior: either INSERT a new row with StudAdmin/AdminCard and TimeIn (DateTime.Now), or UPDATE an existing row to set TimeOut. Do not insert admin/card into TimeIn/TimeOut fields.
  • Stop using the Timer to write to the database. Use the card-read event or the Register button to perform single, explicit DB writes. Timers will create repeated rows and race conditions.
  • Use parameterized queries and proper data types. Store times as Date/Time in Access and pass DateTime values from .NET instead of building time strings.
  • Always open connections inside Using blocks, and show the actual exception message while debugging so you see the real error.

Minimal examples (VB.NET + OleDb; use the same provider and connection string you already have):

Using conn As New OleDbConnection(connString)
  Using cmd As New OleDbCommand("INSERT INTO tbl_studentadm ([Stud Admin],[Admin Card],[TimeIn]) VALUES (?,?,?)", conn)
    cmd.Parameters.AddWithValue("?", admNo)
    cmd.Parameters.AddWithValue("?", card)
    cmd.Parameters.AddWithValue("?", DateTime.Now)
    conn.Open()
    cmd.ExecuteNonQuery()
  End Using
End Using

And to set TimeOut for an existing student:

Using cmd As New OleDbCommand("UPDATE tbl_studentadm SET TimeOut = ? WHERE [Stud Admin] = ?", conn)
  cmd.Parameters.AddWithValue("?", DateTime.Now)
  cmd.Parameters.AddWithValue("?", admNo)
  cmd.ExecuteNonQuery()
End Using

Debug checklist

  • Verify column names and types in Access. Test the SQL in Access directly. Log the SQL/parameters (Debug.WriteLine) and use breakpoints. Replace generic catch-message boxes with Exception.Message while debugging. Moderator was right to suggest relocating this to Software Development — this is a code-level bug.
Member Avatar for rubberman

Please move this to the Software Development forum.

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.