Load Excel file into Database using FileUpload Control Error - ASP.NET Discussion Boards

Actually I need editable grid view for mvc 2.0, not for dynamic database

i have to write a code for

for copying a selected data from excel sheet to data grid
now i m confused that whether i have to open the excel sheet with the program of had to show it to the form because i can't show the whole sheet to the datagrid (as its a requirement of a program) and after opening the sheet how i can copy the data from excel sheet display it on the datagrid

any ideas ??????

Use this function

public static DataTable getDataTable(string strFilename)
    {
        string SQL = "select * from [Sheet1$]";
         string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFilename + ";";
        connectionString += @"Extended Properties=""Excel 12.0;HDR=YES;""";
        OleDbConnection conn = new OleDbConnection(connectionString);
        OleDbCommand cmd = new OleDbCommand(SQL, conn);
        DataTable dt = new DataTable();
        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
        da.Fill(dt);
        da.Dispose();
        conn.Dispose();
        cmd.Dispose();
        return dt;
    }

Gaurav Dudeja
http://www.gdinfotechindia.com

Dont be afraid of changing your life to better !

thnx for ur reply

but

it is simply the binding of excel sheet to datagrid
but i have to copy selected data from excel sheet

means when user click on certain record in excel sheet it gets copied in datagrid

okzzz

and plz do tell me also that how to open excel sheet with program for copying the data from it

thnx in advance

sorry i never do this so no idea.

Gaurav Dudeja
http://www.gdinfotechindia.com

Dont be afraid of changing your life to better !

i have one datalist and i have taken textbox in headerTemplate of DataList.

i want to get value of that TextBox in Button_click() which is outside of Datalist,

i have used foreach loop for getting textbox value but i m not able to get

so plz help me

Hai I have a problem while working with calender control.
****If i have selected from date from calender1 as friday and to date from calender2 as monday, calculated the difference between these days by using timespan.
but my problem is friday to monday should come for two days by excluding weekends.
plz reply.

Dear friends, I am uploading Excel sheet data using FileUpload control and want to store the data into sql server 2005 database. Excel Sheet file name is same as Table name in Database and Column headings are same as columns in database table. In my machine, I don't have MS Office. I am using OpenOffice now. I am getting an error "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly." . I have written the following complete code. Please help me. Its very urgent. Please please please. The code is

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.OleDb;

public partial class _Default : System.Web.UI.Page
{
SqlConnection connection=new SqlConnection("data source=10.0.3.30;initial catalog=rambasedev;user id=sa;password=admin");
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
{
Boolean fileOK = false;
String path = Server.MapPath("~/UploadedFiles/");
if (FileUpload1.HasFile)
{
String fileExtension =
System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
String[] allowedExtensions = { ".xls", ".xlsx" };
for (int i = 0; i < allowedExtensions.Length; i++)
{
if (fileExtension == allowedExtensions[i])
{
fileOK = true;
}
}
}

if (fileOK)
{
try
{

string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath(FileUpload1.FileName) + ";" + "Extended Properties=Excel 8.0;";
using (OleDbConnection connection = new OleDbConnection(xConnStr))
{
OleDbCommand command = new OleDbCommand("Select EMPNO,ENAME,BASIC FROM [Sheet1$]", connection);

connection.Open();

// Create DbDataReader to Data Worksheet
using (OleDbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=10.0.3.30;Initial Catalog=RambaseDev;Integrated Security=True";

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "SamplePS";
bulkCopy.WriteToServer(dr);
}
}
}
}
catch (Exception ex)
{
Success.Text = "File could not be uploaded.";
}
}
else
{
Success.Text = "Cannot accept files of this type.";
}
}

}
}

Please help me.

Regards,
Dileep

For .xlsx files I think u will need another connection string

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename.xlsx;Extended Properties=Excel 12.0;

u can also Add HDR property to connection string
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename.xlsx;Extended Properties=Excel 12.0;HDR=YES;

HDR=YES --> implies that the file has header info in the 1st Row

Try this let me know if it works or not

Tej Aj,
http://opexsolution.com/forum/[^]

Dear Tej,

I have tried Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename.xlsx;Extended Properties=Excel 12.0; for .xlsx files. I am getting the following error. Please help me...

ex = {"The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."}

Regards,
Dileep

may be u don't have excel providers installed

try installing 2007 Office System Driver: Data Connectivity Components from below link

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en[^]

They are used to install drivers on the machine not having excel installed on it.

Tej Aj,
http://opexsolution.com/forum/[^]

protected void Page_Load(object sender, EventArgs e)
   {
      HtmlGenericControl div = (HtmlGenericControl)Page.FindControl("mydiv");
       if (div != null)
       {
           div.InnerHtml = "test message";
       }
   }

i used this code, but not working? is there any solution? i search google but cant get relevant answer. please help me.

The obvious question is why not add the runat attribute?

I know the language. I've read a book. - _Madmatt

if i did add runat="server" i can access mydiv.innerText from .cs page.
please let me know if any way to access that.

When you add a runat=server attribute to an html element it is automatically wrapped as a HtmlGenericControl. Your first attempt gained you nothing.

So rather than this

HtmlGenericControl div = (HtmlGenericControl)Page.FindControl("mydiv");
if (div != null)
{
  div.InnerHtml = "test message";
}

You could have this

div.InnerText = "test message";

I know the language. I've read a book. - _Madmatt

when i add runat="server" no need to access via

HtmlGenericControl div = (HtmlGenericControl)this.FindControl("mydiv");

please check this image[^]

thank you for your time.

You can not access a Div without add runar="server" in code behind.
But you can get input element without add runar="server" like this:

in code behind

string pName = Request.Form['pName'];

Hello!

Is there an example project for ASP.NET www.codeproject.som. In which would be implemented to work with the database, loginizatsiya user, and work with the sessions. It is possible, and other functions, but the main three.

Hello,

I have created many user controls with custom properties, methods, etc. to help interact between my business layer and presentation layer. I have also developed a custom page class that is derived from the base Page class. Everything works well if I create my aspx pages statically and by this I mean insert the markup directly into the aspx pages source.

I had an idea to try and make my application more user friendly and have my pages source come from xml files defined by the end user. Since all of my custom server controls are in a single assembly, I figured I could use reflection to create the controls and insert them into the page. I have the code for this written, however, when I add the controls to the page (using reflection), I cannot access the base page class from within my custom controls.

Example.

I have a base page called UIBase dereived from System.Web.UI.Page.

I have a server control that is derived from System.Web.UI.Control. Within my custom control, I have a property that returns the controls page cast as the UIBase (which ALL of my pages within the site are set as via web.config)

public readonly property CtlPage() as UIBase
Get
Return ctype(me.page,UIBase)
End Get
end property

When I use reflection, I get an error within the return statement above. Unable to cast webform3_aspx as UIBase. I can dynamically add the controls via the page_init and add them to the page with no problem. This error only happens when reflection is used....

Also note that the UIBase class is in the same assembly as all other controls. Could this be the problem?

Here is my code that uses reflection to add the controls to the page.

Dim ctlAssem As System.Reflection.Assembly
Dim an As System.Reflection.AssemblyName = New System.Reflection.AssemblyName("MyWeb.Web.UI.Controlls")
an.CodeBase = "MyWeb.Web.UI.Controlls.dll"
ctlAssem = System.Reflection.Assembly.LoadFile("../../MyWeb.Web.UI.Controls.dll") 'shortened for readability

Private Sub initControls()
Dim mst As uibasepage_master = Page.Master

Dim xmlDoc As New XmlDocument()
xmlDoc.Load(Server.MapPath("~/page_source/Employee.xml"))

Dim root As XmlNode = xmlDoc.DocumentElement
createControls(root, mst.BodyContent)

End Sub

Private Sub createControls(ByRef parentNode As XmlNode, ByRef parentCtl As Control)

If parentNode.ChildNodes.Count > 0 Then
For Each childNode As XmlNode In parentNode.ChildNodes
Dim ctlType As Type = ctlAssem.GetType("MyWeb.Web.UI.Controls." & childNode.Name)
Dim ctl As Control = Activator.CreateInstance(ctlType)

For Each ctlAttr As XmlAttribute In childNode.Attributes
Dim pi As System.Reflection.PropertyInfo = ctlType.GetProperty(ctlAttr.Name)
If pi.PropertyType Is GetType(String) Then
pi.SetValue(ctl, ctlAttr.Value, Nothing)
ElseIf pi.PropertyType Is GetType(Boolean) Then
If ctlAttr.Value.ToString.ToLower = "true" Then
pi.SetValue(ctl, True, Nothing)
Else
pi.SetValue(ctl, False, Nothing)
End If
End If

Next

parentCtl.Controls.Add(ctl)
If childNode.HasChildNodes Then
createControls(childNode, ctl)
End If
Next

End If
End Sub

Thanks to anyone that can help. This has drove me nuts for a few days now.

What are you using the UIBase for? Sounds to me like you should be using a Master page instead. Then you can get to the master using this.Page.Master.

Thanks for the reply. I am using the UIBase page as I have a few different master pages included within the project. I use the UIBase page to dynamically register client scripts depending on which controls or current page. Also, I use the UIBase page to capture errors from the content pages and controls.

I have a problem that I'm not sure what to do about.

I have a asmx web service (as opposed to WCF) that I deployed to an existing website which resideson a secure server (https).

I have a silverlight 3 app on the same site that is supposed to use this service, but when it tries to call one of the methods, I get this exception:

The provided URI scheme 'https' is invalid; expected 'http'. Parameter name: via at System.ServiceModel.Channels.TransportChannelFactory`1.ValidateScheme(Uri via) at System.ServiceModel.Channels.HttpChannelFactory.ValidateCreateChannelParameters(EndpointAddress remoteAddress, Uri via) at System.ServiceModel.Channels.HttpChannelFactory.OnCreateChannel(EndpointAddress remoteAddress, Uri via) at System.ServiceModel.Channels.ChannelFactoryBase`1.InternalCreateChannel(EndpointAddress address, Uri via) at System.ServiceModel.Channels.ChannelFactoryBase`1.CreateChannel(EndpointAddress address, Uri via) at System.ServiceModel.Channels.ServiceChannelFactory.ServiceChannelFactoryOverRequest.CreateInnerChannelBinder(EndpointAddress to, Uri via) at System.ServiceModel.Channels.ServiceChannelFactory.CreateServiceChannel(EndpointAddress address, Uri via) at System.ServiceModel.ClientBase`1.ChannelBase`1..ctor(ClientBase`1 client) at FMDashboard2.FMDashboardReference.FMDashServiceSoapClient.FMDashServiceSoapClientChannel..ctor(ClientBase`1 client) at FMDashboard2.FMDashboardReference.FMDashServiceSoapClient.CreateChannel() at System.ServiceModel.ClientBase`1.CreateChannelInternal() at System.ServiceModel.ClientBase`1.get_Channel() at FMDashboard2.FMDashboardReference.FMDashServiceSoapClient.FMDashboard2.FMDashboardReference.FMDashServiceSoap.BeginGetListOfOrganizations(GetListOfOrganizationsRequest request, AsyncCallback callback, Object asyncState) at FMDashboard2.FMDashboardReference.FMDashServiceSoapClient.BeginGetListOfOrganizations(AsyncCallback callback, Object asyncState) at FMDashboard2.FMDashboardReference.FMDashServiceSoapClient.OnBeginGetListOfOrganizations(Object[] inValues, AsyncCallback callback, Object asyncState) at System.ServiceModel.ClientBase`1.InvokeAsync(BeginOperationDelegate beginOperationDelegate, Object[] inValues, EndOperationDelegate endOperationDelegate, SendOrPostCallback operationCompletedCallback, Object userState)

When I try to browse directly to the web service, I get this asp.net exception:

Server Error in '/fm/tools/batsnet/FMDashboardService' Application. -------------------------------------------------------------------------------- Configuration Error Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately. Parser Error Message: Could not load type 'BATS.NET.Auditing.WebAuthenticationFailureAuditEvent'. Source Error: Line 185: <add name="Audit Events" type="System.Web.Management.WebFailureAuditEvent" startEventCode="0" endEventCode="2147483647"/> Line 186:  Line 187: <add name="Auth Failure Audits" type="BATS.NET.Auditing.WebAuthenticationFailureAuditEvent" startEventCode="0" endEventCode="2147483647"/> Line 188:  Line 189: <add name="Membership Audit Events" type="BATS.NET.Auditing.WebMembershipAuditEvent" startEventCode="0" endEventCode="2147483647"/> Source File: D:\Web\aetc\fm\tools\batsnet\web.config Line: 187

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

I have a dropdownlist2 in a gridview whose datasource and select control parameter is based on the selected value of dropdownlist1 in the same gridview. Everything works great for insert, but when I edit the row in the gridview and change the selected value in dropdownlist1, I get an error of "'dropdownlist2' has a SelectedValue which is invalid because it does not exist in the list of items. Parameter name: value" This kinda makes sense to me. At what stage (maybe drowdownlist1 selectedindexchanged?) do I tell dropdownlist2 that it needs to requery so I don't get the error. I can just tell it to select the 0 index by default here for dropdownlist2. An example would be lovely.

HI all,

I have value stored in DATETIME variable in "2010-03-15 08:00:55" this format

but i want to know to 15 march 2010 is a sunday or monday .......how to do this

Thanks and regards,

Amit Patel

Hiding in plain sight in the documentation

DateTime dt = DateTime.Parse("2010-03-15 08:00:55");
if(dt.DayOfWeek == DayOfWeek.Sunday || dt.DayOfWeeek == DayOfWeek.Monday)
{

}

I know the language. I've read a book. - _Madmatt