• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

Refreshing / Rebinding Datagridview

I have a datagridview which is extracting data from an SQL table.
I have added a combo box to the datagridview.
Based on the selection in the combobox, i want to update the SQL table and refresh the datagridview, each time it should be deleting a line from the datgridview after a combobox selection is made.
It does all the right things in SQL but when it refreshes on screen i lose a column and nothing works thereafter, see attached screen shot.Presentation1.jpg
Thanks for teh help
0
SweetingA
Asked:
SweetingA
  • 6
  • 2
1 Solution
 
brgdotnetcontractorCommented:
Can you show some of the aspx code of the GridView control? The fastest way to help you, is to look at your code.

Also in C# do you call a stored procedure? If so, make sure it is returning data for all of the columns you are displaying in the GridView control.
0
 
SweetingAAuthor Commented:
Sorry, can't believe i forgot to add the code....

Private Sub SelectedComboBox_SelectionChangeCommitted(ByVal sender As Object, ByVal e As EventArgs)

        'Set downtime code in target column equal to column being updated
        Dim SelectedCombobox As ComboBox = DirectCast(sender, ComboBox)
        Try
            If SelectedCombobox.SelectedItem IsNot Nothing Then
                DGVDTRecords(4, DGVDTRecords.CurrentCell.RowIndex).Value = SelectedCombobox.SelectedItem
                SendKeys.Send("{ENTER}")
            End If

            'Save downtime code to SQL database and delete updated row from temptbl_stops
            Dim ID As Integer
            Dim rc As Integer
            Dim row As Integer
            Dim col As Integer
            Dim dc As String
            row = DGVDTRecords.CurrentCell.RowIndex
            col = 4
            ID = DGVDTRecords.Rows(row).Cells(0).Value
            rc = DGVDTRecords.Rows.Count
            dc = DGVDTRecords.Rows(row).Cells(col).Value
            MsgBox("Are you sure you want to save the downtime classifcation?", vbExclamation + vbYesNo, "Message Alert")
            Dim Result As DialogResult
            Result = MsgBox("Are you sure you want to save the downtime classifcation?", vbExclamation + vbYesNo, "Message Alert")
            If Result = System.Windows.Forms.DialogResult.Yes Then
                SQL.RunQuery("UPDATE temptbl_Stops SET DowntimeCode = '" & dc & "' WHERE ID = " & ID & " ") 'update temptbl_Stops
                SQL.RunQuery("stp_UpdateMachineData") 'update tbl_MachineData
                SQL.SQLCmd.CommandType = CommandType.StoredProcedure
                'SQL.RunQuery("DELETE FROM temptbl_Stops WHERE ID = " & ID & " ") 'delete record from temptbl_Stops
                CheckTableExists()
                LoadDowntimeRecords()
                'FormatDGVDTRecords()
            Else
                MsgBox("Information not updated, please make another selection", vbExclamation + vbOKOnly, "Message")
                If SelectedCombobox.SelectedItem = True Then
                    SelectedCombobox.Items.Clear()
                End If
            End If
            SQL.RunQuery("SELECT * FROM temptbl_Stops")
            If SQL.recordcount = 0 Then
                MsgBox("All downtime has now been classified", vbExclamation + vbOKOnly, "Message")
                MainScreen.Show()
                Me.Close()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

Public Sub LoadDowntimeRecords()
        'Load downtime records
        SQL.RunQuery("SELECT * FROM temptbl_Stops")
        'MsgBox(SQL.recordcount)
        SQL.SQLDA.Fill(SQL.SQLDataSet, "temptbl_Stops")
        DGVDTRecords.DataSource = SQL.SQLDataSet.Tables("temptbl_Stops")
        Dim cbo As New DataGridViewComboBoxColumn()
        DGVDTRecords.Columns.Add(cbo)
        cbo.HeaderText = "DC"
        SQL.RunQuery("SELECT DowntimeCode FROM tbl_DowntimeCodes")
        If SQL.SQLCon.State = ConnectionState.Closed Then SQL.SQLCon.Open()
        Dim SQLDR As SqlDataReader = SQL.SQLCmd.ExecuteReader()
        While SQLDR.Read = True
            cbo.Items.Add(SQLDR.Item("DowntimeCode"))
        End While
        SQL.SQLCon.Close()
    End Sub

    Public Sub CheckTableExists()
        'Check if temp data table exists
        SQL.RunQuery("SELECT * FROM INFORMATION_SCHEMA.tables WHERE TABLE_NAME = 'temptbl_Stops' ")
        If SQL.recordcount > 0 Then
            SQL.RunQuery("DELETE FROM temptbl_Stops WHERE [ID] > 0")
        Else
            SQL.RunQuery("CREATE TABLE temptbl_Stops (ID INT IDENTITY NOT NULL PRIMARY KEY, StartDate DATETIME, StopDate DATETIME, StopTime DECIMAL(4,2), DowntimeCode NCHAR(10))")
        End If
        SQL.RunQuery("stp_UpdateTempStops")
        SQL.SQLCmd.CommandType = CommandType.StoredProcedure
    End Sub

I have also added the stored procedures......

-----------------

ALTER PROCEDURE [dbo].[stp_UpdateMachineData]
      -- Add the parameters for the stored procedure here
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

UPDATE d
SET DowntimeCode = s.DowntimeCode
FROM dbo.tbl_MachineData d
INNER JOIN dbo.temptbl_Stops s
ON d.StartDate = s.StartDate

END
------------------
ALTER PROCEDURE [dbo].[stp_UpdateTempStops]
      -- Add the parameters for the stored procedure here
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
      SET NOCOUNT ON;

INSERT INTO temptbl_Stops (StartDate, StopDate, StopTime, DownTimeCode)
SELECT     TOP (100) PERCENT StartDate, StopDate, Avg(CAST(DATEDIFF(s, StartDate, StopDate) AS Numeric(4, 0)) / 60) AS StopTime, DowntimeCode
FROM         dbo.tbl_MachineData
GROUP BY StartDate, StopDate, DowntimeCode
HAVING       Avg(CAST(DATEDIFF(s, StartDate, StopDate) AS Numeric(4, 0)) / 60) IS NOT NULL and DowntimeCode IS null
ORDER BY StartDate

END
UPDATE d
SET DowntimeCode = s.DowntimeCode
FROM dbo.tbl_MachineData d
INNER JOIN dbo.temptbl_Stops s
ON d.StartDate = s.StartDate

END

Thanks a lot for the help
0
 
SweetingAAuthor Commented:
Oh and first time round i am simpling doing the same thing on form load

 Private Sub DowntimeRecords_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'GetDowntimeCodes()
        'FormatDGVDTCodes()
        CheckTableExists()
        LoadDowntimeRecords()
        'FormatDGVDTRecords()
    End Sub
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
SweetingAAuthor Commented:
The first part of the problem is sorted, i was re-formatting and keep adding another column each time.

The second part of the problem is not sorted.

Program runs first time exactly as planned.
   - Make cbo selection
   - Update both tables
   - Rebind to update datagridview deleteing the row which was updated

However when i stay in that datagridview and make a second cbo selection no code is triggered, not even the Combobox.SelectedItem - just nothing happens at all.

Thanks for the help - current code below.............

Private Sub SelectedComboBox_SelectionChangeCommitted(ByVal sender As Object, ByVal e As EventArgs)

        'Set downtime code in target column equal to column being updated
        Dim SelectedCombobox As ComboBox = DirectCast(sender, ComboBox)
        Try
            If SelectedCombobox.SelectedItem IsNot Nothing Then
                DGVDTRecords(4, DGVDTRecords.CurrentCell.RowIndex).Value = SelectedCombobox.SelectedItem
                SendKeys.Send("{ENTER}")
            End If

            'Save downtime code to SQL database and delete updated row from temptbl_stops
            Dim ID As Integer
            Dim rc As Integer
            Dim row As Integer
            Dim col As Integer
            Dim dc As String
            row = DGVDTRecords.CurrentCell.RowIndex
            col = 4
            ID = DGVDTRecords.Rows(row).Cells(0).Value
            rc = DGVDTRecords.Rows.Count
            dc = DGVDTRecords.Rows(row).Cells(col).Value
            MsgBox("Are you sure you want to save the downtime classifcation?", vbExclamation + vbYesNo, "Message Alert")
            Dim Result As DialogResult
            Result = MsgBox("Are you sure you want to save the downtime classifcation?", vbExclamation + vbYesNo, "Message Alert")
            If Result = System.Windows.Forms.DialogResult.Yes Then
                SQL.RunQuery("UPDATE temptbl_Stops SET DowntimeCode = '" & dc & "' WHERE ID = " & ID & " ") 'update temptbl_Stops
                SQL.RunQuery("stp_UpdateMachineData") 'update tbl_MachineData
                SQL.SQLCmd.CommandType = CommandType.StoredProcedure
                CheckTableExists()
                RefreshDowntimeRecords()
                Exit Sub
            Else
                MsgBox("Information not updated, please make another selection", vbExclamation + vbOKOnly, "Message")
                If SelectedCombobox.SelectedItem = True Then
                    SelectedCombobox.Items.Clear()
                End If
            End If
            SQL.RunQuery("SELECT * FROM temptbl_Stops")
            If SQL.recordcount = 0 Then
                MsgBox("All downtime has now been classified", vbExclamation + vbOKOnly, "Message")
                MainScreen.Show()
                Me.Close()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

Public Sub LoadDowntimeRecords()
        'Load downtime records
        SQL.RunQuery("SELECT * FROM temptbl_Stops")
        'MsgBox(SQL.recordcount)
        SQL.SQLDA.Fill(SQL.SQLDataSet, "temptbl_Stops")
        DGVDTRecords.DataSource = SQL.SQLDataSet.Tables("temptbl_Stops")
        Dim cbo As New DataGridViewComboBoxColumn()
        DGVDTRecords.Columns.Add(cbo)
        cbo.HeaderText = "DC"
        SQL.RunQuery("SELECT DowntimeCode FROM tbl_DowntimeCodes")
        If SQL.SQLCon.State = ConnectionState.Closed Then SQL.SQLCon.Open()
        Dim SQLDR As SqlDataReader = SQL.SQLCmd.ExecuteReader()
        While SQLDR.Read = True
            cbo.Items.Add(SQLDR.Item("DowntimeCode"))
        End While
        SQL.SQLCon.Close()
    End Sub

    Public Sub RefreshDowntimeRecords()
        'Load downtime records
        SQL.RunQuery("SELECT * FROM temptbl_Stops")
        'MsgBox(SQL.recordcount)
        SQL.SQLDA.Fill(SQL.SQLDataSet, "temptbl_Stops")
        DGVDTRecords.DataSource = SQL.SQLDataSet.Tables("temptbl_Stops")
    End Sub


    Public Sub CheckTableExists()
        'Check if temp data table exists
        SQL.RunQuery("SELECT * FROM INFORMATION_SCHEMA.tables WHERE TABLE_NAME = 'temptbl_Stops' ")
        If SQL.recordcount > 0 Then
            SQL.RunQuery("DELETE FROM temptbl_Stops WHERE [ID] > 0")
        Else
            SQL.RunQuery("CREATE TABLE temptbl_Stops (ID INT IDENTITY NOT NULL PRIMARY KEY, StartDate DATETIME, StopDate DATETIME, StopTime DECIMAL(4,2), DowntimeCode NCHAR(10))")
        End If
        SQL.RunQuery("stp_UpdateTempStops")
        SQL.SQLCmd.CommandType = CommandType.StoredProcedure
    End Sub

    Private Sub DowntimeRecords_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        GetDowntimeCodes()
        FormatDGVDTCodes()
        CheckTableExists()
        LoadDowntimeRecords()
        FormatDGVDTRecords()
    End Sub
0
 
brgdotnetcontractorCommented:
Hi My friend. I am going to ask another expert to step in here. I thought this was C#, and Asp.net.
0
 
SweetingAAuthor Commented:
No its vb.net, i am using vb express 2010 and SQL server 2012 express
0
 
SweetingAAuthor Commented:
Thanks anyway, all tose vb.net experts, anyone have any ideas?
0
 
SweetingAAuthor Commented:
The column index changed everytime i recreated the combo box so i have to reference names instead of indexes - it was as simple as that.  Fernando Soto solved the puzzle.

To get help from another expert i had to reword the question, so indirectly you helped me solve the problem.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now