hi all, i need to export lakhs of records to excel, if i use the following code means, it doesn't throws any error, select top 50000 * from dbo.CustomerDetail but instead if i use select * from dbo.CustomerDetail (it contains more than 10lakhs of records), it throws me an error.... Significant loss of functionality - the workbook contains data in cells outside of the row and column limit. data beyond 256(IV) columns by 65,536 rows will not be saved..... my need is, if i have a table, it contains 70,000 records, i need to export it to excel, at that time it should automatically split the datas to two worksheets (which means 65000 in sheet1 and remaining 5000 in sheet2)... code snippet in vb.net: Imports Microsoft.Office.Interop.Excel Imports System.Data.SqlClient Imports System.Threading Public Class Form1 Dim dtstart As DateTime = DateTime.Now Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim strUniqueParamId As String strUniqueParamId = System.Guid.NewGuid().ToString Dim xThread As New Thread(AddressOf excelcheck) xThread.Name = strUniqueParamId xThread.Start() MessageBox.Show("Generating") Timer1.Start() Thread.Sleep(1700) 'MessageBox.Show(Environment.TickCount.ToString()); End Sub Sub excelcheck() ' Create the Excel Application object Dim excelApp As New ApplicationClass() ' Create a new Excel Workbook Dim excelWorkbook As Workbook = excelApp.Workbooks.Add(Type.Missing) Dim sheetIndex As Integer = 0 Dim col, row As Integer Dim excelSheet As Worksheet Dim sqlcon As New SqlConnection("server=SHRIGI104\SHRIUNO0301;database=Test;uid=sa;pwd=welcome3#;") sqlcon.Open() Dim ds As New DataSet Dim Da As New SqlDataAdapter() Dim sqlcmd As New SqlCommand sqlcmd.CommandType = CommandType.StoredProcedure sqlcmd.CommandText = "SampleExcel" sqlcmd.Connection = sqlcon Da.SelectCommand = sqlcmd Da.Fill(ds) sqlcon.Close() ' Copy each DataTable as a new Sheet For Each dt As System.Data.DataTable In ds.Tables sheetIndex += 1 ' Copy the DataTable to an object array Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object ' Copy the column names to the first row of the object array

do u know any other way to do this bulk insert operation to excel sheet from vb.net

hi, pls anyone try to solve my problem... i need to fetch some thousands of records and insert/export that to excel file using vb.net... Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim cnt As New ADODB.Connection Dim rst As New ADODB.Recordset Dim xlApp As Object Dim xlWb As Object Dim xlWs As Object Dim recArray As Object Dim strDB As String Dim fldCount As Integer Dim recCount As Long Dim iCol As Integer Dim iRow As Integer strDB = "UNOGI\UNO;database=Test;uid=sa;pwd=welcome3#" 'here am getting the following error "Could not find installable ISAM." cnt.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDB & ";") rst.Open("select top 10000 * from Test.dbo.CustomerDetail", cnt) xlApp = CreateObject("Excel.Application") xlWb = xlApp.Workbooks.Add xlWs = xlWb.Worksheets("Sheet1") xlApp.Visible = True xlApp.UserControl = True fldCount = rst.Fields.Count For iCol = 1 To fldCount xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name Next If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then xlWs.Cells(2, 1).CopyFromRecordset(rst) Else recArray = rst.GetRows recCount = UBound(recArray, 2) + 1 For iCol = 0 To fldCount - 1 For iRow = 0 To recCount - 1 If IsDate(recArray(iCol, iRow)) Then recArray(iCol, iRow) = Format(recArray(iCol, iRow)) ElseIf IsArray(recArray(iCol, iRow)) Then recArray(iCol, iRow) = "Array Field" End If Next iRow Next iCol ' Transpose and Copy the array to the worksheet, ' starting in cell A2 xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _ TransposeDim(recArray) End If ' Auto-fit the column widths and row heights xlApp.Selection.CurrentRegion.Columns.AutoFit() xlApp.Selection.CurrentRegion.Rows.AutoFit() ' Close ADO objects rst.Close() cnt.Close() rst = Nothing cnt = Nothing ' Release Excel references xlWs = Nothing xlWb = Nothing xlApp = Nothing End Sub

Dim fileInfo1 As FileInfo = New FileInfo(M_SqlPathName) --up to this line of code am getting the server path as D:\attachment\JON103\SQLQuery11.sql Dim script As String = fileInfo1.OpenText().ReadToEnd() --then in script am not getting any path so it throws null exceptional error SqlScript = script in M_SqlPathName am getting the above directory root name, but in next stept script=fileInfo1.OpenText().ReadToEnd() it throws an error, object reference

M_SqlPathName = dsExecu.Tables(1).Rows(0)("AppSet_PhysicalPath") + Utilities.Dtgetsource.Rows(J)("FileDir") + Utilities.Dtgetsource.Rows(J)("FileName") Dim sqlcmdsql As New SqlCommand sqlcmdsql.Connection = sqlCon1 Dim fileInfo1 As FileInfo = New FileInfo(M_SqlPathName) --up to this line of code am getting the server path as D:\attachment\JON103\SQLQuery11.sql Dim script As String = fileInfo1.OpenText().ReadToEnd() --then in script am not getting any path so it throws null exceptional error SqlScript = script pls anyone chk this out and tell me how to get the server path and upload the excel file to it:confused::confused:

hi all, i need a help... need to create a dynamic sub folder to store particular job_id's excel files within the sub folder with job_id's name, here the job_id is identity column.. say for eg., JON101, JON123, etc... sqlcmdsql.Parameters.AddWithValue("@fullFileName", "C:\Bcp\" + Utilities.Jobid + ".xls") the above code is for generating excel file within common folder, now i need to create sub_folder... anyone came across this issue, pls help me to resolve :((

[Message Deleted]

[Message Deleted]

CommandArgument='<%# Bind("ID")>' is it possible to send id&name within the same command argument

set the property as, AppendDataBoundItems="True" Select if(DropDownlist1.selectvalue == "0") { response.write("Select atleast one value"); }

set the property as, AppendDataBoundItems="True" Select

Hi, This is simple just include a plus(+)... Look at the code in which i did some alteration. Header head = new Header(); DataSet obj = new DataSet(); obj = head.getdata(); if (obj.Tables[0].Rows.Count < 0) { int i; for (i = 0; i <= obj.Tables[0].Rows.Count - 1; i++) { string text1 = obj.Tables[0].Rows[i]["id"].ToString(); string text2 = obj.Tables[0].Rows[i]["Name"].ToString(); // Just include a plus(+) lblMessage.Text += text1 + " " + text2; } }

no your tags should properly ended... till then it will rise errors...

Just add a panel in your .aspx page because u need some controls like panel or table or place holder to display the dynamic controls, Your code here, DropDownList selPage = new DropDownList(); selPage.ID = "selPage"; selPage.CssClass = "GVFooterSel"; for (int cnt = 0; cnt < GVDoctor.PageCount; cnt++) { int curr = cnt + 1; ListItem item = new ListItem(curr.ToString()); if (cnt == GVDoctor.PageIndex) { item.Selected = true; } selPage.Items.Add(item); } // add this line and run. Now u could see your dropdown in the interface. Panel1.Controls.Add(selPage); selPage.AutoPostBack = true; selPage.SelectedIndexChanged += new EventHandler(selPage_SelectedIndexChanged); selPage.Attributes.Add("onchange", "selPage_SelectedIndexChanged(this,event)");

Dont use two pages.. instead use multiview so that when displaying it acts like displaing 2 pages. In page load give multiview1.viewindex = 0 (this will display the first view) when u want the pop up jus give multiview.viewindex = 1

Set a property or View State, public string Mode { get { object o = ViewState["Mode"]; if (o == null) return String.Empty; else return (string)o; } set { ViewState["Mode"] = value; } } when clicking the Edit tab give the Mode as Mode = "E" when clicking the View tab give the Mode as Mode = "V" Now write the code based on the mode like, if(Mode == "E") { //Your code goes here.... } if(Mode == "V") { //Your code goes here.... }

just put a multi view and when u click the button in a Grid send the primary key to the select query and display the view(that is your 2nd data view)

hey i have a solution!!!!!!!! Just save the values in Temporary datatable and after that run that temporary datatable in a loop and write the insert in it..... private void SaveGridDataInTempTable() { DataTable dtTemp = new DataTable(); if (dtTemp == null || dtTemp.Columns.Count <= 0) { dtTemp.Columns.Add("sino"); dtTemp.Columns.Add("name", typeof(String)); dtTemp.Columns.Add("gender", typeof(String)); dtTemp.Columns.Add("qualification", typeof(String)); dtTemp.PrimaryKey = new DataColumn[] { dtTemp.Columns["sino"] }; } foreach (GridViewRow gvRow in GridView1.Rows) { DataRow drTemp = dtTemp.NewRow(); drTemp["sino"] = int.Parse(GridView1.DataKeys[gvRow.RowIndex]["sino"].ToString()); drTemp["name"] = ((TextBox)gvRow.FindControl("TextBox1")).Text.Trim(); drTemp["gender"] = ((RadioButtonList)gvRow.FindControl("RadioButtonList1")).SelectedValue.ToString(); drTemp["qualification"] = ((DropDownList)gvRow.FindControl("DropDownList1")).SelectedValue.ToString(); dtTemp.Rows.Add(drTemp); } Temp = dtTemp; } //Property to maintain the Datatable public DataTable Temp { get { object o = ViewState["Temp"]; if (o == null) { DataTable dt = new DataTable(); return dt; } else return (DataTable)o; } set { ViewState["Temp"] = value; } } After this your normal insert code, for(int i=0;i