-2
cmd = new SqlCommand("Select Max(Date_Time) From Daily_Sale ", con); cmd.ExecuteNonQuery();
string date_tim = (string)cmd.ExecuteScalar();
MessageBox.Show("date time" + date_tim);

This shows date time in a message box, but when I call this query:

cmdc = new SqlCommand("Select Total_Sale from Daily_Sale Where Date_Time ="+ date_tim,con);
cmdc.ExecuteNonQuery();

I get a syntax error.

Date_Time is saved as nvarchar(50).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
hamid jalil
  • 13
  • 6
  • can you try `"Select Total_Sale from Daily_Sale Where Date_Time ="+ " ' " + date_tim + " ' ",con` ? – rahulaga-msft Apr 08 '18 at 06:20
  • try like this..cmdc = new SqlCommand("Select Total_Sale from Daily_Sale Where Date_Time =' "+ date_tim+" ' ",con); – AnuPrasad Apr 08 '18 at 06:26
  • 6
    You're advising him to keep concatenating? Great help you guys are. Ever heard of a prepared statement? – Gerard H. Pille Apr 08 '18 at 06:34
  • yes it work but this qury int sale_pre = (int)cmdc.ExecuteScalar(); gives error object reference is not set on an instance of an object. while when i was run they query ("select Totale_Sale from Daily_sale ") it give value – hamid jalil Apr 08 '18 at 06:39
  • 4
    *Date_Time is saved in nvarchar(50).* Why? This can only cause you problems. Never store datetime as strings. Use DateTime2 instead. – Zohar Peled Apr 08 '18 at 06:57
  • 2
    [SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](https://xkcd.com/327/) – marc_s Apr 08 '18 at 06:57

1 Answers1

2

First, you need to use parameters to send data to SQL. Never concatenate strings of data to SQL statement. That's a security hole as it's an open door to SQL Injection attacks.
For more information, read How can prepared statements protect from SQL injection attacks? and Microsoft Docs - How to: Perform Parameterized Queries

Second, Never store dates as strings in your database. For date only values, use the Date data type. For time only values, use the Time data type. For date and time values, use the DateTime2 data type (why not use DateTime?).
For more information, read Aaron Bertrand's Bad habits to kick : choosing the wrong data type, and my answer on SO to this question.

Third, you don't need two queries to get the last value of total_sale from the database. You can do that in a single query, without any parameters at all:

SELECT TOP 1 Total_Sale
FROM Daily_Sale 
ORDER BY Date_Time DESC

If you want the date time value as well, simply add that to the query:

SELECT TOP 1 Total_Sale, Date_Time 
FROM Daily_Sale 
ORDER BY Date_Time DESC
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Thanx a lot Zohar Peled . you save my lot of time to waste .it's working now .please help and guide me further in future . – hamid jalil Apr 08 '18 at 10:28
  • [Glad to help :-)](http://meta.stackoverflow.com/questions/291325/how-to-show-appreciation-to-a-user-on-stackoverflow/291327#291327) – Zohar Peled Apr 08 '18 at 10:46