daveofgv
Well-known member
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:
On another form I have it working be updating the database using the wizard and updating text changed from the datagrid view:
I am having difficultly trying to update the first set of code with
Does anyone have any suggestions???
Thanks in advanced
daveofgv
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 ClassOn 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 ClassI 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 IfDoes anyone have any suggestions???
Thanks in advanced
daveofgv
