Genereate Serial Number in Sql Server 2000 or 2005 query

In Sql Server 2005 and above version you can generate serial number as:

select row_number() over(order by empid) as serial_Number, empid,empname from emp

In Sql server 2000, you can generate serial number as:

select (select sum(1) from emp a where a.empid<=obj.empid) as serial_number,* from emp obj

How to get Disticnt rows/value from a DataSet or Datatable

Following line will give you distict result from dataset/datatable .

DataSet ds=new DataSet();
//code to fill dataset from database.

DataTable dt=new DataTable();
dt=ds.Tables["0"].DefaultView.ToTable(true,”columnName”);
//here true means you want distict result.

Insert Excel data to Sql and Sql table data to Excel file

First execute this procedure

EXEC sp_configure ’show advanced options’, 1;

GO

RECONFIGURE;

GO

EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1;

GO

RECONFIGURE;

GO

After executing procedure then write the query

to insert sql table data to excel file.

INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;HDR=YES;’,
‘SELECT * FROM [SheetText1$]‘)
SELECT * from TableName
_____________________________________________________________________________________

to insert excel data to sql table

Insert into dbo.tableName
select * from openrowset(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;database=C:\contact.xls’,
’select * from [Sheet1$]‘)

NOTE:
Excel columns and Table Columns should be same.

Reset or Reseed Identity on tables with identity column in sql server

Run this command to reset or reseed Identity columns of a table.

exec sp_MSforeachtable
‘IF OBJECTPROPERTY(OBJECT_ID(”?”), ”TableHasIdentity”) = 1
BEGIN DBCC CHECKIDENT (”?”,RESEED,0) END’

Once you run this query on a table it will reset Identity column always.

Finding Client Machine name, User name or Domain name in asp.net

Find Client Machine Name, User Name or Domain Name in Asp.net using Java Script

function GetClientInfo()
{
var net = new ActiveXObject(“wscript.network”);

document.write(“Computer Name : “+net.ComputerName +”‘\n User Name : “+net.UserName+”\n Domain Name : ” +net.UserDomain);

}

Show no data to display message when gridview is empty

Show Message when Gridview is empty

there is 2 method to show message when gridview is empty

setting ShowHeaderWhenEmpty=”true” EmptyDataText=”No records found” property of gridview

using EmptyDataTemplate column of gridview

Update Bulk Data Using GridView

In this post I am updating bulk data at a time using gridview with maintaing transaction.

below controls are under gridview template column.

<asp:Label ID=”lblID” runat=”server” Text=” Width=”30px”>

<asp:Label ID=”txtFaults” runat=”server” Text=” Width=”200px”>

<asp:DropDownList CssClass=”dropdown” ID=”ddlRepair” runat=”server” Width=”100px” SelectedValue=”>

<asp:TextBox CssClass=”textbox” ID=”txtRepairedRemarks” runat=”server” Text=” Width=”180px”>

———————————————————–
DataTable objDt = new DataTable();
SqlDataAdapter objAdapter;
SqlConnection cnn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["con"].ToString());
SqlCommand cmd = new SqlCommand();

//Get values in GridView

cmd = new SqlCommand(“SP_Name”, cnn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(“@Parm”, SqlDbType.Int).Value = 12;

objAdapter = new SqlDataAdapter(cmd,cnn);

objAdapter.Fill(objDt);

gv.DataSource = objDt;
gv.DataBind();

//code to update data in database

private void BeginToUpdate()
{
cnn.Open();
SqlTransaction tran = cnn.BeginTransaction();
cmd.Connection = cnn;
cmd.Transaction = tran;
cmd.CommandText = ” UPDATE FAULTS SET REPAIRED=@parm WHERE FAULT_ID=@id”;
SqlParameter p1 = new SqlParameter(“@parm”, SqlDbType.VarChar);
SqlParameter p2 = new SqlParameter(“@id”, SqlDbType.Int);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);

}

private void CompleteToUpdate()
{
try
{
cmd.Transaction.Commit();
}
catch (Exception ex)
{
//show error
}
finally
{
cnn.Close();
}
}

private void UpdateData(string repaired, Int32 id)
{
try
{
cmd.Parameters[0].Value = Convert.ToChar(repaired);
cmd.Parameters[1].Value = Convert.ToChar(id);
cmd.ExecuteNonQuery();
}
catch
{
cmd.Transaction.Rollback();
}
}

//write this under save/update button onClick event

BeginToUpdate();
foreach (GridViewRow row in gv.Rows)
{
if (row.RowType == DataControlRowType.DataRow)
{
Int32 id = Convert.ToInt32(((Label)row.FindControl(“lblid”)).Text);
string repaired = ((DropDownList)row.FindControl(“ddlRepair”)).SelectedValue.ToString();

if (id > 0 && repaired==”R”)
{
UpdateData(repaired,id);
}
}
}

CompleteToUpdate();

Filter table at runtime from dataset and store it in datatable in c#

In this article I am filtering data from dataset and store it in different different table.

//connect to sql
con = new SqlConnection(“Data Source=sujitkumar\\sqlexpress;Initial Catalog=pubs;Integrated Security=SSPI”);
//Here I have filled up dataset with two table, in first table all records are comming and in second table only unique id are coming from database.
String query = “select * from student;select distinct(id) from student”;
da = new SqlDataAdapter(query, con);
da.Fill(ds);
//here I filled up different different table with unique id.
int count = ds.Tables[1].Rows.Count-1;
for (int i = 0; i <= count; i++)
{
DataView dv = ds.Tables[0].DefaultView;
dv.RowFilter = “id=” + ds.Tables[1].Rows[i][0];

DataTable dt = new DataTable();
dt=dv.ToTable(i.ToString());
ds.Tables.Add(dt);
}

Create dynamic control at runtime and maintain viewstate in ASP.NET

If you want to generate runtime control with your page and you want to maintain viewstate for generated runtime control.

you have to do these things.

first create container control on aspx page like:

now on .cs you have to write to generate control and add control to this table Table1.

first create two static variable at class
public static int NoOfRows = 5;
public static int ctlID = 0;

now write a function to create control

public void createTable()
{
try
{
// Total number of cells per row (columns).
int cellCtr;
// Current cell counter
int cellCnt;

//rowCnt = row;
cellCnt = 4;
DataTable objDT = new DataTable();
objDT = clsInspection.Get_Faults();
for (int Counter = 1; Counter <= NoOfRows; Counter++)
{

// Create new row and add it to the table.
TableRow tRow = new TableRow();
Table1.Rows.Add(tRow);
for (cellCtr = 1; cellCtr <= cellCnt; cellCtr++)
{
// Create a new cell and add it to the row.

TableCell tCell = new TableCell();

if (cellCtr == 1)
{
DropDownList ddl = new DropDownList();
ddl.ID = “ddl_” + ctlID.ToString();
ddl.Width = 170;
ddl.CssClass = “dropdown”;

ddl.DataSource = objDT;
ddl.DataTextField = “Faults”;
ddl.DataValueField = “Fault_ID”;
ddl.DataBind();
ddl.Items.Insert(0, “Select Faults”);
tCell.Controls.Add(ddl);

}
else
{
TextBox txt = new TextBox();
txt.ID = “txt_” + ctlID.ToString();
txt.CssClass = “textbox”;
txt.Width = 170;
tCell.Controls.Add(txt);
}

tRow.Cells.Add(tCell);

ctlID++;

}
}
}
catch (Exception ex)
{
//lblErrorMessage.Text = “Error : ” + ex.Message.ToString();
//mdlPopup.Show();
}
}

now for maintaine viewstate you have to override method
protected override void CreateChildControls()
{
// Here we are recreating controls to persist the ViewState on every post back
if (Page.IsPostBack)
{
ctlID = 0;
NoOfRows += 1;
createTable();
}
else
{
createTable();
NoOfRows = 5;
}
}

the above method will maintane viewstate for your created control.

There will be a button on your page and after clicking on that button one new row will be crete with controls in Table1.