Thread: SQL Insert statement syntax
View Single Post
•
•
Join Date: Aug 2008
Posts: 710
Reputation: Teme64 will become famous soon enough Teme64 will become famous soon enough 
Solved Threads: 114
Teme64's Avatar
Teme64 Teme64 is offline Offline
Master Poster

Re: SQL Insert statement syntax

 
0
  #7
Dec 4th, 2008
Here's a slightly modified code:
VB Syntax (Toggle Plain Text) 
  1. Option Explicit
  2.  
  3. Public cn As New ADODB.Connection
  4. Public cmd As New ADODB.Command
  5. Public rs As New ADODB.Recordset
  6.  
  7. Private Sub removeMileDupes()
  8.     Dim strSQL As String
  9.     '
  10.     Dim ConStr As String
  11.     Dim oCmd As ADODB.Command ' Use a local object. You may use global, but make sure you know what it does :)
  12.  
  13.     ' Add error handler
  14.     On Error GoTo ErrorHandler
  15.  
  16. ' I had to set and open connection object in here. You may do it before calling this sub
  17. ConStr = "PROVIDER=SQLOLEDB;Data Source=JASMIN\SQLEXPRESS; INITIAL CATALOG=EditorTest; User ID=XXXXXX; Password=XXXXXX;"
  18. Set oConn = New ADODB.Connection
  19. oConn.Open ConStr
  20.  
  21. Set oCmd = New ADODB.Command
  22. Set oCmd.ActiveConnection = oConn ' Set a connection for the command
  23. ' I think you might have missed this OR the connection's state wasn't Open
  24.  
  25. '  I commented this out. If this opens the connection, you may have to move Set oCmd.ActiveConnection = oConn after this block
  26.     If cn.State = 0 Then
  27.         'fileset says which set of files are being loaded at the moment
  28.         Call doDBaction("openDB", fileSet)
  29.     End If
  30.     'clean_dupes tables have ignore duplicates flag on
  31.     'vb catches the error and stops
  32.     'so resume next.
  33.  
  34.     ' No need to Resume Next in here
  35.     'On Error Resume Next
  36.  
  37.     strSQL = "TRUNCATE TABLE CLEAN_MILE_DUPES"
  38.     oCmd.CommandText = strSQL
  39.     oCmd.CommandType = adCmdText
  40.     Set rs = oCmd.Execute
  41.  
  42. '    strSQL = "INSERT CLEAN_MILE_DUPES (" & vbCrLf _
  43. '           & "       CASE_IDENTIFIER" & vbCrLf _
  44. '           & "      ,DOCUMENT_TYPE" & vbCrLf _
  45. '           & "      ,DOCUMENT_NUMBER" & vbCrLf _
  46. '           & "      ,MILESTONE" & vbCrLf _
  47. '           & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
  48. '           & "      ,MILESTONE_DATE)" & vbCrLf _
  49. '           & "SELECT CASE_IDENTIFIER" & vbCrLf _
  50. '           & "      ,DOCUMENT_TYPE" & vbCrLf _
  51. '           & "      ,DOCUMENT_NUMBER" & vbCrLf _
  52. '           & "      ,MILESTONE" & vbCrLf _
  53. '           & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
  54. '           & "      ,MILESTONE_DATE " & vbCrLf _
  55. '           & "  FROM CASE_MILESTONE_TEMP"
  56.  
  57. ' My "simplified" table 
  58. strSQL = "INSERT CLEAN_MILE_DUPES (" & vbCrLf _
  59.            & "       CASE_IDENTIFIER" & vbCrLf _
  60.            & "       ,MILESTONE_DATE" & vbCrLf _
  61.            & "      ,TEXT_FIELD) " & vbCrLf _
  62.            & "(SELECT CASE_IDENTIFIER" & vbCrLf _
  63.            & "       ,MILESTONE_DATE" & vbCrLf _
  64.            & "       ,TEXT_FIELD " & vbCrLf _
  65.            & "   FROM CASE_MILESTONE_TEMP)"
  66.  
  67.  
  68.     oCmd.CommandText = strSQL
  69.     oCmd.CommandType = adCmdText
  70.     ' Now, here is the only place where Resume Next is needed
  71.     On Error Resume Next
  72.     Set rs = oCmd.Execute
  73.     On Error GoTo ErrorHandler
  74. 'THIS ^^^ IS THE COMMAND THAT IS FAILING. IT HANGS FOR 5 OR
  75. 'MORE SECONDS. DOING COUNT(*) ON THAT TABLE GIVES 0
  76. 'RECORDS. THEREFORE THE FOLLOWING CODE JUST REMOVES
  77. 'EVERYTHING FROM THE TEMP TABLE.
  78.  
  79.     strSQL = "TRUNCATE TABLE CASE_MILESTONE_TEMP"
  80.     oCmd.CommandText = strSQL
  81.     oCmd.CommandType = adCmdText
  82.     Set rs = oCmd.Execute
  83.  
  84.     ' My "simplified" table again. I dropped your strSQL somewhere...
  85.     strSQL = "INSERT CASE_MILESTONE_TEMP (" & vbCrLf _
  86.            & "       CASE_IDENTIFIER" & vbCrLf _
  87.            & "      ,MILESTONE_DATE" & vbCrLf _
  88.            & "      ,TEXT_FIELD) " & vbCrLf _
  89.            & "      SELECT CASE_IDENTIFIER" & vbCrLf _
  90.            & "       ,MILESTONE_DATE" & vbCrLf _
  91.            & "       ,TEXT_FIELD " & vbCrLf _
  92.            & "         FROM CLEAN_MILE_DUPES"
  93.     oCmd.CommandText = strSQL
  94.     oCmd.CommandType = adCmdText
  95.     Set rs = oCmd.Execute
  96.  
  97.     strSQL = "TRUNCATE TABLE CLEAN_MILE_DUPES"
  98.     oCmd.CommandText = strSQL
  99.     oCmd.CommandType = adCmdText
  100.     Set rs = oCmd.Execute
  101.  
  102. Sub_Exit:
  103.   oConn.Close
  104.   Exit Sub
  105. ErrorHandler:
  106.   ' Handle errors here (this is just to trap errors)
  107.   Resume Sub_Exit
  108.  
  109. End Sub
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
Teme64 @ Windows Developer Blog
Reply With Quote