[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

[MSSQL] Dynamic Table Fields Content

Below sample showing dynamically get field from 2 table and join them for its similar fields and then join the field names into string after that execute to get its value.

declare @columns VARCHAR(max)
declare @sql VARCHAR(max)
select @columns=coalesce(@columns + ', ', '') + cast(t1.column_name AS varchar(100)) from (
select * from table_field_name
where table_name='table1'
) as t1
inner join
(select table_name, column_name from (
select * from table_field_name
where table_name='table2'
) as ta) t2
on t2.column_name=t1.column_name
set @sql = 'select ' + @columns + ' from ' + 'table1'
print @sql
exec (@sql)

[MSSQL] List Out All Table Fields In The Database

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'
ORDER BY sysobjects.name,syscolumns.colid

[MSSQL] Attach DB Using Query

exec sp_attach_single_file_db @dbname = 'database_name',
@physname = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\database_name.mdf'

[.NET] Load/Unload images into/from DB table

Introduction

We all often need to store Binary Large Objects (BLOBs) into a DB table and then get them from there. Now I'm going to explain the easiest way to do this.

Prepare Database

Run SQL Server Enterprise Manager and create a new database, call it 'Test'. Create a new table and call it Images.

CREATE TABLE Images ([stream] [image] NULL)

That's all you need.

Store Image into DB table

Collapse

...
byte[] content = ReadBitmap2ByteArray(fileName);
StoreBlob2DataBase(content);
...
protected static byte[] ReadBitmap2ByteArray(string fileName)
{
 using(Bitmap image = new Bitmap(fileName))
 {
   MemoryStream stream = new MemoryStream();
   image.Save(stream, System.Drawing.Imaging.ImageFormat.Bmp);
   return stream.ToArray();
 }
}

protected static void StoreBlob2DataBase(byte[] content)
{
  SqlConnection con = Connection;
  con.Open();
  try
  {
    // insert new entry into table
     SqlCommand insert = new SqlCommand(
    "insert into Images ([stream]) values (@image)",con);
    SqlParameter imageParameter =
    insert.Parameters.Add("@image", SqlDbType.Binary);
    imageParameter.Value = content;
    imageParameter.Size  = content.Length;
    insert.ExecuteNonQuery();
  }
  finally
  {
     con.Close();
  }
}

Store Images for OLEDB provider

Some of us use OLEDB provider to communicate with SQL Server. In this case you should use the code below to store images into your DB. Pay attention to using '?' instead of '@image' in the SQL query.

protected static void StoreBlob2DataBaseOleDb(byte[] content)
{
  try
  {
     using(OleDbConnection con = Connection)
     {
        con.Open();

        // insert new entry into table
         using(OleDbCommand insert = new OleDbCommand(
            "insert into Images ([stream]) values (?)",con))
        {
           OleDbParameter imageParameter =
           insert.Parameters.Add("@image", OleDbType.Binary);
           imageParameter.Value = content;
           imageParameter.Size  = content.Length;
           insert.ExecuteNonQuery();
        }
     }
  }
  catch(Exception ex)
  {
     // some exception processing
   }
}

Get Image from DB table and show it

 // get image
 DataRowView drv = (DataRowView) _cm.Current;
byte[] content = (byte[])drv["stream"];
MemoryStream stream = new MemoryStream(content);
Bitmap image = new Bitmap(stream);

ShowImageForm f = new ShowImageForm();
f._viewer.Image = image;
f.ShowDialog(this);

[VPC] Convert .vhd (virtualpc) file to .vdi (virtualbox) format

convert .vhd (virtualpc) file to .vdi (virtualbox) format

From .vhd to .vdi

When I tried to run xp vhd, provided from microsoft, to test websites on multiple versions of IE (cf. How to test your website on IE6 & IE7 thanks to virtualization), it occurs that each .vhd file has been generated with the same UUID, preventing them from being loaded at the same time in VirtualBox (v2.2).

Although VirtualBox can now transparently handle .vhd natively, I chose to convert these .vhd files into .vdi format so that they have each a unique uuid and can be loaded at the same time in VirtualBox.

Below are the steps:

To do this, I proceeded as follow:

1. install qemu package (sudo apt-get install qemu)
2. convert .vhd file: qemu-img convert -O raw myfile.vhd myfile.bin
3. once the file has been converted, we convert it to .vdi format using VBoxManage: VBoxManage convertfromraw myfile.bin myfile.vdi –variant standard
   note1: “variant” is specified to ensure we have an expandable vdi file (.bin file is 17Gb, .vdi file will be only 1.5Gb)
   note2: in previous version, “convertfromraw” command was called “convertdd”

That’s it.

Simply load your vdi file within VirtualBox, do it for all three xp .vhd files and you can now use all these VM more easily.

Please refer to: http://www.qc4blog.com/?p=721

[VPC] Convert a stand-alone VHD for use in VMWare

Ok, I worked out a solution - at least for me.

It turns out that Converter will convert a VHD - you have to trick it.:

Using a random backup VPC that I had (You could download any demo virtual PC from Microsoft to do this, I think), I edited the VMC file manually to add the following under (substitute your own path and VHD file):

1. E:\VPC2007\DpDisk2\DpDisk2.vhd .\DpDisk2.vhd and then save the VMC file.

2. In VMWare Converter, for source type, select Other.

3. For Virtual Machine, browse to your edited VMC file.

4. On Source Data, you have a couple options. You can convert all the drives, or you can convert only selected drives. I had no reason to convert the active system drive; I only wanted to convert my one data drive. You could, of course, convert the entire virtual machine including all drives. I didn't want to spend the time, so I chose the Select volumes option and only selected drive 1. I left the checkboxes for Ignore page file and for Create separate disks cleared. When I click next at this point, I get a warning about having not selected an active system disk but I could continue anyway. You could avoid this warning by converting the whole VPC but if you only want a single stand-alone drive, why wait for the time it takes to convert the system disk?

5. For destination type, make sure you choose Other Virtual Machine.

6. On Name and Location, put in a fake or random name. Remember that this virtual machine will not be used - it will be useless. We only want the drive that gets created with the machine. You will have to browse to an appropriate location for storing the good VMDK along with the throw-away VMX files.

7. Select the Virtual Machine Options that you want. Unfortunately, this process does not give you the option of selecting the disk type. Even though the source, for me, was an IDE VHD, VMWare Convert converted it as a SCSI drive. They should add an option here to select the interface for the converted drive.

8. Take the defaults on the Networks step. Your VMWare machine won't work anyway so we don't care about the network.

9. Take the defaults on the Customization screen. You don't care about any of these options for this purpose.

10. On the Ready to Complete screen, click Finish.

Here's the output on the Converter window as the process ran:

Step 1 : Connecting to VMware Converter Agent on localhost
Step 2 : Creating target virtual machine and converting data
Configuring parameters for the target virtual machine...
Creating target virtual machine...
Cloning volume...
Updating boot sectors...
Adjusting drive letter mappings...
WARNING: Failed to adjust drive letter mappings
Step 3 : Preparing target virtual machine
ERROR: Unknown error returned by VMware Converter Agent

After about 70% completion, the completion percentage indication immediately jumped to 97% and I got the warning and error messages shown above. I thought my experiment had failed but when I checked the log, I found that the import task succeeded and a later step, DoReconfig, failed. That goes along with the output of the process. The part of the process I needed had succeeded. I added the newly created VMDK to the VM that I needed it in and it worked as expected.

Maybe this will help someone else save some time.

Please refer to: http://communities.vmware.com/thread/188367

[.Net] Resetting your web application or web site without recycling app pool or IIS.

If you don't know by now you can reset your web applications and or web sites with a simple change to the web.config. For example you can FTP(if remote or hosted) to your server and edit the web.config by adding a space or removing a space. Basically you want the application your using editing with think something has changed since you touched the document. Save the form after this has happened and IIS will recycle the application / web site.

This however can be tedious and just well feel like a bit of hacking. So what I have come up with is not rocket science and you most likely do not want in a easy to access area on your web form.

I add this little snippet to a button event on the form. So when ever the site needs a cleansing for what ever reason I push the button of recycle.

public bool RecycleApplication()
{
bool Success = true;

//Method #1
// It requires high security permissions, so it may not
// work in your environment
try
{
HttpRuntime.UnloadAppDomain();
}
catch (Exception ex)
{
Success = false;
}

//if (!Success)
//{
// //Method #2
// // By 'touching' the Web.config file, the application
// // is forced to recycle
// try
// {
// string WebConfigPath = HttpContext.Current.Request.PhysicalApplicationPath + "\\\\Web.config";
// IO.File.SetLastWriteTimeUtc(WebConfigPath, DateTime.UtcNow);
// }
// catch (Exception ex)
// {
// Success = false;
// }
//}

return Success;

}
protected void Button1_Click(object sender, EventArgs e)
{
RecycleApplication();
}



Once again probably not a standards compliant and not an elegant way of recycling the application / web site. However it does the job.

Please refer to: http://weblogs.asp.net/mikedopp/archive/2008/03/29/resetting-your-web-application-or-web-site-without-recycling-app-pool-or-iis.aspx