Solved

How to create dataAdapter update command

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
No row return after calling the fillschema method 4 39
DataGridView Events ? 3 37
Not showing JavaScript in the list 5 36
Advice in Xamarin 21 48
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…
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…

919 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

15 Experts available now in Live!

Get 1:1 Help Now