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.

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

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.