CODEWITHSUNDEEP
c#asp.netsql-server-2005database-connection
user2619602
user2619602

Reputation: 1

Finding time difference between two times stored in database using sql and c#

string sqlUserName3 = "SELECT out_date FROM status where s_id='" + TextBox2.Text + "'";
SqlCommand sqlcmd3 = new SqlCommand(sqlUserName3, sqlcon);


sqlUserName4 = "SELECT in_date FROM status where s_id='"+TextBox2.Text+"'";
SqlCommand sqlcmd4 = new SqlCommand(sqlUserName4, sqlcon);

string q3 = sqlcmd3.ExecuteNonQuery().ToString();
string q4 = sqlcmd4.ExecuteNonQuery().ToString();

DateTime dt1 = DateTime.Parse(q3);
DateTime dt2 = DateTime.Parse(q4);
TimeSpan result = dt1.Subtract(dt2);
string result1 = result.ToString();
TextBox8.Text = result1;


//Response.Redirect("login.aspx");
sqlcon.Close();

Upvotes: 0

Views: 297

Answers (4)

Icarus
Icarus

Reputation: 63966

Your mistake is here:

string q3 = sqlcmd3.ExecuteNonQuery().ToString();
string q4 = sqlcmd4.ExecuteNonQuery().ToString();

DateTime dt1 = DateTime.Parse(q3);
DateTime dt2 = DateTime.Parse(q4);

ExecuteNonQuery does not return a date in a string representation. It returns the number of records affected by the query; hence, when you run DateTime.Parse(number) you get an error.

None of your queries are returning a date so it's unclear how you expect to get a date back from calling the SQL you have in your question...

Update

Do not use string concatenation to build your SQL Statements. You can use parameters to avoid exposing yourself to SQL Injection attacks. One example would be:

string sqlUserName3 = "SELECT out_date FROM status where s_id=@id";
SqlCommand sqlcmd3 = new SqlCommand(sqlUserName3, sqlcon);
sqlcmd3.Parameters.AddWithValue("@id",TextBox2.Text );

Upvotes: 3

Jon Skeet
Jon Skeet

Reputation: 1501043

There are many things wrong with your code at the moment:

  • You shouldn't use string concatenation to build your query. Use parameterized SQL instead. This will avoid SQL injection attacks and conversion issues
  • You're using ExecuteNonQuery when you're trying to execute... a query.
  • You're converting the results into a string which is a bad idea even if it did return a date... instead, get the results in a form you can fetch as just a DateTime. Avoid string conversions wherever you can.

So you should:

  • Use parameters instead of dynamic SQL
  • Use ExecuteReader and get the first result from each reader
  • Use the GetDateTime method to get the DateTime from the results.

I would personally use the subtraction operator afterwards, too:

TimeSpan difference = end - start;

... but that's just a matter of preference, and not an actual mistake in your current code.

Upvotes: 4

Ehsan
Ehsan

Reputation: 32681

You should use execute scalar or pass some out parameter to get value. This should get you some value in q3 and q4.

Now to avoid erros you should also use DateTime.ParseExact instead of simple Parse.

DateTime dt1 = DateTime.ParseExact(q3,"dd/mm/yyyy HH:mm",CultureInfo.InvariantCulture);
DateTime dt2 = DateTime.ParseExact(q4,"dd/mm/yyyy HH:mm",CultureInfo.InvariantCulture);

Upvotes: 0

dmay
dmay

Reputation: 1325

You use SqlCommand.ExecuteNonQuery() but you need SqlCommand.ExecuteScalar(). The first function returns nothing, it's supposed to be used for queries like insert or update. The second returns value of first cell of first row of query output.

Upvotes: 1

Related Questions