ASP.NET Discussion Boards

Auto format an Excel File and import it into Sql Server 2000 [modified]

Nitin Raj21-Oct-08 22:04

21-Oct-08 22:04

hi,

I am trying to autoformat an Excel (.xls) file and then import it into Sql server 2000 table using C# in Asp.net. But when i try to autoformat the excel file i get the exception "Attempted to read or write protected memory. This is often an indication that other memory is corrupt." Someone please help me to solve this problem

Here is the code.

sing System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

using System.Data.OleDb;

using System.Data.Odbc;

using System.Text;

using Microsoft.Office.Interop.Excel;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

SqlConnection Cn = new SqlConnection("server=193.14.45.137; database= NitDB; User ID=re; Password= g5");

SqlCommand Cm = new SqlCommand("Select * from ExcelFile", Cn);

Cn.Open();

SqlDataReader St = Cm.ExecuteReader();

gdExcelFile.DataSource = St;

gdExcelFile.DataBind();

St.Close();

Cn.Close();

}

public static void PrintProgressBar()

{

StringBuilder sb = new StringBuilder();

sb.Append("<div id='updiv' style='Font-weight:bold;font-size:11pt;Left:320px;COLOR:black;font-family:verdana;Position:absolute;Top:140px;Text-Align:center;'>");

sb.Append(" <script> var up_div=document.getElementById('updiv');up_div.innerText='';</script>");

sb.Append("<script language=javascript>");

sb.Append("var dts=0; var dtmax=10;");

sb.Append("function ShowWait(){var output;output='Reading data from excel...PLEASE WAIT!';dts++;if(dts>=dtmax)dts=1;");

sb.Append("for(var x=0;x < dts; x++){output+='';}up_div.innerText=output;up_div.style.color='red';}");

sb.Append("function StartShowWait(){up_div.style.visibility='visible';ShowWait();window.setInterval('ShowWait()',100);}");

sb.Append("StartShowWait();</script>");

HttpContext.Current.Response.Write(sb.ToString());

HttpContext.Current.Response.Flush();

}

public static void ClearProgressBar()

{

StringBuilder sbc = new StringBuilder();

sbc.Append("<script language='javascript'>");

sbc.Append("alert('Data exported successfully');");

sbc.Append("up_div.style.visibility='hidden';");

sbc.Append("history.go(-1)");

sbc.Append("</script>");

HttpContext.Current.Response.Write(sbc);

}

protected void btnExport_Click(object sender, EventArgs e)

{

PrintProgressBar();

{

OdbcConnection connection;

SqlBulkCopy bulkCopy;

string ConnectionString = @"server=323.65.43.134;database= NitDB; User ID=yu; Password=g0";

string connstr = @"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + fyle.Value;

using (connection = new OdbcConnection(connstr))

{

string[] temp;

string fn1;

fn1 = System.IO.Path.GetFileName(fyle.PostedFile.FileName); \\ fyle is the Html File upload control

temp = fn1.Split('.');

object missing = System.Reflection.Missing.Value;

Microsoft.Office.Interop.Excel.Application excel = default(Microsoft.Office.Interop.Excel.Application);

Microsoft.Office.Interop.Excel.Workbook wb = default(Microsoft.Office.Interop.Excel.Workbook);

excel = new Microsoft.Office.Interop.Excel.Application();

// I get the exception at the below statement.

wb = excel.Workbooks.Open(fn1, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);

excel.Visible = true;

//wb.Activate();

excel.Cells.Select();

excel.Columns.AutoFit();

excel.Rows.AutoFit();

OdbcCommand command = new OdbcCommand("Select * FROM [" + temp[0].ToString() + "$]", connection);

connection.Open();

using (OdbcDataReader dr = command.ExecuteReader())

{

using (bulkCopy = new SqlBulkCopy(ConnectionString))

{

bulkCopy.DestinationTableName = "ImportedExcel";

bulkCopy.WriteToServer(dr);

}

dr.Close();

}

}

bulkCopy.Close();

connection.Close();

}

ClearProgressBar();

}

}

Nitin Raj Bidkikar

modified on Wednesday, October 22, 2008 9:11 AM

A Session Question

Xiaoming Qian21-Oct-08 21:45

Xiaoming Qian

21-Oct-08 21:45

Hi everybody:
In my web application,I store some information(UserName etc.) in Session.
In a admin's web page,I want to list all the active Sessions and its data.Also, I wiil be able to stop any Session.
Is there any way?

Re: A Session Question

eyeseetee21-Oct-08 22:08

eyeseetee

21-Oct-08 22:08

Xiaoming Qian wrote:
I want to list all the active Sessions and its data.

http://www.velocityreviews.com/forums/t81765-list-of-active-sessions.html

http://classicasp.aspfaq.com/general/how-do-i-access-all-active-sessions-on-the-server.html

Whats the difference between a triangle and Tottenham Hotspur?
A triangle has 3 points.

Using RFID in asp.net

mukesh.mr0321-Oct-08 21:29

mukesh.mr03

21-Oct-08 21:29

hi,
in my asp.net web application, i want to implement one attendance marking system for employees. can we use RFID in web applications. Is there any other way to do this.
thanks in advance
Mukesh.

application variables question!

mr_muskurahat21-Oct-08 20:39

mr_muskurahat

21-Oct-08 20:39

can anybody guide me that i am interesting in taking more than 100 application variables with following hard coded values...

application("abc")= "value"
application("abc_code")="101"

application("def")= "value"
application("def_code")="101"

application("ghi")= "value"
application("ghi_code")="101"

will these variable effect the performance of that website or not??
if yes then what is the alternative solution.??

umerumerumer