Loop Through Dataset Updating Data From results of another Dataset

[scooby36]

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

[kvprajapati]

Remove line #12.