مشكله فى حفظ سجل جديد - نسخة قابلة للطباعة

كود :
Imports System.Data.SqlClient

Imports Microsoft.Office.Interop.Excel

Imports System.Data

Imports System.Data.OleDb



Public Class Frmbank

    'Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=bank;Integrated Security=True")

    '---------Declared Dapper Classes Mode

    Dim DBcontext As New db_accountDataContext

    Dim Tblbank As New bank()

    Dim pos As Integer

    '----------LoadData----------

    Public Sub LoadData(ByVal Dgv As DataGridView)

        Dgv.Rows.Clear()

        Dim bank = From B In DBcontext.banks Select B

        Dgv.DataSource = bank

    End Sub

    '-------------Function MaxID------------

    Public Function MaxID_bank() As Integer

        Dim Count = (From B In DBcontext.banks Select B).Count() ' جلب عدد السجلات

        Dim Maxid As Integer

        If Count > 0 Then

            Maxid = (From B In DBcontext.banks Select B.bank_id).Max()

            Return Maxid + 1

        Else

            Maxid = 1

            Return Maxid

        End If

    End Function

    '---------Public Sub Cleardata--------

    Public Sub Cleardata_bank()

        Txt_bank_id.Text = ""

        Txt_bank_name.Text = ""

        Txt_bank_address.Text = ""

        Txt_bank_swiftcode.Text = ""

        Txt_bank_phone.Text = ""

        Txt_bank_fax.Text = ""

        Txt_bank_email.Text = ""

        Txt_bank_webpage.Text = ""

        Cmb_bank_user_id.Text = ""

        Dtp_bank_date.Value = now

        Txt_bank_note.Text = ""

      

        Txt_bank_name.focus()

        Txt_bank_id.Text = MaxID_bank()

    End Sub



    Public Sub recordcount()

        Dim Count = (From B In DBcontext.banks Select B).Count() ' جلب عدد السجلات

        'Label4.Text = Count

        TextBoxCount.Text = "السجل" & Dgv.CurrentRow.Index + 1 & "من" & Count



        'للحصول على عدد السجلات

        'Dim dbcontext As New db_accountDataContext

        'Dim c = Aggregate co In dbcontext.banks Into Count()

    End Sub

    Private Sub Frmbank_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        LoadData(Dgv)

        recordcount()

        Dgv.Columns(0).HeaderText = "رقم البنك"

        Dgv.Columns(1).HeaderText = "اسم البنك"

        Dgv.Columns(2).HeaderText = "العنوان"

        Dgv.Columns(3).HeaderText = "السويفت كود"

        Dgv.Columns(4).HeaderText = "التليفون"

        Dgv.Columns(5).HeaderText = "الفاكس"

        Dgv.Columns(6).HeaderText = "البريد الالكتروني"

        Dgv.Columns(7).HeaderText = "الصفحة الالكترونية"

        Dgv.Columns(8).HeaderText = "المستخدم"

        Dgv.Columns(9).HeaderText = "تاريخ التسجيل"

        Dgv.Columns(10).HeaderText = "ملاحظات"

        'Cleardata_bank()

    End Sub



    Private Sub BtnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnNew.Click

        Cleardata_bank()

    End Sub



    Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAdd.Click

        DBcontext = New db_accountDataContext()

        Tblbank = New bank()

        Try

            Tblbank.bank_id = CInt(Txt_bank_id.Text)

            Tblbank.bank_name = Txt_bank_name.Text

            Tblbank.bank_address = Txt_bank_address.Text

            Tblbank.bank_swiftcode = Txt_bank_swiftcode.Text

            Tblbank.bank_phone = Txt_bank_phone.Text

            Tblbank.bank_fax = Txt_bank_fax.Text

            Tblbank.bank_email = Txt_bank_email.Text

            Tblbank.bank_webpage = Txt_bank_webpage.Text

            Tblbank.bank_user_id = CInt(Cmb_bank_user_id.Text)

            Tblbank.bank_date = Dtp_bank_date.Value

            Tblbank.bank_note = Txt_bank_note.Text

           

            DBcontext.banks.InsertOnSubmit(Tblbank)

            DBcontext.SubmitChanges()

            MessageBox.Show("تم حفظ البيانات بنجاح", "حفظ", MessageBoxButtons.OK, MessageBoxIcon.Information)

        Catch ex As Exception

            Return

        End Try

        LoadData(Dgv)

    End Sub



    Private Sub BtnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnEdit.Click

        DBcontext = New db_accountDataContext()

        Tblbank = New bank()

        Try

            Tblbank = (From B In DBcontext.banks Where B.bank_id = CInt(Txt_bank_id.Text) Select B).First()

            Tblbank.bank_name = Txt_bank_name.Text

            Tblbank.bank_address = Txt_bank_address.Text

            Tblbank.bank_swiftcode = Txt_bank_swiftcode.Text

            Tblbank.bank_phone = Txt_bank_phone.Text

            Tblbank.bank_fax = Txt_bank_fax.Text

            Tblbank.bank_email = Txt_bank_email.Text

            Tblbank.bank_webpage = Txt_bank_webpage.Text

            Tblbank.bank_user_id = CInt(Cmb_bank_user_id.Text)

            Tblbank.bank_date = Dtp_bank_date.Value

            Tblbank.bank_note = Txt_bank_note.Text

           

            DBcontext.SubmitChanges()

            MessageBox.Show("تم  تعديل السجل بنجاح", "تعديل", MessageBoxButtons.OK, MessageBoxIcon.Information)

        Catch ex As Exception

            Return

        End Try

        LoadData(Dgv)

    End Sub



    Private Sub BtnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDelete.Click

        DBcontext = New db_accountDataContext()

        Tblbank = New bank()

        Try

            Tblbank = (From B In DBcontext.banks Where B.bank_id = CInt(Txt_bank_id.Text) Select B).Single()

            DBcontext.banks.DeleteOnSubmit(Tblbank)

            DBcontext.SubmitChanges()

            MessageBox.Show("تم حذف السجل بنجاح", "حذف", MessageBoxButtons.OK, MessageBoxIcon.Information)

        Catch ex As Exception

            Return

        End Try

        LoadData(Dgv)

    End Sub



    Private Sub BtnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnFind.Click

        Dim Frmsearch As New Frm_bank_search

        Frmsearch.ShowDialog()

    End Sub



    Private Sub BtnReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnReport.Click

        Dim Frmprint As New Frm_bank_print

        Frmprint.ShowDialog()

    End Sub



    Sub moveData()

        Try

            Txt_bank_id.Text = Dgv.CurrentRow.Cells(0).Value.ToString()

            Txt_bank_name.Text = Dgv.CurrentRow.Cells(1).Value.ToString()

            Txt_bank_address.Text = Dgv.CurrentRow.Cells(2).Value.ToString()

            Txt_bank_swiftcode.Text = Dgv.CurrentRow.Cells(3).Value.ToString()

            Txt_bank_phone.Text = Dgv.CurrentRow.Cells(4).Value.ToString()

            Txt_bank_fax.Text = Dgv.CurrentRow.Cells(5).Value.ToString()

            Txt_bank_email.Text = Dgv.CurrentRow.Cells(6).Value.ToString()

            Txt_bank_webpage.Text = Dgv.CurrentRow.Cells(7).Value.ToString()

            Cmb_bank_user_id.Text = Dgv.CurrentRow.Cells(8).Value.ToString()

            Dtp_bank_date.Value = Dgv.CurrentRow.Cells(9).Value.ToString()

            Txt_bank_note.Text = Dgv.CurrentRow.Cells(10).Value.ToString()



            'TextBoxCount.Text = "السجل" & Dgv.CurrentRow.Index & "من" & Label4.Text



        Catch ex As Exception

            Return

        End Try

    End Sub



    Private Sub BtnFrist_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnFrist.Click

        pos = 0

        Dgv.CurrentCell = Dgv.Rows(pos).Cells(Dgv.CurrentCell.ColumnIndex)

        moveData()

        recordcount()

    End Sub



    Private Sub Txt_Search_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Txt_Search.TextChanged

        Try

            If Txt_Search.Text <> "" Then

                Dgv.Rows.Clear()

                Dgv.DataSource = From b In DBcontext.banks Where b.bank_name.StartsWith(Txt_Search.Text) Select b

            Else

                Dgv.DataSource = From b In DBcontext.banks Select b

            End If

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try

    End Sub



    Private Sub Btnprevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnprevious.Click

        pos -= 1

        If pos >= 0 Then

            Dgv.CurrentCell = Dgv.Rows(pos).Cells(Dgv.CurrentCell.ColumnIndex)

            moveData()

        Else

            pos = 0

            Dgv.CurrentCell = Dgv.Rows(pos).Cells(Dgv.CurrentCell.ColumnIndex)

            MessageBox.Show("هذا اول سجل")

        End If

        moveData()

        recordcount()

    End Sub



    Private Sub BtnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnNext.Click

        pos += 1

        If pos <= Dgv.Rows.Count - 1 Then

            Dgv.CurrentCell = Dgv.Rows(pos).Cells(Dgv.CurrentCell.ColumnIndex)



        Else

            pos = Dgv.Rows.Count - 1

            Dgv.CurrentCell = Dgv.Rows(pos).Cells(Dgv.CurrentCell.ColumnIndex)

            MessageBox.Show("هذا اخر سجل")

        End If

        moveData()

        recordcount()

    End Sub



    Private Sub BtnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnLast.Click

        pos = Dgv.Rows.Count - 1

        Dgv.CurrentCell = Dgv.Rows(pos).Cells(Dgv.CurrentCell.ColumnIndex)

        moveData()

        recordcount()

    End Sub



    Private Sub Dgv_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Dgv.SelectionChanged

        moveData()

    End Sub



    Private Sub BtnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnCancel.Click

        Close()

    End Sub



    Private Sub BtnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnExport.Click

        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-us")

        Dim ExlApp As New Application

        Dim ExlWorkBook As Workbook

        Dim ExlWorkSheet As New Worksheet

        If Dgv.Rows IsNot Nothing Then

            Dim MisValue As Object = System.Reflection.Missing.Value

            ExlWorkBook = ExlApp.Workbooks.Add(MisValue)

            ExlWorkSheet = ExlWorkBook.Sheets.Add

            ExlWorkSheet.Name = "sheet1"

            'ExlWorkSheet = ExlWorkBook.Sheets("sheet1")

            For colheader As Integer = 0 To Dgv.ColumnCount - 1

                ExlWorkSheet.Cells(1, colheader + 1) = Dgv.Columns(colheader).HeaderText

            Next

            For r As Integer = 0 To Dgv.RowCount - 2

                For c As Integer = 0 To Dgv.ColumnCount - 1

                    ExlWorkSheet.Cells(r + 2, c + 1) = Dgv.Rows(r).Cells(c).Value

                Next

            Next

        End If

        SaveFileDialog1.Filter = "Excel Files|*.xlsx|Excel 2003|*.xls"

        If SaveFileDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then

            ExlWorkSheet.SaveAs(SaveFileDialog1.FileName)

        End If

        ExlWorkBook.Close()

        ExlApp.Quit()

        System.Runtime.InteropServices.Marshal.ReleaseComObject(ExlApp)

        System.Runtime.InteropServices.Marshal.ReleaseComObject(ExlWorkBook)

        System.Runtime.InteropServices.Marshal.ReleaseComObject(ExlWorkSheet)

        ExlApp = Nothing

        ExlWorkBook = Nothing

        ExlWorkSheet = Nothing

        If MessageBox.Show("هل تريد فتح الملف ؟", "فتح الملف", MessageBoxButtons.YesNo) Then

            Process.Start(SaveFileDialog1.FileName)

        End If

    End Sub



    Private Sub BtnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnImport.Click

        OpenFileDialog1.Filter = "Excel 2007|*.xlsx|Excel 2003|*.xls"

        If OpenFileDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then

            Dgv.DataSource = Nothing

            Dgv.Columns.Clear()

            Dim path As String = OpenFileDialog1.FileName

            Dim conn As New OleDb.OleDbConnection("provider=microsoft.ACE.oledb.12.0;data source=" & path & "; extended properties=excel 12.0;")

            Dim ds As New DataSet

            Dim ad As New OleDbDataAdapter("select * from [sheet1$]", conn)

            ad.Fill(ds, "[sheet1$]")

            Dgv.DataSource = ds.Tables("[sheet1$]")

        Else

            Close()

        End If

    End Sub

End Class