July Code Blog

There was a failure while initializing the Microsoft Visual SourceSafe source control provider

noreply@blogger.com (xiaoyu) — Fri, 21 Oct 2011 03:18:00 +0000

I kept getting this error when I was trying to open my project.

There was a failure while initializing the Microsoft Visual SourceSafe source control provider. You cannot use this provider to perform source control operations.

Here is the solution:

1. Open VS2005, go to menu Tools -> Options, then click "Source Control"

2. Choose "None" for the drop-down list under "Current souce control plug-in", rather then "Microsoft Visual Source Safe"

Reference:

http://social.msdn.microsoft.com/Forums/en-US/vssourcecontrol/thread/255dfa57-87ab-4db5-ab9b-b79a0c6dc1b7

Cheers!

java.util.ConcurrentModificationException with ArrayList

noreply@blogger.com (xiaoyu) — Mon, 25 Jul 2011 07:09:00 +0000

You will receive java.util.ConcurrentModicationException error, if you are modifying a list while iterating it. For example, the following code will throw you the exception:

for (Object obj : list) {

  if (obj.getCode().equals("something"))

    list.remove(obj);

}

To avoid this exception, the trick is to delete/add an item through the iterator, but not the collection. Here is the example:

Iterator it = list.iterator();

 while (it.hasNext()) {  

  Object obj = (Object)it.next();

  if (obj.getCode().equals("something")) {

    it.remove();

  }

}

Hope this helps.

The referenced assembly could not be resolved because it has a dependency on System.Data.OracleClient

noreply@blogger.com (xiaoyu) — Fri, 06 May 2011 09:05:00 +0000

Received the following build error:

The referenced assembly "xxx" could not be resolved because it has a dependency on "System.Data.OracleClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" which is not in the currently targeted framework ".NETFramework,Version=v4.0,Profile=Client". Please remove references to assemblies not in the targeted framework or consider re-targeting your project.

This build error is caused by the project is targeting .NET Framework 4.0 Client Profile and references to an assembly, which has indirect reference on System.Data.OracleClient.dll that is not included in the .NET Framework 4 Client Profile.

To fix such errors, you can either remove the incorrect assembly reference from the project, or set the project to target to full .NET Framework version 4 instead of .NET Framework 4 Client Profile.

To change the target setting, go to “Project -> Project's Properties -> Application Tab”, and then change to appropriate Target Framework options.

Resize image without distorting it

noreply@blogger.com (xiaoyu) — Tue, 12 Apr 2011 03:08:00 +0000

Has anyone come across a problem where image is being distorted on a webpage?

Here is the simple CSS solution to display images without distorted.

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <style type="text/css">
    .imgresize {
      width: 100px;
      height: 150px;
    }
    .imgresize img {
      width: 100px; // to fix images by width, or
      // height: 150px; // to fix images by height
    }

  </style>

</head>

<body>

  <form id="form1" runat="server">

  </div>

  </form>

</body>

</html>

Note: You need to remove the height and width attribute value from img tag itself.

Reference link http://forums.asp.net/p/1514562/3619288.aspx

"Strict standards : Non-static methods called statically" Error after Joomla Installation

noreply@blogger.com (xiaoyu) — Sun, 22 Aug 2010 04:12:00 +0000

Recently I'm merely setting up a test site on localhost to check out Joomla features. After the installation, there are tons of Strict standards errors appear on my screen when I browse to Joomla homepage. I tried to reinstall several times but it still gave me the same errors.

Finally, I found something useful on Joomla! Discussion Forums.

To solve this issue, simply change the following configuration in your php.ini file.

Change error_reporting = E_ALL | E_STRICT to error_reporting = E_ALL & ~E_STRICT
Change display_errors = On to display_errors = Off

Remember to restart your Apache server after the changes. You should be able to view to Joomla! homepage now.

Resize Textbox/Textarea based on content length

noreply@blogger.com (xiaoyu) — Fri, 30 Jul 2010 01:45:00 +0000

I was asked to implement resizing textbox based on content length in one of my projects, to avoid having to deal with scroll bars.

And here is the code that works for me:

<html>
<head>
<script>
function textAreaResize(o) {
     o.style.height = "1px";
     o.style.height = (25+o.scrollHeight)+"px";
}
</script>
</head>
<body>
<textarea id="textArea1" onkeyup="textAreaResize(this)" style="overflow:hidden"></textarea>
<asp:textbox id="txt1" runat="server" Width="500px" Height="25px" TextMode="MultiLine" onkeyup="textAreaResize(this);"></asp:textbox>
</body>
</html>

Anyway, I found out that this method is works for me only when I type my text in the box.

It is not working when I having no focus on the textbox and call the textAreaResize method to resize it. Position the cursor at the end of the text will resolve the issue.

Try the following code to your page.

<html>
<head>
<script>
function textAreaResize(o) {
     SetCursorToTextEnd(o.id); 
     o.style.height = "1px";
     o.style.height = (25+o.scrollHeight)+"px";
}

function SetCursorToTextEnd(textControlID)
{
  var text = document.getElementById(textControlID);
  if (text != null && text.value.length > 0) {
    if (text.createTextRange) {
                      var FieldRange = text.createTextRange();
                     FieldRange.moveStart('character', text.value.length);
                     FieldRange.collapse();
                     FieldRange.select();
    }
  }
}
</script>
</head>
</html>

Call the textAreaResize method on your page load will do.

Hope it works for you too. =)

Update Query with a JOIN Statement

noreply@blogger.com (xiaoyu) — Wed, 02 Jun 2010 02:54:00 +0000

I found something useful when I am trying to update columns in a master table (tbl1) from data in a temp table (tbl2). These updates can be done by making a query to update a table by doing INNER JOIN.

Below is the example of a joined update query:

UPDATE tbl1
SET field_name = B.field_name
FROM tbl1 A
INNER JOIN tbl2 B
ON A.ID = B.ID

Hope this helps.

Generate random rows with SQL

noreply@blogger.com (xiaoyu) — Tue, 29 Dec 2009 07:59:00 +0000

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.

noreply@blogger.com (xiaoyu) — Fri, 09 Oct 2009 02:40:00 +0000

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

noreply@blogger.com (xiaoyu) — Wed, 16 Sep 2009 04:10:00 +0000

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?)

noreply@blogger.com (xiaoyu) — Tue, 25 Aug 2009 07:29:00 +0000

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

noreply@blogger.com (xiaoyu) — Thu, 11 Jun 2009 02:23:00 +0000

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#

noreply@blogger.com (xiaoyu) — Thu, 12 Feb 2009 08:31:00 +0000

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

noreply@blogger.com (xiaoyu) — Fri, 09 Jan 2009 06:45:00 +0000

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! :)

Write Data to SQL Server Tables Using SqlBulkCopy

noreply@blogger.com (xiaoyu) — Wed, 31 Dec 2008 08:48:00 +0000

SqlBulkCopy is a new feature in ADO .NET 2.0 that speed up a copy operation for a large amount of data from your .NET application to SQL Server tables.

The SqlBulkCopy class can be used to write data only to SQL Server tables from different types of data source, as long as the data can be loaded to a DataTable instance or read with an IDataReader instance.

SqlBulkCopy contains an instance method WriteToServer, which is used to transfer data from a source to the destination table. SqlBulkCopy uses a collection of SqlBulkCopyColumnMapping objects to map a column in a data source to a field in the destination table.

There are some essential properties of SqlBulkCopy that you should be aware of:
  § BatchSize: Number of rows SqlBulkCopy will copy from data source to the destination table.
  § BulkCopyTimeOut: The number of seconds that system should wait to complete the copy operation.
  § ColumnMappings: You can use its Add() method to add in a new SqlBulkCopyColumnMapping object to its collection.
  § DestinationTableName
  § NotifyAfter: SqlRowsCopied event handler will be triggered when the number of rows specified has been copied. This is very helpful if you want to be aware the progress of a copy operation, showing completed % in a progress bar, for example.

Sample Codes
Here, I will demonstrate how to import data from CSV source file into database.

… using System.Data.SqlClient; … … private void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e) {     MessageBox.Show(String.Format("{0} rows have been copied.", e.RowsCopied.ToString())); } private void CopyDataToDestinationTable() {

    string strconn = "
server=localhost;Database=Transaction;User Id=sa;Password=123456;";
    OleDbConnection conn = new OleDbConnection();
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\;Extended Properties=\"text;HDR=Yes;FMT=CSVDelimited\"";

    try {
        conn.Open();

        OleDbCommand myCommand = conn.CreateCommand();

        string commandString = "select * from source.csv";
        myCommand.CommandType = CommandType.Text;
        myCommand.CommandText = commandString;

        OleDbDataReader dataReader = myCommand.ExecuteReader();
        dataReader.Read();

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strconn)) {

            bulkCopy.DestinationTableName = "Products";
            bulkCopy.BulkCopyTimeout = 100000;
            bulkCopy.NotifyAfter = 1000;
            bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
            bulkCopy.ColumnMappings.Add("Brand", "Brand");
            bulkCopy.ColumnMappings.Add("Model", "Model");
            bulkCopy.ColumnMappings.Add("ProductName", "ProductName");

            // call WriteToServer which starts import
            bulkCopy.WriteToServer(dataReader);
        }
    }
    catch (Exception ex) {
        MessageBox.Show(ex.Message);
    }
    finally {
        conn.Close();
    }
}

Well, that’s it. Feel free to ask if there is any problem. Cheers!

BULK INSERT in MS SQL SERVER

noreply@blogger.com (xiaoyu) — Mon, 10 Nov 2008 13:45:00 +0000

In IT environment, it is always a necessary to import data from data files into database.

And recently, I have to deal with import a huge load of data (around 200 MB for 1 CSV input file) from CSV files into MS SQL SERVER. So what I had done to achieve this is by using BULK INSERT.

Firstly, prepare a CSV data file with the following content and save as test.csv.

July,Singapore,25
James,Australia,50
May,China,29

And then run scripts as following to load all the data from the CSV file into database.

--Temp table to store the data
CREATE TABLE CSVTest
   (
    Name VARCHAR(500),
    Country VARCHAR(500),
    Age INT
)
GO

--Bulk insert into CSVTest
BULK INSERT CSVTest
  FROM 'C:\test.csv'
  WITH
     (
    FIELDTERMINATOR=',',
    ROWTERMINATOR = '\n'
      )
GO

--Get all data from CSVTest
SELECT *
   FROM CSVTest
GO

--Drop the temp table
DROP TABLE CSVTest
GO

Kill Excel.exe Process in C# .NET

noreply@blogger.com (xiaoyu) — Mon, 03 Nov 2008 12:38:00 +0000

I am trying to get my C# .NET codes to generate reports into excel files. In the Export function, it creates the excel object, adds in the data and then saves the file.

All of this works fine. But for some reason, the excel.exe process is still running when I check from task manager.

I had tried to quit the excel application. I had tried to use InteropServices.Marshal.ReleaseComObject to get rid of them in .NET. But unfortunately, the excel.exe is still sitting in memory.

Finally, I found a way to kill the process in C# .NET. Take a look on the codes below:

using System.Diagnostics;
using System.Collections;
…
…
…

Hashtable myHashtable;
private void btnExport_Click(object sender, EventArgs e)
{
   // get process ids before running the excel codes
   CheckExcellProcesses();

   // export to excel
   ExportDataToExcel();
 
   // kill the right process after export completed
   KillExcel();
}

private void ExportDataToExcel()
{
   // your export process is here...
}

private void CheckExcellProcesses()
{
  Process[] AllProcesses = Process.GetProcessesByName("excel");
  myHashtable = new Hashtable();
  int iCount = 0;
          
  foreach ( Process ExcelProcess in AllProcesses) {   
    myHashtable.Add(ExcelProcess.Id, iCount);
    iCount = iCount + 1;
  }            
}

private void KillExcel()
{
  Process[] AllProcesses = Process.GetProcessesByName("excel");

   // check to kill the right process
  foreach ( Process ExcelProcess in AllProcesses) {
    if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
                         ExcelProcess.Kill();             
  }

  AllProcesses = null;
}

Hope this helps.

ASP .NET Validators don’t work properly with Update Panel

noreply@blogger.com (xiaoyu) — Thu, 18 Sep 2008 01:24:00 +0000

Recently I am working on a project ASP .NET AJAX. Of course it is very common to have some validators in some forms to control the input values, and unfortunately I experienced problem with validators inside the Update Panel. The validators just don’t work properly as I am expected.

After google searching and here is the solution that I had found: ASP .NET AJAX Validators.

Firstly, downloads the validators.zip file and adds in the DLL into the /bin directory of your project. After that, include the following codes into the pages section of the web.config.

<tagMapping>
  <add tagType="System.Web.UI.WebControls.CompareValidator" mappedTagType="Sample.Web.UI.Compatibility.CompareValidator, Validators, Version=1.0.0.0"/>
  <add tagType="System.Web.UI.WebControls.CustomValidator" mappedTagType="Sample.Web.UI.Compatibility.CustomValidator, Validators, Version=1.0.0.0"/>
  <add tagType="System.Web.UI.WebControls.RangeValidator" mappedTagType="Sample.Web.UI.Compatibility.RangeValidator, Validators, Version=1.0.0.0"/>
  <add tagType="System.Web.UI.WebControls.RegularExpressionValidator" mappedTagType="Sample.Web.UI.Compatibility.RegularExpressionValidator, Validators, Version=1.0.0.0"/>
  <add tagType="System.Web.UI.WebControls.RequiredFieldValidator" mappedTagType="Sample.Web.UI.Compatibility.RequiredFieldValidator, Validators, Version=1.0.0.0"/>
  <add tagType="System.Web.UI.WebControls.ValidationSummary" mappedTagType="Sample.Web.UI.Compatibility.ValidationSummary, Validators, Version=1.0.0.0"/>
</tagMapping>

And the problem solved. Cheers!

OrElse and AndAlso operators in VB .NET

noreply@blogger.com (xiaoyu) — Fri, 29 Aug 2008 06:41:00 +0000

OrElse and AndAlso are two new logical operators in VB .NET and they have some properties that can enhance your codes in two general categories:

1. Avoid executing part of a logical expression.
2. Optimize code by not executing any more of a compound expression than required.

OrElse and AndAlso are quite similar with the And and Or except that VB .NET supports short-circuiting with the OrElse and AndAlso operators. This means that the expressions will only be executed when necessary. Anyway, the And and Or are still present in VB .NET.

For example:

// Assume that str1 = 5,
// x = 1 and y = 1

If x > str1 And y < str1 Then

' code
End If

When performing an And operator in VB .NET, it actually evaluates both of the expressions to get the final outcome. Even the first condition (x greater than str1) returns as FALSE, it still continues to look at the second argument even though it doesn’t need to.

Let’s see how AndAlso evaluates the codes below.

If x > str1 AndAlso y < str1 Then
' code
End If

When using AndAlso, VB .NET knows that the expression will not succeed once it is determined that the first condition (x greater than str1) is FALSE. So it stops evaluating the expression right away without checking the second condition.

The difference of Or and OrElse are also similar to And and AndAlso operator, which Or will check all the conditions and OrElse won’t checking the remaining expression, if it found any of the previous condition is TRUE.

How to get Windows’ Serial Number and Windows Logon User Name in .NET

noreply@blogger.com (xiaoyu) — Tue, 19 Aug 2008 02:19:00 +0000

By using the namespace System.Management, we can easily retrieve the serial number of your Windows OS and windows logon user name.

First of all, you need to add a reference of System.Management into your application. To add a reference into your .NET application, you can right-click on References in the solution explorer and select Add Reference.

From the Add References dialog box, select System.Management and click OK.

The sample codes below written in both C# and VB .NET show you how to read the Windows’ serial number and logon user name.

C# .NET

using System.Management;

public static string getSerialNumber() {
  string _serialNo = string.Empty;

  ManagementObjectSearcher objMOS = new ManagementObjectSearcher("Select * from Win32_OperatingSystem");
  ManagementObjectCollection objMOC;

  objMOC = objMOS.Get();

  foreach (ManagementObject objMO in objMOC) {
    _serialNo = objMO["SerialNumber"].ToString();
  }

  return _serialNo;
}

public static string getlogonUser(){
  string _user = string.Empty;

  ManagementObjectSearcher objMOS = new ManagementObjectSearcher("SELECT * FROM Win32_ComputerSystem");
  ManagementObjectCollection objMOC;

  objMOC = objMOS.Get();

  foreach (ManagementObject objMO in objMOC) {
    _user = objMO ["UserName"].ToString();
  }

  return _user;
}

VB .NET

Imports System.Management

Public Shared Function getSerialNumber() As String
  Dim _serialNo As String = String.Empty

  Dim objMOS As New ManagementObjectSearcher("Select * from Win32_OperatingSystem")
  Dim objMOC As ManagementObjectCollection

  objMOC = objMOS.Get

  For Each objMO As ManagementObject In objMOC
    _serialNo = objMO("SerialNumber").ToString()
  Next

  Return _serialNo
End Function

Public Shared Function getlogonUser() As String
  Dim _user As String = String.Empty

  Dim objMOS As New ManagementObjectSearcher("SELECT * FROM Win32_ComputerSystem")
  Dim objMOC As ManagementObjectCollection

  objMOC = objMOS.Get

  For Each objMO As ManagementObject In objMOC
    _user = objMO("UserName").ToString()
  Next

  Return _user
End Function

Cheers.

Export Data to Excel (Not HTML Table) in C# .NET

noreply@blogger.com (xiaoyu) — Thu, 07 Aug 2008 13:36:00 +0000

In my previous post - Export Data to Excel (Not HTML Tables), I had shown you how to export to excel in VB .NET.

In this post, I will use a C# .NET example instead.

Of cause first thing that you need to do is to add Excel.dll (Microsoft excel 11.0 Object Library) into your .NET project references.

Here is the codes:

private void ExportToExcelFile(System.Data.DataTable dt)
{
   Excel.Application xlsApp = new Excel.ApplicationClass();
   Excel.Workbook xlsWorkbook;
   Excel.Worksheet xlsWorksheet;
   string strhdr;
   int row;
   string strFile = "file1.xls";
   string filename = Server.MapPath(strFile);

   if (dt.Rows.Count > 0)
   {
      //Create new workbook
      xlsWorkbook = xlsApp.Workbooks.Add(true);

      //Get the first worksheet
      xlsWorksheet = (Excel.Worksheet)(xlsWorkbook.Worksheets[1]);

      //Activate current worksheet
      xlsWorksheet.Activate();

      //Set header row to row 1
      row = 1;

      //Add table headers to worksheet
      xlsWorksheet.Cells[row, 1] = "Name";
      xlsWorksheet.Cells[row, 2] = "Gender";
      xlsWorksheet.Cells[row, 3] = "Age";

      //Format header row (bold, extra row height, autofit width)
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Font.Bold = true;
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Rows.RowHeight = 1.5 * xlsWorksheet.StandardHeight;
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).EntireRow.AutoFit();

      //Freeze the columm headers
      xlsWorksheet.get_Range("A" + (row + 1).ToString(), "C" + (row + 1).ToString()).Select();
      xlsApp.ActiveWindow.FreezePanes = true;

      //Write data to Excel worksheet
      foreach (DataRow dr in dt.Rows)
      {
         row += 1;
         if (dr["Name"] != null)
            xlsWorksheet.Cells[row, 1] = dr["Name"];
         if (dr["Gender"] != null)
            xlsWorksheet.Cells[row, 2] = dr["Gender"];
         if (dr["Age"] != null)
            xlsWorksheet.Cells[row, 3] = dr["Age"];
      }

      //Format data rows (align to center and left, autofit width and height)
      xlsWorksheet.get_Range("A2", "C" + row.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
      xlsWorksheet.get_Range("A2", "C" + row.ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
      xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireColumn.AutoFit();
      xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireRow.AutoFit();

      //Make excel workbook visible to user after all data has been added to worksheet.
      xlsApp.DisplayAlerts = false;
      xlsWorkbook.Close(true, filename, null);

      //Export data to client machine
      strhdr = "attachment;filename=" + strFile;
      Response.Clear();
      Response.ContentType = "application/vnd.ms-excel";
      Response.ContentEncoding = System.Text.Encoding.Default;
      Response.AppendHeader("Content-Disposition",strhdr);
      Response.WriteFile(filename);
      Response.Flush();
      Response.Clear();
      Response.Close();
   }
}

Cheers!

How to Insert Value into an Identity Column

noreply@blogger.com (xiaoyu) — Thu, 31 Jul 2008 03:55:00 +0000

In database tables, normally identity columns are used as primary keys that automatically generates numeric values for every new inserted row.

For this identity (AutoNumber) column, you are not allow to insert your own value. So what if you really want to insert your own value into the column?

A very easy way to do this. I will show you how to do this.

Firstly, create a table Sample by using the script below into your database.

CREATE TABLE Sample (
Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(50) NOT NULL )
GO

Normally if we want to insert a new row into this table, you will have to only insert the value of Name, as script below:

INSERT INTO Sample(Name) VALUES ('July')

Now, we try to insert a value into the Id column.

INSERT INTO Sample(Id, Name) VALUES (11, 'July')

When this query is executed, an error will be generated:

Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table 'Sample' when IDENTITY_INSERT is set to OFF.

To insert this record into table without any error, you just need to enable the IDENTITY_INSERT. It should look like this:

SET IDENTITY_INSERT Sample ON

INSERT INTO Sample(Id, Name) VALUES (11, 'July')

SET IDENTITY_INSERT Sample OFF

But there are some points that you need to be aware of about IDENTITY_INSERT.

1. Only 1 table can have the IDENTITY_INSERT property set to ON at a time. If you try to enable IDENTITY_INSERT to ON for a second table while a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server will return an error message.
2. If the value that you have inserted is greater than the current identity seed in your table, the new inserted value will be set as the current identity seed.

For example, table Sample has its current identity value 1 and you want to insert a new row with the identity value 11. When you insert another new row, your identity value will start from 12, instead of 1.

SET IDENTITY_INSERT Sample ON

INSERT INTO Sample(Id, Name) VALUES (11, 'July')

SET IDENTITY_INSERT Sample OFF

INSERT INTO Sample(Name) VALUES ('August')

Result:

Id Name 11 July 12 August

Cheers.

How to Call Remote Web Services from Client Side JavaScript

noreply@blogger.com (xiaoyu) — Fri, 25 Jul 2008 13:18:00 +0000

Web Services is a collection of universally available functions, which allows different applications from different sources to share among each other without time-consuming custom coding by using the XML, SOAP, WSDL and UDDI open standards across the Internet. You can go to here for more detail explanation on it.

Recently, I have been doing some research into how to call a web service from client side JavaScript. ASP .NET AJAX enables you to call a web service from the browser by using client script, namely the page can call server-based methods without a postback and refreshing the whole web page.

Here, I will show you how to call Web Services within the same project and also how to call Web Services remotely.

Consuming a Local Web Service
Simple example of Web Service
I assume that you had installed the ASP .NET AJAX framework. So let us start with create a new ASP .NET AJAX-Enabled Web Application created.

I will firstly show you how to call a web service within the same project, so let’s add new Web Service called webService.asmx to the project (Right-click your project on your solution explorer | Add New Item | Select Web Service and name it | Click Add.).

Let us create a very simple web service that returns current date and time.

In order to make a web service accessible from script, the web service class must be qualified with the ScriptServiceAttribute. See the example below:

...
using System.Web.Script.Services;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ScriptService]
public class WebService : System.Web.Services.WebService
{
  ...
  [WebMethod]
  public string GetCurrentDateTime() {
    string curTime = String.Format("Current datetime is {0}.", DateTime.Now);
    return curTime;
  }
}

And then save it.

Exposing Web Services to Client Script in .aspx Web Page
To enable a web service to be called from client script in your .aspx file, make sure that we have one instance of ScriptManager object.

Then add the Services collection to the ScriptManager object, reference the web service by adding asp:ServiceReference child element to the Services collection and sets its Path attribute to point to the web service. Below is how it is looks like:

<body>
  <form id="form1" runat="server"> 
    <asp:ScriptManager ID="ScriptManager1" runat="server">
      <Services>
      <asp:ServiceReference  Path="WebService.asmx" />
      </Services>
    </asp:ScriptManager>
  </form>
</body>

Client-side Function, Button and Label
Next we need to add in a client-side function to call server-side web service, a button to trigger the web service, and a label to display the returned result. Below is the sample of the web page:

<html>
<head runat="server">
<title>Calling Web Services</title>
<script language="javascript" type="text/javascript">
function GetTime() {
  WebService.GetCurrentDateTime(ShowResult);
}

function ShowResult(result) {
  document.getElementById("lblResult").innerHTML = result.value;
}
</script>
</head>
<body>
  <form id="form1" runat="server">
    ...
    <asp:Label id="lblResult" runat="server">Result will be displayed here.</asp:Label>
    <br/>
    <button id="btn" name="btn" onclick="GetTime()">Get Time</button&gt;
    <br/>
  </form>
</body>
</html>

TIPS: If you want to call the JavaScript function from a separate .js file, copy the functions into the .js file, and the add in Scripts collection to the ScriptManager object, reference the JavaScript function by adding asp:ScriptReference child element to the Scripts collection and sets its Path attribute to point to the JavaScript, as below:

<Scripts>
<asp:ScriptReference Path="CallWebServiceMethods.js" />
</Scripts>

That’s it for consuming web service, which located in the same project.

Consuming Web Services Remotely
WebService Behavior (HTC file)
The first thing that you need to do is to create a separate ASP .NET Web Service application and copy the web service that we created as above into it. I will add another method here to show you how to call a web service and pass parameters to its method through JavaScript.

[WebMethod]
public string EchoYourWord(string yourword) {
  return "You have said that: " + yourword;
}

Save and run it. So now you got your web service ready to test.

WebService behavior enables us to make a method call to a remote web service using a scripted language. It is a reusable DHTML component that uses web services by handling the communication of the SOAP data packets between the browser and the web services.

The following are 3 important methods supported by the web service behavior:

createUseOptions – Allows us to preserve authentication information across remote method invocations. Can be very useful when using SSL to communicate with the remote web service

callService – Allows us to invoke the remote web service method asynchronously

useService – Allows us to establish a friendly name for the web service that can be used while invoking the web service

You can download the HTC file at here. Copy into the same folder as your project.

How to use Web Service Behavior in .aspx Page
I will recommend you that to create another new .aspx page to test on consuming remote web service.

To invoke the GetCurrentDateTime() method, we have to attach the HTC file to a valid element, such as DIV or BODY.

<div id="service" style="BEHAVIOR:url(webservice.htc)" ></div>

Once this behavior is attached, we may need to add some code to the web page to enable use of WebService behavior and make calls to the web service.

Add in this JavaScript function, as below:

function Init() {
  // initialize and create a name "webService" for Web service
  // Note: the "service.use..." is the DIV or BODY element’s id as created just now.
  service.useService("http://localhost/My_WebServices/WebService.asmx?wsdl",  "webService");
}

Next, we will need to invoke the useService method in the onload event to map the web service before any methods on the web service are invoked.

After that, we can add in another JavaScript function and use the callService method to invoke the remote web service method asynchronously.

Your web page should look like this when you are done:

<html>
<head runat="server">
<title>Calling Web Services</title>
<script language="javascript" type="text/javascript">
function Init() {
  // initialize and create a shortcut name "webService" for Web service
  // Note: the "service.use..." is the DIV or BODY element’s id as created just now.
  service.useService("http://localhost/My_WebServices/WebService.asmx?wsdl",  "webService");
}

function GetTime() {
  // Note: the "service.webService..." is the shortcut name of your web service that you had initialize in the Init().
  service.webService.callService(ShowResult, "GetCurrentDateTime");      
}

// sample of passing a parameter to the web service
var myword;
function Echo() {
  myword = document.form1.SayOutLoud.value;
  service.webService.callService(ShowResult, "EchoYourWord", myword);
}

function ShowResult(result) {
  document.getElementById("lblResult").innerHTML = result.value;
}
</script>
</head>
<body onload="Init()">
  <form id="form1" runat="server"> 
    <div id="service" style="BEHAVIOR:url(webservice.htc)" ></div>
    <asp:Label id="lblResult" runat="server">Result will be displayed here.</asp:Label>
    <br/><br/>
    Your words : <input type="text" name="SayOutLoud"/><br/><br/>
    <button id="btn" name="btn" onclick="GetTime()">Get Time</button><br />
    <button id="btn1" name="btn1" onclick="Echo()">Echo Your Word</button><br/>
    <br/>
  </form>
</body>
</html>

Note: Of course, if you are using a separate .js file, you still need to add a reference in a ScriptManager object, as I had mentioned above.

That’s it for calling a remote web service. Any question please feels free to ask.

Cheers!

Displaying Date and time in VB Script with ASP Classic

noreply@blogger.com (xiaoyu) — Fri, 11 Jul 2008 07:53:00 +0000

To display the Date and Time in your ASP page with certain format is very simple indeed. Take a look at the examples as below:

<%=time()%>
- Get current time
- e.g. 9:52:30 AM
<%=date()%>
- Get current date
- e.g. 7/11/2008
<%=now()%>
- Get current date and time
- e.g. 7/11/2008 9:54:03 AM
<%=FormatDateTime(now,0)%>
- e.g. 7/11/2008 9:55:41 AM
<%=FormatDateTime(now,1)%>
- e.g. Friday, July 11, 2008
<%=FormatDateTime(now,2)%>
- e.g. 7/11/2008
<%=FormatDateTime(now,3)%>
- e.g. 9:57:49 AM
<%=FormatDateTime(now,4)%>
- e.g. 09:58
<%=WeekDay(now)%>
- Day of the week
- e.g. 6
<%=WeekDayName(WeekDay(now))%>
- e.g. Friday
<%=Day(date)%>
- Day of the month
- e.g. 11
<%=Month(date)%>
- Month of the year
- e.g. 7
<%=MonthName(Month(date))%>
- e.g. July
<%=Year(date)%>
- Current year
- e.g. 2008
<%=Right(Year(date),2)%>
- Current year
e.g. 08
<%=Hour(now)%>
- Hour part
- e.g. 10
<%=Minute(now)%>
- Minute part
- e.g. 11
<%=Second(now)%>
- Second part
- e.g. 2

Hope you will find this helpful.

Couldn’t find ConfigurationManager in System.Configuration?

noreply@blogger.com (xiaoyu) — Thu, 19 Jun 2008 10:38:00 +0000

I am trying to pull an appSettings key from my web.config. In .NET 1.1, we usually use:

System.Configuration.ConfigurationSettings.AppSettings["strConnection"].ToString();

However, in .NET2.0, using this code will cause the following error:

'System.Configuration.ConfigurationSettings.AppSettings' is obsolete: 'This method is obsolete, it has been replaced by System.Configuration!System.Configuration.ConfigurationManager.AppSettings'

So I just change my code to the following, according to the error above. But the problem is I can’t find ConfigurationManager in the System.Configuration.

The reason is because the original System.Configuration.ConfigurationSettings class is point to the System.dll assembly. There is another new the System.Configuration.dll assembly with the new ConfigurationManager class. In this case, you need to add in the System.Configuration.dll as a reference to your application before you can use it.

Hope this will help someone.