SQL update command in c#

Question

I have a SQL Server table with columns like this:

Mobile No  <>  OTP   <> GenTime <> AuthTime <> IsAuth
9632587410 <> 256389 <> ******  <> ******** <> False
9876543210 <> 258963 <> *****   <> ******   <> False

so on ...

using (SqlConnection conn = new SqlConnection())
{
    string inputn = Console.ReadLine();
    long mobileNo;
    long.TryParse(inputn, out mobileNo);

    string inputo = Console.ReadLine();

    int OTP;
    Int32.TryParse(inputo, out OTP);

    DateTime now = DateTime.Now;

    conn.ConnectionString = "Data Source=10.0.0.98;Initial Catalog=TeletextCMS_Dev;User ID=Testteam;Password=Cognigent33#";
    conn.Open();

    //long r = 8947052876;
    SqlCommand command = new SqlCommand("SELECT * FROM CustomerAuthOTP WHERE MobileNum=" + mobileNo, conn);
    int f = 0;

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            //int OTP = reader[1];
            int OTPGen = int.Parse(string.Format("{0}", reader[1]));
            int a = now.Hour;
            int b = now.Minute;
            int e = now.Day;

            DateTime then = DateTime.Parse(string.Format("{0}", reader[2]));

            int c = then.Hour;
            int d = then.Minute;
            int g = then.Day;

            if (e == g)
            {
                int t = (a - c) * 60 + b - d;

                if (OTP == OTPGen && e == g && t <= 15)
                {
                    Console.WriteLine("Hi");
                    f = 1;                           
                }
                else
                {
                    Console.WriteLine("No");
                }
            }

            if (e > g)
            {
                int t = (a + 24 - c) * 60 + b - d;

                if (OTP == OTPGen && e == g && t <= 15)
                {
                    Console.WriteLine("Hi");
                    f = 1;
                }
                else
                {
                    Console.WriteLine("No");
                }
            }
        }
    }

    if(f == 1)
    {
        SqlCommand cmd = new SqlCommand("UPDATE CustomerAuthOTP  SET IsAuthenticated=True, AuthenticationTime=" + now, conn);
        Console.WriteLine("Hi");
    }
}

Now at the bottom I have an Update command. I tried to execute it but it is not doing anything.

There is no error in the code. Kindly some one help me out if f== 1 then in the CustomerAuthOTP table update the IsAuthenticated value to be true and also set the authentication time to now.DateTime()

First check to make sure f is equal to 1. Next, you are not doing anything with your command. If you want to execute it use cmd.ExecuteNonQuery() — Stephen Brickner
– Stephen Brickner, Commented Dec 8, 2015 at 12:04
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — marc_s
– marc_s, Commented Dec 8, 2015 at 13:00

Vadim Martynov · Accepted Answer · 2015-12-08 12:14:15Z

3

First of all you should execute your commnd:

SqlCommand cmd = new SqlCommand("UPDATE CustomerAuthOTP  SET IsAuthenticated=True, AuthenticationTime=" + now, conn);
cmd.ExecuteNonQuery();

I recommend to use SqlCommand.Parameters

var commandText = "UPDATE CustomerAuthOTP  SET IsAuthenticated=@IsAuthenticated, AuthenticationTime=@AuthenticationTime";
SqlCommand cmd = new SqlCommand(commandText, conn);
cmd.Parameters.AddWithValue("@IsAuthenticated", true);
cmd.Parameters.AddWithValue("@AuthenticationTime", now);
cmd.ExecuteNonQuery();

It'll help SQL provider to determine parameter types and protects against SQL-injections.

edited Dec 8, 2015 at 12:14

answered Dec 8, 2015 at 12:05

Vadim Martynov

8,9705 gold badges36 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Nithin Veer Reddy Kankanti Over a year ago

I have include your commands, But every time it throws an error stating that InvalidOperationalExceptionHandle at cmd.ExecuteNonQuery(); Do i need to include any header and even cmd.ExecuteNonQuery(); is not changing its colour :P

Marc Gravell Over a year ago

InvalidOperationalExceptionHandle ?... do you mean InvalidOperationException? And what is the .Message? Is it perhaps that the connection isn't open?

Vadim Martynov Over a year ago

It can be types problem. Try change AddWithValue-strings to ` cmd.Parameters.Add("@IsAuthenticated", SqlDbType.Bit).Value = true; cmd.Parameters.Add("@AuthenticationTime", SqlDbType.DateTime).Value = now;` And write here exception message.

Nithin Veer Reddy Kankanti Over a year ago

Yes that is the message and the db connection is open , so then i am able to get OTP value from it

Vadim Martynov Over a year ago

@NithinVeerReddyKankanti Look at this article: stackoverflow.com/questions/18383076/…

Marc Gravell · Accepted Answer · 2015-12-08 12:06:58Z

DateTime now = DateTime.Now;
...
SqlCommand cmd = new SqlCommand(
    "UPDATE CustomerAuthOTP  SET IsAuthenticated=True, AuthenticationTime="
    + now, conn);

Note that this will be a string concatenation, and (depending on your locale, etc), the following is not valid TSQL:

UPDATE CustomerAuthOTP  SET IsAuthenticated=True, AuthenticationTime=08/12/2015 12:08:32

The immediate problem is formatting (both the datetime and the boolean are wrong), but it is best fixed by parameterization - you should almost never be concatenating values into TSQL:

SqlCommand cmd = new SqlCommand(
"UPDATE CustomerAuthOTP SET IsAuthenticated=1, AuthenticationTime=@now", conn);
cmd.Parameters.AddWithValue("now", now);
cmd.ExecuteNonQuery();

Or with a tool like "dapper":

conn.Execute("UPDATE CustomerAuthOTP  SET IsAuthenticated=1, AuthenticationTime=@now",
    new { now });

Collectives™ on Stack Overflow

SQL update command in c#

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related