|
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=321.14.45.137; database= NitDB; User ID=sa; Password= g");
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=23.23.44.136;database= NitDB; User ID= fg; Password=g";
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
|
|
|
|
|
This code works fine for me.
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();
wb = excel.Workbooks.Open(@"c:\test.xls", missing, missing,
missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing);
excel.Visible = true;
Check that the file you are trying to open definitely exists. Try testing out just this block of code in a separate test application.
[BTW: If you use <pre> </pre> tags around your code it makes it easier to read.]
Simon
|
|
|
|
|
hello
my Question is about Multi Threads On Multi Processor platforms.
how to be Implement Multi Threads in C# on Multi Processors that the Processors have multi core (e.g 4 core for per Processors).
Distribute of Thread Between core done by OS or done by appliction programer ????
|
|
|
|
|
The distribution is done by the Operating System.
If you want to implement extra threads, then check out the BackgroundWorker-component. There are multiple articles on the CodeProject that explain both the BackgroundWorker and how to create threads from code.
|
|
|
|
|
|
What Operating System does you main server use?
|
|
|
|
|
Windows Server 2003
Thankq
|
|
|
|
|
Would this one work for you?
http://www.codeproject.com/KB/IP/ListNetworkComputers.aspx
|
|
|
|
|
The Link which u have send is the desktop Application.I have tryed that also but it could not help me out.What i want is for Web Application....
Plz Its Urgent for me.....
ThankQ
|
|
|
|
|
Do you want the list of the names of the computers attached to your local network?
Or do you want to know the name of the computer over the internet?
--Edit--
The word "Urgent" is ignored here. Everyone is in a rush these days
|
|
|
|
|
Sorry to Use the word Urgent....
I want the list of the names of the computers attached to local network?
ThankQ....
|
|
|
|
|
anithagaraga wrote: Sorry to Use the word Urgent
Ah, lots of people with questions tend to add the phrase "urgent". We're not being paid, so we don't like to be rushed..
I downloaded the source-code that was attached to the article, and have modified it to work in a web-environment. The modifications are simple; added two using-directives and removed exception-handling. Also, I added the NetworkBrowser-class to the code-file of the ASP.NET page. The rest of the code hasn't been modified.
The code below is a simple webpage, which uses the code from the aforementioned article;
using 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.Runtime.InteropServices;
using System.Security;
public partial class t_Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
NetworkBrowser nb = new NetworkBrowser();
foreach (string pc in nb.getNetworkComputers())
{
ListBox1.Items.Add(pc);
}
}
}
#region NetworkBrowser CLASS
///
/// Provides a mechanism for supplying a list of all PC names in the local network.
/// This collection of PC names is used in the <see cref="ListNetworkComputers.frmMain">
/// form
///
/// This class makes use of a DllImport instruction. The purpose of which is as follows:
/// When a DllImport declaration is made in managed code (C#) it is a call to a legacy
/// unmanaged code module, normally a C++ Dynamic Link Library. These C++ Dll's are
/// usually part of the operating system API, or some other vendors API, and must be
/// used to carry out operations that are not native within the managed code C# framework.
/// This is fairly normal within the windows world. The only thing that needs careful consideration
/// is the construction of the correct type of STRUCTS, object pointers, and attribute markers,
/// which all contribute to making the link between managed (C#) and unmanaged code (C++)
/// more seamless
///
/// This class makes use of the following Dll calls
/// <list type="bullet">/// <item>
/// <description> Netapi32.dll : NetServerEnum, The NetServerEnum function lists all servers
/// of the specified type that are visible in a domain. For example, an application can call
/// NetServerEnum to list all domain controllers only or all SQL servers only.
/// You can combine bit masks to list several types. For example, a value of 0x00000003
/// combines the bit masks for SV_TYPE_WORKSTATION (0x00000001) and SV_TYPE_SERVER (0x00000002).
///
///
/// <item>
/// <description> Netapi32.dll : NetApiBufferFree, The NetApiBufferFree function frees
/// the memory that the NetApiBufferAllocate function allocates. Call NetApiBufferFree
/// to free the memory that other network management functions return.
///
/// ///
public sealed class NetworkBrowser
{
#region Dll Imports
//declare the Netapi32 : NetServerEnum method import
[DllImport("Netapi32", CharSet = CharSet.Auto, SetLastError = true),
SuppressUnmanagedCodeSecurityAttribute]
///
/// Netapi32.dll : The NetServerEnum function lists all servers
/// of the specified type that are visible in a domain. For example, an
/// application can call NetServerEnum to list all domain controllers only
/// or all SQL servers only.
/// You can combine bit masks to list several types. For example, a value
/// of 0x00000003 combines the bit masks for SV_TYPE_WORKSTATION
/// (0x00000001) and SV_TYPE_SERVER (0x00000002)
///
public static extern int NetServerEnum(
string ServerNane, // must be null
int dwLevel,
ref IntPtr pBuf,
int dwPrefMaxLen,
out int dwEntriesRead,
out int dwTotalEntries,
int dwServerType,
string domain, // null for login domain
out int dwResumeHandle
);
//declare the Netapi32 : NetApiBufferFree method import
[DllImport("Netapi32", SetLastError = true),
SuppressUnmanagedCodeSecurityAttribute]
///
/// Netapi32.dll : The NetApiBufferFree function frees
/// the memory that the NetApiBufferAllocate function allocates.
/// Call NetApiBufferFree to free the memory that other network
/// management functions return.
///
public static extern int NetApiBufferFree(
IntPtr pBuf);
//create a _SERVER_INFO_100 STRUCTURE
[StructLayout(LayoutKind.Sequential)]
public struct _SERVER_INFO_100
{
internal int sv100_platform_id;
[MarshalAs(UnmanagedType.LPWStr)]
internal string sv100_name;
}
#endregion
#region Public Constructor
///
/// Constructor, simply creates a new NetworkBrowser object
///
public NetworkBrowser()
{
}
#endregion
#region Public Methods
///
/// Uses the DllImport : NetServerEnum with all its required parameters
/// (see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/netmgmt/netmgmt/netserverenum.asp
/// for full details or method signature) to retrieve a list of domain SV_TYPE_WORKSTATION
/// and SV_TYPE_SERVER PC's
///
/// <returns>Arraylist that represents all the SV_TYPE_WORKSTATION and SV_TYPE_SERVER
/// PC's in the Domain
public ArrayList getNetworkComputers()
{
//local fields
ArrayList networkComputers = new ArrayList();
const int MAX_PREFERRED_LENGTH = -1;
int SV_TYPE_WORKSTATION = 1;
int SV_TYPE_SERVER = 2;
IntPtr buffer = IntPtr.Zero;
IntPtr tmpBuffer = IntPtr.Zero;
int entriesRead = 0;
int totalEntries = 0;
int resHandle = 0;
int sizeofINFO = Marshal.SizeOf(typeof(_SERVER_INFO_100));
try
{
//call the DllImport : NetServerEnum with all its required parameters
//see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/netmgmt/netmgmt/netserverenum.asp
//for full details of method signature
int ret = NetServerEnum(null, 100, ref buffer, MAX_PREFERRED_LENGTH,
out entriesRead,
out totalEntries, SV_TYPE_WORKSTATION | SV_TYPE_SERVER, null, out
resHandle);
//if the returned with a NERR_Success (C++ term), =0 for C#
if (ret == 0)
{
//loop through all SV_TYPE_WORKSTATION and SV_TYPE_SERVER PC's
for (int i = 0; i < totalEntries; i++)
{
//get pointer to, Pointer to the buffer that received the data from
//the call to NetServerEnum. Must ensure to use correct size of
//STRUCTURE to ensure correct location in memory is pointed to
tmpBuffer = new IntPtr((int)buffer + (i * sizeofINFO));
//Have now got a pointer to the list of SV_TYPE_WORKSTATION and
//SV_TYPE_SERVER PC's, which is unmanaged memory
//Needs to Marshal data from an unmanaged block of memory to a
//managed object, again using STRUCTURE to ensure the correct data
//is marshalled
_SERVER_INFO_100 svrInfo = (_SERVER_INFO_100)
Marshal.PtrToStructure(tmpBuffer, typeof(_SERVER_INFO_100));
//add the PC names to the ArrayList
networkComputers.Add(svrInfo.sv100_name);
}
}
}
catch (Exception ex)
{
networkComputers.Add(ex.Message);
return networkComputers;
}
finally
{
//The NetApiBufferFree function frees
//the memory that the NetApiBufferAllocate function allocates
NetApiBufferFree(buffer);
}
//return entries found
return networkComputers;
}
#endregion
}
#endregion
I wish thee "Happy Programming"
|
|
|
|
|
Its running with out any Errors but i am not getting any result from this code.
I have tried like this
protected void Page_Load(object sender, EventArgs e)
{
Response.Write(Request.ServerVariables["LOGON_USER"]);
}
then it is showing my computer name only.But i want all the users Computer Names.
Thank You.....
|
|
|
|
|
The code which you have sent is same as the link which you have sent.I am working for the web application but you have given the code for desktop application....
|
|
|
|
|
anithagaraga wrote: The code which you have sent is same as the link which you have sent
True!
anithagaraga wrote: .I am working for the web application but you have given the code for desktop application....
It is not. The example is a desktop-application, but the code to retrieve the computer-names can be used in both. I have posted an example of how to use this code from a webpage a few posts' ago.
Look for yourself, here it is;
http://www.codeproject.com/script/Forums/View.aspx?fid=1649&msg=2775380
That code runs in a browser and shows a Listbox that contains all the names of the computers that are present in the local network.
Good luck
|
|
|
|
|
But its not working boss.... i am not getting any result in the Listbox....
|
|
|
|
|
Can you see a list of computers when you open the Windows Explorer? Please check the node "My Network Places\Entire Network\Microsoft Windows Network".
Are there more computers in the list, or can you only see your own computer there?
|
|
|
|
|
no i can't see my computer name also.....I am getting empty listbox when i run the application...
|
|
|
|
|
I'm sorry, seems I cannot help here..
|
|
|
|
|
That's the problem with just handing over code. There's no attempt on the part of the receiver to try and understand it and modify it to their own needs.
|
|
|
|
|
Hi,
First of all Thanks alot for helping me yesterday.I have done some changes in that code & right now its working.....Thanks Boss...
|
|
|
|
|
You're welcome and well done
|
|
|
|
|
Hello,
I am using crystal report 2008 to generate report.
I have gathered some data programmatically to display in columns. I using sub reports for each column and i can display the data simply as columns.
How can i give a data grid layout in report to display the data?
I should be able to:
1). Merge header row if more then one columns are associated under same header.
2). The sum or total value of the column should be displayed in dedicated row for Total rather then just at the end of column.
I am very new to crystal report and trying learn it.
Thanks a lot.
|
|
|
|
|
The help of CR 2008 is your best friend.
1) I am not sure I understand what you meant with this one but I am confident that it can be done...
Check for Cross-Tabs and see if it helps.
2) For this one check for Running Totals in help file.
Good luck,
Always keep the Murphy Rules in mind!
|
|
|
|
|
Hi Friends,
how to create 30day trail application using visual studio deployement tools.if possible please send any sample code for that.
Can i use registry entries for creating trail version?
Thanks.
|
|
|
|
|