• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • 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!
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.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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