Datagridview Not Refreshing

I have a SQL table that looks similar to this: 1 | a | stuff... 2 | a | stuff... 3 | b | stuff... 4 | a | stuff... 5 | b | stuff... I only want to show: 3 | b | stuff... 5 | b | st

Solution 1:

When you invoke SqlDataAdapter.Update() the adapter updates the values in the database by executing the respective INSERT, UPDATE, or DELETE(from MSDN). The SELECT command is not executed. So you need to do it like this:

Insert/Update/Delete:

daInstTbl.Update(dsNewInst)

Select:

daInstTbl.Fill(dsNewInst)

Commit:

dsNewInst.AcceptChanges()

Example

Private connection As SqlConnection
Private adapter As SqlDataAdapter
Private data As DataSet
Private builder As SqlCommandBuilder
Private grid As DataGridView

PrivateSub InitData()
    Me.SqlSelect(firstLoad:=True, fillLoadOption:=LoadOption.OverwriteChanges, acceptChanges:=True)
    Me.grid.DataSource = Me.data
    Me.grid.DataMember = "Table"EndSubPublicSub SaveData()
    Me.SqlInsertUpdateAndDelete()
    Me.SqlSelect(fillLoadOption:=LoadOption.OverwriteChanges, acceptChanges:=True)
EndSubPublicSub RefreshData(preserveChanges AsBoolean)
    Me.SqlSelect(fillLoadOption:=If(preserveChanges, LoadOption.PreserveChanges, LoadOption.OverwriteChanges))
EndSubPrivateSub SqlSelect(Optional firstLoad AsBoolean = False, OptionalByVal fillLoadOption As LoadOption = LoadOption.PreserveChanges, Optional acceptChanges AsBoolean = False)

    If (firstLoad) ThenMe.data = New DataSet()
        Me.connection = New SqlConnection("con_str")
        Me.adapter = New SqlDataAdapter("SELECT * FROM Table WHERE Column = 'b'", connection)
        Me.builder = New SqlCommandBuilder(Me.adapter)
    EndIfMe.connection.Open()

    If (firstLoad) ThenMe.adapter.FillSchema(Me.data, SchemaType.Source, "Table")
    EndIfMe.adapter.FillLoadOption = fillLoadOption
    Me.adapter.Fill(Me.data, "Table")

    If (acceptChanges) ThenMe.data.Tables("Table").AcceptChanges()
    EndIfMe.connection.Close()

EndSubPrivateSub SqlInsertUpdateAndDelete()

    If (Me.connection.State <> ConnectionState.Open) ThenMe.connection.Open()
    EndIfMe.adapter.Update(Me.data, "Table")

    Me.connection.Close()

EndSub

PS: (Untested code)

Solution 2:

My solution was to change the way I was declaring variables. Before I had:

Dim daInstTbl As _
    New SqlDataAdapter("SELECT * FROM Table WHERE Column = 'b'", objConn)

The affect was that recalling the subroutine ignored this line because the variable daInstTbl was already declared previously. The solution was:

' Delete old instance of a Data____ classes
    da = Nothing
    ds = Nothing
    dt = NothingIf GetAll = FalseThen
        da = New SqlDataAdapter(sSelCmd, objConn)
    Else
        da = New SqlDataAdapter(sSelAllCmd, objConn)
    EndIf' Create an instance of a DataSet, and retrieve data from the Authors table.
    ds = New DataSet
    da.FillSchema(ds, SchemaType.Source)
    da.Fill(ds)

This cleared the information and allowed me to assign a new value. My subroutines serve double duty by assigning two query strings and then using an optional boolean to determine which version to use.

Dim sSelCmd AsString = "SELECT * FROM Table WHERE Coloumn= 'b'"Dim sSelAllCmd AsString = "SELECT * FROM Table"PrivateSub GetData(OptionalByVal GetAll AsBoolean = False)
    Dim objConn AsNew SqlConnection(sConnectionString)
    objConn.Open()

And that leads into the code above! Thanks for the help. Some of your concepts got my thoughts turning in the right direction and motivated me to clean up my code into a much more readable form.