• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

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.  
0
mwalsh2000
Asked:
mwalsh2000
  • 9
  • 6
1 Solution
 
vadim63Commented:
Did you try:
rma.AcceptChanges()
0
 
mwalsh2000Author Commented:
what code would I use to update the column for that specific row?
0
 
vadim63Commented:
The same:
rma.Tables(YourTable).Rows(YourRow).AcceptChanges()
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mwalsh2000Author Commented:
Do I need to use a DataTable for this to work?  I don't currently have one defined
0
 
vadim63Commented:
What I mean is
rma.Tables(0).Rows(RowIndex).AcceptChanges()
0
 
mwalsh2000Author Commented:
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
0
 
vadim63Commented:
In your case, you can use just
row.AcceptChanges()
since you wrapped it in
For Each row In rma.Tables(0).Rows
...
Next
0
 
mwalsh2000Author Commented:
vadim63, thanks that worked.  After all of that it didn't help me solve my issue.
0
 
vadim63Commented:
Try this:

row.EndEdit()
row.AcceptChanges()
0
 
mwalsh2000Author Commented:
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?
0
 
vadim63Commented:
Is it primary key?
0
 
vadim63Commented:
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
0
 
vadim63Commented:
Also check this:
http://msdn2.microsoft.com/en-us/library/y06xa2h1.aspx
Could be usefull
0
 
mwalsh2000Author Commented:
Thanks Vadim63!
0
 
vadim63Commented:
Tnanks for the points.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now