Getting SQL to work from C# with Dates

Question

I have a simple SQL query that works in SQL:

 select txdate, acct from trans where txdate = '1/31/2017'

I cannot get it to work from C#:

  DateTime sDate = dtStart.Value;  // date time picker
  // connect to server
  SqlCommand cmd = new SqlCommand("select txdate from trans where txdate = '@sdate'", cx);
  cmd.Parameters.AddWithValue("@sdate", sDate);
  var retVal = cmd.ExecuteNonQuery();

var always returns -1 when it returns a set of records in SQLServer. I've been on this for hours. Any help would be greatly appreciated.

This is in a loop that increments days. My objective is to get the results of the first date. I've tried no quotes and it doesn't make a difference.

What are you trying to do? Get the results of the query? Use [`ExecuteReader`](https://msdn.microsoft.com/en-us/library/9kcbe65k(v=vs.110).aspx) instead. Also your parameter shouldn't have single quotes round it. — Martin Smith, Mar 05 '17 at 17:00
Look on this http://stackoverflow.com/a/21709663/5888230 I hope this can help you — kalit, Mar 05 '17 at 17:02
Also how many rows are there in trans per date? Do you really need multiple identical rows returned by `select txdate from trans where txdate =` or just the count? — Martin Smith, Mar 05 '17 at 17:08
`ExecuteNonQuery` doesnt return data, but rows affected (for updates, inserts, deletes). It *may* always return `-1` even when rows were affected. It depends on configuration (`set nocount` in particular). Don't use `AddWithValue`. To select data, use `ExecuteReader` or `ExecuteScalar` depending on your needs. Remove the quotes around `@sdate`. — pinkfloydx33, Mar 05 '17 at 17:15
I think @pinkfloydx33 called it -- Remove the quotes around `@sdate` — SqlZim, Mar 05 '17 at 17:17

Usman · Accepted Answer · 2017-03-05T17:34:15.490

ExecuteScalar() only returns the value from the first column of the first row of your query. ExecuteReader() returns an object that can iterate over the entire result set. ExecuteNonQuery() does not return data at all: only the number of rows affected by an insert, update, or delete.

in your case you are trying to get data not inset update or delete that is why ExecuteNonQuery() is returning -1 because no row is affected. so you can use ExecuteScalar() like

 DateTime sDate = dtStart.Value;  // date time picker
  // connect to server
  SqlCommand cmd = new SqlCommand("select txdate from trans where txdate = @sdate", cx);
  cmd.Parameters.AddWithValue("@sdate", sDate);
  var result = (DateTime)cmd.ExecuteScalar();

if you are returning multiple values then you should use SqldataAdapter or ExecuteReader()

When I use var retVal = (DateTime)cmd.ExecuteScalar();, I keep getting : Object reference not set to an instance of an object. — Missy, Mar 05 '17 at 20:44
I put it in a try block incrementing the date until it is the first known date and it works now. — Missy, Mar 05 '17 at 21:49

Getting SQL to work from C# with Dates

1 Answers1