How to automatically update database from windows form?

daveofgv

Well-known member
Joined
Sep 17, 2008
Messages
218
Location
Dallas, TX
Programming Experience
1-3
Hello all -

I have a program that I am connecting to the database through code and not using the wizard.

My problem is that I need to update the database automatically and not connect using the wizard:

right now my code looks like this:

VB.NET:
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Data.OleDb
Imports System.IO.File
Imports System.IO
Imports System.Text

Public Class frmsearch
    Dim con As New OleDbConnection

    Private Sub frmsearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source= C:\Users\dbush\Desktop\new phcc database\PHCC_Tracking.mdb"
        Label2.Text = ""
    End Sub

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
        con.Open()

        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter

        da = New OleDbDataAdapter("Select `Resident Name`, `Date Admitted`, `Date Discharged`, `Record Number`, `Box Number`, `Shelf Number`  from masterlisting where `Resident Name` like '%" & TextBox1.Text & "%'", con)
        'da = New OleDbDataAdapter("Select resident_name, date_admitted, date_discharged, record_number, box_number, shelf_number, from PHCC_Box_Tracking where resident_name like '%" & TextBox1.Text & "%'", con)

        da.Fill(dt)

        DataGridView1.DataSource = dt.DefaultView

        con.Close()

        Label2.Text = DataGridView1.Rows.Count.ToString()

    End Sub

    Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox2.TextChanged
        con.Open()

        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter

        da = New OleDbDataAdapter("Select `Resident Name`, `Date Admitted`, `Date Discharged`, `Record Number`, `Box Number`, `Shelf Number`  from masterlisting where `Date Admitted` like '%" & TextBox2.Text & "%'", con)
        'da = New OleDbDataAdapter("Select resident_name, date_admitted, date_discharged, record_number, box_number, shelf_number, from PHCC_Box_Tracking where resident_name like '%" & TextBox1.Text & "%'", con)

        da.Fill(dt)

        DataGridView1.DataSource = dt.DefaultView

        con.Close()

        Label2.Text = DataGridView1.Rows.Count.ToString()


    End Sub

    Private Sub TextBox3_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox3.TextChanged
        con.Open()

        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter

        da = New OleDbDataAdapter("Select `Resident Name`, `Date Admitted`, `Date Discharged`, `Record Number`, `Box Number`, `Shelf Number`  from masterlisting where `Date Discharged` like '%" & TextBox3.Text & "%'", con)
        'da = New OleDbDataAdapter("Select resident_name, date_admitted, date_discharged, record_number, box_number, shelf_number, from PHCC_Box_Tracking where resident_name like '%" & TextBox1.Text & "%'", con)

        da.Fill(dt)

        DataGridView1.DataSource = dt.DefaultView

        con.Close()

        Label2.Text = DataGridView1.Rows.Count.ToString()

    End Sub

    Private Sub TextBox4_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox4.TextChanged
        con.Open()

        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter

        da = New OleDbDataAdapter("Select `Resident Name`, `Date Admitted`, `Date Discharged`, `Record Number`, `Box Number`, `Shelf Number`  from masterlisting where `Record Number` like '%" & TextBox4.Text & "%'", con)
        'da = New OleDbDataAdapter("Select resident_name, date_admitted, date_discharged, record_number, box_number, shelf_number, from PHCC_Box_Tracking where resident_name like '%" & TextBox1.Text & "%'", con)

        da.Fill(dt)

        DataGridView1.DataSource = dt.DefaultView

        con.Close()

        Label2.Text = DataGridView1.Rows.Count.ToString()


    End Sub

    Private Sub TextBox5_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox5.TextChanged
        con.Open()

        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter

        da = New OleDbDataAdapter("Select `Resident Name`, `Date Admitted`, `Date Discharged`, `Record Number`, `Box Number`, `Shelf Number`  from masterlisting where `Box Number` like '%" & TextBox5.Text & "%'", con)
        'da = New OleDbDataAdapter("Select resident_name, date_admitted, date_discharged, record_number, box_number, shelf_number, from PHCC_Box_Tracking where resident_name like '%" & TextBox1.Text & "%'", con)

        da.Fill(dt)

        DataGridView1.DataSource = dt.DefaultView

        con.Close()

        Label2.Text = DataGridView1.Rows.Count.ToString()


    End Sub

    Private Sub TextBox6_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox6.TextChanged
        con.Open()

        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter

        da = New OleDbDataAdapter("Select `Resident Name`, `Date Admitted`, `Date Discharged`, `Record Number`, `Box Number`, `Shelf Number`  from masterlisting where `Shelf Number` like '%" & TextBox6.Text & "%'", con)
        'da = New OleDbDataAdapter("Select resident_name, date_admitted, date_discharged, record_number, box_number, shelf_number, from PHCC_Box_Tracking where resident_name like '%" & TextBox1.Text & "%'", con)

        da.Fill(dt)

        DataGridView1.DataSource = dt.DefaultView

        con.Close()

        Label2.Text = DataGridView1.Rows.Count.ToString()


    End Sub

    Private Sub btnexportcsv_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnexportcsv.Click
        ' Dim myStream As Stream
        Dim saveFileDialog1 As New SaveFileDialog()

        saveFileDialog1.Filter = "Excel files (*.xls)|*.xls|csv files (*.csv)|*.csv|txt files (*.txt)|*.txt|doc files (*.doc)|*.doc|All files (*.*)|*.*"
        saveFileDialog1.FilterIndex = 2
        saveFileDialog1.RestoreDirectory = True

        If saveFileDialog1.ShowDialog() = DialogResult.OK Then

            FileOpen(1, saveFileDialog1.FileName, OpenMode.Output)

            Dim iX As Integer


            Dim sY As String


            For iX = 0 To DataGridView1.RowCount - 1

                sY = DataGridView1(0, iX).Value.ToString & "," & DataGridView1(1, iX).Value.ToString & _
                "," & DataGridView1(2, iX).Value.ToString & "," & DataGridView1(3, iX).Value.ToString & _
                "," & DataGridView1(4, iX).Value.ToString & "," & DataGridView1(5, iX).Value.ToString & _
                "," & DataGridView1(6, iX).Value.ToString


                Print(1, sY)


            Next

            FileClose(1)

            ' myStream.Close()
        End If




        '    myStream = saveFileDialog1.OpenFile()
        '    If (myStream IsNot Nothing) Then

        '        'FileOpen(1, "C:\Users\dbush\Desktop\new phcc database\csvtest.csv", OpenMode.Output)

        'End If

        'FileOpen(1, "C:\Users\dbush\Desktop\new phcc database\csvtest.csv", OpenMode.Output)


        'Dim iX As Integer


        'Dim sY As String


        'For iX = 0 To DataGridView1.RowCount - 1

        '    sY = DataGridView1(0, iX).Value.ToString & "," & DataGridView1(1, iX).Value.ToString & "," & DataGridView1(2, iX).Value.ToString & "," & DataGridView1(3, iX).Value.ToString & "," & DataGridView1(4, iX).Value.ToString & "," & DataGridView1(5, iX).Value.ToString & "," & DataGridView1(6, iX).Value.ToString


        '    Print(1, sY)


        'Next

        'FileClose(1)

    End Sub

    Private Sub DatabasePathToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DatabasePathToolStripMenuItem.Click
        MessageBox.Show("T:\Development\PHCC_Box_Tracking\PHCC Traking\Database\PHCC_Tracking.mdb")
    End Sub

    Private Sub OpenFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk

    End Sub

    Private Sub SaveFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles SaveFileDialog1.FileOk


        'FileOpen(1, "C:\Users\dbush\Desktop\new phcc database\csvtest.csv", OpenMode.Output)


        'Dim iX As Integer


        'Dim sY As String


        'For iX = 0 To DataGridView1.RowCount - 1

        '    sY = DataGridView1(0, iX).Value.ToString & "," & DataGridView1(1, iX).Value.ToString & "," & DataGridView1(2, iX).Value.ToString & "," & DataGridView1(3, iX).Value.ToString & "," & DataGridView1(4, iX).Value.ToString & "," & DataGridView1(5, iX).Value.ToString & "," & DataGridView1(6, iX).Value.ToString


        '    Print(1, sY)


        'Next

        'FileClose(1)
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Call ExportWord()
    End Sub


    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Call ExportText()

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Call ExportExcel()


        'Dim xlApp As Excel.Application
        'Dim xlWorkBook As Excel.Workbook
        'Dim xlWorkSheet As Excel.Worksheet
        'Dim misValue As Object = System.Reflection.Missing.Value
        'Dim i As Integer
        'Dim j As Integer

        'xlApp = New Excel.ApplicationClass
        'xlWorkBook = xlApp.Workbooks.Add(misValue)
        'xlWorkSheet = xlWorkBook.Sheets("sheet1")

        'For i = 0 To DataGridView1.RowCount - 2
        '    For j = 0 To DataGridView1.ColumnCount - 1
        '        xlWorkSheet.Cells(i + 1, j + 1) = _
        '            DataGridView1(j, i).Value.ToString()
        '    Next
        'Next

        'xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
        'xlWorkBook.Close()
        'xlApp.Quit()



        'MsgBox("You can find the file C:\vbexcel.xlsx")


        '**************************************************************************************

        'Dim xlApp As Excel.Application
        'Dim xlWorkBook As Excel.Workbook
        'Dim xlWorkSheet As Excel.Worksheet
        'Dim misValue As Object = System.Reflection.Missing.Value
        'Dim i As Integer
        'Dim j As Integer

        'xlApp = New Excel.ApplicationClass
        'xlWorkBook = xlApp.Workbooks.Add(misValue)
        'xlWorkSheet = xlWorkBook.Sheets("sheet1")

        'For i = 0 To DataGridView1.RowCount - 2
        '    For j = 0 To DataGridView1.ColumnCount - 0
        '        xlWorkSheet.Cells(i + 1, j + 1) = _
        '            DataGridView1(j, i).Value.ToString()
        '    Next
        'Next

        'xlWorkSheet.SaveAs("C:\Users\dbush\Desktop\new phcc database\vbexcel.xlsx")
        'xlWorkBook.Close()
        'xlApp.Quit()



        'MsgBox("You can find the file C:\vbexcel.xlsx")




        '' Dim myStream As Stream
        'Dim saveFileDialog1 As New SaveFileDialog()

        'saveFileDialog1.Filter = "Excel files (*.xls)|*.xls"
        'saveFileDialog1.FilterIndex = 2
        'saveFileDialog1.RestoreDirectory = True

        'If saveFileDialog1.ShowDialog() = DialogResult.OK Then

        '    FileOpen(1, saveFileDialog1.FileName, OpenMode.Output)

        '    Dim iX As Integer


        '    Dim sY As String


        '    For iX = 0 To DataGridView1.RowCount - 1

        '        sY = DataGridView1(0, iX).Value.ToString & "," & DataGridView1(1, iX).Value.ToString & _
        '        "," & DataGridView1(2, iX).Value.ToString & "," & DataGridView1(3, iX).Value.ToString & _
        '        "," & DataGridView1(4, iX).Value.ToString & "," & DataGridView1(5, iX).Value.ToString & _
        '        "," & DataGridView1(6, iX).Value.ToString


        '        Print(1, sY)


        '    Next

        '    FileClose(1)

        '    ' myStream.Close()
        'End If
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        Call ExportCSV()






        '' Dim myStream As Stream
        'Dim saveFileDialog1 As New SaveFileDialog()

        'saveFileDialog1.Filter = "csv files (*.csv)|*.csv"
        'saveFileDialog1.FilterIndex = 2
        'saveFileDialog1.RestoreDirectory = True

        'If saveFileDialog1.ShowDialog() = DialogResult.OK Then

        '    FileOpen(1, saveFileDialog1.FileName, OpenMode.Output)

        '    Dim iX As Integer


        '    Dim sY As String


        '    For iX = 0 To DataGridView1.RowCount - 1

        '        sY = DataGridView1(0, iX).Value.ToString & "," & DataGridView1(1, iX).Value.ToString & _
        '        "," & DataGridView1(2, iX).Value.ToString & "," & DataGridView1(3, iX).Value.ToString & _
        '        "," & DataGridView1(4, iX).Value.ToString & "," & DataGridView1(5, iX).Value.ToString & _
        '        "," & DataGridView1(6, iX).Value.ToString


        '        Print(1, sY)


        '    Next

        '    FileClose(1)

        '    ' myStream.Close()
        'End If
    End Sub


    Private Sub MenuStrip1_ItemClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolStripItemClickedEventArgs) Handles MenuStrip1.ItemClicked

    End Sub

    Private Sub btnprint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnprint.Click
        PrintDocument1.Print()
    End Sub

    Private Sub PrintPreviewDialog1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PrintPreviewDialog1.Load


    End Sub

    Private Sub PrintDocument1_PrintPage(ByVal sender As System.Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDocument1.PrintPage
        Dim bm As New Bitmap(Me.DataGridView1.Width, Me.DataGridView1.Height)
        DataGridView1.DrawToBitmap(bm, New Rectangle(0, 0, Me.DataGridView1.Width, Me.DataGridView1.Height))
        e.Graphics.DrawImage(bm, 0, 0)

    End Sub

    Private Sub Label2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label2.Click

    End Sub

    Private Sub Label2_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Label2.TextChanged

    End Sub

    Private Sub btnmain_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub

    Private Sub DataGridView1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.DoubleClick

    End Sub

    Private Sub DataGridView1_TabIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.TabIndexChanged

    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        con.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source= C:\Users\dbush\Desktop\new phcc database\PHCC_Tracking.mdb"
        Label1.Text = ""
        con.Open()

        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter

        da = New OleDbDataAdapter("Select * from masterlisting where `Open or Closed` = True", con)
        'da = New OleDbDataAdapter("Select resident_name, date_admitted, date_discharged, record_number, box_number, shelf_number, from PHCC_Box_Tracking where resident_name like '%" & TextBox1.Text & "%'", con)

        da.Fill(dt)

        DataGridView1.DataSource = dt.DefaultView

        con.Close()

        Label1.Text = DataGridView1.Rows.Count.ToString()




        'Dim fe As New frmedit
        'fe.Show()

        'DataGridView1.RowCount.ToString()
        'MessageBox.Show(DataGridView1.RowCount)

    End Sub

    Private Sub CheckBox1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub btnshowall_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnshowall.Click
        con.Open()

        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter

        da = New OleDbDataAdapter("Select * from masterlisting", con)
        'da = New OleDbDataAdapter("Select resident_name, date_admitted, date_discharged, record_number, box_number, shelf_number, from PHCC_Box_Tracking where resident_name like '%" & TextBox1.Text & "%'", con)

        da.Fill(dt)

        DataGridView1.DataSource = dt.DefaultView

        con.Close()

        Label2.Text = DataGridView1.Rows.Count.ToString()

    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        'Call ExporterDtgView()
        Me.BackColor = Color.Green

    End Sub
    Private Sub ExporterDtgView()

        SaveFileDialog2.Filter = "txt files (*.txt)|*.txt"
        SaveFileDialog2.FilterIndex = 2
        SaveFileDialog2.RestoreDirectory = True


        Try
            'chose the distination file
            If SaveFileDialog2.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                'It validates the edition of the DataGridView
                DataGridView1.EndEdit()
                'We are preparing a brief to write formatted data to the file
                Dim ToSave As New StringBuilder()
                'We'll put the headers as required :-)
                Dim Headers As String = String.Empty
                For index As Integer = 0 To DataGridView1.Columns.Count - 1
                    Headers &= DataGridView1.Columns(index).HeaderText & "?"
                Next
                'The loop adds a ";" at the end if it's useless
                Headers = Headers.Remove(Headers.LastIndexOf("?"), 1)
                'Now it is stored in the memory
                ToSave.AppendLine(Headers)

                'Loop over all available lines
                For i As UInt64 = 0 To DataGridView1.Rows.Count - 1
                    'It is a variable to store a line
                    Dim OneRow As String = String.Empty
                    'You can do a loop on all the columns available if the line is not empty
                    If DataGridView1.Rows(i).IsNewRow = False Then
                        For j As Integer = 0 To DataGridView1.Rows(i).Cells.Count - 1
                            OneRow &= DataGridView1.Rows(i).Cells(j).Value & "?"
                        Next
                        OneRow = OneRow.Remove(OneRow.LastIndexOf("?"), 1)
                        ToSave.AppendLine(OneRow)
                    End If
                Next
                'now trying to write the file
                IO.File.WriteAllText(SaveFileDialog2.FileName, ToSave.ToString(), Encoding.Default)
            End If

        Catch ex As Exception
            MessageBox.Show(String.Format("{0}{1}{1}{2}", ex.Message, Environment.NewLine, ex.StackTrace))
        End Try
    End Sub
    Private Sub ExportText()

        SaveFileDialog2.Filter = "txt files (*.txt)|*.txt"
        SaveFileDialog2.FilterIndex = 2
        SaveFileDialog2.RestoreDirectory = True


        Try
            'chose the distination file
            If SaveFileDialog2.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                'It validates the edition of the DataGridView
                DataGridView1.EndEdit()
                'We are preparing a brief to write formatted data to the file
                Dim ToSave As New StringBuilder()
                'We'll put the headers as required :-)
                Dim Headers As String = String.Empty
                For index As Integer = 0 To DataGridView1.Columns.Count - 1
                    Headers &= DataGridView1.Columns(index).HeaderText & "?"
                Next
                'The loop adds a ";" at the end if it's useless
                Headers = Headers.Remove(Headers.LastIndexOf("?"), 1)
                'Now it is stored in the memory
                ToSave.AppendLine(Headers)

                'Loop over all available lines
                For i As UInt64 = 0 To DataGridView1.Rows.Count - 1
                    'It is a variable to store a line
                    Dim OneRow As String = String.Empty
                    'You can do a loop on all the columns available if the line is not empty
                    If DataGridView1.Rows(i).IsNewRow = False Then
                        For j As Integer = 0 To DataGridView1.Rows(i).Cells.Count - 1
                            OneRow &= DataGridView1.Rows(i).Cells(j).Value & "?"
                        Next
                        OneRow = OneRow.Remove(OneRow.LastIndexOf("?"), 1)
                        ToSave.AppendLine(OneRow)
                    End If
                Next
                'now trying to write the file
                IO.File.WriteAllText(SaveFileDialog2.FileName, ToSave.ToString(), Encoding.Default)
            End If

        Catch ex As Exception
            MessageBox.Show(String.Format("{0}{1}{1}{2}", ex.Message, Environment.NewLine, ex.StackTrace))
        End Try
    End Sub
    Private Sub ExportWord()

        SaveFileDialog2.Filter = "doc files (*.doc)|*.doc"
        SaveFileDialog2.FilterIndex = 2
        SaveFileDialog2.RestoreDirectory = True


        Try
            'chose the distination file
            If SaveFileDialog2.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                'It validates the edition of the DataGridView
                DataGridView1.EndEdit()
                'We are preparing a brief to write formatted data to the file
                Dim ToSave As New StringBuilder()
                'We'll put the headers as required :-)
                Dim Headers As String = String.Empty
                For index As Integer = 0 To DataGridView1.Columns.Count - 1
                    Headers &= DataGridView1.Columns(index).HeaderText & "?"
                Next
                'The loop adds a ";" at the end if it's useless
                Headers = Headers.Remove(Headers.LastIndexOf("?"), 1)
                'Now it is stored in the memory
                ToSave.AppendLine(Headers)

                'Loop over all available lines
                For i As UInt64 = 0 To DataGridView1.Rows.Count - 1
                    'It is a variable to store a line
                    Dim OneRow As String = String.Empty
                    'You can do a loop on all the columns available if the line is not empty
                    If DataGridView1.Rows(i).IsNewRow = False Then
                        For j As Integer = 0 To DataGridView1.Rows(i).Cells.Count - 1
                            OneRow &= DataGridView1.Rows(i).Cells(j).Value & "?"
                        Next
                        OneRow = OneRow.Remove(OneRow.LastIndexOf("?"), 1)
                        ToSave.AppendLine(OneRow)
                    End If
                Next
                'now trying to write the file
                IO.File.WriteAllText(SaveFileDialog2.FileName, ToSave.ToString(), Encoding.Default)
            End If

        Catch ex As Exception
            MessageBox.Show(String.Format("{0}{1}{1}{2}", ex.Message, Environment.NewLine, ex.StackTrace))
        End Try
    End Sub

    Private Sub ExportExcel()

        SaveFileDialog2.Filter = "xls files (*.xls)|*.xls"
        SaveFileDialog2.FilterIndex = 2
        SaveFileDialog2.RestoreDirectory = True


        Try
            'chose the distination file
            If SaveFileDialog2.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                'It validates the edition of the DataGridView
                DataGridView1.EndEdit()
                'We are preparing a brief to write formatted data to the file
                Dim ToSave As New StringBuilder()
                'We'll put the headers as required :-)
                Dim Headers As String = String.Empty
                For index As Integer = 0 To DataGridView1.Columns.Count - 1
                    Headers &= DataGridView1.Columns(index).HeaderText & "?"
                Next
                'The loop adds a ";" at the end if it's useless
                Headers = Headers.Remove(Headers.LastIndexOf("?"), 1)
                'Now it is stored in the memory
                ToSave.AppendLine(Headers)

                'Loop over all available lines
                For i As UInt64 = 0 To DataGridView1.Rows.Count - 1
                    'It is a variable to store a line
                    Dim OneRow As String = String.Empty
                    'You can do a loop on all the columns available if the line is not empty
                    If DataGridView1.Rows(i).IsNewRow = False Then
                        For j As Integer = 0 To DataGridView1.Rows(i).Cells.Count - 1
                            OneRow &= DataGridView1.Rows(i).Cells(j).Value & "?"
                        Next
                        OneRow = OneRow.Remove(OneRow.LastIndexOf("?"), 1)
                        ToSave.AppendLine(OneRow)
                    End If
                Next
                'now trying to write the file
                IO.File.WriteAllText(SaveFileDialog2.FileName, ToSave.ToString(), Encoding.Default)
            End If

        Catch ex As Exception
            MessageBox.Show(String.Format("{0}{1}{1}{2}", ex.Message, Environment.NewLine, ex.StackTrace))
        End Try
    End Sub


    Private Sub ExportCSV()

        SaveFileDialog2.Filter = "xls files (*.xls)|*.xls"
        SaveFileDialog2.FilterIndex = 2
        SaveFileDialog2.RestoreDirectory = True


        Try
            'chose the distination file
            If SaveFileDialog2.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                'It validates the edition of the DataGridView
                DataGridView1.EndEdit()
                'We are preparing a brief to write formatted data to the file
                Dim ToSave As New StringBuilder()
                'We'll put the headers as required :-)
                Dim Headers As String = String.Empty
                For index As Integer = 0 To DataGridView1.Columns.Count - 1
                    Headers &= DataGridView1.Columns(index).HeaderText & "?"
                Next
                'The loop adds a ";" at the end if it's useless
                Headers = Headers.Remove(Headers.LastIndexOf("?"), 1)
                'Now it is stored in the memory
                ToSave.AppendLine(Headers)

                'Loop over all available lines
                For i As UInt64 = 0 To DataGridView1.Rows.Count - 1
                    'It is a variable to store a line
                    Dim OneRow As String = String.Empty
                    'You can do a loop on all the columns available if the line is not empty
                    If DataGridView1.Rows(i).IsNewRow = False Then
                        For j As Integer = 0 To DataGridView1.Rows(i).Cells.Count - 1
                            OneRow &= DataGridView1.Rows(i).Cells(j).Value & "?"
                        Next
                        OneRow = OneRow.Remove(OneRow.LastIndexOf("?"), 1)
                        ToSave.AppendLine(OneRow)
                    End If
                Next
                'now trying to write the file
                IO.File.WriteAllText(SaveFileDialog2.FileName, ToSave.ToString(), Encoding.Default)
            End If

        Catch ex As Exception
            MessageBox.Show(String.Format("{0}{1}{1}{2}", ex.Message, Environment.NewLine, ex.StackTrace))
        End Try
    End Sub

    Private Sub AboutToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AboutToolStripMenuItem1.Click
        AboutBox1.Show()

    End Sub

    Private Sub TextBox7_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox7.TextChanged
        con.Open()

        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter

        da = New OleDbDataAdapter("Select * from masterlisting where `Checked Out` like '%" & TextBox7.Text & "%'", con)
        'da = New OleDbDataAdapter("Select resident_name, date_admitted, date_discharged, record_number, box_number, shelf_number, from PHCC_Box_Tracking where resident_name like '%" & TextBox1.Text & "%'", con)

        da.Fill(dt)

        DataGridView1.DataSource = dt.DefaultView

        con.Close()

        Label2.Text = DataGridView1.Rows.Count.ToString()
    End Sub

    Private Sub TextBox8_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox8.TextChanged
        con.Open()

        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter

        da = New OleDbDataAdapter("Select * from masterlisting where `Checked In Date` like '%" & TextBox8.Text & "%'", con)
        'da = New OleDbDataAdapter("Select resident_name, date_admitted, date_discharged, record_number, box_number, shelf_number, from PHCC_Box_Tracking where resident_name like '%" & TextBox1.Text & "%'", con)

        da.Fill(dt)

        DataGridView1.DataSource = dt.DefaultView

        con.Close()

        Label2.Text = DataGridView1.Rows.Count.ToString()
    End Sub

    Private Sub TextBox9_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox9.TextChanged
        con.Open()

        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter

        da = New OleDbDataAdapter("Select * from masterlisting where `Priority` like '%" & TextBox9.Text & "%'", con)
        'da = New OleDbDataAdapter("Select resident_name, date_admitted, date_discharged, record_number, box_number, shelf_number, from PHCC_Box_Tracking where resident_name like '%" & TextBox1.Text & "%'", con)

        da.Fill(dt)

        DataGridView1.DataSource = dt.DefaultView

        con.Close()

        Label2.Text = DataGridView1.Rows.Count.ToString()
    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        Dim fco As New frmedit
        fco.Show()

    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        con.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source= C:\Users\dbush\Desktop\new phcc database\PHCC_Tracking.mdb"
        Label1.Text = ""
        con.Open()

        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter

        da = New OleDbDataAdapter("Select * from masterlisting where `Open or Closed` = False", con)
        'da = New OleDbDataAdapter("Select resident_name, date_admitted, date_discharged, record_number, box_number, shelf_number, from PHCC_Box_Tracking where resident_name like '%" & TextBox1.Text & "%'", con)

        da.Fill(dt)

        DataGridView1.DataSource = dt.DefaultView

        con.Close()

        Label1.Text = DataGridView1.Rows.Count.ToString()
    End Sub
End Class

On another form I have it working be updating the database using the wizard and updating text changed from the datagrid view:

VB.NET:
Public Class Form1
    Dim UpdatePending As Boolean = False
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'PHCC_Tracking1DataSet.Box_Tracking' table. You can move, or remove it, as needed.


        Me.Box_TrackingTableAdapter.Fill(Me.PHCC_Tracking1DataSet.Box_Tracking)

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        If UpdatePending Then
            Me.Box_TrackingTableAdapter.Update(Me.PHCC_Tracking1DataSet.Box_Tracking)
            Me.UpdatePending = False
        End If

    End Sub

    Private Sub BoxTrackingBindingSource_CurrentChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BoxTrackingBindingSource.CurrentChanged

    End Sub

    Private Sub BoxTrackingBindingSource_ListChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ListChangedEventArgs) Handles BoxTrackingBindingSource.ListChanged
        If Me.PHCC_Tracking1DataSet.HasChanges Then
            Me.UpdatePending = True
        End If
    End Sub

    Private Sub Box_TrackingDataGridView_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles Box_TrackingDataGridView.CellContentClick

    End Sub

    Private Sub Box_TrackingDataGridView_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles Box_TrackingDataGridView.RowValidated
        If UpdatePending Then
            Me.Box_TrackingTableAdapter.Update(Me.PHCC_Tracking1DataSet.Box_Tracking)
            Me.UpdatePending = False
        End If
    End Sub

    Private Sub BindingNavigatorDeleteItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorDeleteItem.Click

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        frmsearch.Show()

    End Sub

    Private Sub BindingNavigator1_Refre****ems(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigator1.Refre****ems

    End Sub
End Class

I am having difficultly trying to update the first set of code with

VB.NET:
  If UpdatePending Then
            Me.Box_TrackingTableAdapter.Update(Me.PHCC_Tracking1DataSet.Box_Tracking)
            Me.UpdatePending = False
        End If

Does anyone have any suggestions???

Thanks in advanced

daveofgv
 

Latest posts

Back
Top