Hii !!
I try to update excel file by using Odbc and Oledb objects
with out success.

my problem when i try to update string in cell that the Odbc "think" this cell is have different datatype.

how can i force the Object to put string at cell that have different datatype, or how can i make the object to not "guess " the datatype

i write 2 function that connect to mlay_List.xls
and do some query.


thank's for any help...
this is my code....

public Boolean UpDateExcl_ODBC(string Q)
        {
            OdbcConnection Con = null;
            try
            {
                string FileName = @"Mlay_List.xls";
                string ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + FileName + ";DefaultDir=" + Application.StartupPath + ";Readonly=0;IMEX=1;HDR=Yes;ImportMixedTypes=Text;";
                Con = new OdbcConnection(ConnectionString);
                OdbcCommand cmd = new OdbcCommand(Q);
                cmd.Connection = Con;
                Con.Open();
                cmd.ExecuteNonQuery();
                Con.Close();
                return true;
            }
            catch (Exception ex)
            {
                try { Con.Close(); }
                catch { }
                return false;
            }
        }
public Boolean UpDateExcl_OleDb(string Q)
        {
            OleDbConnection conn = null;
            try
            {
                string FileName = @"Mlay_List.xls";
                string connstr = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + FileName + ";Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=YES;ReadOnly=0;\"";
                //connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Mode=ReadWrite;ReadOnly=false;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

                conn = new OleDbConnection(connstr);
                OleDbCommand cmd = new OleDbCommand(Q, conn);
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception EX)
            {
                conn.Close();
                return false;
            }
        }

What is the value of Q?

Q is query.

both this function are getting Query and execute it;

for example ->

Q = "UPDATE [Sheet$] Set NUMBER_1='IAW 8F.5T',MANU = 'MAGNETI MARELLI',CAR = 'FIAT',CAR_TYPE = 'PUNTO 75',CC = '1.2G',GEAR_BOX = '',YEAR_1 = '1996',MORE_D = '',LOC = 'L3',SN = '3927',SELL_TO = 'mikel',SELL_DATE = '22/01/2010' WHERE SN=3927"

>how can i force the Object to put string at cell that have different datatype, or how can i make the object to not "guess " the datatype

Parameters collection of Command object.

Which are the problem fields? I guess you are trying to store number eg 1991 as strings. This is often a problem with telephone numbers where Excel will drop off leading 0 or +
If you want to force this when typing in Excel you key a single quote first eg: '01234 . You could try prefixing the numbers with a single quote.

Which are the problem fields? I guess you are trying to store number eg 1991 as strings. This is often a problem with telephone numbers where Excel will drop off leading 0 or +
If you want to force this when typing in Excel you key a single quote first eg: '01234 . You could try prefixing the numbers with a single quote.

No,I try to store string like Mikel as "integer" - the Odbc "think" this cell is have integer datatype.

This can occur if the frist eight rows of the column contain only numeric values. By default, the ODBC driver will read the first eight rows and then guess the datatype based on the values. You can specify a higher number in the conenction string but a current bug prevents this from taking effect. Read this support article if you want to try changing the value via the registry.

If you need to have mixed data in a field, try formatting the column in Microsoft Excel as Text (on the Format menu, click Cells, and select the Text option on the Numbers tab) before you enter data.

I am not in a position to try it at the moment but I think prefixing the field with a single quote will force it to text as well. The single quote will be ignored by Excel (except to force text).

Thats true, if you put a single ' on all the values then the numbers will be stored as text and prevent the ODBC from reading the column as a number type.

Thank you Ryshad and CSW110

it's now work for me i putted ' in all column Cell
and now the ODBC looking on it as text
and i can save text in column Cells.

I search alot of this problem on the net and there is not answer about it I sure there is more elegant way to dell whit it but it good for me!!
again
Thank's alot to You.

This question has already been answered. Start a new discussion instead.