Solved

Refreshing / Rebinding Datagridview

Posted on 2014-07-27
8
191 Views
Last Modified: 2014-08-01
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
Comment
Question by:SweetingA
  • 6
  • 2
8 Comments
 
LVL 2

Expert Comment

by:brgdotnet
ID: 40223400
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
 

Author Comment

by:SweetingA
ID: 40223735
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
 

Author Comment

by:SweetingA
ID: 40223736
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:SweetingA
ID: 40225121
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
 
LVL 2

Accepted Solution

by:
brgdotnet earned 500 total points
ID: 40225923
Hi My friend. I am going to ask another expert to step in here. I thought this was C#, and Asp.net.
0
 

Author Comment

by:SweetingA
ID: 40226029
No its vb.net, i am using vb express 2010 and SQL server 2012 express
0
 

Author Comment

by:SweetingA
ID: 40226030
Thanks anyway, all tose vb.net experts, anyone have any ideas?
0
 

Author Closing Comment

by:SweetingA
ID: 40234273
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question