[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

update database and reflect changes in grid

Hi, im trying to update a field in the database and reflect those changes in a datagrid without having to fill the dataset
Here is what is was doing before:

         Dim strSQL As String
         Dim cmd As New SqlCeCommand
         Dim da as new SqlceDataadapter

        strSQL = "UPDATE Detail SET Level = ? WHERE FK_ID = ?"

        With cmd
            .Connection = PW_DS.cn
            .CommandText = strSQL
            .CommandType = CommandType.Text
            .Parameters.Add("@Level", SqlDbType.Decimal, 8, "Level").value = 500
            .Parameters.Add("@FK_ID", SqlDbType.SmallInt, 8, "FK_ID").value = 100
         End With

        '..here check connection state and open

Until here, fine. But i was wondering how can  i update the query to modify various fields, in the database, at the same time and show those changes in the grid without filling again.

I was using the dataadapter but i ddint update anything if i removed the line cmd.executenonquery.

        With da
            .UpdateCommand = cmd
          '  .UpdateCommand.Parameters("@DatumLevel").Value = 222
            '.UpdateCommand.Parameters("@FK_ID").Value = 170
        End With

How can i get this to work?

1 Solution
You have to execute your command if you want to update your database. After the update, you also have to re-query the table to see the affected changes and bind your datagrid (to update the grid also).

Another thing you can do, is create a datatable using the adapter:
Dim da as sqldataadapter
Dim dt as new datatable
da.fill(dt) 'executes the select command

In your dataadapter, you need to specify your select and update commands. That way, you can use 1 adapter to do all of your database functionality.

Just modify your update command as needed with the paramters, and fill your datatable. The fill method will automatically open the connection, execute the query, then close the connection.

But no matter what, you always have to bind your datagrid to see any changes that took place.
arcrossAuthor Commented:
Thanks af4643, that is what iwas doing. After the update query i was checking the records returned by the query and then fill again the datatable. But i wondered if i could execute an action query and update the datatable at the same time without having to fill again. I was just curious!

Thanks again for your comments!

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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