1
I have a page that inserts excel sheet to access database. I am using asp. What I want to do is to check the inserting record if it is in the database. Basicly checking the dublicate record.
My code inserts records one by one using addnew-updatebatch. If there is a duplicate in the db, it will display "already exists" and if it is not in the db it will display "record added".
Below is my asp code I found on the net and changed it to my needs. The script is almost working but it does not display the texts properly. Any help would be appreciated.
Expand|Select|Wrap|Line Numbers
- <html>
- <head>
- <title>Import Data From Excel into Access</title>
- </head>
- <body>
- <%
- on error resume next
- Dim cnnExcel, rstExcel
- Dim cnnAccess, rstAccess
- Dim strExcelFile
- Dim strAccessFile,xIndex
- strExcelFile = Server.MapPath(".") & "/database/Companies.xls"
- strAccessFile = Server.MapPath(".") & "/database/test.mdb"
- ' Open Excel Connection
- Set cnnExcel = Server.CreateObject("ADODB.Connection")
- cnnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
- "Data Source=" & strExcelFile & ";" & _
- "Extended Properties=""Excel 8.0;HDR=NO;"""
- ' Response.Write "Excel connection opened<BR>"
- ' Load ADO Recordset with Excel Data
- Set rstExcel = Server.CreateObject("ADODB.Recordset")
- rstExcel.Open "Select * from [Sheet1$A2:Q10000]", cnnExcel, adOpenStatic
- ' Response.Write "Excel Recordset loaded<BR>"
- ' Open Access Connection
- Set cnnAccess = Server.CreateObject("ADODB.Connection")
- cnnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
- "Data Source=" & strAccessFile & ";" & _
- "Persist Security Info=False"
- ' Response.Write "Access connection opened<BR>"
- ' Load ADO Recordset with Access Data
- Set rstAccess = Server.CreateObject("ADODB.Recordset")
- rstAccess.Open "coup", cnnAccess, adOpenKeyset, adLockOptimistic, adCmdTable
- ' Response.Write "Access Recordset loaded<BR>"
- %><table border="1">
- <tr>
- <%
- 'Synchronize Recordsets and Batch Update
- Do Until rstExcel.EOF
- If IsNull(rstExcel.Fields(0).Value) Then Exit Do
- Response.Write("<tr>")
- Response.Write("<td> " & rstExcel.Fields(0).Value & "</td>")
- Response.Write("<td> " & rstExcel.Fields(1).Value & "</td>")
- Response.Write("<td> " & rstExcel.Fields(2).Value & "</td>")
- Response.Write("<td> " & rstExcel.Fields(3).Value & "</td>")
- Response.Write("<td> " & rstExcel.Fields(4).Value & "</td>")
- Response.Write("<td> " & rstExcel.Fields(5).Value & "</td>")
- Response.Write("<td> " & rstExcel.Fields(6).Value & "</td>")
- Response.Write("<td> " & rstExcel.Fields(7).Value & "</td>")
- Response.Write("<td> " & rstExcel.Fields(8).Value & "</td>")
- if err.Number <> 0 then
- Response.Write "<td> record exist."
- Response.Write " </td>"
- err.clear
- else
- With rstAccess
- .AddNew
- .Fields("other") = rstExcel.Fields(0).Value
- .Fields("name") = rstExcel.Fields(1).Value
- .Fields("amount") = rstExcel.Fields(2).Value
- .Fields("percent") = rstExcel.Fields(3).Value
- .Fields("startdate") = rstExcel.Fields(4).Value
- .Fields("enddate") = rstExcel.Fields(5).Value
- .Fields("limit") = rstExcel.Fields(6).Value
- .Fields("products") = rstExcel.Fields(7).Value
- .Fields("minimum") = rstExcel.Fields(8).Value
- Response.Write "<td> record added</td>"
- End With
- end if
- Response.Write("</tr>")
- rstExcel.MoveNext
- Loop
- Response.Write("</tr></table>")
- rstAccess.UpdateBatch
- ' Response.Write "Excel data written to Access<BR>"
- 'Clean up
- rstExcel.Close
- Set rstExcel = Nothing
- rstAccess.Close
- Set rstAccess = Nothing
- cnnExcel.Close
- Set cnnExcel = Nothing
- cnnAccess.Close
- Set cnnAccess = Nothing
- Response.Write "Import from Excel to database completed<BR>"
- %>
- </body>
- </html>
