create a payment schedule on new user added sql

[mikejs]

Hi how would I go about creating a payment schedule based on a date and a number of months 3 or 6 or 12 to then populate the database with reoccurring dates

so lets say new user added on 31/10/2011 for 6 months

I need the database to add all the dates for the six months

29/11/2011
30/12/2011
30/01/2012
28/02/2012
30/03/2012
29/04/2012

something like

dim i as Integer
counter = TextBoxNoOfMonths.text
For i = counter
 Dim cmd2 As New SqlCommand
                        Dim connPaString As String = My.Settings.strConn
                        Dim connPa As New SqlConnection(connPaString)
                        Try
                            connPa.Open()

                            cmd2 = connPa.CreateCommand

                            cmd2.CommandText = "INSERT INTO payments(t_id, firstName, lastName, propRef, rentDue, dueDate) VALUES(@t_id, @firstName, @lastName,  @propRef, @rentDue, @dueDate )"
                            ' Add Parameters to Command Parameters collection

                            cmd2.Parameters.Add(New SqlParameter("@t_id", SqlDbType.VarChar, 10))
                            cmd2.Parameters("@t_id").Value = TextBoxTenantId.Text

                            cmd2.Parameters.Add(New SqlParameter("@firstName", SqlDbType.VarChar, 20))
                            cmd2.Parameters("@firstName").Value = TextBoxFirstName.Text

                            cmd2.Parameters.Add(New SqlParameter("@lastName", SqlDbType.VarChar, 20))
                            cmd2.Parameters("@lastName").Value = TextBoxLastName.Text

                            cmd2.Parameters.Add(New SqlParameter("@rentpaid", SqlDbType.Decimal, 18, 0))
                            cmd2.Parameters("@rentpaid").Value = RadMaskedEditBoxRentPaid.Text

                           
                            cmd2.Parameters.Add(New SqlParameter("@propRef", SqlDbType.VarChar, 10))
                            cmd2.Parameters("@propRef").Value = DropDownList1.Text

                            cmd2.Parameters.Add(New SqlParameter("@dayDue", SqlDbType.Decimal, 18, 0))
                            cmd2.Parameters("@dayDue").Value = LabelDueDate.Text

                            cmd2.Parameters.Add(New SqlParameter("@rentDue", SqlDbType.Decimal, 18, 0))
                            cmd2.Parameters("@rentDue").Value = TextBoxRent.Text
                            cmd2.Parameters.Add(New SqlParameter("@rentDue", SqlDbType.Decimal, 18, 0))
                            cmd2.Parameters("@dueDate").Value = TextBoxDueDate.Text
                            cmd2.ExecuteNonQuery()

                        Catch ex As SqlException
                            My.Computer.Audio.Play(My.Resources.femaleerror, AudioPlayMode.WaitToComplete)
                            DesktopAlert1.Show()
                            DesktopAlert1.CaptionText = "Sorry !"
                            DesktopAlert1.ContentText = ("An Error Occurred!" + Environment.NewLine + ex.Message.ToString)

                        Finally
                            cmd1.Dispose()
                            cmd2.Dispose()
                            connPa.Close()
                        End Try
next

I need to be able to amend this value before it loops again to the next date

cmd2.Parameters("@dueDate").Value = TextBoxDueDate.Text

is this possible or is there an easier method of achieving this?

this is a screen showing the results manually created

http://imageshack.us/photo/my-images/855/screensa.png

[Zeth643]

Hey.

You can try this code out, btw, i tried my best to understand your problem, and came out with this response, i hope it is kind of what you were looking for.
(just adapt the first 3 lines of code and the last 2 to adapt to your database connections)

Dim cmd2 As New SqlClient.SqlCommand
        Dim con As New SqlClient.SqlConnection(My.Settings.strConn)
        con.Open()

        Dim d As Date = CDate(TextboxDueDate.Text) 'save the original date

        For i = 1 To CInt(TextBoxNoOfMonths.Text)
            cmd2.CommandText = "Insert Into Payments(t_id,firstName,lastName,propRef,rentDue,dueDate) Values(@t_id,@firstName,@lastName,@propRef,@rentDue,@dueDate)"
            With cmd2.Parameters
                .AddWithValue("@t_id", TextboxTenantId.Text)
                .AddWithValue("@firstName", TextboxFistName.Text)
                .AddWithValue("@lastName", TextboxLastName.Text)
                .AddWithValue("@propRef", DropDownList1.Text)
                .AddWithValue("@rentDue", TextboxRent.Text)
                .AddWithValue("@dueDate", d) 'insert first record with the original date
            End With
            d = d.AddDays(30) 'add 30 days from the original date so the next record has the updated value

            cmd2.Connection = con
            cmd2.ExecuteNonQuery()
        Next