locked
Uploading RRS feed

  • Question

  • User-367032067 posted

    I guys.

    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);
                try
                {
                    try
                    {

                        if (FileUpload1.HasFile)
                        {
                            if (CheckFileType(FileUpload1.FileName))
                            {
                                FileUpload1.SaveAs(savePath);

                                string strfilenamea = Server.MapPath(filename);

                                 string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strfilenamea + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";

                                #region.....................

                                //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);

                                excelConnection.Open();
                                OleDbDataReader dReader;
                                dReader = cmd.ExecuteReader();

                                SqlBulkCopy sqlBulk = new SqlBulkCopy(Controller.ConnectionString);
                                sqlBulk.DestinationTableName = "tbl_PinVerification";
                                sqlBulk.ColumnMappings.Add("MatricNo", "MatricNo");
                                sqlBulk.WriteToServer(dReader);
                                Utilities.PortalMessage = "Successfully uploaded";

                                #endregion
                            }
                        }
                    }
                    catch
                    {
                        Utilities.PortalMessage = "The file you are uploading have been previously uploaded. ";
                    }
                }
                catch
                {
                    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";

            }

    Tuesday, March 16, 2010 1:17 PM

All replies

  • User-1878433365 posted

    Hello!

    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");

     


     

     

    Tuesday, March 16, 2010 2:24 PM