Link to home
Start Free TrialLog in
Avatar of mwalsh2000
mwalsh2000

asked on

Updating a dataset

I am using the following code:

Private Sub btnProcess_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProcess.Click
        Dim cn As New SqlClient.SqlConnection()
        Dim cmd As New SqlClient.SqlCommand()
        Dim ad As New SqlClient.SqlDataAdapter()
        Dim rma As New DataSet()
        Dim strcn As String
        Dim str1 As String
        Dim row As DataRow
        Dim retdocid As String
        Dim docdate As Date
        Dim rmastat As String
        Dim thir As String
        Dim forty As String
        Dim sixty As String
        Dim sop As String
        Dim this30date As Date = Today.AddDays(-30)
        Dim this45date As Date = Today.AddDays(-45)
        Dim this60date As Date = Today.AddDays(-60)
        Dim count As String
        Dim fax As String
        Dim cntc As String
        Dim rmaup As Integer

        Me.lblstatus.Text = "Processing..."


        Try
            strcn = "Server=atl-s02;initial catalog=RMA;UID=sa;PWD=sa"
            cn.ConnectionString = strcn
            str1 = "select retdocid,rma_status,entdte,replace_sop_number,custnmbr,sopnumbe,docdate,thirty,fortyfive,sixty,faxnumbr,cntcprsn,rma_updated from rma.dbo.rma_ack where rma_status = 0"
            cmd.CommandText = str1
            ad.SelectCommand = cmd
            ad.SelectCommand.Connection = cn
            ad.Fill(rma)

            count = rma.Tables(0).Rows.Count
            writeLog(count)


            If rma.Tables(0).Rows.Count = 0 Then
                Me.lblstatus.Text = "No RMA's to process"

            Else
                For Each row In rma.Tables(0).Rows
                    Me.tboxMain.Text = "Processing..." + row.Item("retdocid")
                    retdocid = row.Item("retdocid").ToString
                    rmastat = row.Item("rma_status")
                    thir = row.Item("thirty")
                    forty = row.Item("fortyfive")
                    sixty = row.Item("sixty")
                    docdate = row.Item("docdate")
                    sop = RTrim(row.Item("sopnumbe").ToString)
                    fax = RTrim(row.Item("faxnumbr").ToString)
                    cntc = RTrim(row.Item("cntcprsn").ToString)
                    rmaup = row.Item("rma_updated")


                    If rmaup = 0 And rmastat = 0 And docdate < this30date And docdate > this45date And thir = "1/1/1900" And forty = "1/1/1900" And sixty = "1/1/1900" Then
                        thirty(retdocid, sop, fax, cntc)
                    Else
                        If rmaup = 0 And rmastat = 0 And docdate < this45date And docdate > this60date And forty = "1/1/1900" And sixty = "1/1/1900" Then
                            fortyfive(retdocid, sop, fax, cntc)
                        Else
                            If rmaup = 0 And rmastat = 0 And docdate < this60date And sixty = "1/1/1900" Then
                                oversixty(retdocid, sop, fax, cntc)
                                 
                                'XXXXXX'


                            End If
                        End If
                    End If

                Next

                rma.Clear()
                CloseACR()

                Me.tboxMain.Text = ""
                Me.lblstatus.Text = "TR"

            End If
        Catch ex As Exception
            MsgBox(ex.ToString)

        End Try

When coming out of the oversixty function I need to update the dataset (specifically the rma_updated column).  I have tried many things to no avail.  I am able to update table directly using a regular sql query connection, but not the dataset.  I also tried refilling the dataset at the beginning of the loop but that errored out.  
Avatar of vadim63
vadim63
Flag of United States of America image

Did you try:
rma.AcceptChanges()
Avatar of mwalsh2000
mwalsh2000

ASKER

what code would I use to update the column for that specific row?
The same:
rma.Tables(YourTable).Rows(YourRow).AcceptChanges()
Do I need to use a DataTable for this to work?  I don't currently have one defined
What I mean is
rma.Tables(0).Rows(RowIndex).AcceptChanges()
How can I determine the row index value?  Is there a way to reference the current row?  Sorry I am new to the dataset concept....thanks
ASKER CERTIFIED SOLUTION
Avatar of vadim63
vadim63
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
vadim63, thanks that worked.  After all of that it didn't help me solve my issue.
Try this:

row.EndEdit()
row.AcceptChanges()
Yes, I will use that, thanks.  Is there a way to update a dataset globally, meaning I when I pass through a row with a docid for example I would like to update all rows with that particular docid not just the row I am currently in, is that possible?
Is it primary key?
OK! I got it!

For Each Row As DataRow In rma.Tables(0).Select("docid=YourValue")
            If Row.RowState = DataRowState.Modified Then
                Row.EndEdit()
                Row.AcceptChanges()
            End If
Next
Thanks Vadim63!
Tnanks for the points.