none
Update data into excel as database with OLEDB appear Apostrophe in front of data RRS feed

  • Question

  • It is really weird thing.

    Here is my code

    dim sql as string = "update [Sheet1$] set Att = @att where phone1 = @phone or phone2 = @phone"
    using sqlCom = new system.data.oledb.oledbcommand(sql,connection)
    sqlCom.parameters.add("@att",oledb.oledbtype.varchar).value="Check"
    sqlCom.parameters.add("@phone",oledb.oledbtype.varchar).value=txtPhone.text
    sqlCom.executenonquery()
    end using

    In face it is working fine. But when I open excel file ... in normal view cell the word "Check" is shown but in formula bar it appears like this 'Check

    with another Apostrophe in front. I try different approach such as dim a string = "Check"

    sqlCom.parameters.add("@att",oledb.oledbtype.varchar).value=a

    result still 'Check

    this is not the first time i encounter this. when i wrote insert into [sheet1$] values ('" & txtName.Text & "') I also get those Apostrophe in front however it disappeared when i use parameters.add 

    but this time it comes again.

    Really appreciated with some explanation about this. and thank so much



    Thursday, March 29, 2018 3:56 AM

Answers

  • Hello Paul and Karen

    I do try to

    Dim a as String = "Check" which i think this is enough to make Check as a text ... I really stuck on this one.

    Excel doesn't know that the incoming data will be text. That is why it needs to be defined in the Worksheet/Workbook. Try formatting the column as Text:


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by SoNewBie Friday, March 30, 2018 5:41 AM
    Friday, March 30, 2018 3:43 AM

All replies

  • Hello,

    Best guess (as this is not uncommon) is Excel sees Check as a mixed data type from other cell values in that column where the apostrophe escapes the value to text/string. 

    Try this Google search on Excel apostrophe before text.


    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. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, March 29, 2018 9:31 AM
    Moderator
  • What happens if you define the column(s) format type as Text in Excel? Also, when I use SQL DDL to create an Excel Workbook and Worksheet I do not see the behavior you describe. Unfortunately, the byproduct of using OLEDB is that apostrophes are used to identify the data as Text because Excel doesn't really know what it is supposed to be unless it is defined.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, March 29, 2018 1:50 PM
  • Hello Paul and Karen

    I do try to

    Dim a as String = "Check" which i think this is enough to make Check as a text ... I really stuck on this one.

    Friday, March 30, 2018 2:55 AM
  • Hello Paul and Karen

    I do try to

    Dim a as String = "Check" which i think this is enough to make Check as a text ... I really stuck on this one.

    Excel doesn't know that the incoming data will be text. That is why it needs to be defined in the Worksheet/Workbook. Try formatting the column as Text:


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by SoNewBie Friday, March 30, 2018 5:41 AM
    Friday, March 30, 2018 3:43 AM