Problem in copying MS Excel data to MS SQL via ASP

[kashif73]

I have an application which updates record from my excel sheet into the MS SQL server DB. Thefirst line of my excel sheet has the column names of my database table. The problem arises with DATE column in my DB table. while updating I always get this error: "Microsoft VBScript runtime error '800a000d'
Type mismatch: 'FormatDateTime ' ". .

Following is the code of ASP file, which looks OK to me though.
IS it essential that in my excel sheet, datevalue columns should be categorized as DATE.?

<% 
 
Set ExcelConn = Server.CreateObject("ADODB.Connection")
Set myConn = Server.CreateObject("ADODB.Connection")
 
myconn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = CreditRegistry; User Id = BPA3; Password=sa"
 
    set rs = server.createobject("adodb.recordset")
Set ExcelRS = Server.CreateObject("ADODB.Recordset")
ExcelConn.Provider = "Microsoft.Jet.OLEDB.4.0"
ExcelConn.Properties("Extended Properties").Value = "Excel 8.0"
ExcelConn.Open "C:\inetpub\wwwroot\CreditRegistry\UpdateInfo.xls"
 
sSQL2 = "SELECT * FROM [CreditInfoUpdate2$]"
set ExcelRS = ExcelConn.Execute(sSQL2)
 
Do until ExcelRS.EOF 
 
 
myConn.Execute("UPDATE CreditInfo Set NameCreditGrantor = '" & ExcelRS("NameCreditGrantor") & "', DateAcctOpened ='" & FormatDateTime(ExcelRS("DateAcctOpened"),1) & "', OrgBal = " & ExcelRS("OrgBal") & ", MonthlyPaymt = " & ExcelRS("MonthlyPaymt") & ", DateLastPaymt = '" & FormatDateTime(ExcelRS("DateLastPaymt"),1) & "', Balance = " & ExcelRS("Balance") & ", CreditbySector = '" & ExcelRS("CreditbySector") & "', MannerofPaymt = '" & ExcelRS("MannerofPaymt") & "', Security = '" & ExcelRS("Security") & "', DescofCollateral = '" & ExcelRS("DescofCollateral") & "', AssetClass = '" & ExcelRS("AssetClass") & "' WHERE ElectNo = '" & ExcelRS("ElectNo") & "' AND id = "& ExcelRS("id") &"" )
 
 
ExcelRS.MoveNext
Loop
Response.Write("<p><span class=inlineblack>Data Successfully updated in the Credit Info Table.</span></p>")
 
ExcelRS.Close
set ExcelRS = NOTHING
ExcelConn.Close
set ExcelConn = NOTHING
myConn.close
set myconn = NOTHING
 
%>

I dont want to use DTS. Your help will be much appreciated. I am really stuck on it for quite a long time now.Thanks.

[DrBunchman]

Hi kashif73,

The function FormatDateTime is being passed something other than a date. If you comment out the update and just write the values to the screen what does the date field look like?

Dr B

[kashif73]

Dr. B,
Thanks for the reply. I did a response.write of the statement. It stucks at record # 4 of my excel sheet. In my excel sheet the date is written like 07/07/2007 & its categorized as DATE. But when I change its category to GENERAL(Format Cell>> Number Tab>> Category>> GENERAL), the date value turns into a number (39270). so i think this number is passed to my statement & thats y error is generated. I am sure this is the problem.
Infact I noticed that few of the date records when their CATEGORY was changed to GENERAL, they turned into flat numbers like quoted above. So this means that these records create problem for my SQL statement. I hope you get my point here.??
If i remove these lines from my excel (with above problem of date turning into numbers), the records are successfully inserted to DB.
How to get over with this problem ??? i have attached my excel file.
many thanks foryour help.

[kashif73]

Anyone plz? need help on it urgently. Thanks.

[jhardman]

Anyone plz? need help on it urgently. Thanks.

When I opened up youjr file, the dates are in the format dd/mm/yyyy (26/09/2003 for example) but my excel program interpreted them as if they were in US standard date format: mm/dd/yyyy (09/26/2003). This is of course wrong and most of the numbers were not valid dates. I would guess that this is the problem you are facing. Try to convert them to a non-ambiguous date format (26-Sep-2003 for example) before passing them to the db - this should solve the problem.

Jared

[kashif73]

Thanks Jared for the suggestion.I have changed the date format to be as US, once they are retrieved in my excel from DB and henceforth they r inserted back again in the same format as in DB. So this solves the problem. Thanks again.