943,682 Members | Top Members by Rank

Software Development > Visual Basic 4 / 5 / 6 > SQL Insert statement syntax
Ad:
Permalink
Dec 2nd, 2008
0

SQL Insert statement syntax

Expand Post »
Hello,

I'm using VB6 and SQL Server 2000.

I've got a problem with an insert statement that seems to be randomly failing. Its job is to copy everything from one table into another, removing duplicates using WITH IGNORE_DUP_KEY ON [PRIMARY]. Then my code copies the records back to the original table, duplicate free.

This is my VB 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)"

The SQL works in TOAD and Query Analyzer, every time. The code works in my program, on the machine I'm using right now, also every time. I've got two other machines I'm using for testing, with their own databases, and this code fails in the program, but works in TOAD or Query Analyzer.

As I'm debugging this today, it is failing. I removed the second pair of parentheses from the code above (I saw an insert statement here today while trying to find the answer, and it didn't have the select statement in parentheses) and it worked. I am now ripping my hair out.

Either form works in TOAD or QA. It would seem that either works in VB, *unless it doesn't feel like working*. Does anyone have any idea about this?

Thanks,
teresa
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
pixelmeow is offline Offline
9 posts
since Oct 2008
Permalink
Dec 3rd, 2008
0

Re: SQL Insert statement syntax

What is the exact error message that you get? What error number you get from oConn.Errors(0).NativeError (oConn being your connection object)?

A few things come to my mind straight away. Are the tables identical? Is any of the fields char/varchar type and contains '-character in the source table? Null values?

Do you have an error handler in your code where you can set a breakpoint and check, what does the strSQL variable actually hold?
Reputation Points: 218
Solved Threads: 201
Veteran Poster
Teme64 is offline Offline
1,024 posts
since Aug 2008
Permalink
Dec 3rd, 2008
0

Re: SQL Insert statement syntax

Click to Expand / Collapse  Quote originally posted by Teme64 ...
What is the exact error message that you get? What error number you get from oConn.Errors(0).NativeError (oConn being your connection object)?
It's not a connection object error. The first truncate works fine, the first insert doesn't insert anything into the CLEAN table, the second truncate works fine, then there's nothing to be inserted into the CASE table.

Click to Expand / Collapse  Quote originally posted by Teme64 ...
A few things come to my mind straight away. Are the tables identical? Is any of the fields char/varchar type and contains '-character in the source table? Null values?
The only differences between the tables are the ID column in the CLEAN table and the REMARKS column in the CASE table. The REMARKS column isn't included in the SELECT statement, so that doesn't affect anything. If I DESC (Oracle term, but it's a function I have in SS), the tables are identical. They should be, the CREATE statement for the CLEAN table was exactly the same as the CASE table with the ID column and without the REMARKS column. Null values are allowed everywhere except primary keys, as long as this is in development.

Click to Expand / Collapse  Quote originally posted by Teme64 ...
Do you have an error handler in your code where you can set a breakpoint and check, what does the strSQL variable actually hold?
I've got a lot of breakpoints to see what's going on, and the strSQL variable holds the SQL statements that are passed to the DB. All of them work, except *that* one, and that one seems to fail at random.

I just ran my program on the DB on my machine, and as usual, all SQL worked just fine. I've got to run it on the test machine now, and I'll let you know how it goes.

Thank you for your questions, they help me make sure everything's right! :-)
Reputation Points: 10
Solved Threads: 0
Newbie Poster
pixelmeow is offline Offline
9 posts
since Oct 2008
Permalink
Dec 3rd, 2008
0

Re: SQL Insert statement syntax

I'm at a complete loss. The SQL above, and two other similar statements, work fine on my machine. Now, instead of just the one statement above giving me problems, all three are. They act as if they are copying records to the CLEAN table, then truncate the CASE table, then when it's time to copy the records back from the CLEAN table to the CASE table, there are no records to copy back! I don't understand!

I created two new databases for two new datasets, but these SQL statements work FINE in those databases. Does anyone have any idea?

Thanks,
teresa
Last edited by pixelmeow; Dec 3rd, 2008 at 4:41 pm. Reason: bad grammar
Reputation Points: 10
Solved Threads: 0
Newbie Poster
pixelmeow is offline Offline
9 posts
since Oct 2008
Permalink
Dec 4th, 2008
0

Re: SQL Insert statement syntax

I'm trying to re-produce your case.

Here's my simplified tables.
Temp-table:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. USE [EditorTest]
  2. GO
  3. /****** Object:  Table [dbo].[CASE_MILESTONE_TEMP]    Script Date: 12/04/2008 10:35:36 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. SET ANSI_PADDING ON
  9. GO
  10. CREATE TABLE [dbo].[CASE_MILESTONE_TEMP](
  11. 	[CASE_IDENTIFIER] [int] NULL,
  12. 	[MILESTONE_DATE] [datetime] NULL,
  13. 	[TEXT_FIELD] [varchar](50) NULL,
  14. 	[DROP_THIS_FIELD] [varchar](50) NULL
  15. ) ON [PRIMARY]
  16.  
  17. GO
  18. SET ANSI_PADDING OFF
and Clean-table:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. USE [EditorTest]
  2. GO
  3. /****** Object:  Table [dbo].[CLEAN_MILE_DUPES]    Script Date: 12/04/2008 10:35:05 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. SET ANSI_PADDING ON
  9. GO
  10. CREATE TABLE [dbo].[CLEAN_MILE_DUPES](
  11. 	[CASE_IDENTIFIER] [int] NULL,
  12. 	[MILESTONE_DATE] [datetime] NULL,
  13. 	[TEXT_FIELD] [varchar](50) NULL
  14. ) ON [PRIMARY]
  15.  
  16. GO
  17. SET ANSI_PADDING OFF
with CASE_IDENTIFIER as a duplicate key:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. USE [EditorTest]
  2. GO
  3. CREATE UNIQUE NONCLUSTERED INDEX [test] ON [dbo].[CLEAN_MILE_DUPES]
  4. (
  5.  [CASE_IDENTIFIER] ASC
  6. )
  7. WITH (IGNORE_DUP_KEY = ON)
and I tested with this code:
VB Syntax (Toggle Plain Text)
  1. Option Explicit
  2.  
  3. Private oConn As ADODB.Connection
  4. Private oCmd As ADODB.Command
  5.  
  6. Private Sub Command1_Click()
  7. '
  8. Dim strSQL As String
  9. Dim ConStr As String
  10.  
  11. On Error GoTo ErrorHandler
  12.  
  13. ConStr = "PROVIDER=SQLOLEDB;Data Source=JASMIN\SQLEXPRESS; INITIAL CATALOG=EditorTest; User ID=XXXXXXX; Password=XXXXXXX;"
  14. Set oConn = New ADODB.Connection
  15. oConn.Open (ConStr)
  16.  
  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. Set oCmd = New ADODB.Command
  27. oCmd.CommandText = strSQL
  28. oCmd.CommandType = adCmdText
  29. Set oCmd.ActiveConnection = oConn
  30. oCmd.Execute
  31.  
  32. Sub_Exit:
  33.   oConn.Close
  34.   Exit Sub
  35. ErrorHandler:
  36.   ' Breakpoint here
  37.   Resume Sub_Exit
  38.  
  39. End Sub
The error I get in the Breakpoint-line is:
? oConn.Errors(0).Description
Duplicate key was ignored.
and that comes from a duplicate CASE_IDENTIFIER-field value from the Temp-table. And now the Case-table has all the rows from the Temp-table, except one dropped duplicate, and the column DROP_THIS_FIELD is missing of course.

Is this anything similar to your code?

(I tested with SQL Server 2005 EE, but that shouldn't make any difference)
Last edited by Teme64; Dec 4th, 2008 at 4:54 am. Reason: Added DB info
Reputation Points: 218
Solved Threads: 201
Veteran Poster
Teme64 is offline Offline
1,024 posts
since Aug 2008
Permalink
Dec 4th, 2008
0

Re: SQL Insert statement syntax

Click to Expand / Collapse  Quote originally posted by Teme64 ...
I'm trying to re-produce your case.

Here's my simplified tables.
Temp-table:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. USE [EditorTest]
  2. GO
  3. /****** Object:  Table [dbo].[CASE_MILESTONE_TEMP]    Script Date: 12/04/2008 10:35:36 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. SET ANSI_PADDING ON
  9. GO
  10. CREATE TABLE [dbo].[CASE_MILESTONE_TEMP](
  11. 	[CASE_IDENTIFIER] [int] NULL,
  12. 	[MILESTONE_DATE] [datetime] NULL,
  13. 	[TEXT_FIELD] [varchar](50) NULL,
  14. 	[DROP_THIS_FIELD] [varchar](50) NULL
  15. ) ON [PRIMARY]
  16.  
  17. GO
  18. SET ANSI_PADDING OFF
Here's my milestone table:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. CREATE TABLE [CASE_MILESTONE_TEMP] (
  2. 	[CASE_IDENTIFIER] [varchar] (10)  NULL,
  3. 	[DOCUMENT_TYPE] [varchar] (3) NULL ,
  4. 	[DOCUMENT_NUMBER] [varchar] (3) NULL,
  5. 	[MILESTONE] [varchar] (10) NULL,
  6. 	[MILESTONE_DATE_TYPE] [varchar] (1) NULL,
  7. 	[MILESTONE_DATE] [varchar] (14) NULL,
  8. 	[MILESTONE_REMARKS] [varchar] (500) NULL 
  9. ) ON [PRIMARY]
  10. GO
I forgot there's no primary key... sorry...

Click to Expand / Collapse  Quote originally posted by Teme64 ...
and Clean-table:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. USE [EditorTest]
  2. GO
  3. /****** Object:  Table [dbo].[CLEAN_MILE_DUPES]    Script Date: 12/04/2008 10:35:05 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. SET ANSI_PADDING ON
  9. GO
  10. CREATE TABLE [dbo].[CLEAN_MILE_DUPES](
  11. 	[CASE_IDENTIFIER] [int] NULL,
  12. 	[MILESTONE_DATE] [datetime] NULL,
  13. 	[TEXT_FIELD] [varchar](50) NULL
  14. ) ON [PRIMARY]
  15.  
  16. GO
  17. SET ANSI_PADDING OFF
Here's my clean table:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. CREATE TABLE [CLEAN_MILE_DUPES] (
  2. 	[ID] [int] NULL ,
  3. 	[CASE_IDENTIFIER] [varchar] (10) NULL,
  4. 	[DOCUMENT_TYPE] [varchar] (3) NULL,
  5. 	[DOCUMENT_NUMBER] [varchar] (3) NULL,
  6. 	[MILESTONE] [varchar] (10) NULL,
  7. 	[MILESTONE_DATE_TYPE] [varchar] (1) NULL,
  8. 	[MILESTONE_DATE] [varchar] (10) NULL
  9. ) ON [PRIMARY]
  10. GO

Click to Expand / Collapse  Quote originally posted by Teme64 ...
with CASE_IDENTIFIER as a duplicate key:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. USE [EditorTest]
  2. GO
  3. CREATE UNIQUE NONCLUSTERED INDEX [test] ON [dbo].[CLEAN_MILE_DUPES]
  4. (
  5.  [CASE_IDENTIFIER] ASC
  6. )
  7. WITH (IGNORE_DUP_KEY = ON)
Here's my index:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. CREATE  UNIQUE  INDEX [RM_MILE_DUPES] ON [dbo].[CLEAN_MILE_DUPES]
  2. (
  3.     [CASE_IDENTIFIER]
  4.    ,[DOCUMENT_TYPE]
  5.    ,[DOCUMENT_NUMBER]
  6.    ,[ID]
  7.    ,[MILESTONE]
  8.    ,[MILESTONE_DATE_TYPE]
  9.    ,[MILESTONE_DATE]
  10. ) 
  11. WITH  IGNORE_DUP_KEY  ON [PRIMARY]
  12. GO

Click to Expand / Collapse  Quote originally posted by Teme64 ...
and I tested with this code:
VB Syntax (Toggle Plain Text)
  1. Option Explicit
  2.  
  3. Private oConn As ADODB.Connection
  4. Private oCmd As ADODB.Command
  5.  
  6. Private Sub Command1_Click()
  7. '
  8. Dim strSQL As String
  9. Dim ConStr As String
  10.  
  11. On Error GoTo ErrorHandler
  12.  
  13. ConStr = "PROVIDER=SQLOLEDB;Data Source=JASMIN\SQLEXPRESS; INITIAL CATALOG=EditorTest; User ID=XXXXXXX; Password=XXXXXXX;"
  14. Set oConn = New ADODB.Connection
  15. oConn.Open (ConStr)
  16.  
  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. Set oCmd = New ADODB.Command
  27. oCmd.CommandText = strSQL
  28. oCmd.CommandType = adCmdText
  29. Set oCmd.ActiveConnection = oConn
  30. oCmd.Execute
  31.  
  32. Sub_Exit:
  33.   oConn.Close
  34.   Exit Sub
  35. ErrorHandler:
  36.   ' Breakpoint here
  37.   Resume Sub_Exit
  38.  
  39. End Sub
The error I get in the Breakpoint-line is:
? oConn.Errors(0).Description
Duplicate key was ignored.
and that comes from a duplicate CASE_IDENTIFIER-field value from the Temp-table. And now the Case-table has all the rows from the Temp-table, except one dropped duplicate, and the column DROP_THIS_FIELD is missing of course.

Is this anything similar to your code?

(I tested with SQL Server 2005 EE, but that shouldn't make any difference)
Your code is very similar, just differences in how we do the database code:
VB Syntax (Toggle Plain Text)
  1. Option Explicit
  2. Public cn As New ADODB.Connection
  3. Public cmd As New ADODB.Command
  4. Public rs As New ADODB.Recordset
  5.  
  6. Private Sub removeMileDupes()
  7.     Dim strSQL As String
  8.     If cn.State = 0 Then
  9.         'fileset says which set of files are being loaded at the moment
  10.         Call doDBaction("openDB", fileSet)
  11.     End If
  12.     'clean_dupes tables have ignore duplicates flag on
  13.     'vb catches the error and stops
  14.     'so resume next.
  15.     On Error Resume Next
  16.     strSQL = "TRUNCATE TABLE CLEAN_MILE_DUPES"
  17.     cmd.CommandText = strSQL
  18.     cmd.CommandType = adCmdText
  19.     Set rs = cmd.Execute
  20.  
  21.     strSQL = "INSERT CLEAN_MILE_DUPES (" & vbCrLf _
  22.            & "       CASE_IDENTIFIER" & vbCrLf _
  23.            & "      ,DOCUMENT_TYPE" & vbCrLf _
  24.            & "      ,DOCUMENT_NUMBER" & vbCrLf _
  25.            & "      ,MILESTONE" & vbCrLf _
  26.            & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
  27.            & "      ,MILESTONE_DATE)" & vbCrLf _
  28.            & "SELECT CASE_IDENTIFIER" & vbCrLf _
  29.            & "      ,DOCUMENT_TYPE" & vbCrLf _
  30.            & "      ,DOCUMENT_NUMBER" & vbCrLf _
  31.            & "      ,MILESTONE" & vbCrLf _
  32.            & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
  33.            & "      ,MILESTONE_DATE " & vbCrLf _
  34.            & "  FROM CASE_MILESTONE_TEMP"
  35.     cmd.CommandText = strSQL
  36.     cmd.CommandType = adCmdText
  37.     Set rs = cmd.Execute
  38. 'THIS ^^^ IS THE COMMAND THAT IS FAILING. IT HANGS FOR 5 OR 
  39. 'MORE SECONDS. DOING COUNT(*) ON THAT TABLE GIVES 0 
  40. 'RECORDS. THEREFORE THE FOLLOWING CODE JUST REMOVES 
  41. 'EVERYTHING FROM THE TEMP TABLE.
  42.  
  43.     strSQL = "TRUNCATE TABLE CASE_MILESTONE_TEMP"
  44.     cmd.CommandText = strSQL
  45.     cmd.CommandType = adCmdText
  46.     Set rs = cmd.Execute
  47.  
  48.     strSQL = "INSERT CASE_MILESTONE_TEMP (" & vbCrLf _
  49.            & "       CASE_IDENTIFIER" & vbCrLf _
  50.            & "      ,DOCUMENT_TYPE" & vbCrLf _
  51.            & "      ,DOCUMENT_NUMBER" & vbCrLf _
  52.            & "      ,MILESTONE" & vbCrLf _
  53.            & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
  54.            & "      ,MILESTONE_DATE)" & vbCrLf _
  55.            & "      SELECT CASE_IDENTIFIER" & vbCrLf _
  56.            & "             ,DOCUMENT_TYPE" & vbCrLf _
  57.            & "             ,DOCUMENT_NUMBER" & vbCrLf _
  58.            & "             ,MILESTONE" & vbCrLf _
  59.            & "             ,MILESTONE_DATE_TYPE" & vbCrLf _
  60.            & "             ,MILESTONE_DATE " & vbCrLf _
  61.            & "         FROM CLEAN_MILE_DUPES"
  62.     cmd.CommandText = strSQL
  63.     cmd.CommandType = adCmdText
  64.     Set rs = cmd.Execute
  65.  
  66.     strSQL = "TRUNCATE TABLE CLEAN_MILE_DUPES"
  67.     cmd.CommandText = strSQL
  68.     cmd.CommandType = adCmdText
  69.     Set rs = cmd.Execute
  70. End Sub
I want to remove all records that are duplicates on all fields. This code works perfectly on my machine, but deletes everything on the other two machines. It is failing randomly, it seems, and I'm stumped. I'm an Oracle person, and have been working with SQL Server only 4 months. Even so, I don't know that doing this with Oracle would make a difference.

Thank you so much for your help!
Reputation Points: 10
Solved Threads: 0
Newbie Poster
pixelmeow is offline Offline
9 posts
since Oct 2008
Permalink
Dec 4th, 2008
0

Re: SQL Insert statement syntax

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
Reputation Points: 218
Solved Threads: 201
Veteran Poster
Teme64 is offline Offline
1,024 posts
since Aug 2008
Permalink
Dec 4th, 2008
0

Re: SQL Insert statement syntax

I'm debugging right now, and of course it's working fine on my machine.

One of the other databases didn't have 0 records in the milestone table, it just had about 200,000 less records than it should have.

I'm going to try your advice above, and let you know what happens. Thank you so much!
Reputation Points: 10
Solved Threads: 0
Newbie Poster
pixelmeow is offline Offline
9 posts
since Oct 2008
Permalink
Dec 4th, 2008
0

Re: SQL Insert statement syntax

Click to Expand / Collapse  Quote originally posted by Teme64 ...
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>

Click to Expand / Collapse  Quote originally posted by Teme64 ...
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
Reputation Points: 10
Solved Threads: 0
Newbie Poster
pixelmeow is offline Offline
9 posts
since Oct 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: VB.. help please
Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: need some help





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC