5141

Query from C# code outputting differently from SQL Server query

Question:

Working with:

<ul><li>ASP.Net web-forms application </li> <li>C# code not vb.net</li> <li>SQL Server with hard coded test data</li> </ul>

<strong>Note</strong>: this issue doesn't cause any errors or cause any disruption in the code, however it outputs differently from expected.

What I am trying to do is populate a Gridview using code behind file, which can be updated by the user on button click.

Code to populate:

protected void PopulateReport() { // create connection and add commands SqlConnection con = new SqlConnection(GetConnectionString()); con.Open(); if(RP_SelectEmp.Text == "ALL") { string query1 = "SELECT RequestID, empName, RequestType, RequestDesc, RequestStartDate FROM TOR WHERE (RequestStartDate > @StartDate)" + " AND (RequestEndDate < @EndDate) AND (granted = @State)"; SqlCommand cmd = new SqlCommand(query1, con); // needed conversions DateTime startD = Convert.ToDateTime(RP_FromDateSelect.Text); DateTime endD = Convert.ToDateTime(RP_EndDateSelect.Text); Boolean state = Convert.ToBoolean("True"); // needed parameters cmd.Parameters.AddWithValue("@State", state); cmd.Parameters.AddWithValue("@StartDate", startD); cmd.Parameters.AddWithValue("@EndDate", endD); // import into gridview using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { GridView1.DataSource = reader; GridView1.DataBind(); } else { RP_ErroField.Text = "failed to bind data (reader not read) check C# code"; } } con.Close(); } }

This compiles and returns no errors but outputs:

<img alt="on Run Page output (pic)" class="b-lazy" data-src="https://i.stack.imgur.com/8PxBR.png" data-original="https://i.stack.imgur.com/8PxBR.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

The database table includes all the correct data types and column names:

<img alt="The Table's show data view" class="b-lazy" data-src="https://i.stack.imgur.com/yuOAS.png" data-original="https://i.stack.imgur.com/yuOAS.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

What I have tried:

<ol><li>

creating a static data Source and passing in the same select string from the above code (this returns the hard coded event, with the same exact input from the fields seen in the picture) - which tells me the query isn't wrong <a href="https://i.stack.imgur.com/J4PlF.png" rel="nofollow">AddingDataSource</a>,<a href="https://i.stack.imgur.com/pM1dv.png" rel="nofollow">InputingData</a>,<a href="https://i.stack.imgur.com/JDDio.png" rel="nofollow">Correct event Grabbed</a>

</li> <li>

I have tried changing the conversions in the code, DateTime.Parse and Convert.ToDateTime had the same result. Same can be said for bool and Boolean

</li> <li>

I have tried the each where clause separately and got the same no data to display result.

</li> <li>

I have debugged this if statement for 2 hrs and all the variable data is doing exactly what it should (going to the if, converting, setting the values, running the reader, and databinding)

</li> </ol>

I don't know what else to try. I would like help on an action plan to fix this; maybe I am missing something, or my approach is wrong/outdated.

Answer1:

This is really just a debugging exercise.

First, double-check that you haven't simply named the two date-picker controls backwards! That happens a lot.

Next: go to SSMS, and take your existing query:

SELECT RequestID, empName, RequestType, RequestDesc, RequestStartDate FROM TOR WHERE (RequestStartDate > @StartDate) AND (RequestEndDate < @EndDate) AND (granted = @State)

Now; we know that you've used Convert.ToDateTime to parse the dates, and that's great. You might want to check the cultures that it is parsing to what you <em>expect</em> it to parse to (is 1/2/2018 the first of Feb? or the 2nd of Jan?), and when you're 100% sure what the <em>actual date</em> of startD and endD are, prepend these to your query using an unambiguous format (just to help us debug); do the same thing with state; for example:

DECLARE @StartDate datetime = '01 Jan 2018'; DECLARE @EndDate datetime = '03 Jan 2018'; DECLARE @State bit = 1;

or are they?

DECLARE @StartDate datetime = '01 Jan 2018'; DECLARE @EndDate datetime = '01 March 2018'; DECLARE @State bit = 1;

So now we have spoofed the parameters and you have the exact same query: run it. 99% of the time, doing this will show you what is wrong with the query. I would <em>expect</em> that the query in SSMS now behaves like the query from your application does. So; now go fix it!

Recommend

  • VBA auto return dates based on date range
  • C#: How to compare two dates
  • Failed to convert parameter value from a DateTime to a Byte[]
  • Convert datatable date into dd/mm/yyyy format in asp.net
  • Change the datetime format from '2014-03-11T14:10:46+11:00' to '20140311141046+11:00&
  • can i call java method from native code?if so how?
  • match two columns and display values from 3rd column using vlookup
  • operations with different int types
  • Combining multiple functions
  • Where does the JLS specify that the result of an addition is int if its operands are of smaller type
  • Oracle Type Casting
  • Not a valid month in oracle when add_months is used
  • My database system cannot find the file specified in asp.net
  • Posting to Facebook page from Facebook application without login to facebook
  • Output from SQL Server and C# different
  • add user define properties to a domain class
  • An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.
  • Detect which app has been launched in android
  • Unable to save a query as a view table
  • Is there ever a reason to use is versus as? [duplicate]
  • Int to char conversion rule in C when int is outside the range of char
  • Getting Started with LightInject
  • ASP.NET Gridview Paging Problem
  • Why is it ambiguous to call overloaded ambig(long) and ambig(unsigned long) with an integer literal?
  • View Paypal shopping cart contents on my site
  • Timeout a query
  • VSCode change debug shell to bash on windows
  • Error processing multiple files
  • How Get arguments value using inline assembly in C without Glibc?
  • How to make R's read_csv2() recognise the text characters properly
  • Convert SQLite database to XML
  • Reading a file into a multidimensional array
  • Object and struct member access and address offset calculation
  • WPF Visiblity Binding to Boolean Expression with multiple Variables
  • Implementation of State Monad
  • Conversion from string “a” to type 'Boolean' is not valid
  • Hardware Accelerated Image Scaling in windows using C++
  • Magento Fatal error: Maximum execution error solution, on WAMP
  • How do I pass the string value parameter of the selected list item from an auto-populated dropdown l
  • Arrays break string types in Julia