T-SQL Replace Every Occurrence of Specific Text in a Column with Other Text

This syntax can be used to replace any instance of a word or string of some specific text that appears anywhere in a column (in any row) with some other text. 

To make a global change to column "Comment":

update MyTable set Comment = replace (Comment ,'all instances','any instance') 

This selective edit came in very handy the other day.

remove entry from run command

Open up regedit.exe through the start menu run box, and then navigate down to the following key: HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\RunMRU

Reference for Asci for special symbols

Found this excellent reference http://amp-what.com  . Gives the asci for special chars with a very nice visual display. 

Enter the request in search field: triangle, arrow, etc.

For example, use asci &#9656 for the following small right pointing arrow: ▸

Why use an image when a character will do?

I came across some very cool t-sql to generate a comma delimited list of (char,varchar) column values from selected rows:  (Note: the '' in the code snippets are two single quotes ' ', not a double quote)

DECLARE @MyList VARCHAR(1000)
SELECT @MyList = ISNULL(@MyList,'') + Title + ', ' FROM Titles
SELECT @MyList


For a comma delimited list of integer values, use:

DECLARE @MyList VARCHAR(1000)
SELECT @MyList = ISNULL(@MyList,'') + CAST(Id AS CHAR(5)) + ', ' FROM MyPeople WHERE firstname=''  AND  lastname= ''
SELECT @MyList


I find this very useful for generating a list to be used in a t-sql  WHERE clause, for example:
Let's say the above query generated the result  :   2,4,6,25,66,
1. I copy the string  2,4,6,25,66,
2. Then remove the last comma to get 2,4,6,25,66
3. and paste the edited result into the following example query:
UPDATE MyPeople SET  col_note= 'Bad Data' where Id in (2,4,6,25,66)

To see this code in action, use the following script:

CREATE TABLE Titles(
PKey int NOT NULL,
Title varchar(50) NULL
)

insert Titles ( pkey, title) values (1, 'Doctor')
insert Titles ( pkey, title) values (2, 'Nurse')
insert Titles ( pkey, title) values (3, 'Administrator')
insert Titles ( pkey, title) values (4, 'CMA')

select * from Titles

DECLARE @MyList VARCHAR(1000)
SET @MyList = ''
SELECT @MyList = ISNULL(@MyList,'') + Title + ', ' FROM Titles
SELECT @MyList DECLARE @MyList VARCHAR(1000) SELECT @MyList = ISNULL(@MyList,'') + cast(contacts_PKey as char(5)) + ', ' FROM MedicalPersons where firstname='' and lastname= '' select @MyList

Using REGEX to Change Case Selectively

I had a list of generic medications where the names were capitalized. I needed to change the spelling to lower case, which is the accepted way to spell them.

However, converting the medication name ToLower was too simplistic, since certain capitals needed to be preserved:

abobotulinumtoxinA
alteplase, tPA
aspirin, ASA
avian influenza A (H5N1) virus vaccine

Here is the code I used:

private string ConvertStringToLC(string name)
{

foreach (Match m in Regex.Matches(name, @"[A-Z]{1}[a-z]+"))
{
name = name.Substring(0, m.Index ) + char.ToLower(name[m.Index]) + name.Substring(m.Index + 1);
}

return name;
}

Because I was updating a sql database column I also had to take care of strings that had an apostrophe in them, such as burow's solution.

drugNameNew = drugNameNew.Replace("'", "''");

One last thing. If the generic drug name is used at the start of sentence it needs to be capitalized.

drugNameGeneric = char.ToUpper(drugNameGeneric[0]) + drugNameGeneri.Substring(1);

How to Convert an Image in jpg or other Format to an Icon

To convert an image in jpg or other format to an Icon

Open the image in Ms Paint

Click on File -> Save As and

1. Choose “Save as Type” as 24-bit Bitmap(*.bmp)
and
2. name the file with an extention of .ico: TheFileName.ico

The image does not need to be resized (to 32 x 32 or other icon size)

Two C# Solutions for the FizzBuzz Problem

In case you are interested...

namespace FizzBuzz
{
    class Program
    {
        static void Main(string[] args)
        {
            const string fizz = "FIZZ";
            const string buzz = "BUZZ";
            const string fizzbuzz = "FIZZBUZZ";

            // I did this solution second
            for (int i = 1; i < 101; i++)
            {
                string output;
                if (i / 15 * 15 == i) output = fizzbuzz;
                else if (i / 3 * 3 == i) output = fizz;
                else if (i / 5 * 5 == i) output = buzz;
                else output = i.ToString();
                Console.WriteLine(output);
            }

            // and this solution third 
            for (int i = 1; i < 101; i++)
            {
                string output = (i / 15 * 15 == i) ? fizzbuzz : (i / 3 * 3 == i)
                    ? fizz : (i / 5 * 5 == i) ? buzz : i.ToString();
                Console.WriteLine(output);
            }
   
            Console.ReadLine();
        }
    }
}
// Trust me, you don't want to see the one I did first.

Thoughts on n-Tiered Programming

One of the problems that I had when first learning Object Oriented Programming was that the examples shown to illustrate concepts were so basic that they made no sense.

Why add piles of seemingly needless complexity when there were much easier and more straightforward ways to program solutions?

Why code interfaces that didn’t seem to add anything but more programming work and confusion?

Why create multiple Layers when all I was doing was passing unchanged values down the chain?

A lot of it started to make sense when I started doing things that were more complicated.

If the data does not change as it is passed from one layer to the next, having multiple layers doesn’t seem to make sense.

However, imagine a more usual scenario where the way your data is stored in the database does not match what is displayed in the UI.

Then it starts to get interesting.

I find it helps to think of n-tiered programming this way: The BusinessLayer translates between the data in the database and the data in the UI.

I usually have a more or less one to one correspondence between the data in the database and the objects which are created in the DataLayer and passed back and forth to the BusinessLayer.

I usually have a more or less one to one one correspondence between the data in the BusinessLayer and the data in the UI.

The BusinessLayer is where the translation happens. This is whether retrieving data from the database to display in the UI, or collecting data entered in the UI and storing it in the database.

(My other layer is an EntitiesLayer. I contains classes with properties to store the data for passing around.)

Outlook 2007 Cannot start Microsoft Outlook. Cannot open the Outlook window

Outlook starts to open, but then gets the error in the title of this post.

Fixed by running outlook.exe /resetnavpane in the All Programs textbox:

Disable Windows7 Automatic Resize to Max

Windows 7 has a feature where, when you drag a midsize window to the edge of the screen, Windows 7 maximizes it.

I found this really annoying because I frequently have several partial windows open and I drag some over to the side so they don't block each other.

To turn this feature off:

Control Panel -> Ease of Access Center

select Make the Mouse Easier to Use

check Prevent windows from being automatically arranged...

ok to save setting

Which Templated Checkboxes in an Infragistics Webdatagrid Column are Checked

This is the codebehind I use to see which templated checkboxes in an infragistics webdatagrid column are checked.

 
       private void TakeActionOnSelectedCustomers ()
        {
            if (igWdgCustomers.Rows.Count != 0)
            {
                foreach (GridRecord row in igWdgCustomers.Rows)
                {
                    CheckBox theCB = (CheckBox)row.Items[0].FindControl("cbSelect");
                        if ( theCB!= null && theCB.Checked)  
               //  check for null because footer row doesn’t have cb 
                        {
                             //  take action 
                        }
                }
            }
        }

This is the column being checked.

   <ig:TemplateDataField Key="cbSelect" width="30px">
            <Header Text=" "   />
               <ItemTemplate>
                 <asp:CheckBox ID="cbSelect" runat="server" />
                </ItemTemplate>
   </ig:TemplateDataField>  

In this case the checkbox is not databound, but it could be.

I have not found a way to use the CRUD feature of the datasource that the WebDataGrid uses to update the db value of a boolean represented by a databound checkbox.

DateTime.ParseExact

So after never having had CultureInfo.CurrentCulture on my radar, I ended up using it twice this week.

For the first use, see my previous post on ToUpperInvariant().

Today, I ran into a problem where I tried DateTime.Parse on a string which I had generated in a gridview using:

<asp:BoundField DataField="DateStarted"  
     DataFormatString="{0:ddd MMM d hh:mmtt}" />

The compiler couldn't parse it.

I ended up having to decode it using the following:

DateTime dateCreated =DateTime.ParseExact(sdateCreated, 
   "ddd MMM d hh:mmtt", CultureInfo.InvariantCulture);

ToUpperInvariant()

Whoa! Jon Skeet answered a question of mine on StackOverflow this week! I am honored.

The question was:

In C# what is the difference between ToUpper() and ToUpperInvariant()?

And the answer was, in a nutshell, "Unless you are programming in Turkish, no difference."

What brought me to ask that question started with a need to always reformat user input for first and last name with Initial Letter Case: first letter capitalized, rest lower case. (I know, I know, this is not all last names follow this convention, but hey, I didn't define the user requirements.) (In word I use Shift-F3 all the time.)

So I went looking in intellisense for a string method called InitialLetterCase(). After all, there are string methods ToUpper() and ToLower().

There was no InitialLetterCase() method, but I found ToUpperInvariant(). What did that do? I did a google search but the first few hits didn't seem to answer the question.

So thank you, Jon Skeet and StackOverflow, for the answer.

FYI, the solution to my request is a simple

tbFirstName.Text =
CultureInfo.CurrentCulture.TextInfo.ToTitleCase(tbFirstName.Text); :

RegisterStartupScript vs Response.Write

Even though putting windows popup alerts into your UI can be bad design, annoying to your users, etc, sometimes it's just what you need to get the job done. I feel it's ok to use an occasional windows alert, as long as you don't abuse them.

If you are also using Ajax, you have a choice of 2 ways to program that alert from the code behind: Response.Write or ScriptManager.RegisterStartupScript.

The difference is that Response.Write will popup an alert box before the page loads, ie on a white background, and ScriptManager.RegisterStartupScript will pop it up after the page has loaded, so the page is visible behind the popup.

I prefer using ScriptManager.RegisterStartupScript, but maybe I just haven't come across an application where Response.Write would be better.

In any case, the following is the code to use for each case:

Response.Write("<script language='javascript'> 
       alert('This pops up on a blank page 
     (ie before the page loads) using response.write');</SCRIPT> ");

ScriptManager.RegisterStartupScript(this, typeof(Page), UniqueID, 
      "alert('This pops up after the page loads, 
      using ScriptManager.RegisterStartupScript');", true);

I have also popped up an alert on pageload by using the following in the html < body> tag of the page:

<body onLoad="alert('Update. \r\n \r\n Thank you for your interest, 
 \r\n however, \r\n we are not taking any new orders at this time.')" >

The \r\n causes a line break.

Because I always forget proc names ...

protected void Page_PreInit(object sender, EventArgs e)
{
string msg = "Fires 1";
}

protected void Page_Init(object sender, EventArgs e)
{
string msg = "Fires 2";
}


protected void Page_Load(object sender, EventArgs e)
{
string msg = "Fires 3";
}

protected override void OnPreRender(EventArgs e)
{
base.OnPreRender(e);
string msg = "Fires last";
}

WebDataGrid Numeric Pager Truncates List of Page Numbers

The WebDataGrid Numeric Pager was truncating the list of page numbers after reaching the end of the one line (table width) allocated for the pager display, as shown in the example below. There were scroll bars for the height and width of the data records, but no way to scroll past the end of the line of numbers and see page 21 in this example:



My solution was to create the following Css

<style type="text/css">
.tallPager {
height: 100px;
word-wrap: break-word;
text-align:left;
}
</style>

and apply it to the Paging property

  <Behaviors>
        <ig:Paging PageSize="25" PagerMode="Numeric" EnableInheritance="True"
             PagerCssClass="tallPager" />

etc...

The following shows the pager section for a different table, with the PagerCssClass ="tallPager"


Note: Word wraps can occur in the middle of a page number like 108:


Fortunately, I have a higher class of users and they were able to deal with that.

tsql INSERT INTO for bulk copy of records from SqlServer table to table

I have been using tsql

Select * into Tbl2 from Tbl1 where FName = 'David'

for a long time, as a shortcut to create new table Tbl2, a copy of Tbl1 including schema and data.

I'm glad I finally found out about :

Insert into Tbl2 ( LastName , FirstName , AdmitDate) select Last, First, RevDate from Tbl1 where FName = 'David'

to use when Tbl2 is already in existence.

Calling all Technical Women in Technology for...

What we have all been waiting for ...

A NJ Technical Women in Technology
Event


Register Now ->

Recommended Audiences: IT Professionals, Administrators, Developers, Architects

Are you a Technical Women in Technology?

Are you tired of always being surrounded by technical males that don't ____ (fill in the blank)?

Are you tired of trying to explain your technical issues to other women that just don't get it?

Are you looking to connect with other women with common interests?

Then, you may be interested in helping to form a Technical Women in Technology Group...

Refreshments: Appetizers & Water/Soda

Sponsors: Microsoft / SetFocus

Purpose:

To allow NJ Technical Women in Technology to meet and to discuss a future direction for a group



Event Updates: updates

Agenda:

Overview

Attendee Introductions

Panel Discussion (2-3 women sharing their experiences as a Technical Women in Technology

Future Direction Discussion - In-Person and/or Virtual meetings, frequency, meeting content, etc.



For Additional Information about how you can help kick start this community, please email melissa[@]sqldiva.com.

Include a derived control in my aspx page

register control at top of page giving it a TagPrefix:

<%@ Register Assembly="iProjectName" Namespace="iNamespceInCsFile" TagPrefix="ifc" %>

use tag prefix and control name in aspx

<ig:TemplateDataField Key="key1" Width="30px">
     <ItemTemplate>
     <ifc:CtrlClass ID="id1" runat="server" Text='<%# Eval "BoundCol1") %>' />
     </ItemTemplate>
</ig:TemplateDataField>

"CtrlClass" is the derived control class name, which is in a cs file name doesn't matter, but namespace is "iNamespceInCsFile" in project ="iProjectName".

public class Comment2 : TextBox
{ etc ...

Comparison of ig bound Templated Checkbox to boolean BoundDataField

Shows up as text "true" or "false":
        <ig:BoundDataField DataFieldName="IsCostSaving" Key="IsCostSaving" width="30px">
            <Header Text="Cost Saving" />
          </ig:BoundDataField>    

Shows up as a checkbox with checked value picked up from bound field:
         <ig:TemplateDataField Key="cb1" width="30px">
            <Header Text="Cost Saving" />
               <ItemTemplate>
                 <asp:CheckBox ID="cb1" runat="server"  Checked='<%# Eval("IsCostSaving") %>' Enabled="false"  />
                </ItemTemplate>
          </ig:TemplateDataField>