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
|
|
|
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 |
|
|
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
|
|
|
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 |
|
|
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
|
|
|
NickT
Posts: 18
|
Posted: 07/30/2008, 1:04 PM |
|
No, you can use NewRecord1.GetControl<MTFileUpload>("FileUpload1").FileName
_________________
Nick T.
InMotion Systems |
|
|
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
|
|
|
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
|
|
|