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
SweetingAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.