Generate random rows with SQL

The secret to retrieve random rows from database is really simple. Here are some example SQL statements that do not require additional application logic.

SQL Server:
SELECT TOP 1 *
FROM Users
ORDER BY NEWID()

MySQL:
SELECT * FROM Users
ORDER BY RAND()
LIMIT 1;

Oracle:
SELECT * FROM
(SELECT * FROM Users
ORDER BY dbms_random.value )
WHERE rownum = 1

PostgreSQL:
SELECT * FROM Users
ORDER BY RANDOM()
LIMIT 1

Access:
SELECT TOP 1 *
FROM Users
ORDER BY Rnd(UserID)


Cheers!

InvalidOperationException: Cross-thread operation not valid: Control ‘Form1’ accessed from a thread other than the thread it was created on.

When I first time met with this error, it was caused by an update of a windows form control from a separate thread.

Here is the workaround.

// delegation to cross the thread boundary
private delegate void AddComboBoxItemDelegate(object item);

private void AddComboBoxItem(object item)
{
  if (this.comboBox1.InvokeRequired) {
    // Invoke delegate so that the main UI thread is able to update the controls in the form
this.comboBox1.Invoke(new AddComboBoxItemDelegate(this.AddComboBoxItem), item);
  }
  else {
    this.comboBox1.Items.Add(item);
  }
}


Hope this helps. Cheers!

Shrinking Transaction Log File in SQL SERVER

Sometimes it is necessary to shrink a transaction log in a database manually if the log file grows unexpectedly in order to save some disk space.

Here are the steps use to truncate transaction log in a database:

BACKUP LOG <databasename> TO DISK = '<backupfile>'
DBCC SHRINKFILE (<filename>, <targetsize>) WITH NO_INFOMSGS

e.g.:
BACKUP LOG NORTHWND TO DISK = 'C:\NorthwindLog.bak'
DBCC SHRINKFILE (Northwind_log, 100) WITH NO_INFOMSGS

To check your logical file name of your log file, you can run the query as below to get the logical file name something like 'xxx_log':
sp_helpdb '<databasename>'


More information:
http://msdn.microsoft.com/en-us/library/ms189493.aspx
http://support.microsoft.com/kb/907511

CS0234: The type or namespace name 'Linq' does not exist in the namespace 'System.Data' (are you missing an assembly reference?)

Add the line below into web.config:

<add assembly="System.Data.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>

Reference: http://forums.asp.net/t/1264863.aspx

System.Web.Hosting.HostingEnvironmentException: Failed to access IIS metabase

I came across with this error yesterday and just had to share it.

CAUSE: You have installed Internet Information Services (IIS) after the installation of .NET Framework 2.0

RESOLUTION: Repair your .NET Framework 2.0 by running the following command line to reset the IIS registry settings for aspnet user.

C:\WINDWOS\Microsoft.NET\Framework\v2.0.50727>aspnet_regiis -i

How to Register DLLs in C#

The Regsvr32 tool is used to register or un-register DLL or OCX files.

Usage:
Regsvr32 [/u] [/s] [/n] [/i[:cmdline]] dll_name
/u – un-register server
/s – to run silently without display any message boxes
dll_name – indicates the path and file name of the DLL to register. More than one DLL is allowed in this command.

Example:
regsvr32 C:\example.ocx
regsvr32 /u C:\example.ocx


Sometimes we need to register DLLs programmatically, the codes below will show you how to invoke the regsvr32.exe and register a dll file in C#.

using System.Diagnostics;
…
public static void registerDLL(string dllPath)
{
  try {
    //'/s' : indicates regsvr32.exe to run silently.
    string fileinfo = "/s" + " " + "\"" + dllPath + "\"";

    Process reg = new Process();
    reg.StartInfo.FileName = "regsvr32.exe";
    reg.StartInfo.Arguments = fileinfo;
    reg.StartInfo.UseShellExecute = false;
    reg.StartInfo.CreateNoWindow = true;
    reg.StartInfo.RedirectStandardOutput = true;
    reg.Start();
    reg.WaitForExit();
    reg.Close();
  }
  catch(Exception ex) {
    MessageBox.Show(ex.Message);
  }
}

Hope this will help.

Error Message “No more new fonts may be applied in this workbook.” In Excel

I am currently working on a project, which I need to export an excel file with more than 100 charts need to be included. And guess what? Yeah…Microsoft Excel throws me this error when I tried to export data into the excel file:

No more new fonts may be applied in this workbook.

According to Microsoft Help and Support, this problem is being caused because of the enabled Auto Scale setting in my charts and textboxes (I am also adding textboxes in my excel file).

This problem occurs because of the Auto scale setting. When you add a chart to the workbook, the Auto scale setting is enabled by default. This setting causes charts to use two or more fonts instead of one. When you add multiple charts to a workbook with this setting enabled, the font limitation for a workbook may be reached.

For Microsoft Excel 2000 and later, the maximum number of fonts is 512. If you add charts manually or if you copy and paste existing charts, you can reach the font limitation for a workbook. The following is an example of copying existing charts:

§ You create a chart object in the worksheet.
§ You copy and paste the chart object on the same worksheet ten or more times.
§ You then copy the worksheet several times in the same workbook.

So what I do is unchecked all the Auto Scale checkbox in my charts and textboxes. And now I am able to export the data into excel file without any error. :)

To disable Auto Scale in charts or textboxes:
1. Select a chart or textbox.
2. For chart, right-click and select Format Chart Area. For textbox, right-click and select Format Text Box.
3. Under the Font tab, deselect the Auto Scale checkbox.
4. Click OK.

Cheers! :)