Click here to Skip to main content
15,897,518 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
i have 3 controls

Number of Months
Start date
End Date

if i give number of months 5 and start date 1/7/2015 then am calculating end date automatically to 1/12/2015 based on (number of months)'

while inserting these values into database i want to bind number of months 5 into 5 columns i.e

jul2015 Aug2015 Sep2015 Oct2015 Nov2015 Dec2015 start date end date


C#
protected void btnSubmit_Click(object sender, EventArgs e)
 {
     SqlCommand cmd = new SqlCommand();
     SqlDataAdapter da = new SqlDataAdapter(cmd);
     cmd.CommandType = CommandType.StoredProcedure;
     cmd.CommandText = "SP_PaymentTracker";
     cmd.Parameters.AddWithValue("@Name", txtName.Text);
     cmd.Parameters.AddWithValue("@InvestmentType", ddlInvestmentType.SelectedItem.ToString());
     cmd.Parameters.AddWithValue("@Amount", ddlAmount.SelectedItem.ToString());
     cmd.Parameters.AddWithValue("@MonthlyPay", txtMonthlyPay.Text);
     cmd.Parameters.AddWithValue("@NumberOfMonths", txtNoOfMonths.Text);
     cmd.Parameters.AddWithValue("@StartDate", txtStartDate.Text);
     cmd.Parameters.AddWithValue("@EndDate", txtEndDate.Text);
     cmd.Parameters.AddWithValue("@Phone", txtMobile.Text);
     cmd.Parameters.AddWithValue("@Address", txtAddress.Text);
     cmd.Connection = con;
     lblmsg.Visible = true;
     lblmsg.Text = "Inserted";
     try
     {
         con.Open();

         cmd.ExecuteNonQuery();
         BindDummyRow();
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         con.Close();
         con.Dispose();
         txtName.Text = ddlInvestmentType.Text = ddlAmount.Text = txtMonthlyPay.Text =
         txtNoOfMonths.Text = txtStartDate.Text = txtEndDate.Text = txtMobile.Text = txtAddress.Text = string.Empty;
     }
 }

 protected void txtStartDate_TextChanged(object sender, EventArgs e)
 {
     string inputString = txtStartDate.Text;
     DateTime dt = DateTime.ParseExact(inputString, "MM/dd/yyyy", CultureInfo.InvariantCulture);
     dt = dt.AddMonths(Convert.ToInt32(txtNoOfMonths.Text));
     txtEndDate.Text = dt.ToString("MM/dd/yyyy");
     txtMobile.Focus();
 }

how to generate number of months columns automatically based on start and end date
Posted
Updated 15-Jul-15 21:34pm
v2

1 solution

Since you are passing the parameters to SP SP_PaymentTracker that naturally means all the required columns need to be generated in the SP. You can use below logic to generate the columns and tailor it as per your requirement.

SQL
SELECT A.*, cast(DATENAME(M, DATEADD(month, A.N, @StartDate)) as varchar(3)) + CAST(YEAR(@StartDate) as varchar(4))
FROM 
(
SELECT TOP (5) n = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects ORDER BY n
) A
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900