如何使用开始日期和结束日期自动绑定月数列 [英] how to bind Number of months columns automatically using start date and end date

问题描述

我有3个控件



个月数

开始日期

结束日期



如果我给出月数5和开始日期2015年1月7日，那么根据（月数）自动计算结束日期到2015年1月12日'


将这些值插入数据库时​​我想将5个月的数量绑定到5列，即



jul2015 Aug2015 Sep2015 2015年11月2015年11月2015年12月开始日期结束日期

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

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

推荐答案

由于您将参数传递给SP SP_PaymentTracker ，这自然意味着需要在SP中生成所有必需的列。您可以使用以下逻辑生成列并根据您的要求定制它。

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.

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

这篇关于如何使用开始日期和结束日期自动绑定月数列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！