Last post Mar 16, 2010 02:24 PM by aderegil
Mar 16, 2010 01:17 PM|Oladipo|LINK
am tryg to load a recod from an .xls file it works directly on my personal system but on geting to a test server it fails and also some record are existing already how do i go about uploading the record that is not in the database a leave out those that
are existing before becouse its generating an error that the record is existing and none of the record are uploaded. below is the code that am using for it.Also am facing the challenge of telling a client to name the sheet in the .xls with these "matric_generate_File"
because am hard coding it how can i make it flexible so that the client can enter the name as it is in the excel sheet.
The matric no is the primary key in the microsoft sql server.
protected void btnUpload_Click(object sender, EventArgs e)
string filename = FileUpload1.PostedFile.FileName;
string saveDir = @"";
string appPath = Request.PhysicalApplicationPath;
string savePath = appPath + saveDir + Server.HtmlEncode(FileUpload1.FileName);
string strfilenamea = Server.MapPath(filename);
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strfilenamea + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select * from [matric_generate_File$]", excelConnection);
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(Controller.ConnectionString);
sqlBulk.DestinationTableName = "tbl_PinVerification";
Utilities.PortalMessage = "Successfully uploaded";
Utilities.PortalMessage = "The file you are uploading have been previously uploaded. ";
Utilities.PortalMessage = "The file is not supported.You can only upload .xls file format.";
//check if file to be uploaded is with the extenstion name xls.
bool CheckFileType(string fileName)
return Path.GetExtension(fileName).ToLower() == ".xls";
Mar 16, 2010 02:24 PM|aderegil|LINK
I do not see any option on the BulkCopy class to only insert new records. So here's an idea!
Use a WorkTable with the same structure as your real table.
1. Delete your WorkTable before the bulkcopy
2. Perform the bulkcopy
3. Insert into your RealTable only the non-existing records taken from the WorkTable.
Now about the .xls filename, you do not need your users to name their files in any special format because you can rename it on the server:
string savePath = appPath + saveDir + Server.HtmlEncode("the_name_you_want.xls");