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

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related