Masterplug TVRC8N Instructions

By popular request:

TVRC8N Instructions

Use Overtype Mode Greyed Out

Okay, so Microsoft seem to have added quite a bit of confusion with the over-type functionality in Word.  Having thought that we now had this sussed, we found that one user was unable to enable over-type mode in the Advanced settings as the checkbox was disabled.

After hours of Googling, testing, experimenting, etc we stumbled upon the reason by accident.

If you have Track Changes enabled on a document you cannot enable over-type mode.

Hope this saves someone else hours of frustration!

Clear Up SSRS Subscriptions

One of the things with SSRS subscriptions is that they create a load of Agent Jobs that don’t clearly link back to specific reports.

SQL warns you not to mess with these directly, so you need to find the report and remove or edit the subscriptions via the SSRS interface.

We had loads that were expired, but the question was, what reports to they relate to?

The list below shows a number of Subscriptions that have not run for a while and are not scheduled to run again.

So, how do we find the associated reports?

If you double click a Job you’ll see the screen below

Click On “Steps”

Then Click on “Edit”

What we want is the @EventData which is the Subscription ID

If you then open MS Management Studio and run the following Code against your SSRS Report Database (replace the id with the one shown in the @EventData above):

SELECT 
	c.Path,
	c.Name
FROM dbo.Subscriptions s JOIN dbo.Catalog c
	ON s.Report_OID = c.ItemID
WHERE s.SubscriptionID = '56d31dd0-aca0-420c-940b-7d45f862db1c'

This will then show you the report path and name that the subscription relates to.

You can then connect to your SSRS reports and delete the subscription via the subscription options.

Copy DataGridView to Clipboard

Following on from exporting a DataGridView to a CSV file someone asked this week if it’s possible to copy the contents to the clipboard for pasting into another application.

The code below copies the content of the passed DataGridView to the clipboard inserting the specified delimiter between each column.

The basic idea is to scan each row and write the contents of the cell to a string builder, inserting the delimiter after each column (except the last one in the row). At the end of the row append a new line to the string builder. When the end of the grid is reached copy the contents of the string builder to the clipboard.

The procedure returns either true (if the DataGridView had data in it) or false (if the DataGridView is empty).

[csharp gutter=”false”]
//accepts two parameters:
// dgvGrid – reference to a valid DataGridView
// colDelimiter – character(s) to insert between each column
//returns:
// true if the grid has rows
// false if the grid has no rows
private bool copyGridToClipboard(DataGridView dgvGrid, string colDelimiter)
{
//variable to hold the value stored in each cell
string cellValue;
//variable to hold the text extracted from the grid
StringBuilder sb = new StringBuilder();

//set row and column count variables
int rowCount = dgvGrid.RowCount;
int colCount = dgvGrid.ColumnCount;

//check to see if the grid has some rows
if (rowCount > 0)
{
//initialise the varible to hold each grid row
DataGridViewRow gridRow = new DataGridViewRow();

//loop through rows
for (int row = 0; row <= rowCount – 1; row++)
{
//set gridRow to the current row
gridRow = dgvGrid.Rows[row];
//loop through columns
for (int col = 0; col <= colCount – 1; col++)
{
//if the gridRow cell is not null assign the contents to cellValue
if (gridRow.Cells[col].Value != null)
{
cellValue = gridRow.Cells[col].Value.ToString();
}
//otherwise set cell value to an empty string
else
{
cellValue = string.Empty;
}
//append the cell value to the stringbuilder
sb.Append(cellValue);

//if the column is not the last one append the column delimiter
if(col < colCount – 1)
{
sb.Append(colDelimiter);
}
}
//end of row columns: so append newline to the stringbuilder
sb.AppendLine();
}//end of final row: so copy the entire stringbuilder to the clipboard & return "true"

Clipboard.SetData(System.Windows.Forms.DataFormats.Text, sb.ToString());
return true;
}else
//otherwise return "false"
{
return false;
}
}
[/csharp]

Below is an example of calling the procedure (NB the calling form has a DataGridView called dgvExcel)

[csharp gutter=”false”]
//call sending the DataGridView dgvExcel and a comma as the delimiter
if (copyGridToClipboard(dgvExcel, ","))
{
MessageBox.Show("Data Copied to the Clipboard", "Copy – Complete", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("No Data to Copy", "Copy – Failed", MessageBoxButtons.OK, MessageBoxIcon.Information);
} }

[/csharp]

Simple SSRS Menu

This post assumes you know how to create a basic SSRS report and link it to a SQL Stored Procedure.

We were starting to build up a fair number of SSRS reports that the users were running on an ad hoc basis. Initially I was emailing links to each individual report and people were saving them to their favourites.

It wasn’t long before I realised that a menu would be a much better idea. I had built SSRS menus in ASP.net / C# before but wanted a quicker, easier solution.

First thing was to organise the reports.

On the report server I created a “User Reports” folder. And within this a folder for each department (there are some lower level sub folders that have a higher privilege security setting, but the principle holds true no matter what level you go to).

For the permissions for each folder I created a corresponding Active Directory Group and gave permissions to that group (e.g. AD Group called SSRS_Finance, SSRS_IT, etc)

Next step was to create two SQL Tables to hold the Report Details.

Table one is Report Groups, simply used to group reports and provide a meaningful name for each group.

Table two holds the report details.

ReportID is simply an auto incrementing ID column

ReportGroupID is the Foreign Key to the ReportGroup Table

ReportName is the name of the report as stored on the SSRS Server with the relative path from the User Reports folder. E.g. if you look at ReportID 8, this report is in the Finance Folder then the Finance_Level2 subfolder and is called RAY_Ledger_Transactions.

DisplayName is the Name that the User will see in the Menu

Report Description shows next to the DisplayName to clarify what the report shows (if needed)

NB: I avoid using spaces in the report names or folders for simplicity.

We also need a stored procedure to return the information we want to see in our Menu (we could hardcode this in the report dataset but I prefer to use a stored procedure):

[sql gutter=”false”]
CREATE PROC [dbo].[RAY_UserReportsMenuSp]
AS
SELECT
RG.ReportGroupID ,
RG.GroupName,
R.ReportID ,
R.ReportName ,
R.DisplayName ,
R.ReportDescription
FROM dbo.Report R JOIN dbo.ReportGroup RG
ON R.ReportGroupID = RG.ReportGroupID
ORDER BY RG.GroupName, R.DisplayName
GO
[/sql]

We are now ready to build the Menu.

This is simply a report that uses the information stored in the tables to provide a menu with clickable links to each report.

One important thing to note is that the Menu must be in the root folder directly below your subfolders, in this case “User Reports”. If this is not the case, you would need to modify the ReportName as this is used as the link to the reports (as we will see in a minute).

Open Visual Studio (or your editor of choice) and create a new report Menu, that has a data source that links to the database where your tables and stored procedure are located.

Create a dataset that calls the data from the stored procedure.

Add a Tablix to the report and add the columns as below:

To get the hyperlink on the Report Column (which will launch the report when you click on the DisplayName) you need to open the textbox properties and on the action tab set the properties as below.

This tells SSRS to go to the report specified in the ReportName when you click on the DisplayName).

Your final Menu should now look something like the one below:

You can then send the URL for this report to all your users.

Don’t Fall Victim To The Windows / Microsoft Phone Scam!

There are many phone scams where someone will ring you claiming to be from Microsoft, Dell, Apple, or some other known IT company.

They will tell you that they have detected issues with your computer, normally “security” issues.

They will often ask you to open the “Event Viewer” on your computer to show you the errors.

They will then ask you to go to a particular webpage and download some software that will fix your computer

Consider the following:

1. Do Microsoft, Dell, Apple, etc have your telephone number: Probably NOT
2. Do Microsoft, Dell, Apple, etc offer free support where they monitor your computer and offer to fix it over the phone: Definitely NOT
3. Can Microsoft, Dell, Apple, etc see errors on your computer: Definitely NOT
4. Would Microsoft, Dell, Apple, etc ring you and ask you to visit a webpage or install software on your computer: Definitely NOT
5. If you do open the “Event Viewer” on your computer, as advised by the caller, will you see errors: Almost Certainly YES*
6. Are these errors they can see or fix: Definitely NOT

Imagine someone knocked on your door and said, “I can tell your oven is broken and I can fix it if you give me your credit card details”, what would you do?

• NEVER believe that any IT company will ring you out of the blue telling you that you have computer issues
• NEVER go to a website that any caller tells you to go to
• NEVER install any software that any caller tells you to
• NEVER give your credit / debit card, or bank details to any cold caller

*All Windows computers will show errors in the “Event Viewer” (this is something the hoaxers rely on). They are generally unimportant errors that you don’t need to worry about, but the scammers rely on you thinking they have “seen” these errors and you need to take action.

Remove Characters in String T-SQL with No Loop Part 2

Okay, expanding on what I did yesterday.

I Built a table, called ExclusionList with one field, ExcludedText

E.g.

Then created a function:

CREATE FUNCTION [dbo].[udf_clean_string]
	(
	@string VARCHAR(100)
	) RETURNS VARCHAR(100)
AS
BEGIN	

SELECT
    @string = REPLACE(@string,ExcludedText,'')
FROM ExclusionList

RETURN @string

You can then use like this:

SELECT
	dbo.udf_clean_string(FieldName)
FROM TableName

Remove Characters in String T-SQL with No Loop

Ok, I’ve seen many ways to do this using loops, but nothing in plain T-SQL

Let’s say we have a string that may contain illegal characters, in my case it was project identifiers that needed to be used as file names for exporting. However, many had / or * in the names.

One thing I hate in T-SQL is loops, so having only found looping ways to solve this I started from scratch.

He’s my solution:

Build a table of exclusions, i.e. characters or strings that you want to remove from your string (in my example below this is a temp table, but I would make it a permanent table in the end solution).

Then use REPLACE joined to this table to clean the string e.g.

/*Create the temp table for the exclusion list table*/
CREATE TABLE #ExclusionList
	(
	ExcludedChar VARCHAR(50)
	)

/*Add the stuff we want to exclude*/
INSERT INTO #ExclusionList SELECT '\'
INSERT INTO #ExclusionList SELECT '|'
INSERT INTO #ExclusionList SELECT '?'
INSERT INTO #ExclusionList SELECT '*'
INSERT INTO #ExclusionList SELECT ':'
INSERT INTO #ExclusionList SELECT '<'
INSERT INTO #ExclusionList SELECT '>'
INSERT INTO #ExclusionList SELECT '"'
INSERT INTO #ExclusionList SELECT 'egg'

/*Get the horrible string that we want to clean*/
DECLARE @string VARCHAR(50) = 'th""<i>???s:: :|||"<egg>wo|r|k|**s*< ">we*l::l::'

/*Do the SELECT*/
SELECT
	@string = REPLACE(@string,ExcludedChar,'')
FROM #ExclusionList

/*Show the string post processing*/
SELECT @string

/*Drop the temp table*/
DROP TABLE #ExclusionList

If you run this you’ll see that all the excluded text has been stripped and you’re left with “this works well”.

I’m all ready thinking of ways this could be expanded, e.g. Exclusion List type added to the exclusion list, you could then maintain different lists for different things, e.g. File Names, Email Addresses, etc and just add a WHERE clause to the REPLACE select statement.

Any comments or ideas please let me know!

Store

You can buy my programming and computer related gifts from my shop!

Dynamic CSS files in ASP.net Master Page using C#

I have a reporting portal that serves up SSRS (SQL Server Reporting Services) reports to users. The users are from different organisations, indicated by a user group. I needed to have the front end personalised for each different organisation. The approach below loads a different CSS file depending on the user group of the logged in user. I’m using a master page solution, so the CSS is loaded in the master page file. The CSS files for each organisation are stored in a CSS folder.

If we look at a standard link to a style sheet that we would place in the head of our master page

<head runat="server">
    <title></title>
    <link href="CSS/StyleSheet.css" type="text/css" rel="stylesheet" />
</head>

We are going to change this to what we see below

<head runat="server">
    <title></title>
    <link id="CurrentStyleSheet" type="text/css" rel="stylesheet" runat="server"/>
</head>

You need to note three things in the above code. We need to ensure that runat=”server” is added, that there is no “href” to point to our CSS file as we be adding this in code, and we have given our link an id.

Firstly lets load the CSS file in the Page_Load event of the master page. We reference the link using its id and add the “href” attribute and value to the link (the !Page.IsPostBack stops this from being done repeatedly on every page postback).

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                CurrentStyleSheet.Attributes.Add("href", "CSS/StyleSheet.css");
            }
        }

This is all good, however we are still just loading one CSS file regardless. In my database I have a field that holds the CSS file name for each user group and a function getCssFile() that returns the CSS file name for the logged in user. (I’m not going to cover how that function reads the value from the database here).

So the code now becomes

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                CurrentStyleSheet.Attributes.Add("href", string.Format("CSS/{0}", getCssFile()));
            }
        }

This goes and gets the name of the style sheet from the database for the logged in user and inserts this into the “href” attribute.