Hi All,
Finally getting somewhere with this project and can see the light at the end of a very long tunnel..
I have one more hurdle to overcome so any help in pointing me in the right direction would be greatly appreciated.
I have a bound datagrid which I fill from various search options that are available to the end user.
Currently I have one of the columns (status) set as a DataGridViewComboBoxColumn with a collection of two items. 'Open' and 'Closed'
When the I select for example 'Closed' and hit the 'Update' button an exception is passed with the caption
'Update unable to find TableMapping or DataTable 'Tracker''
I have been looking at hope the mapping works for the last 3 hours but getting nowhere fast and I really need to get this project completed by the end of the week.
The bound controls are:
Binding Source = BS
Data Member = Tracker
Data Source = TrackerDataSet
Many thanks in advance
The code is as follows:
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Net
Imports Outlook = Microsoft.Office.Interop.Outlook
Imports Microsoft.Win32
Imports System.Data.SqlClient
Imports System.Data.Common
Public Class FrmSearch
Dim i As Integer
Dim PathStr As String
Dim ToStr As String
Dim dbProvider As String
Dim dbSource As String
Dim sql As String
Dim LogW As String
Dim userFullName As String
Dim LogPath As String
Dim inc As Integer
Dim MyTable As New DataTable
Dim con As New OleDb.OleDbConnection
Dim da As New OleDb.OleDbDataAdapter
Dim ds As New DataSet
Dim dataSet As New DataSet
Private Const CP_NOCLOSE_BUTTON As Integer = &H200
Protected Overloads Overrides ReadOnly Property CreateParams() As CreateParams
Get
Dim myCp As CreateParams = MyBase.CreateParams
myCp.ClassStyle = myCp.ClassStyle Or CP_NOCLOSE_BUTTON
Return myCp
End Get
End Property
Private Sub FrmSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'log time and user logon name to log file
userFullName = System.Environment.UserName
LogPath = "\\iuser\Shared\004\ls-fileshare-01\UKSC Scotland\33_Scottish Water Tracker\Tracker Log\Tracker.Log"
Me.DGView1.DataSource = Me.TrackerBindingSource
ButExport.Enabled = False
ButEmail.Enabled = False
ButUpdate.Enabled = False
End Sub
Private Sub ButSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButSearch.Click
ButUpdate.Enabled = True
'Clears the contents of the datagrid and datasource
MyTable.Clear()
DGView1.DataSource = Nothing
'Sets the size of the datagrid
DGView1.Size = New System.Drawing.Size(1324, 446)
Dim x As Integer
Dim y As Integer
x = Screen.PrimaryScreen.WorkingArea.Width
y = Screen.PrimaryScreen.WorkingArea.Height - Me.Height
Do Until x = Screen.PrimaryScreen.WorkingArea.Width - Me.Width
x = x - 1
Me.Location = New Point(40, 50)
Loop
'read config ini file for database path
Dim PathName As String = "\\iuser\Shared\004\ls-fileshare-01\UKSC Scotland\33_Scottish Water Tracker\Config.ini"
If System.IO.File.Exists(PathName) = True Then
Dim objReader As New System.IO.StreamReader(PathName)
PathStr = objReader.ReadToEnd
objReader.Close()
Else
MsgBox("The config.ini file does not exist")
End If
'sets up path for database
dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0; "
dbSource = "Data Source = " & PathStr & "SWTracker.accdb;"
con.ConnectionString = dbProvider & dbSource
con.Open()
If CBSearch.Checked = True Then
sql = "SELECT TrioleNo, TrioleOpenDate, ItemDescription, Qty, Price, SWCostCentre, SWGateKeeper, UserName, EmployeeID, " & _
"OrderDate, Status, NextAction, NextActionDate, Chase, DeliveryDate, Strike, Notes FROM Tracker " & _
"WHERE TrioleNo = " + TxtTriNum.Text + ""
ElseIf CBDate.Checked = True Then
ButExport.Enabled = True
ButEmail.Enabled = True
If CBConClosed.Checked = True And CBConOpen.Checked = False Then
sql = "SELECT TrioleNo, TrioleOpenDate, ItemDescription, Qty, Price, SWCostCentre, SWGateKeeper, UserName, EmployeeID, " & _
"OrderDate, Status, NextAction, NextActionDate, Chase, DeliveryDate, Strike, Notes FROM Tracker " & _
"WHERE (((TrioleOpenDate) >= #" & Format(SchDateFrom.Value.Date, "MM/dd/yyyy") & "# ) " & _
"AND ((TrioleOpenDate) <= #" & Format(SchDateTo.Value.Date, "MM/dd/yyyy") & "#))" & _
"AND Status = ('Closed')" & _
"ORDER BY TrioleOpenDate DESC"
ElseIf CBConOpen.Checked = True And CBConClosed.Checked = False Then
sql = "SELECT TrioleNo, TrioleOpenDate, ItemDescription, Qty, Price, SWCostCentre, SWGateKeeper, UserName, EmployeeID, " & _
"OrderDate, Status, NextAction, NextActionDate, Chase, DeliveryDate, Strike, Notes FROM Tracker " & _
"WHERE (((TrioleOpenDate) >= #" & Format(SchDateFrom.Value.Date, "MM/dd/yyyy") & "# ) " & _
"AND ((TrioleOpenDate) <= #" & Format(SchDateTo.Value.Date, "MM/dd/yyyy") & "#))" & _
"AND Status = ('Open')" & _
"ORDER BY TrioleOpenDate DESC"
ElseIf CBConOpen.Checked = False And CBConClosed.Checked = False Then
MsgBox(" You need to check all or one box")
ElseIf CBConOpen.Checked = True And CBConClosed.Checked = True Then
sql = "SELECT TrioleNo, TrioleOpenDate, ItemDescription, Qty, Price, SWCostCentre, SWGateKeeper, UserName, EmployeeID, " & _
"OrderDate, Status, NextAction, NextActionDate, Chase, DeliveryDate, Strike, Notes FROM Tracker " & _
"WHERE (((TrioleOpenDate) >= #" & Format(SchDateFrom.Value.Date, "MM/dd/yyyy") & "# ) " & _
"AND ((TrioleOpenDate) <= #" & Format(SchDateTo.Value.Date, "MM/dd/yyyy") & "#))" & _
"ORDER BY TrioleOpenDate DESC"
End If
ElseIf CBAllOpen.Checked = True Then
sql = "SELECT TrioleNo, TrioleOpenDate, ItemDescription, Qty, Price, SWCostCentre, SWGateKeeper, UserName, EmployeeID, " & _
"OrderDate, Status, NextAction, NextActionDate, Chase, DeliveryDate, Strike, Notes FROM Tracker " & _
"WHERE Status = ('Open') " & _
"ORDER BY TrioleOpenDate DESC"
ElseIf CBAllOpen.Checked = False And CBDate.Checked = False And CBSearch.Checked = False Then
MsgBox("Please check a box before you attempt a search")
End If
Try
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(MyTable)
DGView1.DataSource = MyTable
con.Close()
Catch ex As System.Exception
MessageBox.Show(ex.Message)
Finally
con.Dispose()
End Try
'color code datagrid.
For i As Integer = 0 To Me.DGView1.Rows.Count - 1
If Date.Parse(DGView1.Rows(i).Cells(1).Value.ToString) < Date.Parse(Date.Now.AddDays(-3).ToString) Then
Me.DGView1.Rows(i).DefaultCellStyle.BackColor = Color.Firebrick
For c As Integer = 0 To Me.DGView1.Rows.Count - 1
If Date.Parse(DGView1.Rows(i).Cells(1).Value.ToString) >= Date.Parse(Date.Now.AddDays(-7).ToString) Then
Me.DGView1.Rows(i).DefaultCellStyle.BackColor = Color.Yellow
End If
Next
End If
Next
End Sub
Private Sub ButUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButUpdate.Click
dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0; "
dbSource = "Data Source = " & PathStr & "SWTracker.accdb;"
con.ConnectionString = dbProvider & dbSource
con.Open()
Dim cb As New OleDb.OleDbCommandBuilder(da)
Try
da.Update(ds, "Tracker")
Catch ex As System.Exception
LogW = "User " & userFullName & " Exception error - " + ex.ToString + " on " & Date.Now & vbCrLf
My.Computer.FileSystem.WriteAllText(LogPath, LogW, True)
MsgBox("Exception error caught, please check logfile", MessageBoxIcon.Error)
Exit Sub
End Try
MsgBox("Data Updated")
con.Close()
End Sub
End Class