Loop Through Dataset Updating Data From results of another Dataset

view full story

http://www.daniweb.com – Hi guys, I will start by saying what I need my project to do! I have two Datagrids, One grid has all the customers garments on it with style number and contact length. The other grid has the users who have garment issued to them. the style number is in both grids. I need to loop through the users grid and say if the contract number is 1 from the first grid then the contract date on the second grid will be todays date + 365 days (year contract) I have looked at using a stored procedure and also a for each command but I am just getting stuck with it all. here is some of the code if it helps Public Sub ActionGarments()         Dim ds As New DataSet         Dim StartDate As DateTime = DateTime.Today         If con.State = ConnectionState.Closed Then             con.Open()         End If         Dim Disp As New SqlDataAdapter("SPActionAllGarments", con)         Dim Build As New SqlCommandBuilder(Disp)         Disp.SelectCommand.CommandType = CommandType.StoredProcedure         Disp.SelectCommand.Parameters.Add("@RentalID", SqlDbType.Int).Value = RentID         Disp.SelectCommand.ExecuteNonQuery()         Disp.Fill(ds, "Action")         Dim AdpGarm As New SqlDataAdapter("Select RentalID,DateIssued,RenewalDate From dbo.TblRentalGarments Where RentalID=@RentalID", con)         Dim AdpBuild As New SqlCommandBuilder(AdpGarm)         AdpGarm.SelectCommand.Parameters.Add("@RentalID", SqlDbType.Int).Value = RentID         AdpGarm.Fill(ds, "Garment")         Try                     Dim i As Integer             For i = 1 To ds.Tables("Action").Rows.Count - 1                 If ds.Tables("Action").Rows(i)("ContractTerm") = 1 Then                     ds.Tables("Garment").Rows(i)("RenewalDate") = StartDate.AddDays(365)                 ElseIf ds.Tables("Action").Rows(i)("ContractTerm") = 2 Then                     ds.Tables("Garment").Rows(i)("RenewalDate") = StartDate.AddDays(24)                 ElseIf ds.Tables("Action").Rows(i)("ContractTerm") = 3 Then                     ds.Tables("Garment").Rows(i)("RenewalDate") = StartDate.AddMonths(36)                 End If             Next         Catch ex As Exception             MessageBox.Show("Error" & ex.ToString())         End Try         Try             Disp.Update(ds, "Garment")         Catch ex As Exception             MessageBox.Show("Failed To Update Database" & ex.ToString())         End Try     End Sub Any help on this would be very much appreciated (General)