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_s op_number, custnmbr,s opnumbe,do cdate,thir ty,fortyfi ve,sixty,f axnumbr,cn tcprsn,rma _updated from rma.dbo.rma_ack where rma_status = 0"
cmd.CommandText = str1
ad.SelectCommand = cmd
ad.SelectCommand.Connectio n = 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").ToStr ing
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.
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
cmd.CommandText = str1
ad.SelectCommand = cmd
ad.SelectCommand.Connectio
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").ToStr
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")
fax = RTrim(row.Item("faxnumbr")
cntc = RTrim(row.Item("cntcprsn")
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.
ASKER
what code would I use to update the column for that specific row?
The same:
rma.Tables(YourTable).Rows (YourRow). AcceptChan ges()
rma.Tables(YourTable).Rows
ASKER
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(RowInde x).AcceptC hanges()
rma.Tables(0).Rows(RowInde
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
vadim63, thanks that worked. After all of that it didn't help me solve my issue.
Try this:
row.EndEdit()
row.AcceptChanges()
row.EndEdit()
row.AcceptChanges()
ASKER
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("doci d=YourValu e")
If Row.RowState = DataRowState.Modified Then
Row.EndEdit()
Row.AcceptChanges()
End If
Next
For Each Row As DataRow In rma.Tables(0).Select("doci
If Row.RowState = DataRowState.Modified Then
Row.EndEdit()
Row.AcceptChanges()
End If
Next
ASKER
Thanks Vadim63!
Tnanks for the points.
rma.AcceptChanges()