Avatar of TanyaDH
TanyaDH
 asked on

View sql written .net query result during debug.

I need to see if the query within .net is bringing in any records.  How can I view the query result in .net?

Thanks in advance!
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]

Open in new window

.NET ProgrammingMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Kevin Cross

8/22/2022 - Mon
Umar Topia

Take its output in dataset
krunal_shah



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

Open in new window

krunal_shah

for further details check link below,
http://forums.asp.net/t/1366664.aspx
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Sara bhai

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

ASKER
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.
Kevin Cross

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
TanyaDH

ASKER
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!
Kevin Cross

*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.  
TanyaDH

ASKER
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]

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Kevin Cross

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
TanyaDH

ASKER
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
TanyaDH

ASKER
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!
Kevin Cross

Glad to hear it!
Best regards and happy coding,
Kevin
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.