0

I have an application in which I had to change the selection of a day be a range of days instead. The query when ran in Management Studio works and returns the correct results. However in the application nothing is returned.

Code:

SqlCommand com = new SqlCommand();
com.Connection = Program.ConnnectDatabase();
com.CommandText = Resources.FillProcessing;

string strDay = string.Empty;

if (cboProcessDay.Text.ToString().Trim() == "Monday")
    strDay = "'Monday'";

com.Parameters.Add(new SqlParameter("@Day", strDay));

DataTable dt = new DataTable();

SqlDataAdapter adt = new SqlDataAdapter();
adt.SelectCommand = com;

adt.Fill(dt);

SQL:

SELECT data.* 
FROM  Rec_data data, acc_info info 
WHERE Day IN (@Day) 
  AND info.foracid = data.foracid

Any idea what i am doing wrong?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shon
  • 486
  • 4
  • 9
  • 4
    try `strDay = "Monday"` without the quotes – wero May 25 '16 at 13:19
  • 4
    [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged – marc_s May 25 '16 at 13:22
  • you are facing `Day` keyword in your query. Which might cause error. Try enclosing Day in Single Opening Quote. – Ishtiaq May 25 '16 at 13:22
  • 3
    If the query returns nothing, it's because there is nothing to return. You are asking for a date name that is surrounded by single quotes, ie `'Monday'`. I suspect there are no such rows in the database. You probably wanted to search for just `Monday` and accidentally escaped the value using T-SQL syntax, even though it is already a C# string – Panagiotis Kanavos May 25 '16 at 13:24
  • 2
    Also, you can't pass in a comma delimited string as a parameter to an `IN` clause if that's where you're going. There are various ways to do that; see [here](http://stackoverflow.com/questions/9384446) and [here](http://stackoverflow.com/questions/337704) and [here](http://stackoverflow.com/questions/10374082) – D Stanley May 25 '16 at 13:33
  • What I am trying to do is set ranges such that the user can select monday to Friday and the application will pass all of the information in the @Day variable. However I will try what was suggested – Shon May 25 '16 at 13:37
  • @D Stanley your comment worked. Instead of using one variables and attempting to pass all of the information through it i split it into multiple variables and used a series of Day=variable where then all i had to do was adjust the logic to pass the correct information to the correct variables. – Shon May 25 '16 at 13:57

1 Answers1

0

I suggest to use SQL profiler to trace what statement is really run from c#.

Myo Myint Aung
  • 147
  • 1
  • 3