How to Date Time Format In SQL Server

SQL Server provides a number of options you can use to
format a date/time string. One of the first considerations
is the actual date/time needed. The most common is the
current date/time using getdate(). This provides the
current date and time according to the server providing
the date and time. If a universal date/time is needed,
then getutcdate() should be used. To change the format
of the date, you convert the requested date to a string
and specify the format number corresponding to the
format needed. Below is a list of formats and an example
of the output:

DATE FORMATS
-------------------------------------------------------------------------------------
Format # |Query (current date: 12/30/2006) | Sample
-------------------------------------------------------------------------------------
1 | select convert(varchar, getdate(), 1) | 12/30/06
2 | select convert(varchar, getdate(), 2) | 06.12.30
3 | select convert(varchar, getdate(), 3) | 30/12/06
4 | select convert(varchar, getdate(), 4) | 30.12.06
5 | select convert(varchar, getdate(), 5) | 30-12-06
6 | select convert(varchar, getdate(), 6) | 30 Dec 06
7 | select convert(varchar, getdate(), 7) | Dec 30, 06
10 | select convert(varchar, getdate(), 10) | 12-30-06
11 | select convert(varchar, getdate(), 11) | 06/12/30
101 | select convert(varchar, getdate(), 101) | 12/30/2006
102 | select convert(varchar, getdate(), 102) | 2006.12.30
103 | select convert(varchar, getdate(), 103) | 30/12/2006
104 | select convert(varchar, getdate(), 104) | 30.12.2006
105 | select convert(varchar, getdate(), 105) | 30-12-2006
106 | select convert(varchar, getdate(), 106) | 30 Dec 2006
107 | select convert(varchar, getdate(), 107) | Dec 30, 2006
110 | select convert(varchar, getdate(), 110) | 12-30-2006
111 | select convert(varchar, getdate(), 111) | 2006/12/30
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------

TIME FORMAT
-------------------------------------------------------------------------------------
8 or 108 | select convert(varchar, getdate(), 8) | 00:38:54
9 or 109 | select convert(varchar, getdate(), 9) | Dec 30 2006 12:38:54:840AM
14 or 114 | select convert(varchar, getdate(), 14) | 00:38:54:840
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------

SAMPLE STATEMENT
-------------------------------------------------------------------------------------
select replace(convert(varchar, getdate(),101),'/','') | 12302006
select replace(convert(varchar, getdate(),101),'/','') + | replace(convert(varchar, getdate(),108),':','') 12302006004426
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------

GETDATE() is field that datetime

How to Store Procedure for Update Multi Record In Table

You can update multi record by using store procedure.
If is very easy and good processes, you not write store procedure
a lot of store procedure for update data a lot of row. but if you know about
update multi data you can reduce of write store procedure.
please see store procedure below:


ALTER PROCEDURE [dbo].[Pro_UpdateTblOption]
@optionOne varchar(50),
@optionTwo varchar(50),
@optionThree varchar(50),
@optionFour varchar(50),
@optionFive varchar(50),
@SMS varchar(30) Output
AS
Update tbl_Option Set parametervalue = @optionOne
Where optionID=1;
Update tbl_Option Set parametervalue = @optionTwo
Where optionID=2;
Update tbl_Option Set parametervalue = @optionThree
Where optionID=3;
Update tbl_Option Set parametervalue = @optionFour
Where optionID=4;
Update tbl_Option Set parametervalue = @optionFive
Where optionID=5;
Set @SMS='Update Successful...'

If is help you easy.

How to Show Header of GridView when Empty Data in C#

In using gridview it always show data when in table have data.
if in table not have data when you use it, not show anything in
gridview. but we can show header by nothing data in table.
If you want to know and want to use it please see code and
practice it as below:

/// Show grid even if datasource is empty

protected void EmptyGridFix(GridView grdView)
{
// normally executes after a grid load method

if (grdView.Rows.Count == 0 &&
grdView.DataSource != null)
{
DataTable dt = null;

// need to clone sources otherwise it will be indirectly adding to

// the original source


if (grdView.DataSource is DataSet)
{
dt = ((DataSet)grdView.DataSource).Tables[0].Clone();
}
else if (grdView.DataSource is DataTable)
{
dt = ((DataTable)grdView.DataSource).Clone();
}

if (dt == null)
{
return;
}

dt.Rows.Add(dt.NewRow()); // add empty row

grdView.DataSource = dt;
grdView.DataBind();

// hide row

grdView.Rows[0].Visible = false;
grdView.Rows[0].Controls.Clear();
}

// normally executes at all postbacks

if (grdView.Rows.Count == 1 &&
grdView.DataSource == null)
{
bool bIsGridEmpty = true;

// check first row that all cells empty

for (int i = 0; i < grdView.Rows[0].Cells.Count; i++)
{
if (grdView.Rows[0].Cells[i].Text != string.Empty)
{
bIsGridEmpty = false;
}
}
// hide row

if (bIsGridEmpty)
{
grdView.Rows[0].Visible = false;
grdView.Rows[0].Controls.Clear();
}
}
}


/// This code below for select data form table. write it in page load in page

protected void LoadGrid()
{
DataSet dsMyDataSet = new Dataset();
SqlDataAdapter Dadapter=new SqlDataAdapter _
("Select * From TableName",Connection);
Dadapter.Fill(dsMyDataSet);


// obtain dataset/datatable from DAL/BAL


grdYourGrid.DataSource = dsMyDataSet.Table[0];
grdYourGrid.DataBind();

this.EmptyGridFix(grdYourGrid);
}

How to Store Procedure For Add Data to Table

Using Store Procedure for add data to table is very important
and all developer like use it. because it provide fast and
good process when you add data to table. this is a example
and code for Add data to table.


USE [SecDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[tbl_Group_AddData]
@ID int,
@Name varchar(50),
@Sex int,
@SMS varchar(100) Output
As
If Exists(Select * From tbl_Name Where ID=@ID)
Begin
Set @SMS='Data exist already. Please Change!'
End
Else
Begin
Insert Into tbl_Group
(
ID,
Name,
Sex
)

Values
(
@ID,
@Name,
@Sex
)
Set @SMS='Insert data successful'
End

If can show message when insert successful and
when have data already.

How to Get Data From Excel Show on Form in VB.Net

If you want to to get data from excel show in form you can
do it by use code below.
1. You must have excel file that have data


2. Open Visual Basic and Design form as below

3. Write code below

Imports System.Data

Public Class ComboBox

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Try

Dim MyConnection As System.Data.OleDb.OleDbConnection

Dim DtSet As System.Data.DataSet

Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

MyConnection = New System.Data.OleDb.OleDbConnection _

("provider=Microsoft.Jet.OLEDB.4.0; “ _

“ Data Source='E:\Computer Magazine\ReadDataFromExcel\ _

ReadDataFromExcel\Employee.xlsx'; “ Extended Properties=Excel 8.0;")

MyConnection.Open()

MyCommand = New System.Data.OleDb.OleDbDataAdapter _

("Select * From [Sheet1$]", MyConnection)

MyCommand.TableMappings.Add("Table", "TestTable")

DtSet = New System.Data.DataSet

MyCommand.Fill(DtSet)

DataGridView1.DataSource = DtSet.Tables(0)

MyConnection.Close()

Catch ex As Exception

MsgBox(ex.ToString)

End Try

End Sub

End Class

4. Show result