CODEWITHSUNDEEP
c#sql-server
Missy
Missy

Reputation: 1368

Getting SQL to work from C# with Dates

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.

Upvotes: 1

Views: 80

Answers (1)

Usman
Usman

Reputation: 4693

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

Upvotes: 1

Related Questions