Printable View
I am trying to import excel data into a sql table useing ADO in VB.
Everything works fine but, there are two fields in the excel file that come as date and currency. My table in sql has datetime and money data type for these fields. When try to append the data
by this method
rsSql.Fields(0)=rsExcel.Fields(0)
'
'
'
'
'
rsSql.Fields(10)=rsExcel.Fields(10)
all of the fields are updated but these two fields the date and currency are not updated can some one help me out with this?
I'm not totally sure here, but have you tried changing formats of the imported fields to Doubles? Most SQL style databases interpret money and time as Double precision numbers. Try using:
rsSql.Fields(0)=CDbl(rsExcel.Fields(0))
See how you get on!
Thanks,
But the rsExcel.Fields(0) doesn't even populated with any values, when I go in debug mode there is null value. Also
the date is formatted as custom mm"/"dd"/"yyyy in excel file......
any thoughts???
Hmmm...
I'm not sure about why you're not getting anything back, but the custom format doesn't matter. (You'll see what I mean if you change the format for a date to:
#,##0.0000
Dates are stored in Excel as a number since a specified date. The value that you will be transferring is probably just this number rather than the date itself.
If you use a date in SQL Server, the SQL string would normally be something like "Update MyTable Set MyDate=#12/10/2001#".
You will probably have to read the date into your program and then format it correctly for SQL Server.
In the mean time, have you thought of using the Excel object in your program to access the spreadsheet data instead. Then you can read the data as a date!