March 11, 2015 at 11:49 am
The thing is, I don't want to add text to my web page. I want a popup box to appear that I can click off and let the code continue to run.
And I'm not sure breakpoints are going to help me either. Because they won't tell me what my values are.
March 11, 2015 at 11:55 am
GAH.
"A breakpoint could not be inserted at this location."
And when I do find a place where I can insert a breakpoint, there is NOTHING in the immediate window. It's empty.
Useless. Absolutely freakin' useless. How the heck am I supposed to debug this if I can't even figure out what values are coming back from my proc and what the variables are reading as?
Grrr. I wish I could be coding this in VB.
March 11, 2015 at 12:02 pm
Brandie Tarvin (3/11/2015)
GAH."A breakpoint could not be inserted at this location."
And when I do find a place where I can insert a breakpoint, there is NOTHING in the immediate window. It's empty.
Useless. Absolutely freakin' useless. How the heck am I supposed to debug this if I can't even figure out what values are coming back from my proc and what the variables are reading as?
Grrr. I wish I could be coding this in VB.
When you say VB do you mean VB.NET because C# is basically the same thing.
You can put a breakpoint on ANY line of executable code. You can't put break points on empty lines or spaces between methods that sort of thing. It must be on a line of code that will do something or make a decision.
The immediate window isn't going to display anything. It lets you run code and will show the results. If you have a bunch of variables and such you want to evaluate a break point is EXACTLY the thing you want. You can mouse over any object at runtime and it will show you the values. The debugger in .NET is actually extremely powerful you just haven't yet figured out how to harness it for what you need.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 11, 2015 at 12:06 pm
Brandie Tarvin (3/11/2015)
The thing is, I don't want to add text to my web page. I want a popup box to appear that I can click off and let the code continue to run.
Yes exactly my point of using Response.Write. It isn't text that would always be there, just during development. That was the only way we could debug web pages in classic asp. It sucked bad and doing the same thing today in .NET is not any better.
And I'm not sure breakpoints are going to help me either. Because they won't tell me what my values are.
I think you might be confusing a breakpoint with a watch. A breakpoint does not evaluate anything, it just stops execution at that point so you can evaluate whatever you need to. A watch is something you setup on a given variable or object property, the value of the watch will adjust with the value of the variable during execution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 11, 2015 at 12:14 pm
Sean Lange (3/11/2015)
Brandie Tarvin (3/11/2015)
GAH."A breakpoint could not be inserted at this location."
And when I do find a place where I can insert a breakpoint, there is NOTHING in the immediate window. It's empty.
Useless. Absolutely freakin' useless. How the heck am I supposed to debug this if I can't even figure out what values are coming back from my proc and what the variables are reading as?
Grrr. I wish I could be coding this in VB.
When you say VB do you mean VB.NET because C# is basically the same thing.
You can put a breakpoint on ANY line of executable code. You can't put break points on empty lines or spaces between methods that sort of thing. It must be on a line of code that will do something or make a decision.
The immediate window isn't going to display anything. It lets you run code and will show the results. If you have a bunch of variables and such you want to evaluate a break point is EXACTLY the thing you want. You can mouse over any object at runtime and it will show you the values. The debugger in .NET is actually extremely powerful you just haven't yet figured out how to harness it for what you need.
Obviously I'm missing a lot. I got thrown in the deep end here because the Dev insisted on recoding the original project in C#.Net instead of VB.Net when he added stuff. So when it came time for me to add to the project, I'm now forced to learn a specific subset of "how to code in C#" instead of improving my VB.Net skills and building on what I know.
So I did the mouseover thing. Didn't help. All I got was stuff like "(local variable) int returnjobstat" and "(local variable) string imagename". I don't know how that helps me since I already know that information.
How do I run code in the immediate window? I must be doing the mouse over wrong. What obvious thing did I miss because I didn't actually have a chance to learn C# from the ground up?
March 11, 2015 at 12:19 pm
I suddenly have this horrible feeling that the project isn't even making it to my void method.
<headdesk>
I had the dev sitting over my shoulder showing me stuff this morning, and he forgot to tell me how to get the stuff into the page_load. I'm betting that's my issue.
March 11, 2015 at 12:25 pm
Brandie Tarvin (3/11/2015)
I suddenly have this horrible feeling that the project isn't even making it to my void method.<headdesk>
I had the dev sitting over my shoulder showing me stuff this morning, and he forgot to tell me how to get the stuff into the page_load. I'm betting that's my issue.
There should be a page_load method in your code behind. It gets created by default but isn't required for the page to run. Do you have a page_load method? Assuming you have a page_load method you can either add more code to the appropriate place in that method or call another method.
If you can share some code and what you want to have happen I will be happy to help.
I feel awful here Brandie. I work with this stuff all day everyday and can help you in about 2 seconds. It is just so difficult to help point you in the right direction over the forum.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 11, 2015 at 12:46 pm
Sean Lange (3/11/2015)
Brandie Tarvin (3/11/2015)
I suddenly have this horrible feeling that the project isn't even making it to my void method.<headdesk>
I had the dev sitting over my shoulder showing me stuff this morning, and he forgot to tell me how to get the stuff into the page_load. I'm betting that's my issue.
There should be a page_load method in your code behind. It gets created by default but isn't required for the page to run. Do you have a page_load method? Assuming you have a page_load method you can either add more code to the appropriate place in that method or call another method.
If you can share some code and what you want to have happen I will be happy to help.
I feel awful here Brandie. I work with this stuff all day everyday and can help you in about 2 seconds. It is just so difficult to help point you in the right direction over the forum.
Here's the entire bit of code:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.VisualBasic;
using System.Collections;
using System.Diagnostics;
using System.IO;
namespace MyDashboard
{
public partial class MonthEndMonitor : System.Web.UI.Page
{
public enum ReturnStatus {
FAILED = 0, SUCCESS = 1, RUNNING = 4, NOTSTARTED = 5
}
public string[] JobNames = new string[] {
"Job1","Job2"
};
protected void Page_Load(object sender, EventArgs e)
{
System.Web.UI.HtmlControls.HtmlGenericControl menuitem = (System.Web.UI.HtmlControls.HtmlGenericControl)Master.FindControl("MonthEndLi");
menuitem.Attributes.Add("class", "active");
}
void findstatus ()
{
int returnjobstat = 5;
foreach (string name in JobNames)
{
SqlConnection sqlC = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "EXECUTE dbo.spJobStatusCheck '" + name + "';";
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlC;
sqlC.Open();
returnjobstat = (int)cmd.ExecuteScalar();
string imagename = string.Empty;
if (returnjobstat == 0 )
imagename = "images/small_red.jpg";
else if (returnjobstat == 1 )
imagename = "images/small_green.jpg";
else if (returnjobstat == 4)
imagename = "images/small_yellow.jpg";
else if (returnjobstat == 5)
imagename = "images/small_gray.jpg";
sqlC.Close();
switch (name) {
case "Job1": this.DTDQC.Src = Page.ResolveClientUrl(imagename);
break;
case "Job2": this.XMEDPTX.Src = Page.ResolveClientUrl(imagename);
break;
}
}
}
}
}
And your help is greatly appreciated, Sean. I know you've got your own job and here I am acting like a whiny little brat (for which I apologize). I was just so ready to throw this monitor out the window.
This project actually comes up with a webpage with multiple "tabs" at the top. My page is not the first one that loads. I have to click on it. Essentially I have a bunch of little circle images (red, yellow, green, and gray) that are going to change depending on the status of my job. You can see the attached picture for a sample of what I want it to look like.
I did the page design with all green circles. I know my code isn't working because job 1 and job 2 should be showing gray. They are not. I'm trying to code just a small number of the jobs right now, to work all the bugs out, before I add the other jobs to the array.
Oh, and the proc that I'm calling is below. I KNOW without a doubt the proc works. I tested it plenty before trying to code it into the application.
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[med].[spJobStatusCheck]')
AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.spJobStatusCheck;
GO
CREATE PROCEDURE dbo.spJobStatusCheck
(@JobName VARCHAR(200))
AS
/* --------------------------------------------------------------------------------
Install DB: MyDB
Description: Pulls the job status for any month end job into the
Month End Process Dashboard.
Version History:
2015.03.11 / Brandie
Colors (based on the sysjobhistory table run_status values in BOL:
gray = 5 (not yet run)
green = 1 (success)
yellow = 4 (running)
red = 0,2,3 (failure)
sysjobhistory run_status values:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
------------------------------------------------------------------------------ */
--DECLARE @JobName VARCHAR(200); --Troubleshooting code
DECLARE @MonthEndDate DATE = (SELECT TOP 1 CONVERT(DATE,EndDate,101)
FROM dbo.BillingCycles
WHERE GETDATE() <= EndDate AND GETDATE() >= BeginDate),
@TodayDate DATE = (SELECT CONVERT(DATE,GETDATE(),101)),
@MEDateDiff SMALLINT,
@Job VARCHAR(200) = @JobName;
SELECT @MEDateDiff = DATEDIFF(dd,@MonthEndDate,@TodayDate);
/* This is a negative number if @TodayDate is still prior to the month end date.*/
IF @MEDateDiff >= -3 AND @MEDateDiff < 0
BEGIN
SET @MEDateDiff = @MEDateDiff * -1;
END;
/* Month end time frame extends from month end date to month end +3
This sets the variable to a positive value within that time
in order to properly pull the job status
*/
--SELECT @MEDateDiff; --Troubleshooting Code
IF @MEDateDiff < 0
BEGIN
SELECT 5 AS JobStatus;
--The job has not run this month because it's not time for it to run
END;
ELSE
IF @MEDateDiff >= 0
BEGIN
SELECT TOP 1 CASE WHEN sjh.run_status IN (0,2,3) THEN 0
WHEN sjh.run_status = 1 THEN 1 WHEN sjh.run_status = 4 THEN 4
ELSE 5 END AS JobStatus
FROM msdb..sysjobs sj
INNER JOIN msdb..sysjobhistory sjh
ON sj.job_id = sjh.Job_id
INNER JOIN (SELECT sja.[name], sja.job_id,
MAX(sjha.instance_id) as InstanceID
FROM msdb.dbo.sysjobs sja
INNER JOIN msdb.dbo.sysjobhistory sjha
ON sja.job_id = sjha.Job_id
WHERE sjha.step_id > 0
GROUP BY sja.[name], sja.job_id) LastRun
ON sj.job_id = LastRun.job_id
AND sjh.instance_id = LastRun.InstanceID
INNER JOIN msdb..sysjobsteps sjs
ON sj.job_id = sjs.job_id
WHERE sj.name = @Job
AND CONVERT(DATE,(SUBSTRING(CONVERT(CHAR(8),sjh.run_date),5,2)
+ '/' + SUBSTRING(CONVERT(CHAR(8),sjh.run_date),7,2)
+ '/' + SUBSTRING(CONVERT(CHAR(8),sjh.run_date),1,4)))
>= @MonthEndDate --Job ran after or on month end day
AND CONVERT(DATE,(SUBSTRING(CONVERT(CHAR(8),sjh.run_date),5,2)
+ '/' + SUBSTRING(CONVERT(CHAR(8),sjh.run_date),7,2)
+ '/' + SUBSTRING(CONVERT(CHAR(8),sjh.run_date),1,4)))
<= DATEADD(dd,3,@MonthEndDate);
--Job ran within 3 days of month end day
--Pull current job status for job
END;
GO
March 11, 2015 at 1:08 pm
No worries Brandie. I understand the frustration. You are so close and you are absolutely right that your void method isn't running. Two things you need to do. First, assuming you want your code to run on page_load you just need to add a call inside the page_load method. Second is you need to defined the access modifier. The access modifier you can leave off but it will use a default and I never remember what it is. Since this is a webpage and not a class I would say that private is probably appropriate.
Something along these lines:
protected void Page_Load(object sender, EventArgs e)
{
System.Web.UI.HtmlControls.HtmlGenericControl menuitem = (System.Web.UI.HtmlControls.HtmlGenericControl)Master.FindControl("MonthEndLi");
menuitem.Attributes.Add("class", "active");
findstatus();
}
private void findstatus ()
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2015 at 5:39 am
Wow. I can't believe it was that simple.
Thank you, Sean. You're a life-saver.
March 12, 2015 at 7:20 am
Brandie Tarvin (3/12/2015)
Wow. I can't believe it was that simple.Thank you, Sean. You're a life-saver.
Excellent!!! I am glad I was able to help. And more importantly I am glad you got it working.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2015 at 7:25 am
Sean Lange (3/12/2015)
Brandie Tarvin (3/12/2015)
Wow. I can't believe it was that simple.Thank you, Sean. You're a life-saver.
Excellent!!! I am glad I was able to help. And more importantly I am glad you got it working.
Now I get to see if I can get the rest of it working. I have 4 servers I have to hit for this and I chose the easiest one to reach.
Funfunfun.
March 12, 2015 at 9:09 am
I have more C# experience than SQL experience. I prefer a job doing both, but have tended to bounce back and forth because DB Dev and App Dev are often considered mutually exclusive positions. So I couldn't help but look at the C# method you posted.
A few items about the code you posted...
- The SQL Connection and command can be created once and reused in the loop. This is a very minor resource savings.
- The command type should be stored procedure whenever you call a stored procedure. General SQL injection protection, though you really don't have that risk here.
- Always, always, always put a SQL connection inside a using or a try/catch/finally. If there is an error calling SQL, the connection pool doesn't get the connection back. If the error is repetitive (server is down or inaccessible), the app can crash as a result. I have dealt with this very issue at my job recently, in a C# app written by a DBA. Note that when the using block exits at its final "}", the connection is closed. There is no need to call Close.
- The programmer attempted to provide the default value of 5 in case the stored procedure returns no rows (based on the SQL, this is a possibility though it doesn't look like it's an expected result). But in fact, ExecuteScalar will return null (.NET "null", not SQL "NULL"), and an exception will be thrown. The value must be put into an object type and checked for null before casting to int. This is a crash waiting to happen and the most important change that I made below.
- The if block testing returnjobstat should be a case statement. It works either way, but if it fits the switch/case pattern, use switch/case. Just a style guide.
private void findstatus()
{
using (
var sqlC =
new SqlConnection(
System.Web.Configuration.WebConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString))
{
var cmd = new SqlCommand
{
CommandText = "dbo.spJobStatusCheck",
CommandType = CommandType.StoredProcedure,
Connection = sqlC
};
cmd.Parameters.Add("@JobName", SqlDbType.VarChar, 200);
sqlC.Open();
foreach (string name in JobNames)
{
cmd.Parameters[0].Value = name;
object returnValue = cmd.ExecuteScalar();
int returnjobstat = returnValue == null ? 5 : (int)returnValue;
string imagename = string.Empty;
switch (returnjobstat)
{
case 0:
imagename = "images/small_red.jpg";
break;
case 1:
imagename = "images/small_green.jpg";
break;
case 4:
imagename = "images/small_yellow.jpg";
break;
case 5:
imagename = "images/small_gray.jpg";
break;
}
switch (name)
{
case "Job1":
this.DTDQC.Src = Page.ResolveClientUrl(imagename);
break;
case "Job2":
this.XMEDPTX.Src = Page.ResolveClientUrl(imagename);
break;
}
}
}
}
March 12, 2015 at 9:13 am
Nice work Stephanie. Are you involved in QA by any chance? If not, you should be:-)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 12, 2015 at 9:18 am
Stephanie Giovannini (3/12/2015)
I have more C# experience than SQL experience. I prefer a job doing both, but have tended to bounce back and forth because DB Dev and App Dev are often considered mutually exclusive positions. So I couldn't help but look at the C# method you posted.A few items about the code you posted...
- The SQL Connection and command can be created once and reused in the loop. This is a very minor resource savings.
- The command type should be stored procedure whenever you call a stored procedure. General SQL injection protection, though you really don't have that risk here.
- Always, always, always put a SQL connection inside a using or a try/catch/finally. If there is an error calling SQL, the connection pool doesn't get the connection back. If the error is repetitive (server is down or inaccessible), the app can crash as a result. I have dealt with this very issue at my job recently, in a C# app written by a DBA. Note that when the using block exits at its final "}", the connection is closed. There is no need to call Close.
- The programmer attempted to provide the default value of 5 in case the stored procedure returns no rows (based on the SQL, this is a possibility though it doesn't look like it's an expected result). But in fact, ExecuteScalar will return null (.NET "null", not SQL "NULL"), and an exception will be thrown. The value must be put into an object type and checked for null before casting to int. This is a crash waiting to happen and the most important change that I made below.
- The if block testing returnjobstat should be a case statement. It works either way, but if it fits the switch/case pattern, use switch/case. Just a style guide.
private void findstatus(){
using (
var sqlC =
new SqlConnection(
System.Web.Configuration.WebConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString))
{
var cmd = new SqlCommand
{
CommandText = "dbo.spJobStatusCheck",
CommandType = CommandType.StoredProcedure,
Connection = sqlC
};
cmd.Parameters.Add("@JobName", SqlDbType.VarChar, 200);
sqlC.Open();
foreach (string name in JobNames)
{
cmd.Parameters[0].Value = name;
object returnValue = cmd.ExecuteScalar();
int returnjobstat = returnValue == null ? 5 : (int)returnValue;
string imagename = string.Empty;
switch (returnjobstat)
{
case 0:
imagename = "images/small_red.jpg";
break;
case 1:
imagename = "images/small_green.jpg";
break;
case 4:
imagename = "images/small_yellow.jpg";
break;
case 5:
imagename = "images/small_gray.jpg";
break;
}
switch (name)
{
case "Job1":
this.DTDQC.Src = Page.ResolveClientUrl(imagename);
break;
case "Job2":
this.XMEDPTX.Src = Page.ResolveClientUrl(imagename);
break;
}
}
}
}
Thank you for the code updates, Stephanie. I will go through what I've developed and fix accordingly.
The "5" thing was my attempt to say "everything should be gray before the code is run and anything is checked". I guess that's not what you saw?
And looking at your code, I'm not sure how the USING part of it makes it any more reusable. Or are you saying the current code has to recreate the connection every time it loops through?
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply