CodeCharge Studio
search Register Login  

Visual PHP Web Development

Visually Create Internationalized Web Applications, Web Reports, Calendars, and more.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> ASP.NET - InMotion Framework

 Excel connection string

Print topic Send  topic

Author Message
kescott


Posts: 49
Posted: 07/30/2008, 5:23 AM

I am trying to connect to an Microsoft Excel spreadsheet and import the file to Microsoft SQL 2005 Express Database Server by using the following code:

//Button Button_Insert Event On Click. Action Custom Code @7-2A29BDB7
// -------------------------
// Write your own code here.
// -------------------------
//Establishing Connection to Excel Spreasheet called Book1.xls
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\fileuploads\temp\" + ((MTFileUpload)sender).FileName + ";Extended Properties='Excel 8.0;HDR=YES;'";

using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
{
excelConnection.Open();
//Getting source data
OleDbCommand command = new OleDbCommand ("Select Data FROM [Data$]", excelConnection);
// Initialize SqlBulkCopy object
using (DbDataReader dr = command.ExecuteReader())
{
// Copy data to destination
string sqlConnectionString = "Data Source=CCSSWEBS02; Initial Catalog=Test;Integrated Security=True";
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "Data";
bulkCopy.ColumnMappings.Add("Data", "Data");
bulkCopy.WriteToServer(dr);
}
}
//Closing connection
excelConnection.Close();
}
// -------------------------
//End Button Button_Insert Event On Click. Action Custom Code


However, now I am gettig the following error message.

System.InvalidCastException: Unable to cast object of type 'InMotion.Web.Controls.MTButton' to type 'InMotion.Web.Controls.MTFileUpload'.

_________________
B.S. of Computer Science
Class of 2005
Norfolk State University
Norfolk, VA
http://www.nsu.edu
View profile  Send private message
NickT

Posts: 18
Posted: 07/30/2008, 12:23 PM

This error occur because you try use ((MTFileUpload)sender) in onclick button event, where sender is MTButton. If you want use fileupload control that situated in the same form that a button, you can use next expression

FormName.GetControl<MTFileUpload>("FileUploadName").FileName
_________________
Nick T.
InMotion Systems
View profile  Send private message
kescott


Posts: 49
Posted: 07/30/2008, 12:37 PM

NickT,
Thanks for assisting me. I need more information on how I can write the expression. I'm not familiar with it. Do you know where I can look?

I am I to replace :

string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\fileuploads\temp\" + ((MTFileUpload)sender).FileName + ";Extended Properties='Excel 8.0;HDR=YES;'";

with the following:

string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\fileuploads\temp\" + FormName.GetControl<MTFileUpload>("FileUploadName").FileName + ";Extended Properties='Excel 8.0;HDR=YES;'";
_________________
B.S. of Computer Science
Class of 2005
Norfolk State University
Norfolk, VA
http://www.nsu.edu
View profile  Send private message
NickT

Posts: 18
Posted: 07/30/2008, 12:49 PM

Yes, your replacement is correct. Also you must use real name of form and FileUpload control instead "FormName" and "FileUploadName".
_________________
Nick T.
InMotion Systems
View profile  Send private message
kescott


Posts: 49
Posted: 07/30/2008, 12:57 PM

According to my HTML code

<form id="NewRecord1" name="{HTMLFormName}" action="{Action}" method="post" enctype="{HTMLFormEnctype}">

<!-- BEGIN FileUpload FileUpload1 --><input id="NewRecord1FileUpload1ControlName" type="hidden" value="{State}" name="{ControlName}">

FormName = HTMLFormName
FileUploadName = NewRecord1FileUpload1ControlName
_________________
B.S. of Computer Science
Class of 2005
Norfolk State University
Norfolk, VA
http://www.nsu.edu
View profile  Send private message
NickT

Posts: 18
Posted: 07/30/2008, 1:04 PM

No, you can use NewRecord1.GetControl<MTFileUpload>("FileUpload1").FileName
_________________
Nick T.
InMotion Systems
View profile  Send private message
kescott


Posts: 49
Posted: 07/30/2008, 1:11 PM

:-/

I not sure why I'm get this error message:
Compiler Error Message: CS1002: ; expected

with this code:

Line 55: //Establishing Connection to Excel Spreasheet called Book1.xls
Line 56: string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\fileuploads\temp\" + NewRecord1.GetControl<MTFileUpload>("FileUpload1").FileName + ";Extended Properties=""Excel 8.0;HDR=YES;""";

_________________
B.S. of Computer Science
Class of 2005
Norfolk State University
Norfolk, VA
http://www.nsu.edu
View profile  Send private message
kescott


Posts: 49
Posted: 07/30/2008, 1:19 PM

Should double hashes '\\' be used instead of single '\'

string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\fileuploads\temp\" + NewRecord1.GetControl<MTFileUpload>("FileUpload1").FileName + ";Extended Properties=""Excel 8.0;HDR=YES;""";


string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Inetpub\\wwwroot\\fileuploads\\temp\\" + NewRecord1.GetControl<MTFileUpload>("FileUpload1").FileName + ";Extended Properties=""Excel 8.0;HDR=YES;""";
_________________
B.S. of Computer Science
Class of 2005
Norfolk State University
Norfolk, VA
http://www.nsu.edu
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

MS Access to Web

Convert MS Access to Web.
Join thousands of Web developers who build Web applications with minimal coding.

CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.