Search code examples
c#excelodbcoledbinsert-update

OLEDB excel insert unwanted character( ' )

After insert in excel formula bar i got unwanted character ' which restricts the field type like date to convert in specific format

query="INSERT INTO [book$] ([Book Name]) VALUES ('" + txtbookname.Text +"')";

This value is of string type after insert

For example: if i have inserted I AM in excel cell it will be the same.

But when going to edit that cell it shows 'I AM. I dont want that ' character at start

Solution

  • The ' (like you almost said) indicates that the cell is of the text type.

    In short, for text values, don't sweat it: this is just Excel's way of saying "this is a text content, not a formula or anything". It doesn't show up anyway. (Historical note: I believe this convention stems from original Lotus 123 compatibility, if memory serves).

    This is to prevent things from going wrong (imagine if you inserted the text =3*7, should it be interpreted as a formula?).

    Now, I haven't used Excel with the OleDB provider before, but I'm almost sure that you can get the desired effect by actually inserting a date (or number, or ...), instead of a string value. E.g.:

    INSERT INTO [book$] ([Copies]) VALUES (42)

    Would insert the cell content 42

    INSERT INTO [book$] ([Copies]) VALUES ('42')

    Would insert the cell content '42