473,219 Members | 1,616 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,219 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
  13. strExcelFile = Server.MapPath(".") & "/database/Companies.xls"
  14. strAccessFile = Server.MapPath(".") & "/database/test.mdb"
  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>"
  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>"
  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>"
  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
  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
  63. .AddNew
  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>"
  77. End With
  79. end if
  80. Response.Write("</tr>")
  82. rstExcel.MoveNext
  83. Loop
  84. Response.Write("</tr></table>")
  85. rstAccess.UpdateBatch
  86. ' Response.Write "Excel data written to Access<BR>"
  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
  99. Response.Write "Import from Excel to database completed<BR>"
  101. %>
  104. </body>
  105. </html>
Sep 3 '08 #1
2 3079
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
Sep 4 '08 #2
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.

Sep 4 '08 #3

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

Similar topics

by: middletree | last post by:
I had some text links at the top of all my pages (in one include file), which worked just fine. But I was asked to make it so that people in a certain department, (this is an Intranet app) would...
by: martlaco1 | last post by:
Trying to fix a query that (I thought) had worked once upon a time, and I keep getting a Data Type Mismatch error whenever I enter any criteria for an expression using a Mid function. Without the...
by: TD | last post by:
This is the code under a command button - Dim ctl As Control For Each ctl In Me.Controls If ctl.BackColor <> RGB(255, 255, 255) Then ctl.BackColor = RGB(255, 255, 255) End If Next ctl
by: Ramesh | last post by:
hi I am trying to insert 50,000 random records in the SQL server. I have created COM for inserting records. When i started inserting, after 44152 records it displayed following error message. "...
by: Mamatha | last post by:
Hi I want to insert data into the database from VB.NET,before inserting i want to check those records for eleminate of duplication. I tried with thte dataset but i didn't get the correct...
by: pradeepss | last post by:
I am developing an application which grab a comma delimited file and inserts them into a database. but while inserting it gives me an error. the code is below. Do While Not EOF(iDrop) Line...
by: Phil Latio | last post by:
I am inserting data into user table which contains 5 fields, sounds simple enough normally but 2 of the fields are designated as UNIQUE. If someone does enter a value which already exists, how do I...
by: franc sutherland | last post by:
Hello, I am using Access 2003. I am having trouble trapping the "can't append all the records in the append query" error message when appending data to a query from a table which is linked to...
by: rando1000 | last post by:
Okay, here's my situation. I need to loop through a file, inserting records based on a number field (in order) and if the character in a certain field = "##", I need to insert a blank record. ...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.