Insert multiple dates in a Single column SQL
Anybody plz help.. plz plz..
I have a table in SQL with a stored procedure. I want add a column in which i can add unlimited number of Dates. Table and procedure is as below
CREATE TABLE OPDetails
(
Id Int not null Identity(1,1) PRIMARY KEY,
OPNo Int not null,
RegDate DateTime not null,
Name varchar(50) not null,
age decimal(5,2) not null,
Gender varchar(25) not null,
OPFees Int not null,
LastVisitDate DateTime not null,
NoofVisits Int Not null,
Address varchar(100) not null,
Income decimal not null
)
Create Procedure GetOPDetailsForReport(
@FromDate DateTime,
@ToDate DateTime
)
AS
BEGIN
SET FMTONLY OFF
select
(select SUM(OPFees)
from OPDetails
where RegDate between @FromDate and @ToDate) as TotalAmount,
(select COUNT(*) from OPDetails
where RegDate between @FromDate and @ToDate
)as NewOp ,
(select COUNT(*) from OPDetails
where LastVisitDate between @FromDate and @ToDate
and NoofVisits>1 )as OldOp,
into #temp
Select * from #temp
DROP TABLE #temp
SET FMTONLY ON
END
Then I want to add the current dates to this column whenever I submit a visit again button in my .net aplication. Currently I am updating the "Last Visit Date" while submiting visit again. But I need to store all the visited days too... So pl Help.. Plz..
The Visit again code is given below
public Boolean ValueFromOPEntry(String OPNo, Int32 RegDate)
{
OPNoNew = OPNo;
RegDateNew = RegDate;
Boolean IsValid = true;
try
{
//String connectionString = SQLConnection();
using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["OPTConnectionString"].ConnectionString))
{
//connection.ConnectionString = connectionString;
connection.Open();
SqlParameter parameter1 = new SqlParameter("@OPNo", SqlDbType.Int);
//SqlParameter parameter2 = new SqlParameter("@MobNo", SqlDbType.VarChar);
SqlParameter parameter3 = new SqlParameter("@RegDate", SqlDbType.Int);
parameter1.Value = OPNo;
//parameter2.Value = MobileNo;
parameter3.Value = RegDate;
string selectQuery = "";
if (OPNo != "")
selectQuery = "Select OPNo,RegDate,Name,Age,Gender,OPFees,LastVisitDate,NoofVisits,Address,Income from dbo.OPDetails where OPNo=@OPNo and Year(RegDate)=@RegDate;";
else
{
MessageBox.Show("Please enter OP No to proceed", "Note", MessageBoxButtons.OK, MessageBoxIcon.Warning);
IsValid = false;
}
if (selectQuery != "")
{
SqlCommand command = new SqlCommand(
selectQuery, connection);
if(OPNo!="")
command.Parameters.Add(parameter1);
if(RegDate!=null)
command.Parameters.Add(parameter3);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
DataSet Datset = new DataSet();
reader.Close();
SqlDataAdapter adap = new SqlDataAdapter(command);
adap.Fill(Datset);
string exeFolder = Path.GetDirectoryName(Process.GetCurrentProcess().MainModule.FileName);
string reportPath = exeFolder + @"\OPTicketOld.rdlc";
reportViewer1.LocalReport.ReportPath = reportPath;
reportViewer1.LocalReport.ReportEmbeddedResource = @"../../OPTicketOld.rdlc";
//Microsoft.Reporting.WinForms.ReportDataSource("Datset", lt);
ReportParameter Param0 = new ReportParameter("OPNo", Convert.ToString(OPNo));
// ReportParameter Param1 = new ReportParameter("MobileNo", Convert.ToString(MobileNo));
//ReportParameter Param2 = new ReportParameter("RegDate", Convert.ToString(RegDate));
reportViewer1.LocalReport.SetParameters(new ReportParameter[] { Param0 });
//reportViewer1.LocalReport.ReportEmbeddedResource = "OPTicketOld.rdlc";
Microsoft.Reporting.WinForms.ReportDataSource datasource = new ReportDataSource("DataSet1", Datset.Tables[0]);
reportViewer1.LocalReport.DataSources.Clear();
reportViewer1.LocalReport.DataSources.Add(datasource);
//reportViewer1.LocalReport.DataBind();
reportViewer1.LocalReport.Refresh();
reportViewer1.LocalReport.ReleaseSandboxAppDomain();
}
else
{
reader.Close();
DialogResult dr = MessageBox.Show("No Data found or Night OP Data","Message",MessageBoxButtons.OK,MessageBoxIcon.Error);
if (dr == DialogResult.OK)
{
IsValid = false;
}
}
}
connection.Close();
}
}
catch (Exception ex)
{
Trace.WriteLine(ex);
}
return IsValid;
}
private void button1_Click(object sender, EventArgs e)
{
DialogResult dr = MessageBox.Show("Click OK to add visiting Details", "Message", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
if (dr == DialogResult.OK)
{
Int32 NoofVisits = 0;
//String connectionString = SQLConnection();
using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["OPTConnectionString"].ConnectionString))
{
//connection.ConnectionString = connectionString;
connection.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = connection;
SqlParameter parameter1 = new SqlParameter("@OPNo", SqlDbType.Int);
SqlParameter parameter2 = new SqlParameter("@RegDate", SqlDbType.Int);
parameter1.Value = OPNoNew;
parameter2.Value = RegDateNew;
string selectQuery = "select NoofVisits from dbo.OPDetails where OPNo=@OPNo and Year(RegDate)=@RegDate;";
SqlCommand command = new SqlCommand(
selectQuery, connection);
command.Parameters.Add(parameter1);
command.Parameters.Add(parameter2);
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
if (reader[0] != DBNull.Value)
{
NoofVisits = Convert.ToInt32(reader[0]);
}
}
}
finally
{
// Always call Close when done reading.
reader.Close();
}
cmd.CommandText = @"UPDATE OPDetails SET LastVisitDate=@LastVisitDate,NoofVisits=@NoofVisits where OPNo=@OPNo and Year(RegDate)=@RegDate; ";
//cmd.Parameters.Add(parameter1);
cmd.Parameters.AddWithValue("@OPNo", Convert.ToInt32(OPNoNew));
cmd.Parameters.AddWithValue("@RegDate", Convert.ToInt32(RegDateNew));
cmd.Parameters.AddWithValue("@LastVisitDate", DateTime.Today);
cmd.Parameters.AddWithValue("@NoofVisits", NoofVisits + 1);
cmd.ExecuteNonQuery();
connection.Close();
DialogResult dr1 = MessageBox.Show("Visiting Details Added Successfully", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
if (dr1 == DialogResult.OK)
{
this.Close();
}
}
}
else
{
return;
}
}
private void OPOldDetailsForm_FormClosing(object sender, FormClosingEventArgs e)
{
reportViewer1.LocalReport.ReleaseSandboxAppDomain();
}
}
}