Thread: SQL Insert statement syntax
View Single Post
•
•
Join Date: Oct 2008
Posts: 9
Reputation: pixelmeow is an unknown quantity at this point 
Solved Threads: 0
pixelmeow pixelmeow is offline Offline
Newbie Poster

Re: SQL Insert statement syntax

 
0
  #9
Dec 4th, 2008
•
•
•
•
Originally Posted by Teme64 View Post
Here's a slightly modified code:
VB Syntax (Toggle Plain Text) 
  1. '    strSQL = "INSERT CLEAN_MILE_DUPES (" & vbCrLf _
  2. '           & "       CASE_IDENTIFIER" & vbCrLf _
  3. '           & "      ,DOCUMENT_TYPE" & vbCrLf _
  4. '           & "      ,DOCUMENT_NUMBER" & vbCrLf _
  5. '           & "      ,MILESTONE" & vbCrLf _
  6. '           & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
  7. '           & "      ,MILESTONE_DATE)" & vbCrLf _
  8. '           & "SELECT CASE_IDENTIFIER" & vbCrLf _
  9. '           & "      ,DOCUMENT_TYPE" & vbCrLf _
  10. '           & "      ,DOCUMENT_NUMBER" & vbCrLf _
  11. '           & "      ,MILESTONE" & vbCrLf _
  12. '           & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
  13. '           & "      ,MILESTONE_DATE " & vbCrLf _
  14. '           & "  FROM CASE_MILESTONE_TEMP"
  15.  
  16. ' My "simplified" table 
  17. strSQL = "INSERT CLEAN_MILE_DUPES (" & vbCrLf _
  18.            & "       CASE_IDENTIFIER" & vbCrLf _
  19.            & "       ,MILESTONE_DATE" & vbCrLf _
  20.            & "      ,TEXT_FIELD) " & vbCrLf _
  21.            & "(SELECT CASE_IDENTIFIER" & vbCrLf _
  22.            & "       ,MILESTONE_DATE" & vbCrLf _
  23.            & "       ,TEXT_FIELD " & vbCrLf _
  24.            & "   FROM CASE_MILESTONE_TEMP)"
  25.  
  26.  
  27.     oCmd.CommandText = strSQL
  28.     oCmd.CommandType = adCmdText
  29.  
  30.     ' Now, here is the only place where Resume Next is needed
  31.     On Error Resume Next
  32.     Set rs = oCmd.Execute
  33.     On Error GoTo ErrorHandler
Just got this far. SELECT COUNT(*) FROM CLEAN_MILE_DUPES = 0. No error, it just went to the next line. This is on *my* machine! <going to go tear out my hair>

•
•
•
•
Originally Posted by Teme64 View Post
I tried your code and it hang up. Then I removed On Error Resume Next and started to trap errors. First thing that failed in the code was missing Set oCmd.ActiveConnection = oConn i.e. command object did not have an associated connection object.

Second, I used a local command object. You may use a global one, but you have to make sure that it's valid in your subroutine. Besides, you can always pass command and/or connection objects as ByRef parameters rather than use global ones.

I moved On Error Resume Next to the only place where it's actually needed (remove duplicates) and then turned error handling back.

Now the code works just fine for me. I hope this helps you a bit
I want your code to work for me!
Last edited by pixelmeow; Dec 4th, 2008 at 12:34 pm. Reason: close CODE tag
Reply With Quote