Solved

How to create dataAdapter update command

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

810 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