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['Tracker'] 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
PublicClassFrmSearch
Dim i AsInteger
Dim PathStr AsString
Dim ToStr AsString
Dim dbProvider AsString
Dim dbSource AsString
Dim sql AsString
Dim LogW AsString
Dim userFullName AsString
Dim LogPath AsString
Dim inc AsInteger
Dim MyTable AsNewDataTable
Dim con AsNew OleDb.OleDbConnection
Dim da AsNew OleDb.OleDbDataAdapter
Dim ds AsNewDataSet
Dim dataSet AsNewDataSet
PrivateConst CP_NOCLOSE_BUTTON AsInteger = &H200
ProtectedOverloadsOverridesReadOnlyProperty CreateParams() AsCreateParams
Get
Dim myCp AsCreateParams = MyBase.CreateParams
myCp.ClassStyle = myCp.ClassStyle Or CP_NOCLOSE_BUTTON
Return myCp
EndGet
EndProperty
PrivateSub FrmSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.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
EndSub
PrivateSub 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 AsInteger
Dim y AsInteger
x = Screen.PrimaryScreen.WorkingArea.Width
y = Screen.PrimaryScreen.WorkingArea.Height - Me.Height
DoUntil x = Screen.PrimaryScreen.WorkingArea.Width - Me.Width
x = x - 1
Me.Location = NewPoint(40, 50)
Loop
'read config ini file for database path
Dim PathName AsString = "\\iuser\Shared\004\ls-fileshare-01\UKSC Scotland\33_Scottish Water Tracker\Config.ini"
If System.IO.File.Exists(PathName) = TrueThen
Dim objReader AsNew System.IO.StreamReader(PathName)
PathStr = objReader.ReadToEnd
objReader.Close()
Else
MsgBox("The config.ini file does not exist")
EndIf
'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 = TrueThen
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 = TrueThen
ButExport.Enabled = True
ButEmail.Enabled = True
If CBConClosed.Checked = TrueAnd CBConOpen.Checked = FalseThen
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 = TrueAnd CBConClosed.Checked = FalseThen
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 = FalseAnd CBConClosed.Checked = FalseThen
MsgBox(" You need to check all or one box")
ElseIf CBConOpen.Checked = TrueAnd CBConClosed.Checked = TrueThen
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"
EndIf
ElseIf CBAllOpen.Checked = TrueThen
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 = FalseAnd CBDate.Checked = FalseAnd CBSearch.Checked = FalseThen
MsgBox("Please check a box before you attempt a search")
EndIf
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()
EndTry
'color code datagrid.
For i AsInteger = 0 ToMe.DGView1.Rows.Count - 1
IfDate.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 AsInteger = 0 ToMe.DGView1.Rows.Count - 1
IfDate.Parse(DGView1.Rows(i).Cells(1).Value.ToString) >= Date.Parse(Date.Now.AddDays(-7).ToString) Then
Me.DGView1.Rows(i).DefaultCellStyle.BackColor = Color.Yellow
EndIf
Next
EndIf
Next
EndSub
PrivateSub 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 AsNew 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
EndTry
MsgBox("Data Updated")
con.Close()
EndSub
EndClass
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['Tracker'] 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
PublicClassFrmSearch
Dim i AsInteger
Dim PathStr AsString
Dim ToStr AsString
Dim dbProvider AsString
Dim dbSource AsString
Dim sql AsString
Dim LogW AsString
Dim userFullName AsString
Dim LogPath AsString
Dim inc AsInteger
Dim MyTable AsNewDataTable
Dim con AsNew OleDb.OleDbConnection
Dim da AsNew OleDb.OleDbDataAdapter
Dim ds AsNewDataSet
Dim dataSet AsNewDataSet
PrivateConst CP_NOCLOSE_BUTTON AsInteger = &H200
ProtectedOverloadsOverridesReadOnlyProperty CreateParams() AsCreateParams
Get
Dim myCp AsCreateParams = MyBase.CreateParams
myCp.ClassStyle = myCp.ClassStyle Or CP_NOCLOSE_BUTTON
Return myCp
EndGet
EndProperty
PrivateSub FrmSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.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
EndSub
PrivateSub 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 AsInteger
Dim y AsInteger
x = Screen.PrimaryScreen.WorkingArea.Width
y = Screen.PrimaryScreen.WorkingArea.Height - Me.Height
DoUntil x = Screen.PrimaryScreen.WorkingArea.Width - Me.Width
x = x - 1
Me.Location = NewPoint(40, 50)
Loop
'read config ini file for database path
Dim PathName AsString = "\\iuser\Shared\004\ls-fileshare-01\UKSC Scotland\33_Scottish Water Tracker\Config.ini"
If System.IO.File.Exists(PathName) = TrueThen
Dim objReader AsNew System.IO.StreamReader(PathName)
PathStr = objReader.ReadToEnd
objReader.Close()
Else
MsgBox("The config.ini file does not exist")
EndIf
'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 = TrueThen
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 = TrueThen
ButExport.Enabled = True
ButEmail.Enabled = True
If CBConClosed.Checked = TrueAnd CBConOpen.Checked = FalseThen
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 = TrueAnd CBConClosed.Checked = FalseThen
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 = FalseAnd CBConClosed.Checked = FalseThen
MsgBox(" You need to check all or one box")
ElseIf CBConOpen.Checked = TrueAnd CBConClosed.Checked = TrueThen
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"
EndIf
ElseIf CBAllOpen.Checked = TrueThen
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 = FalseAnd CBDate.Checked = FalseAnd CBSearch.Checked = FalseThen
MsgBox("Please check a box before you attempt a search")
EndIf
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()
EndTry
'color code datagrid.
For i AsInteger = 0 ToMe.DGView1.Rows.Count - 1
IfDate.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 AsInteger = 0 ToMe.DGView1.Rows.Count - 1
IfDate.Parse(DGView1.Rows(i).Cells(1).Value.ToString) >= Date.Parse(Date.Now.AddDays(-7).ToString) Then
Me.DGView1.Rows(i).DefaultCellStyle.BackColor = Color.Yellow
EndIf
Next
EndIf
Next
EndSub
PrivateSub 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 AsNew 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
EndTry
MsgBox("Data Updated")
con.Close()
EndSub
EndClass
