Please help me to review the code n get the correct code

Question

1.00/5 (1 vote)

See more:

i want to compare current date with database date which is expired date...if current date greater than expired date in database then status column in database should changed to "Expired" using C#...i m not getting the code to implement this pls help me its a window application...

What I have tried:

string constring = "Data Source=LENOVO-PC\\SUMIT;Initial Catalog=Ngo_Mgmt;User ID=sa;Password=123";
SqlConnection con = new SqlConnection(constring);
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select Subscription_Expire,Status,currentdate from AddMember";
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
if (dr["Subscription_Expire"].ToString().Trim() == dr["currentdate"].ToString().Trim())
{

cmd.CommandText = "update AddMember set Status=@Status ";
string ans="Expired";
cmd.Parameters.AddWithValue("@Status",ans);
cmd.ExecuteNonQuery();
}

}
}

else
{
//MessageBox.Show("No Such record Exists", "Alert!");
}
cmd.Dispose();
dr.Close();
}
catch (Exception ex)
{
//MessageBox.Show("Can not open connection ! ");
}
finally
{
con.Close();
}

Posted 15-Nov-16 6:10am

Member 12852119

Updated 15-Nov-16 9:34am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2016-11-15T06:53:00

Don't do that! Never convert dates to strings and try to convert them - always leave them as DateTime objects and compare those.

C#

DateTime expireDate = (DateTime) dr["Subscription_Expire"];
if (expireDate <= DateTime.Now.Date)
   {
   // Expired.
   ...

This assumes that your DB holds dates as DATE or DATETIME columns - and if it doesn't it should as string based dates cause enormous problems very, very quickly!

Richard Deeming · Answer 2 · 2016-11-15T06:52:00

You can do that with a single query:

SQL

UPDATE
    AddMember
SET
    Status = 'Expired'
WHERE
    Subscription_Expire < GetDate()
;

You are storing your dates as one of the date types[^], and not as strings, right? If not, fix that first, before you run into the significant problems that come with dates stored as strings.

NB: Your application should never connect to the database as sa. That is an unlimited user which could be used to take over your server, and possibly your network. Create a specific user which has only the privileges your application needs, or use Windows authentication.

C#

// Using Windows authentication:
const string constring = "Data Source=LENOVO-PC\\SUMIT;Initial Catalog=Ngo_Mgmt;Trusted_Connection=True;"; 

const string query = @"UPDATE AddMember SET Status = 'Expired' WHERE Subscription_Expire < GetDate();";

using (var connection = new SqlConnection(constring))
using (var command = new SqlCommand(query, connection))
{
    connection.Open();
    command.ExecuteNonQuery();
}

Member 10425001 · Answer 3 · 2016-11-15T09:34:00

perhaps a better solution would be a computed column in the db.

create table test1
(
column1 int identity(1,1) not null primary key,
column2 datetime2 not null default(sysdatetime()),
column3 as case when column2 < sysdatetime() then 'this'
else 'that' end
)

insert into test1(column2) select '1/1/2010'
insert into test1(column2) select '1/1/2050'

select * from test1

Please help me to review the code n get the correct code

3 solutions

Solution 2

Solution 1

Solution 4

Add your solution here

Preview 0