Auto number in gridview

<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False"
DataKeyNames="ProductID,ProductName"
DataSourceID="SqlDataSource1"
BackColor="White"
BorderColor="#3366CC"
BorderStyle="None"
BorderWidth="1px"
CellPadding="4"
onselectedindexchanged="GridView1_SelectedIndexChanged">
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<RowStyle BackColor="White" ForeColor="#003399" />
<Columns>
<asp:TemplateField>
<HeaderTemplate>NO</HeaderTemplate>
<ItemTemplate><%#Container.DataItemIndex+1%></ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />
<asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit" SortExpression="QuantityPerUnit" />
<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" SortExpression="UnitPrice" />
<asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock" SortExpression="UnitsInStock" />
<asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder" SortExpression="UnitsOnOrder" />
</Columns>
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
<AlternatingRowStyle BackColor="#CCFFFF" />
</asp:GridView>

Reference

Single quote in sql

USE Northwind

SELECT CHAR(39)+ CompanyName FROM Customers

SELECT a='Thailand''s', b='Thailand'+'''s'

How to use variable as column Name

USE Northwind

DECLARE @SqlQuery VARCHAR(1000)
DECLARE @ColumnName VARCHAR(100)
DECLARE @TableName VARCHAR(100)

SET @ColumnName = N'Address'
SET @TableName = N'Customers'
SET @SqlQuery = N'SELECT '+ @ColumnName+ ' FROM ' + @TableName

EXEC (@SqlQuery)

Error 7405: Heterogeneous queries (SQL Server 2000)

ก่อนคำสั่ง Create Procedure ให้ใส่

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO

http://support.microsoft.com/kb/296769

Replace newline to blank in .NET

For example in C#

protected void Page_Load(object sender, EventArgs e)
{
string str = "Hello \r\n world";
Response.Write(str);
Response.Write("<br/>\n");
str = str.Replace(Environment.NewLine, string.Empty);
Response.Write(str);
}

Then preview in browser and view html source.

* Environtment.NewLine is equal to \r\n

Change temporary datetime regional to SQL Server

If my database set to COLLATE Thai_CI_AS. Then, I select datetime format 106, result below

SELECT CONVERT(VARCHAR,GETDATE(),106)
Result: 26 พ.ค. 2008


If I want to select in english format, I must use command this

SET LANGUAGE us_english
SELECT CONVERT(VARCHAR,GETDATE(),106)
Result: 26 May 2008

SET LANGUAGE us_english
SELECT CONVERT(VARCHAR,GETDATE(),106)

SET LANGUAGE thai
SELECT CONVERT(VARCHAR,GETDATE(),106)

************************************************************

Case Study:
My problem, I created web application with ASP.NET and the application use datetime in format "dd-MMM-yyyy" for example "16-May-2008"(I set CultureInfo to "en-US" in Global.asax shown below).

void Application_BeginRequest(object sender, EventArgs e)
{
// Code that runs on application startup
System.Globalization.CultureInfo vNewCulture = new System.Globalization.CultureInfo("en-US");
System.Threading.Thread.CurrentThread.CurrentCulture = vNewCulture;
}

In test environment I connected the application to SQL Server database(in same IIS). The server set regional language to "en-US". When I query data from database, result is nomally.

Then I migrate database to real database server. In the real database server set regional to Thai "th-TH". When the application query data from database server, I founded error.

I fixed this problem by insert "SET LANGUAGE us_english" at the beginning of query data to all query.

************************************************************

http://mark-dot-net.blogspot.com

How to send username and password in ftp url path

If you want to access to ftp://localhost/ with username 'myUserName' and password 'myPassword'. There are 3 method to access this ftp by explorer windows.

Method 1
Step1: Start -> run
Step2: Input ftp://localhost/
Step3: Input username and password into dialog box.
Step4: Access completed.

Method 2 Auto insert username on dialog box
Step1. Start -> run
Step2. Input ftp://myUserName@localhost/
Step3. Input only password into dialog box.
Step4: Access completed.

Method 3 Auto insert username and password in diaglog box.
Step1. Start -> run
Step2. Input ftp://myUserName:myPassword@localhost/
Step3. Access completed.