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.

Expand|Select|Wrap|Line Numbers

 
<%

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

Quote:

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

 
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

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

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

Similar ASP / Active Server Pages bytes

/bytes/development

/bytes/it

/bytes/about