TZU

What is the word?

Refer to previous post, the code is actually refer to a word.
However it is my word, because the word is

Yeah, it is my word, can see my word now?

Why? look at your keyboard, if you using notebook, get one desktop and back to my blog again, and look at your keyboard one more time.

haha, cheers.

78952 7895123 7412369

This challenge was came across my mind last week, and I upload it to my MSN for one week.

That will be easy for somebody, and someone may find it hard.

Actually my friend Kelvin already broke the code yesterday since he’d read something similar to this challenge. However I swear that I never refer to any resources or books, it is just something I think is funny so I put it here to crack your head.

:-P:-P

~~~ * ~~~ * ~~~ *~~~

Here come the challenge, try to guess the meanings of the code below:

~~~ * ~~~ * ~~~ *~~~

This week hint:

1) 12358 1235789 1478963

2) Think of something relate to me…

3) No more clue, answer will be given directly.

~~~ * ~~~ * ~~~ *~~~

几个月前试过很不错的 Firefox plugin，很多都是以前玩过的，

玩到很过瘾下。

夠誇張的 FireFox 附加元件 – 紅白機模擬器

安装步骤就参考上面的 link。

至于可按的 key 有：

[上下左右，CTRL，Z，X]，

[8546 (就是上下左右)，7，9]

抓一幅图来先睹为快。

Donkey Kong

You need to add reference before you start (Refer to my previous post – Rename Field in MS Access Programmatically using ADOX (C#.NET) )

using ADOX;

// Skipped namespace and public class here...

private void btnUpdate_Click(object sender, EventArgs e)
 {
   string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Northwind.mdb;Persist Security Info=False";
   CatalogClass cat = new CatalogClass();

   cat.let_ActiveConnection(connString)

   AddField(cat, "TargetTable", "Employee_Name_2", DataTypeEnum.adVarWChar, 50, string.Empty);
}

private void AddField(ADOX.CatalogClass cat, string tableName, string newFieldName, DataTypeEnum varType, int size, string defaultValue)
 {
   // #1
   // cat.Tables[tableName].Columns.Append(newFieldName, varType, size);

   ColumnClass col = new ColumnClass();
   col.Name = newFieldName;
   col.Type = varType;
   col.DefinedSize = size;

   // Make this field become nullable field.
   col.Attributes = ColumnAttributesEnum.adColNullable;

   // #2
   cat.Tables[tableName].Columns.Append((object)col, DataTypeEnum.adInteger, 0);
   // #3 - Assign default value after column appended.
   if (!string.IsNullOrEmpty(defaultValue))
   {
      col.Properties["Default"].Value = defaultValue;
   }
}

Issues when using Append() method

Issues when using Append() method (Refer #1 in source code above):

1. You only can defines field name, variable type and size for the new field.
2. New field are not allow NULL when created. (Tested in Access 2003).
3. You cannot sets default value for new field.

In order to create nullable column with default value,  I use a ColumnClass object – col, and set the necessary properties.

Few things here:

1. From reference #2, col object need to pass as object type in Append(…) method.
2. When you pass col object into Append() method, the rest of the arguments in Append(…) actually do NOT overwrite the ‘Type’ and ‘DefinedSize’ properties in col object. So the 2^nd argument and 3^rd argument is just to fill up the extra places in Append(…).
3. In reference #3, you only can assign the default value after appended the new field. (Refer to FIX: Cannot Use ADOX to Set Default Value for Column Property)

~~~ [ End ] ~~~

Reference:

1. Rename Field in MS Access Programmatically using ADOX (C#.NET)
2. FIX: Cannot Use ADOX to Set Default Value for Column Property

Keyword: ALTER COLUMNS, ALTER TABLE, Microsoft Access

MS Access is a lightweight database and lack of some database feature, for example it does not support the SQL query to rename the field. Therefore we need to workaround to rename the field in MS Access.

MS Access SQL reference: Microsoft Office Access – SQL Reference

Instead I never had VB6 in my PC but only .NET, so I looking for workaround in .NET. Below are the references:

1. ADO.NET Programmer’s Reference – Chapter 16 – COM Interoperability (Just search for ‘ADOX’ in this article to check for ADOX usage).
2. How to rename the column name in MS Access table (Reference in VB6, Main source for me to convert ADOX into .NET)

You need to add the ADOX COM reference into your .NET project because we are going to use ADOX to do the trick.

Add ADOX COM Reference from Solution Explorer

First you need to to go to Add Reference from Solution Explorer, add Microsoft ADO Ext.2.8 for DDL and Security from COM tab.

Then, use the code below,

using ADOX;

// Skipped namespace and public class here...

private void btnUpdate_Click(object sender, EventArgs e)
 {
   string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Northwind.mdb;Persist Security Info=False";
   CatalogClass cat = new CatalogClass();
   // cat.ActiveConnection = connString    <-- Error here
   // Cannot assign to cat.ActiveConnection directly.
   // Use code at below to assign connection string in C#.

   cat.let_ActiveConnection(connString);

   RenameField(cat, "TargetTable", "Staff", "Employee");

}

private void RenameField(CatalogClass cat, string tableName, string originalFieldName, string newFieldName)
 {
    cat.Tables[tableName].Columns[originalFieldName].Name = newFieldName;
}

Run the button btnUpdate_Click to make the trick work, so the field Staff will rename to Employee.

Note that you cannot assign the connection string directly to cat.ActiveConnection since it is not a string object, and I guess it only accept Connection object from ADO. Use cat.let_ActiveConnection(connString) instead.

Micorsoft example on ADOX connection string – PRB: Unhandled Exception When You Set ADO Property to a String in Visual C# .NET

~~~ End ~~~

Upcoming article: Add New Field in MS Access Programmatically using ADOX (C#.Net)

Reference:

1. Microsoft Office Access – SQL Reference
2. ADO.NET Programmer’s Reference – Chapter 16 – COM Interoperability
3. How to rename the column name in MS Access table
4. http://support.microsoft.com/kb/309047

Keyword:  ALTER COLUMNS, ALTER TABLE, Microsoft Access

Today I facing the exact situation written in reference #1, I tried to pass the property to TryParse out parameter.

Below is the sample code:

int i;

public int I
{
    get;
    set;
}

int.TryParse(str, out I);   // pass I (not i) as out parameter

But I got this error message:

A property or indexer may not be passed as an out or ref parameter.

Till now I cannot get any satisfactory explanation about this error, however I think the most acceptable answer for now is from reference #2.

The fact that a property looks and feels like a variable is deceiving. When compiled, a property becomes two methods: one for the Getter and one of the Setter. This is why the property fails when you try to use it in TryParse().

Solution is simple, just place the original variable as out parameter.

This question make me need to understand MSIL one day to find out what happen inside there…

Reference:

1. Mano’s Tech Blog – Different ways to convert string to an integer in C#
2. Why TryParse Can’t Get Property?

Keyword: Accessor, get,set,getter,setter,error

I cannot afford Visio, I don’t like to draw in MS Word, and I just need to draw a simple diagram.

I found this Diagram Designer, it is simple and has everything I needs.

(I can’t remember how i found this software, probably from Sourceforge.net, but it is not there anymore, :-p )

The software is straight forward, drag the shape you want and drop it to the canvas, to format the text inside shape, just follow the text formatting code while do it. For example, to show Bold text, just put \BBold\b in the textbox.

There are more features to discover, and you will find it useful.

Software link: http://meesoft.logicnet.dk/

 

~~~ * ~~~

Keyword: UML, Architect, Flowchart, Graph, Design

Reference: SQL to Select a random row from a database table

Quick reference to article above:

-- Select a random row with MySQL:
SELECT column FROM table
ORDER BY RAND()
LIMIT 1

-- Select a random row with PostgreSQL:
SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

-- Select a random row with Microsoft SQL Server:
SELECT TOP 1 column FROM table
ORDER BY NEWID()

-- Select a random row with IBM DB2
SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

-- Thanks Tim

-- Select a random record with Oracle:
SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Keyword:  random record

There are three types of UDF (refer to reference), however I will talk about Scalar UDF  here (Which means that function only return single value, you may refer to reference links below for other types).

UDF is quite useful for sometimes, ok, lets go to the point:

Environment:

1. MS SQL Server 2000 or above.
2. Northwind sample database from Microsoft.

Objective:

• List all products in ONE row according to supplier id.

You can try it without UDF, do post your method if you know how to do that.

Here is the part of Product data:

Northwind – Products

First: We need to create a function to concat all product names into one line for each supplier id.

USE NORTHWIND
go

CREATE FUNCTION dbo.ConcatProductName (@sSupplierID as varchar(5))
RETURNS VARCHAR(1000)
AS
BEGIN

   DECLARE @sProduct as varchar(500)

   SET @sProduct = ''

   SELECT @sProduct = @sProduct + ProductName + ', ' FROM PRODUCTS
   WHERE SupplierID = @sSupplierID
   --	group by ProductName

   SET @sProduct = Left(@sProduct, Len(RTrim(@sProduct)) - 1)

   RETURN @sProduct
END

Second: Run and create the function above.

Third: Run the query below the check the result:

Select dbo.ConcatProductName(SupplierID) as ProductName

, SupplierID

From Products

Group by SupplierID

-- drop function ConcatProductName

Query with UDF

See? It work great.

Refer to references below to know pros & cons, and more details about UDF.

References:

Northwind & pubs sample database from Microsoft.

User Defined Functions in Microsoft SQL Server 2000

User Defined Functions

Keywords: Customize function in MSSQL

If you install Visual Studio 2005, you would not face this problem, because the commond prompt and other tools already appear in your classical Windows Start Menu.

However if you install Visual Studio Express Edition, you would not get any shortcuts for those tools, let say you need the command prompt which support Visual Studio .Net, you need to type “cmd” in ‘Run’ dialog, and change the directory to .NetFramework folder.

Unfortunately I using Express edition in office, therefore I need to work around to get the ‘Visual Studio Command Prompt’. After compare with ‘real’ Visual Studio command prompt, below is my work around, the key is:

1. Call a new console screen from batch file.
2. Attach necessary path for .Net in new console screen, so you do not need to change to .Net directory when u need the command in .Net.

Please refer to your own .Net Framework version when doing this, example below is just for .Net version 2.0 .

Steps:

1. Create an empty batch file, insert line in # 2 into batch file, DO include double quote.
2. cmd /K “path=%path%;C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727&&cls”
3. Place the batch file in appropriate directory, create a shortcut for the batch file.
4. Select Properties of the shortcut, change ‘Start in:‘ from current directory to ‘C:\(arbitrary directory)’
5. Now double click the shortcut, Windows prompt up a clean cmd console screen which support .Net Framework command (since already setup in PATH).

 

Explaination for step # 2:

• Command string after /K will be carry out and apply to new console process.
• the ‘&&’ is the separator for seperated commands in same line, the line above consists two commands, first is append the path, second is clear the screen after append path.

 

Explanation for steps # 3 and # 4:

• There are two ways to set the PROMPT in console window, the first way already show in steps 3 & 4, so you just put any startup directory you like in step 4.
• The second way, you need to amend step 2 a bit, change it to below:

cmd /K “path=%path%;C:\WINDOWS\Microsoft.NET\Framework\

v2.0.50727&&PROMPT=C:\Windows$G&&cls”

Ya, it is just add in another command for new console screen (Set the PROMPT=C:\Windows$G).

 

Keyword: Visual Studio Console Window, Tool, Tools, IDE, Command prompt