[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.

Expand|Select|Wrap|Line Numbers

1. <%
2. Set objConn = Server.CreateObject("ADODB.Connection") 
3. objConn.open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.xls; Extended Properties=Excel 8.0;" 
4. set objRS = server.createObject("ADODB.recordset") 
5. objRS.open objConn, "SELECT * FROM tblproduct" 
6.  
7. set conn=Server.CreateObject("ADODB.Connection")  
8. conn.Open "dbConnectionStr" 
9. set RS = server.createObject("ADODB.recordset") 
10. RS.open conn, "SELECT * FROM tblproduct", adOpenDynamic, adLockOptimistic 
11.  
12. do until objRS.eof 
13.    RS.addnew 
14.    for each x in objRS 
15.       RS(x) = objRS(x) 
16.    next 
17.    RS.update 
18.    objRS.moveNext 
19. loop 
20.  
21. %>
22.  

***********************[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]

Originally Posted by pexp

***********************[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:

Expand|Select|Wrap|Line Numbers

1. do until objRS.eof 
2.    RS.addnew 
3.    for each x in objRS 
4.       response.write "<!-- adding " & x & "; datatype: " x.type & " -->" & vbNewLine
5.       RS(x) = objRS(x) 
6.    next 
7.    RS.update 
8.    objRS.moveNext 
9. loop 
10.  
11. %>
12.  

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