Solved

How to create dataAdapter update command

Posted on 2011-03-08
3
376 Views
Last Modified: 2012-05-11
I have a dataset bound to a grid control.  I change information on the grid, and that seems to work fine. After I change the item on the grid I want to update the underlying sql database with the dataset changes.  I thought that if I provided the dataapdater the update sqlcommand object, that anytime I did a ds.update it would know what to do with the updated records, ie update it, add a record or delete a record.  The following is the code I am using, I come up with error message after running the DA.update line.  Can anyone show me error of my ways.  This a webform.



thanks

Private Sub gvCategories_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles gvCategories.RowUpdating
        Dim dt As DataTable = dsCategories.Tables(0)

        'Update the values.
        Dim row = gvCategories.Rows(e.RowIndex)
        dt.Rows(row.DataItemIndex)("Short_Name") = (CType((row.Cells(2).Controls(0)), TextBox)).Text 'Assuming you have a Textbox in the column
        dt.Rows(row.DataItemIndex)("Long_Name") = (CType((row.Cells(3).Controls(0)), TextBox)).Text 'Assuming you have a Textbox in the column
        dt.Rows(row.DataItemIndex)("Active") = (CType((row.Cells(4).Controls(0)), CheckBox)).Checked  'Assuming you have a checkbox in the column
        'Reset the edit index.
        gvCategories.EditIndex = -1

        'Bind data to the GridView control.
        gvCategories.DataSource = dt
        gvCategories.DataBind()
        UpdateTable()

Open in new window

Private Sub UpdateTable()
        Dim myUpdateCMD As New SqlCommand
        '' Dim strUpdate As String
        Dim i As Integer = 0

        With myUpdateCMD
            .Connection = myConn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "TAG_UpdateCategories"
            .Parameters.Add(New SqlParameter("@ID", dsCategories.Tables(0).Columns.Item(0)))
            .Parameters.Add(New SqlParameter("@Long_Name", dsCategories.Tables(0).Columns.Item(1)))
            .Parameters.Add(New SqlParameter("@Short_Name", dsCategories.Tables(0).Columns.Item(2)))
            .Parameters.Add(New SqlParameter("@Color", dsCategories.Tables(0).Columns.Item(3)))
            .Parameters.Add(New SqlParameter("@Active", dsCategories.Tables(0).Columns.Item(4)))
            .Parameters.Add(New SqlParameter("@LocationID", dsCategories.Tables(0).Columns.Item(5)))
            '    .Parameters(0).Value = dsCategories.Tables(0).Columns.Item(0)
            '    .Parameters(1).Value = dsCategories.Tables(0).Columns.Item(1)
            '    .Parameters(2).Value = dsCategories.Tables(0).Columns.Item(2)
            '    .Parameters(3).Value = dsCategories.Tables(0).Columns.Item(3)
            '    .Parameters(4).Value = dsCategories.Tables(0).Columns.Item(4)

        End With

        Dim daAdapter As New SqlDataAdapter

        Try
            myConn.Open()
            daAdapter.UpdateCommand = myUpdateCMD
            daAdapter.Update(dsCategories.Tables(0))
            myConn.Close()
        Catch ex As Exception
            Dim myError As String
            myError = ex.Message
        End Try

Open in new window

0
Comment
Question by:mgmhicks
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
gdupadhyay earned 500 total points
ID: 35072266
0
 

Author Comment

by:mgmhicks
ID: 35072845
Here is how I had to get through it.  Not really what I wanted I thought that if the adapter had the parameter fields, the dataset, and all the add, update, delete commands, it would just run whenever you do a ds.update.   However this works, and should be adequate for what I am trying to do.
If dsCategories.HasChanges Then
            For i = 0 To dsCategories.Tables(0).Rows.Count - 1
                If dsCategories.Tables(0).Rows(i).RowState = DataRowState.Added Then

                ElseIf dsCategories.Tables(0).Rows(i).RowState = DataRowState.Deleted Then
                ElseIf dsCategories.Tables(0).Rows(i).RowState = DataRowState.Modified Then
                    Dim myRow As DataRow

                    myRow = dsCategories.Tables(0).Rows(i)
                    With myCMD
                        .Connection = myConn
                        .CommandText = "TAG_UpdateCategories"
                        .CommandType = CommandType.StoredProcedure
                        .Parameters.Add(New SqlParameter("@ID", myRow.Item(0)))
                        .Parameters.Add(New SqlParameter("@Long_Name", myRow.Item(1)))
                        .Parameters.Add(New SqlParameter("@Short_Name", myRow.Item(2)))
                        .Parameters.Add(New SqlParameter("@Color", myRow.Item(3)))
                        .Parameters.Add(New SqlParameter("@Active", myRow.Item(4)))
                        .Parameters.Add(New SqlParameter("@LocationID", myRow.Item(5)))
                       
                    End With
                End If
            Next

        Else
            ' nothing to change

        End If
        Dim daAdapter As New SqlDataAdapter
        Try
            myConn.Open()
            daAdapter.UpdateCommand = myCMD

            daAdapter.Update(dsCategories.Tables(0))
            myConn.Close()
        Catch ex As Exception
            Dim myError As String
            myError = ex.Message
        End Try

Open in new window

0
 
LVL 9

Expert Comment

by:gdupadhyay
ID: 35074648
Yes, it is same as explained in first URL.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

685 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