Src=” causes double page requests

A html tag with src=” (ie img) will cause the browser to try to request that resource at the same URL as the current page, hence causes double requests.

Formatting Dates, Times and Numbers in C#

Good reference (with examples)  http://www.4guysfromrolla.com/articles/111010-1.aspx

Crosstab query (2)

Part one here.

Another way to do vertical to horizontal query. Let’s have a table with sales_id, prod_id, sales_date, and sales_amount fields. This query will return 4 latest sales_id in 3 columns:

SELECT prod_id, (SELECT TOP 1 sales_id FROM table WHERE prod_id=s.prod_id ORDER BY sales_date) as sales_id1,

(SELECT TOP 1 sales_id FROM table WHERE prod_id=s.prod_id AND sales_id NOT IN (SELECT TOP 1 sales_id FROM table WHERE prod_id=s.prod_id ORDER BY sales_date) ORDER BY sales_date) as sales_id2,

(SELECT TOP 1 sales_id FROM table WHERE prod_id=s.prod_id AND sales_id NOT IN (SELECT TOP 2 sales_id FROM table WHERE prod_id=s.prod_id ORDER BY sales_date) ORDER BY sales_date) as sales_id3,

(SELECT TOP 1 sales_id FROM table WHERE prod_id=s.prod_id AND sales_id NOT IN (SELECT TOP 3 sales_id FROM table WHERE prod_id=s.prod_id ORDER BY sales_date) ORDER BY sales_date) as sales_id3

FROM table as s GROUP BY prod_id

Then you can join this view/sub-query to get detailed data for each row.

 

C# String Format examples for Double

From http://www.csharp-examples.net/string-format-double/

The following examples show how to format float numbers to string in C#. You can use static method String.Format or instance methods double.ToString and float.ToString.

Digits after decimal point

This example formats double to string with fixed number of decimal places. For two decimal places use pattern „0.00“. If a float number has less decimal places, the rest digits on the right will be zeroes. If it has more decimal places, the number will be rounded.

[C#]

// just two decimal places
String.Format("{0:0.00}", 123.4567);      // "123.46"
String.Format("{0:0.00}", 123.4);         // "123.40"
String.Format("{0:0.00}", 123.0);         // "123.00"

Next example formats double to string with floating number of decimal places. E.g. for maximal two decimal places use pattern „0.##“.

[C#]

// max. two decimal places
String.Format("{0:0.##}", 123.4567);      // "123.46"
String.Format("{0:0.##}", 123.4);         // "123.4"
String.Format("{0:0.##}", 123.0);         // "123"

Digits before decimal point

If you want a float number to have any minimal number of digits before decimal point use N-times zero before decimal point. E.g. pattern „00.0“ formats a float number to string with at least two digits before decimal point and one digit after that.

[C#]

// at least two digits before decimal point
String.Format("{0:00.0}", 123.4567);      // "123.5"
String.Format("{0:00.0}", 23.4567);       // "23.5"
String.Format("{0:00.0}", 3.4567);        // "03.5"
String.Format("{0:00.0}", -3.4567);       // "-03.5"

Thousands separator

To format double to string with use of thousands separator use zero and comma separator before an usual float formatting pattern, e.g. pattern „0,0.0“ formats the number to use thousands separators and to have one decimal place.

[C#]

String.Format("{0:0,0.0}", 12345.67);     // "12,345.7"
String.Format("{0:0,0}", 12345.67);       // "12,346"

Zero

Float numbers between zero and one can be formatted in two ways, with or without leading zero before decimal point. To format number without a leading zero use # before point. For example „#.0“ formats number to have one decimal place and zero to N digits before decimal point (e.g. „.5“ or „123.5“).

Following code shows how can be formatted a zero (of double type).

[C#]

String.Format("{0:0.0}", 0.0);            // "0.0"
String.Format("{0:0.#}", 0.0);            // "0"
String.Format("{0:#.0}", 0.0);            // ".0"
String.Format("{0:#.#}", 0.0);            // ""

Align numbers with spaces

To align float number to the right use comma „,“ option before the colon. Type comma followed by a number of spaces, e.g. „0,10:0.0“ (this can be used only in String.Format method, not in double.ToString method). To align numbers to the left use negative number of spaces.

[C#]

String.Format("{0,10:0.0}", 123.4567);    // "     123.5"
String.Format("{0,-10:0.0}", 123.4567);   // "123.5     "
String.Format("{0,10:0.0}", -123.4567);   // "    -123.5"
String.Format("{0,-10:0.0}", -123.4567);  // "-123.5    "

Custom formatting for negative numbers and zero

If you need to use custom format for negative float numbers or zero, use semicolon separator „;“ to split pattern to three sections. The first section formats positive numbers, the second section formats negative numbers and the third section formats zero. If you omit the last section, zero will be formatted using the first section.

[C#]

String.Format("{0:0.00;minus 0.00;zero}", 123.4567);   // "123.46"
String.Format("{0:0.00;minus 0.00;zero}", -123.4567);  // "minus 123.46"
String.Format("{0:0.00;minus 0.00;zero}", 0.0);        // "zero"

Some funny examples

As you could notice in the previous example, you can put any text into formatting pattern, e.g. before an usual pattern „my text 0.0“. You can even put any text between the zeroes, e.g. „0aaa.bbb0“.

[C#]

String.Format("{0:my number is 0.0}", 12.3);   // "my number is 12.3"
String.Format("{0:0aaa.bbb0}", 12.3);          // "12aaa.bbb3"

Reseed an Identity column in SQL 2008

In SQL 2000/2005, I used to be able to change the seed of an identity field in table designer. It will just prompt a message saying this table and a few other table (if having FK on) will be modified. However somehow in SQL 2008, I can not do that any more. The table seems can not be dropped.

I found the workaround:

dbcc checkident (my_table, reseed, 1052)

This will raise the next identity value in my_table to be 1052. It does NOT change the column definition but change the run-time seed instead.

You have to make sure the new seed value is greater than all existing values. Otherwise you will get UNIQUE violation errors.

Also to temporarily allow inserting values into the identity column, you can do this:

set identity_insert mytable on

and this will turn it off

set identity_insert mytable off

But you can only run it on one table at a time.

P.S. I figured out the reason why SQL 2008 will report “Saving changes is not permitted. ..” error when trying to change the identity properties. By default, in SQL 2008 Management Studio, Tools->Options->Designers->Table and Database Designer,  “Prevent saving changes that require table re-creation” is checked. Changing identify field will internally drop and re-create the table. Just uncheck it and you can change it in UI.

Scan stored procedures source script

Want to remove an obsolete table column but don’t want to break any views that may still reference it?

SELECT v.NAME, c.[text] FROM syscomments c, sys.VIEWS v WHERE v.OBJECT_ID=c.id
AND c.TEXT LIKE ‘%MY FIELD%’

The above is just for the view, this query will give you all the objects (views, stored procedures, functions etc)

SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE ‘%table or field name%’

(xtype column: P for stored procedures; V for views; FN for functions)

Email notification in MS SQL 2005/2008

A few undocumented steps, on top of the obvious ones, before being able to send email notifications

• Database Mail -> Configuration -> Manage profile security, make sure there is at least one public and default profile.
• SQL Server Agent -> Properties -> Alert System, make sure to check “Enable mail profile” and choose correct mail system and profile. Test button will be disabled if “Database Mail” is selected.
• Create operator
• Restart SQL Agent

Why EntitySet not auto-generated by LINQ-to-SQL?

It turns out the the child table must have a primary key. If not set in database level, that can be set in the *dbml file. Multiple-field primary key works too.

Client-side validator not firing in FF, only in IE

Symptom:

For a legacy web app converted from older version of ASP.NET, the client javascript form validation only fires in IE, not in FF.

Fix:

Change this line in web.config

<xhtmlConformance mode="Legacy"/>

to:

<xhtmlConformance mode="Transitional"/>

Full text search time out problem

This is a MS SQL 2005 server. The full text search seems time out after some idle time. Interestingly, this time out only happens to ASP.NET connections. Query Analyzer never timed out no matter what login name used.  This MS KB link explains it:

http://support.microsoft.com/?scid=kb%3Ben-us%3B915850&x=15&y=8

However, my server DOES have internet conneciton. Nevertheless, this command still fixed the problem:

sp_fulltext_service 'verify_signature', 0;

Even Microsoft says there are securities concerns but it doesn’t seem there is an option. To check the current configuration value without changing it, run

sp_fulltext_service 'verify_signature'

I had to restart SQL service for it to take effect.