Insert a data into a SQL Server 2000 from Excel file using ASP

[pexp]

Hello All,

I would like to insert/update a database table using excel file.

I have a list of products in an excel file. If product exist in a database table tblproduct than it should update the content and if product does not exist it should insert the new row.

I am using sql server 2000.In excel I have 16 columns.

tblproduct has following columns

ID (int) - Identity column
ProductCode (nvarachar)
ProductName (nvarchar)
Description (nvarchar)
costprice(money)
retailprice(money)
saleprice(money)
splsaleprice(money)
splboxprice(money)
boxqty(int)
boxprice(money)
stock_level(int)
weight(float)
Image(nvarchar)
display(int)
brandid(int)

I was trying to use follwoing code but gave me the error.

<%
Set objConn = Server.CreateObject("ADODB.Connection") 
objConn.open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.xls; Extended Properties=Excel 8.0;" 
set objRS = server.createObject("ADODB.recordset") 
objRS.open objConn, "SELECT * FROM tblproduct" 
 
set conn=Server.CreateObject("ADODB.Connection")  
conn.Open "dbConnectionStr" 
set RS = server.createObject("ADODB.recordset") 
RS.open conn, "SELECT * FROM tblproduct", adOpenDynamic, adLockOptimistic 
 
do until objRS.eof 
   RS.addnew 
   for each x in objRS 
      RS(x) = objRS(x) 
   next 
   RS.update 
   objRS.moveNext 
loop 
 
%>
 

***********************[Error]*************************
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
***********************[/Error]*************************

Any help will be really appreciated.

Thanks in advance.

[jhardman]

***********************[Error]*************************
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
***********************[/Error]*************************

Any help will be really appreciated.

Thanks in advance.

The error just means that one of the data types in the excel file doesn't match the data type in the db. You need to find out the exact place the error is occurring - try this:

do until objRS.eof 
   RS.addnew 
   for each x in objRS 
      response.write "<!-- adding " & x & "; datatype: " x.type & " -->" & vbNewLine
      RS(x) = objRS(x) 
   next 
   RS.update 
   objRS.moveNext 
loop 
 
%>
 

This will print out a new comment for each line, the last comment should tell you the field that is causing the error. Let me know if this helps.

Jared