View sql written .net query result during debug.

Take its output in dataset

SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString
);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM (SELECT  lt.[Transaction_Date], lt.[Equipment_Number], lt.[Lube_Type], lt.[Quantity]  FROM dbo.EquipmentMaster em INNER JOIN dbo.LubeTransactions lt ON em.Equipment_Number = lt.Equipment_Number WHERE lt.[Transaction_Date] Between '12/1/09' AND '12/31/09' AND  lt.[Lube_Type] Like  '%85%'  Or lt.[Lube_Type] Like  '%Grease%' And Report_Classification = '99' ) tbl PIVOT (SUM([Quantity]) FOR [Lube_Type] IN ([Grease], [W85])) pvt ORDER BY [Equipment_Number]";
cmd.CommandType = CommandType.Text;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//do your logic part here
}
reader.Close();
conn.Close();
}

Select allOpen in new window

for further details check link below,
http://forums.asp.net/t/1366664.aspx

add break point when ever data in object and see the values of that object.

Thank you all for the quick response, but I'm totally lost.  Above my head.  This is the first VB.net program I am trying to write and so, I'm a newby.  Sorry...

umartopia:
"Take its output in dataset"
...So, I need to create a table to insert into?  Change the SELECT to INSERT to the table I create?

krunal_shah:
Is your code in VB or C#.  I'm writing VB.

sarabhai:
"add break point when ever data in object and see the values of that object."
...I have tried that, but nothing shows when I mouse over the field names.  The Dates are there because they are assigned, but nothing else.

You will have to use the query somehow like in a DataGridView or if you Add New Item to your project and choose DataSet, you can paste the query you have as the source SQL.  There is a preview there.  You would then use the DataSet in your VB.NET page.  

In your other question, I don't know why I thought you already had this, but was merely saying you were not seeing the preview while in Visual Studio which is why I made the comment that typically you will have to run the project to see the actual rows in the data grid.

Hopefully we get this sorted for you.

mwvisa1
I tried the dataset suggestion and it won't work. Get errors near tbl and , and several others.   I will try to build the data a different way.  The Pivot/Crosstab qry is just not friendly enough in .net for my mentality.  LOL!
Maybe I can get the info I need through several update queries.  I was just hoping to lesson the process with a pivot.

Thanks!

*laughing* Yes it is a more complex syntax.  As I said to you previously, you could consider creating a view with the data pivoted and then select from the view.  

Okay, I've tried doing the view in SQL SERVER database.  Using the same query that is in .net, I had to adjust some syntax to make it work in SQL View.  I still have the following errors:

Error in WHERE clause near 'tbl'.
Error in WHERE clause near ','.
Error in WHERE clause near 'ORDER'.
Unable to parse query text.

I will need to, somehow...pass variables from the .net project to this View for the Date Range.

Is this possible?

SELECT * FROM (SELECT  lt.[Transaction_Date], lt.[Equipment_Number], lt.[Lube_Type], lt.[Quantity]  
FROM EquipmentMaster em INNER JOIN LubeTransactions lt ON em.Equipment_Number = lt.Equipment_Number 
WHERE lt.[Transaction_Date] Between '12/01/09' and  '12/31/09'  AND  lt.[Lube_Type] Like  '85% ' Or lt.[Lube_Type] Like  '%Grease% ' AND em.Report_Classification = '99 ' tbl 
PIVOT (SUM([Quantity]) FOR [Lube_Type] IN ([W85], [Grease])) pvt 
ORDER BY [Equipment_Number]

Select allOpen in new window

I copied and pasted your latest code into a new view in SQL Server 2005 and the SQL Server closes up.  I've attached a screen shot of what happens.  
ServerError.jpg

I figured out how to see the query results in SQL Server 2005...finally!
I opened a "New Query" and pasted your latest code to it.  Executed and it would let me know what is wrong.  I made corrections until it ran and gave me the info I needed.
I then copied and pasted this into the .net code and it works perfectly!

Thanks for all the help!

Glad to hear it!
Best regards and happy coding,
Kevin