none
Error While Reading Excel Data RRS feed

  • Question

  • Error While Reading Excel Data which is Having Punctuation Character like ( ' ) Single Quote From ASP.NET / C#

    i want to Insert Excel data into The Database Table (Sql Server) no matter what kind of data is inserted.

    i m using OLEDbCommand - OLEDbConnection.

    Please Help me, Thanks in Advance.

    Tuesday, October 15, 2013 11:42 AM

Answers

  • I'm not exactly sure what type of problem you are having but you should be using Command Parameters for the INSERT. This will allow you to handle special characters in the values:

           Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                            "Data Source=" & "c:\Test Files\ExcelWB.xls" & ";" & _
                                                            "Extended Properties=""Excel 8.0;HDR=Yes""")
    
            ExcelConnection.Open()
    
            Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [SheetName] (FileName) VALUES (?)", ExcelConnection)
    
            ExcelCommand.Parameters.AddWithValue("Param1", "FileNameValue")
    
            ExcelCommand.ExecuteNonQuery()
            ExcelConnection.Close()
    If you are still having a problem you may want to post a code example and indicate where the error occurs.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, October 15, 2013 12:32 PM
  • Hello,

    Apostrophes if not handled properly will cause issues which is why it is important to use parameterized SQL as Paul indicated. For example if you had O'Brian without parameters the apostrophe is unbalanced and using parameters will balance out the single apostrophe for you.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Friday, October 18, 2013 11:02 AM

All replies

  • I'm not exactly sure what type of problem you are having but you should be using Command Parameters for the INSERT. This will allow you to handle special characters in the values:

           Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                            "Data Source=" & "c:\Test Files\ExcelWB.xls" & ";" & _
                                                            "Extended Properties=""Excel 8.0;HDR=Yes""")
    
            ExcelConnection.Open()
    
            Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [SheetName] (FileName) VALUES (?)", ExcelConnection)
    
            ExcelCommand.Parameters.AddWithValue("Param1", "FileNameValue")
    
            ExcelCommand.ExecuteNonQuery()
            ExcelConnection.Close()
    If you are still having a problem you may want to post a code example and indicate where the error occurs.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, October 15, 2013 12:32 PM
  • Hello,

    Apostrophes if not handled properly will cause issues which is why it is important to use parameterized SQL as Paul indicated. For example if you had O'Brian without parameters the apostrophe is unbalanced and using parameters will balance out the single apostrophe for you.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Friday, October 18, 2013 11:02 AM