Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Refreshing / Rebinding Datagridview

Posted on 2014-07-27
8
Medium Priority
?
202 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 1000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

783 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