Solved

How to create dataAdapter update command

Posted on 2011-03-08
3
371 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

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

Join & Write a Comment

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 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

14 Experts available now in Live!

Get 1:1 Help Now