update database and reflect changes in grid

Posted on 2006-04-13
Last Modified: 2010-04-23
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 =
            .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

        ' 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?

Question by:arcross
    LVL 2

    Accepted 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.
    LVL 8

    Author Comment

    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!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
    Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now