excel update using oledb and IMEX=1???

Hi All,

I am new at this and getting frustrated...I have an excel sheet I am trying to read into a dataset then modify the dataset and then write the modified data back to the same excel sheet. I am using visual studio .net 2003 with c#.

The basics of the code is as follows:

DataSet dsTest = new DataSet();
string excelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data.xls;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
OleDbConnection dataConnection = new OleDbConnection(excelConn);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select ORIGIN, BRAND, MODEL from [Sheet1$]",dataConnection);
dataAdapter.Fill(dsTest,"data");

OleDbCommand iCmd = new OleDbCommand("INSERT INTO [Sheet1$] (ORIGIN,BRAND,MODEL) VALUES (@ORIGIN,@BRAND,@MODEL)", dataConnection);
iCmd.Parameters.Add(new OleDbParameter("@ORIGIN", OleDbType.VarWChar, 30, "ORIGIN"));
iCmd.Parameters.Add(new OleDbParameter("@BRAND", OleDbType.VarWChar, 30, "BRAND"));
iCmd.Parameters.Add(new OleDbParameter("@MODEL", OleDbType.VarWChar, 30, "MODEL"));
dataAdapter.InsertCommand=iCmd;

OleDbCommand uCmd = new OleDbCommand("UPDATE [Sheet1$] SET ORIGIN=@ORIGIN, BRAND=@BRAND WHERE MODEL=@MODEL", dataConnection);
uCmd.Parameters.Add(new OleDbParameter("@ORIGIN", OleDbType.VarWChar, 30, "ORIGIN"));
uCmd.Parameters.Add(new OleDbParameter("@BRAND", OleDbType.VarWChar, 30, "BRAND"));
uCmd.Parameters.Add(new OleDbParameter("@MODEL", OleDbType.VarWChar, 30, "MODEL"));
dataAdapter.UpdateCommand=uCmd;

this.dgExcel.DataSource=this.dsAlmar.Tables["data"];

I then change some datain the DataGrid (dsExcel) and then run the code to update

dataAdapter.Update(dsTest,"data");

With IMEX=1 I get the error that operation must use an updatable query. I needed to use the IMEX=1 as the columns have mixed data types. I tried limiting to one datatype then using IMEX=2 but now get the error Concurrency violation: the UpdateCommand affected 0 records. The excel sheet is very simple and in fact it starts empty with just the 3 column headers origin, brand and model. All the data in it can be read as text and written back as text. Since I get an error with the IMEX=2 also it makes me wonder if something else is wrong.

To solve this problem I have tried some alternatives like using excel automation from c# but here I can write to excel sheet but then have a problem that excel application seems to stay active and then can't be reopened until I control alt delete the EXCEL process left open. The other thing I was thinking of doing was using access as the database but then have a few questions. Can I have my program use an access database even if the computer running the program does not have access and secondly can I write from access to that same excel sheet without encountering similar problems Alternatively would it be possible to overwrite the excel sheet each time so just creating a new one and deleting old

Sorry for the long question but I have been trying to figure this out on my own and just keep reaching dead ends.

Thanks in advance

Lorne

Answer this question

excel update using oledb and IMEX=1???

2162
I believe IMEX accepts only 0 and 1. 0 means use native type and 1 - treat everything as strings. Try to set it to 0. I believe you also need to change type of the parameter to the proper one (not a VarWchar), in a case if IMEX set to 0
Shauliz
I will have to test this out. IMEX = 1 shows as import mode and IMEX=0 for export mode. Perhaps open file and get datain program with IMEX =1 then export back to excel using IMEX=0 for export.

Not sure how to know the correct types of the parameters. VarWchar as far as I can tell maps to string and the input and output is all text which seems to make sense to map to string.
excel update using oledb and IMEX=1???

Answer this question