By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,339 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

Error checking while inserting records in access

P: 1
Hi to all,

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
  1. <html>
  2. <head>
  3. <title>Import Data From Excel into Access</title>
  4. </head>
  5. <body>
  6. <%
  7. on error resume next
  8. Dim cnnExcel, rstExcel
  9. Dim cnnAccess, rstAccess 
  10. Dim strExcelFile
  11. Dim strAccessFile,xIndex
  12.  
  13. strExcelFile = Server.MapPath(".") & "/database/Companies.xls"
  14. strAccessFile = Server.MapPath(".") & "/database/test.mdb"
  15.  
  16. ' Open Excel Connection
  17. Set cnnExcel = Server.CreateObject("ADODB.Connection")
  18. cnnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  19. "Data Source=" & strExcelFile & ";" & _
  20. "Extended Properties=""Excel 8.0;HDR=NO;"""
  21. ' Response.Write "Excel connection opened<BR>"
  22.  
  23. ' Load ADO Recordset with Excel Data
  24. Set rstExcel = Server.CreateObject("ADODB.Recordset")
  25. rstExcel.Open "Select * from [Sheet1$A2:Q10000]", cnnExcel, adOpenStatic 
  26. ' Response.Write "Excel Recordset loaded<BR>"
  27.  
  28. ' Open Access Connection
  29. Set cnnAccess = Server.CreateObject("ADODB.Connection")
  30. cnnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  31. "Data Source=" & strAccessFile & ";" & _
  32. "Persist Security Info=False"
  33. ' Response.Write "Access connection opened<BR>"
  34.  
  35. ' Load ADO Recordset with Access Data
  36. Set rstAccess = Server.CreateObject("ADODB.Recordset")
  37. rstAccess.Open "coup", cnnAccess, adOpenKeyset, adLockOptimistic, adCmdTable
  38. ' Response.Write "Access Recordset loaded<BR>"
  39. %><table border="1">
  40. <tr>
  41. <%
  42. 'Synchronize Recordsets and Batch Update
  43.  
  44. Do Until rstExcel.EOF
  45. If IsNull(rstExcel.Fields(0).Value) Then Exit Do
  46. Response.Write("<tr>")
  47. Response.Write("<td>&nbsp;" & rstExcel.Fields(0).Value & "</td>")
  48. Response.Write("<td>&nbsp;" & rstExcel.Fields(1).Value & "</td>")
  49. Response.Write("<td>&nbsp;" & rstExcel.Fields(2).Value & "</td>")
  50. Response.Write("<td>&nbsp;" & rstExcel.Fields(3).Value & "</td>")
  51. Response.Write("<td>&nbsp;" & rstExcel.Fields(4).Value & "</td>")
  52. Response.Write("<td>&nbsp;" & rstExcel.Fields(5).Value & "</td>")
  53. Response.Write("<td>&nbsp;" & rstExcel.Fields(6).Value & "</td>")
  54. Response.Write("<td>&nbsp;" & rstExcel.Fields(7).Value & "</td>")
  55. Response.Write("<td>&nbsp;" & rstExcel.Fields(8).Value & "</td>")
  56. if err.Number <> 0 then
  57. Response.Write "<td>&nbsp;record exist." 
  58. Response.Write "&nbsp;</td>"
  59. err.clear 
  60. else
  61. With rstAccess
  62.  
  63. .AddNew
  64.  
  65. .Fields("other") = rstExcel.Fields(0).Value
  66. .Fields("name") = rstExcel.Fields(1).Value
  67. .Fields("amount") = rstExcel.Fields(2).Value
  68. .Fields("percent") = rstExcel.Fields(3).Value 
  69. .Fields("startdate") = rstExcel.Fields(4).Value
  70. .Fields("enddate") = rstExcel.Fields(5).Value 
  71. .Fields("limit") = rstExcel.Fields(6).Value
  72. .Fields("products") = rstExcel.Fields(7).Value 
  73. .Fields("minimum") = rstExcel.Fields(8).Value
  74. Response.Write "<td>&nbsp;record added</td>"
  75.  
  76.  
  77. End With
  78.  
  79. end if
  80. Response.Write("</tr>")
  81.  
  82. rstExcel.MoveNext
  83. Loop
  84. Response.Write("</tr></table>")
  85. rstAccess.UpdateBatch
  86. ' Response.Write "Excel data written to Access<BR>"
  87.  
  88.  
  89. 'Clean up
  90. rstExcel.Close
  91. Set rstExcel = Nothing
  92. rstAccess.Close
  93. Set rstAccess = Nothing
  94. cnnExcel.Close
  95. Set cnnExcel = Nothing
  96. cnnAccess.Close
  97. Set cnnAccess = Nothing
  98.  
  99. Response.Write "Import from Excel to database completed<BR>"
  100.  
  101. %>
  102.  
  103.  
  104. </body>
  105. </html>
Sep 3 '08 #1
Share this Question
Share on Google+
2 Replies


omerbutt
100+
P: 638
it does not display which text can you specify the line on which error is occouring and do wrap your code part with the code tags that makes your post readable
regards,
omer
Sep 4 '08 #2

jhardman
Expert 2.5K+
P: 3,405
I agree with Omer, you need to specify what you mean by "does not display the texts properly". Does it not display them at the correct time? Does it not show them at all? Please clarify.

Jared
Sep 4 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.