471,066 Members | 1,633 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 software developers and data experts.

Error checking while inserting records in access

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
2 2862
omerbutt
638 512MB
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
3,406 Expert 2GB
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.

Similar topics

8 posts views Thread by middletree | last post: by
1 post views Thread by Ramesh | last post: by
10 posts views Thread by Phil Latio | last post: by
4 posts views Thread by franc sutherland | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.