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 instead. Also your parameter shouldn't have single quotes round it. — Martin Smith
– Martin Smith, Commented Mar 5, 2017 at 17:00
Look on this stackoverflow.com/a/21709663/5888230 I hope this can help you — kalit
– kalit, Commented Mar 5, 2017 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
– Martin Smith, Commented Mar 5, 2017 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
– pinkfloydx33, Commented Mar 5, 2017 at 17:15
I think @pinkfloydx33 called it -- Remove the quotes around @sdate — SqlZim
– SqlZim, Commented Mar 5, 2017 at 17:17

Usman · Accepted Answer · 2017-03-05 17:34:15Z

1

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()

edited Mar 5, 2017 at 17:34

answered Mar 5, 2017 at 17:16

Usman

4,7032 gold badges19 silver badges36 bronze badges

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

2 Comments

Missy Over a year ago

When I use var retVal = (DateTime)cmd.ExecuteScalar();, I keep getting : Object reference not set to an instance of an object.

Missy Over a year ago

I put it in a try block incrementing the date until it is the first known date and it works now.

Collectives™ on Stack Overflow

Getting SQL to work from C# with Dates

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related