0

I have a system where i capture Student Session Information in SQL Server table. In SessionDetail table i have SID,StartTime,EndTme,SessiondDate,TherapistID(TID).

Now we get a Excel file from provider with billing information and we have to add StartTime,EndTime for the kids based on SID and TID match. We automate this process and add starttime and endtime throught our system and update the excel sheet rather doing it manually.

Now i have this code in place.

string selectString = "Update [Sheet1$] set StartTime = '10:30',EndTime='11:00' where SID='12'";

                    con = new OleDbConnection(connectionString);
                    cmd = new OleDbCommand(selectString, con);
                    con.Open();
                    cmd.ExecuteNonQuery();

and this works fine when i put SID or/and TID in where condition and update the sheet. But when i add the sessiondate in where condition it dosent update anything. i tried by adding just SessionDate in where condition it dosent update anything. SID and TID are formatted as Number in Excel sheet and date is formatted as [$-1010409 mm/dd/yyyy] in excel sheet.

I thought its something with date and string comparison on date filed and datatypes are not matching. SO i tried this query

 string selectString = "Update [Sheet1$] set SCIN_ACT_GRP_SIZE = 'P' where sessiondate=09/09/2014";

and 

 string selectString = "Update [Sheet1$] set SCIN_ACT_GRP_SIZE = 'P' where SessionDate='09/09/2014'";

one with '' and on without '' around date in where clause. First one execute but dost update the sheet and another one gives critical datatype mismatch error when its executed.

i am lost and not sure what to do.Please help.

Thanks.

arpan shah
  • 277
  • 2
  • 18
  • here is a Tutorial that may help you in regards to solving your Issue [How To Update data in Excel using OLEDB](http://csharp.net-informations.com/excel/csharp-excel-oledb-update.htm) – MethodMan Oct 20 '14 at 18:22
  • @DJKRAZE thanks! but i used this exact page to write the update logic. It seem to work on when i put SID and TID (which are numbers in Excel sheet) in where condition but dose not seem to work with date comparison. – arpan shah Oct 20 '14 at 18:26
  • this could be something to do with how the date is being handled in regards to the datatype of that particular date field.. can you open up the excel and inspect that particular date field property to see what the datatype of the date field is defined as ..? – MethodMan Oct 20 '14 at 18:31
  • as i mention above its formatted as [$-1010409 mm/dd/yyyy] in Excel sheet when i click format cell. – arpan shah Oct 20 '14 at 18:36
  • take a look at this article as well `SO` previous posting to give better example / insight http://stackoverflow.com/questions/894805/excel-number-format-what-is-409 – MethodMan Oct 20 '14 at 18:38

0 Answers0