[MYSQL] Get All The Primary Keys Field Names

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'database'
AND TABLE_NAME = 'table'
AND COLUMN_KEY = 'PRI'

Required Bin dll files from O2003PIA:

* Microsoft.Office.Interop.Excel.dll
* Microsoft.Vbe.Interop.dll
* office.dll

[.NET] Dataset to Excel, It is very simple!

After hours of Googling finally a very simple yet effective way to convert Dataset into Excel file.

Public Sub dataset2Excel(ByVal sqlStr As String)
'excel file path
Dim excelPath As String = ""
excelPath = Server.MapPath("").Replace("\", "") & "\\Sheet1.xls"
'convertion to string
ds = objdb.ExeQuery(sqlStr, "NICEPMData")
Dim grid As New System.Web.UI.WebControls.DataGrid()
grid.HeaderStyle.Font.Bold = True
grid.DataSource = ds
grid.DataBind()
Dim sw As New StreamWriter(excelPath)
Dim hw As New System.Web.UI.HtmlTextWriter(sw)
grid.RenderControl(hw)
End Sub

Cheers!!

[.NET] How To Create an Excel Macro by Using Automation from Visual Basic .NET

Add COM references:
Microsoft Excel <8.0> Object Library, (optional)
Microsoft Office <8.0> Object Library, and
Microsoft Visual Basic for Applications Extensibility Library

NOTE: version must be the same




Please refer to http://support.microsoft.com/kb/303871

[.NET] Reading any text base file

Sample of reading any text base file. Text base means like *.txt, *.js, *.css, etc.

Imports System.IO
...
...
DimFullPath As String
Dim jspath As String = Server.MapPath("folder").Replace("\folder", "") & "\textfile.txt"
...
...
Public Function readFile(ByVal FullPath As String) As String
Dim strContents As String
Dim objReader As StreamReader
Try
objReader = New StreamReader(FullPath)
strContents = objReader.ReadToEnd()
objReader.Close()
readFile = strContents
Catch Ex As Exception
readFile = "read file error"
End Try
End Function

[CSS] Center the web page compatibility for Firefox & IE

* inside the css file is:

body {
text-align: -moz-center;
}

* inside the body tag is:

<@!--[if gte IE 7.0]@>
<@style type="text/css"@>
body {
text-align: center;
}

<@![endif]--@>

NOTE: remove the @ character to get the code

[MSSQL] To view table fields information

There are 2 way to view table field information.

1. select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='table name'
* the field is in order according to the table
* have more information about the table

2. select * from table_field_name where table_name='table name'
* the field is not in order according to the table
* less information about the table

create view table_field_name as
SELECT
table_name=sysobjects.name, column_name=syscolumns.name,
datatype=systypes.name, length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id=syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype='U'

[MSSQL] Format Date

To convert date to string date formatted with mm/dd/yyyy.

SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [MM/DD/YYYY]

To convert date to string date formatted with dd/mm/yyyy.


select convert(varchar,getdate(),103)

[MSSQL] String Functions

String functions are mainly used to change the case of strings,concatenate strings,reverse strings,extract various part of strings and perform many other types of string manipulation.

In SQL Server there is a Several built-in string functions to perform string manipulations.All below functions takes string input value and return a string or numeric value.

ASCII : Returns the ASCII code value of a character(leftmost character of string).

Syntax: ASCII(character)

SELECT ASCII('a')->97
SELECT ASCII('A')->65
SELECT ASCII('1')->49
SELECT ASCII('ABC')->65

For Upper character 'A' to 'Z' ASCII value 65 to 90
For Lower character 'A' to 'Z' ASCII value 97 to 122
For digit '0' to '9' ASCII value 48 to 57

UNICODE : UNICODE function works just like ASCII function,except returns Unicode standard integer value. UNICODE could be useful if you are working with international character sets.

Syntax: UNICODE(character)

SELECT UNICODE('F')->70
SELECT UNICODE('STRING FUNCTION')->83 (leftmost character of string)

LOWER : Convert character strings data into lowercase.

Syntax: LOWER(string)

SELECT LOWER('STRING FUNCTION')->string function

UPPER : Convert character strings data into Uppercase.

Syntax: UPPER(string)

SELECT UPPER('string function')->STRING FUNCTION

LEN : Returns the length of the character string.

Syntax: LEN(string)

SELECT LEN('STRING FUNCTION')->15

REPLACE : Replaces all occurrences of the second string(string2) in the first string(string1) with a third string(string3).

Syntax: REPLACE('string1','string2','string3')

SELECT REPLACE('STRING FUNCTION','STRING','SQL')->SQL Function

Returns NULL if any one of the arguments is NULL.

LEFT : Returns left part of a string with the specified number of characters counting from left.LEFT function is used to retrieve portions of the string.

Syntax: LEFT(string,integer)

SELECT LEFT('STRING FUNCTION', 6)->STRING

RIGHT : Returns right part of a string with the specified number of characters counting from right.RIGHT function is used to retrieve portions of the string.

Syntax: RIGHT(string,integer)

SELECT RIGHT('STRING FUNCTION', 8)->FUNCTION

LTRIM : Returns a string after removing leading blanks on Left side.(Remove left side space or blanks)

Syntax: LTRIM(string)

SELECT LTRIM(' STRING FUNCTION')->STRING FUNCTION

RTRIM : Returns a string after removing leading blanks on Right side.(Remove right side space or blanks)

Syntax: RTRIM( string )

SELECT RTRIM('STRING FUNCTION ')->STRING FUNCTION

REVERSE : Returns reverse of a input string.

Syntax: REVERSE(string)

SELECT REVERSE('STRING FUNCTION')->NOITCNUF GNIRTS

REPLICATE : Repeats a input string for a specified number of times.

Syntax: REPLICATE (string, integer)

SELECT REPLICATE('FUNCTION', 3)->FUNCTIONFUNCTIONFUNCTION

SPACE : Returns a string of repeated spaces.The SPACE function is an equivalent of using REPLICATE function to repeat spaces.

Syntax: SPACE ( integer) (If integer is negative,a null string is returned.)

SELECT ('STRING') + SPACE(1) + ('FUNCTION')->STRING FUNCTION

SUBSTRING : Returns part of a given string.

SUBSTRING function retrieves a portion of the given string starting at the specified character(startindex) to the number of characters specified(length).

Syntax: SUBSTRING (string,startindex,length)

SELECT SUBSTRING('STRING FUNCTION', 1, 6)->STRING
SELECT SUBSTRING('STRING FUNCTION', 8, 8)->FUNCTION

STUFF : Deletes a specified length of characters and inserts another set of characters at a specified starting point.

STUFF function is useful to inserts a set of characters(string2) into a given string(string1) at a given position.

Syntax: STUFF (string1,startindex,length,string2)

SELECT STUFF('STRING FUNCTION', 1, 6, 'SQL')->SQL FUNCTION
SELECT STUFF('SQL FUNCTION', 5, 8, 'Tutorial')->SQL Tutorial

CHARINDEX : Returns the starting position of the specified string(string1) in a character string(string2).

Syntax: CHARINDEX (string1,string2 [,start_location ])

SELECT CHARINDEX('SQL','Useful SQL String Function')->8

SELECT CHARINDEX('SQL','Useful SQL String Function')->19

If string1 is not found within string2,CHARINDEX returns 0.

PATINDEX : PATINDEX function works very similar to CHARINDEX function.PATINDEX function returns the starting position of the first occurrence of a pattern in a specified string, or zeros if the pttern is not found.

Using PATINDEX function you can search pattern in given string using Wildcard characters(%).The % character must come before and after pattern.

Syntax: PATINDEX('%pattern%',string)

SELECT PATINDEX('%SQL%','Useful SQL String Function')->8

SELECT PATINDEX('Useful%','Useful SQL String Function')->1

SELECT PATINDEX('%Function','Useful SQL String Function')->19

If pattern is not found within given string,PATINDEX returns 0.

Please refer to http://www.blogger.com/post-create.g?blogID=999774227547356751

[VPC] Extend Disk Capacity From Remaining Empty Partition

1. At a command prompt, type diskpart.exe.
2. Type list volume to display the existing volumes on the computer.
3. Type Select volume volume number where volume number is number of the volume that you want to extend.
4. Type extend [size=n] [disk=n] [noerr]. The following describes the parameters:
   size=n
   The space, in megabytes (MB), to add to the current partition. If you do not specify a size, the disk is extended to use all the next contiguous unallocated space.
   
   disk=n
   The dynamic disk on which to extend the volume. Space equal to size=n is allocated on the disk. If no disk is specified, the volume is extended on the current disk.
   
   noerr
   For scripting only. When an error is thrown, this parameter specifies that Diskpart continue to process commands as if the error did not occur. Without the noerr parameter, an error causes Diskpart to exit with an error code.
5. Type exit to exit Diskpart.exe.

Please refer to http://support.microsoft.com/kb/325590

[VPC] CONVERTING FAT32 to NTFS in Windows XP

I have successfully converting FAT32 into NTFS with following DOS command.

CONVERT C: /FS:NTFS

Please refer to http://www.aumha.org/win5/a/ntfscvt.php