Imports System.Data.OleDb
Imports System.IO
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

Public Class EditFrm

    Dim Dst = New DataSet
    Dim con As New OleDbConnection
    Dim myDA As OleDbDataAdapter

    Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClose.Click
        Try

            OleCn.Close()
            Me.Close()

        Catch ex As Exception
        End Try
    End Sub

    Private Sub EditFrm_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        Try

            OleCn.Close()
            Me.Dispose()

        Catch ex As Exception
        End Try
    End Sub

    Private Sub cmdClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClear.Click
        txtSerialNo.ReadOnly = True
        Try
            txtName.Clear()
            txtDate.Value = Today
            txtIDProof.Clear()
            txtLogin.Clear()
            TxtLogout.Clear()
            TxtDuration.Clear()
            TxtAmount.Clear()
            TxtParticular.Clear()

        Catch ex As Exception
        End Try

    End Sub

    Public Sub serialnogenerate()
        Try
            With OleCn
                If .State <> ConnectionState.Open Then
                    .ConnectionString = StrConnection()
                    .Open()
                End If
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        Dim sqlquery
        Dim a
        Dim cmd As OleDbCommand
        Dim dr As OleDbDataReader
        Dim i As Integer
        i = 0

        Call SNoNavigation()

        Try
            sqlquery = "select serialno from Table1 "
            cmd = New OleDbCommand(sqlquery, OleCn)
            dr = cmd.ExecuteReader
            While dr.Read
                a = dr(0)
            End While
            Dim srlnotxt As Integer
            If Integer.TryParse(CStr(CDbl(a) + 1), srlnotxt + 1) Then
                'i = Dst.Tables("Table1").rows.count - 1
                'MessageBox.Show(i.ToString)
                a = Dst.Tables("Table1").Rows(i)("SerialNo")
                txtSerialNo.Text = srlnotxt.ToString
            End If
            OleCn.Close()

        Catch ex As Exception
            MsgBox(ex.Message(), MsgBoxStyle.Critical, "Auto Genrate Serial-No Error...")
        End Try
    End Sub
    Private Function SNoExist()
        Dim Str, Str1 As String
        Dim i As Integer
        Str = txtSerialNo.Text
        i = 0
        While i <> Dst.Tables("Table1").rows.count
            Str1 = Dst.Tables("Table1").Rows(i)("SerialNo")
            If Str = Str1 Then
                Return True
            End If
            i += 1
        End While
        Return False
    End Function
    Private Function RequiredEntry() As Boolean
        If txtSerialNo.Text = "" Or txtName.Text = "" Or txtDate.Text = "" Or txtLogin.Text = "" Or TxtLogout.Text = "" Or TxtDuration.Text = "" Then
            MsgBox("Please enter required (*) information....", MsgBoxStyle.Critical, "Attention...")
            Return True
            Exit Function
        End If
    End Function
    Private Sub SNoNavigation()
        Try
            OleCn.Close()
            con.ConnectionString = "Provider=Microsoft.Ace.oledb.12.0; Data Source=" & My.Application.Info.DirectoryPath.ToString() & "\BackUp\Printmaker11.Accdb;Persist Security Info=False"
            If con.State - System.Data.ConnectionState.Closed Then
                con.Open()
            End If
            myDA = New OleDbDataAdapter("SELECT * From Table1 order by SerialNo", con)
            myDA.Fill(Dst, "Table1")
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message(), MsgBoxStyle.Critical, "Serial-No Error...")
        End Try
    End Sub

    Private Sub cmdEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdEdit.Click

        Try
            Dim msg As DialogResult = MessageBox.Show("Do you want to Update this Record? ", "Response", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
            If (msg = vbYes) Then

                If RequiredEntry() = True Then
                    Return
                End If

                Try
                    With OleCn
                        If .State <> ConnectionState.Open Then
                            .ConnectionString = StrConnection()
                            .Open()
                        End If
                    End With
                Catch ex As Exception
                    MessageBox.Show(ex.Message, "Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try


                Try

                    'Dim cmd2 As New OleDbCommand("Update Table1 Set CabinNo=@CabinNo,Dat=@Dat,CustomerName=@CustomerName,ID_Proof=@ID_Proof,Amount=@Amount,LoginTime=@LoginTime,LogoutTime=@LogoutTime,Duration=@Duration,Particular=@Particular where SerialNo=@SerialNo ", OleCn)
                    Dim cmd2 As New OleDbCommand("Update Table1 Set CabinNo=@CabinNo,Dat=@Dat,CustomerName=@CustomerName,ID_Proof=@ID_Proof,LoginTime=@LoginTime,LogoutTime=@LogoutTime,Duration=@Duration,Amount=@Amount,Particular=@Particular where SerialNo=" & txtSerialNo.Text & " ", OleCn)


                    'CabinNo
                    Dim CabinNo As OleDbParameter = New OleDbParameter("@CabinNo", SqlDbType.BigInt)
                    CabinNo.Value = CBFormState.Text.ToString()
                    cmd2.Parameters.Add(CabinNo)

                    'Date
                    Dim Dat As OleDbParameter = New OleDbParameter("@Dat", OleDbType.VarChar, 20)
                    Dat.Value = txtDate.Text.ToString()
                    cmd2.Parameters.Add(Dat)

                    'CustomerName
                    Dim name As OleDbParameter = New OleDbParameter("@CustomerName", OleDbType.VarChar, 20)
                    name.Value = txtName.Text.ToString()
                    cmd2.Parameters.Add(name)

                    'ID_Proof
                    Dim ID_Proof As OleDbParameter = New OleDbParameter("@ID_Proof", OleDbType.VarChar, 20)
                    ID_Proof.Value = txtIDProof.Text.ToString()
                    cmd2.Parameters.Add(ID_Proof)

                    'LoginTime
                    Dim LoginTime As OleDbParameter = New OleDbParameter("@LoginTime", OleDbType.VarChar, 20)
                    LoginTime.Value = Me.txtLogin.Text.ToString()
                    cmd2.Parameters.Add(LoginTime)

                    'LogoutTime
                    Dim LogoutTime As OleDbParameter = New OleDbParameter("@LogoutTime", OleDbType.VarChar, 20)
                    LogoutTime.Value = Me.txtLogin.Text.ToString()
                    cmd2.Parameters.Add(LogoutTime)

                    'Duration
                    Dim Duration As OleDbParameter = New OleDbParameter("@Duration", OleDbType.VarChar, 20)
                    Duration.Value = Me.TxtDuration.Text.ToString()
                    cmd2.Parameters.Add(Duration)

                    'Amount
                    Dim Amount As OleDbParameter = New OleDbParameter("@Amount", OleDbType.VarChar, 10)
                    Amount.Value = TxtAmount.Text.ToString()
                    cmd2.Parameters.Add(Amount)

                    'Particular
                    Dim Particular As OleDbParameter = New OleDbParameter("@Particular", OleDbType.VarChar, 20)
                    Particular.Value = TxtParticular.Text.ToString()
                    cmd2.Parameters.Add(Particular)

                    If cmd2.ExecuteNonQuery() Then

                        OleCn.Close()
                        con.Close()
                        MsgBox("Record Update successfully ", MsgBoxStyle.Information, "Record Update")
                        MainFrm.cmdRefresh.PerformClick()
                        Me.Close()
                    Else
                        MsgBox("Record Updation Failed ", MsgBoxStyle.Critical, "Updation Failed")
                        Return
                    End If

                Catch ex As Exception
                    MessageBox.Show(ex.Message.ToString(), "Data Error")
                    Exit Sub
                End Try
            End If

        Catch ex As Exception
        End Try
    End Sub

    Private Sub EditFrm_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles Me.KeyDown
        e.SuppressKeyPress = e.Control
        If e.KeyCode = Keys.Enter Then
            SendKeys.Send("{tab}")
        End If
    End Sub

    Private Sub EditFrm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
      
        txtSerialNo.ReadOnly = True
        Me.KeyPreview = True
    End Sub

    Private Sub txtSerialNo_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtSerialNo.KeyPress
        Dim NotAllowed As String = "abcdefghijklmnopqrstuvwxyz~`@#$%^&*()_-+={[}]:;'.><?|/\.!"

        'Allowed Numbers Only
        If e.KeyChar <> ControlChars.Back = True Then
            If NotAllowed.IndexOf(e.KeyChar) = -1 = False Then
                e.Handled = True
            End If
        End If
    End Sub

    Private Sub txtName_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtName.KeyPress
        Dim NotAllowed As String = "1234567890~`@#$%^&*()_-+={[}]:;!><?/|\"

        'Allowed Letters only
        If e.KeyChar <> ControlChars.Back = True Then
            If NotAllowed.IndexOf(e.KeyChar) = -1 = False Then
                e.Handled = True
            End If
        End If
    End Sub

    Private Sub TxtAmount_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TxtAmount.KeyPress
        Dim NotAllowed As String = "abcdefghijklmnopqrstuvwxyz~`@#$%^&*()_-+={[}]:;'.><?|/\.!"

        'Allowed Numbers Only
        If e.KeyChar <> ControlChars.Back = True Then
            If NotAllowed.IndexOf(e.KeyChar) = -1 = False Then
                e.Handled = True
            End If
        End If
    End Sub

    Private Sub txtLogin_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtLogin.KeyPress
        Dim NotAllowed As String = "bcdefghijklnoqrstuvwxyz~`@#$%^&*()_-+={[}];'><?|/\,!"

        'Allowed Numbers ( : . A M P)
        If e.KeyChar <> ControlChars.Back = True Then
            If NotAllowed.IndexOf(e.KeyChar) = -1 = False Then
                e.Handled = True
            End If
        End If
    End Sub

    Private Sub TxtLogout_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TxtLogout.KeyPress
        Dim NotAllowed As String = "bcdefghijklnoqrstuvwxyz~`@#$%^&*()_-+={[}];'><?|/\,!"

        'Allowed Numbers ( : . A M P)
        If e.KeyChar <> ControlChars.Back = True Then
            If NotAllowed.IndexOf(e.KeyChar) = -1 = False Then
                e.Handled = True
            End If
        End If
    End Sub

    Private Sub TxtDuration_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TxtDuration.KeyPress
        Dim NotAllowed As String = "bcdefghijklnoqrstuvwxyz~`@#$%^&*()_-+={[}];'><?|/\,!"

        'Allowed Numbers ( : . A M P)
        If e.KeyChar <> ControlChars.Back = True Then
            If NotAllowed.IndexOf(e.KeyChar) = -1 = False Then
                e.Handled = True
            End If
        End If
    End Sub

    Private Sub cmdPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrint.Click
        Dim cryRpt As New ReportDocument
        Dim crtableLogoninfos As New TableLogOnInfos
        Dim crtableLogoninfo As New TableLogOnInfo
        Dim crConnectionInfo As New ConnectionInfo
        Dim CrTables As Tables
        Dim CrTable As Table

        Try
            With OleCn
                If .State <> ConnectionState.Open Then
                    .ConnectionString = StrConnection()
                    .Open()
                End If
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try


        Try
            cryRpt.Load(My.Application.Info.DirectoryPath & "\Reports\Report1.rpt")

            With crConnectionInfo
                .ServerName = Application.StartupPath & "\BackUp\Printmaker11.Accdb"
                .DatabaseName = Application.StartupPath & "\BackUp\Printmaker11.Accdb"
                .UserID = ""
                .Password = ""
            End With

            CrTables = cryRpt.Database.Tables
            For Each CrTable In CrTables
                crtableLogoninfo = CrTable.LogOnInfo
                crtableLogoninfo.ConnectionInfo = crConnectionInfo
                CrTable.ApplyLogOnInfo(crtableLogoninfo)
            Next


            Dim QueryString As String
            QueryString = "Select * from Table1 where SerialNo like @SerialNo"

            Dim Cmd As New OleDbCommand(QueryString, OleCn)
            Cmd.Parameters.Add("@SerialNo", OleDbType.VarChar).Value = txtSerialNo.Text

            Dim Adapter As OleDbDataAdapter = New OleDbDataAdapter(Cmd)
            Dim ds As DataSet = New DataSet()
            Adapter.Fill(ds, "Table1")

            cryRpt.SetDataSource(ds)
            Reports.CrystalReportViewer1.ReportSource = cryRpt
            Reports.GroupBox1.Visible = False
            Reports.cmdPrint.Visible = False
            Reports.cmdRefresh.Visible = False
            Reports.cmdExit.Visible = False
            Reports.Show()
            OleCn.Close()

        Catch ex As Exception
            MsgBox(ex.Message(), MsgBoxStyle.Critical, "Print Report Error...")
        End Try
    End Sub
End Class