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
        cmd.executenonquery

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
            .Update(Detail.ds.Tables("Detail"))
        End With

How can i get this to work?

Ta!
LVL 8
arcrossAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

af4643Commented:
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.

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
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!
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.