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

In your search method you're reading the query directly into a datatable, which you then use as a datasource for the datagridview.

But in the update method you're using a dataset as the source for the da.Update method.
The dataset has not been filled with a datatable named "Tracker".

I also noticed that you assign a databindingsource to the datagridview in the form load event.
But you're not using it.

Hi,

Thanks for the reply, the code in the load event was redundant and has now been removed.

Pertaining to the da.Update method, is it possible to update the datatable?

Regards.

Hi Oxiegen,

Changed my code to reflect your comments but now have a new error being trapped..

'Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.'

Many thanks

Private Sub ButSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButSearch.Click

        Dim myTable As DataTable = MydataSet.Tables("Tracker")

        ButUpdate.Enabled = True

        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")
            Exit Sub
        End If

        'Sets the size of the datagrid
        DGView1.Visible = True
        DGView1.Size = New System.Drawing.Size(1324, 446)

        Try

            da_myTable = New OleDb.OleDbDataAdapter(sql, con)

            da_myTable.Fill(MydataSet, "Tracker")

            Dim dtTracker As DataTable = MydataSet.Tables("Tracker")

            DGView1.DataSource = dtTracker

            con.Close()

        Catch ex As System.Exception

            LogW = "User " & userFullName & " Exception error in Search Form " & Date.Now & vbCrLf
            My.Computer.FileSystem.WriteAllText(LogPath, LogW, True)
            LogW = "--------------------- Exception Message Start ---------------------" & vbCrLf
            My.Computer.FileSystem.WriteAllText(LogPath, LogW, True)
            LogW = ex.Message.ToString & vbCrLf
            My.Computer.FileSystem.WriteAllText(LogPath, LogW, True)
            LogW = "---------------------- Exception Message End ----------------------" & vbCrLf
            My.Computer.FileSystem.WriteAllText(LogPath, LogW, True)

            MsgBox("Exception error caught, please check logfile", MessageBoxIcon.Error)

        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_myTable)

        Try

            da_myTable.Update(MydataSet, "Tracker")

        Catch ex As System.Exception

            LogW = "User " & userFullName & " Exception error in Search Form " & Date.Now & vbCrLf
            My.Computer.FileSystem.WriteAllText(LogPath, LogW, True)
            LogW = "--------------------- Exception Message Start ---------------------" & vbCrLf
            My.Computer.FileSystem.WriteAllText(LogPath, LogW, True)
            LogW = ex.Message.ToString & vbCrLf
            My.Computer.FileSystem.WriteAllText(LogPath, LogW, True)
            LogW = "---------------------- Exception Message End ----------------------" & 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

Got it working, error message was pertaining to the primary key not being in the select statement.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.