[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.
Question
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
9.1

vb6 error cannot insert into oracle

Asked by Maritimer in VB Database Programming, Microsoft Programming, Oracle 8.x

I cannot get any of my sql commands for oracle to Update or insert etc. No errors are generated, so I am unsure what is wrong. My select does return a record so I know I am connected. Below is my code. I am using VB6 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:
337:
338:
339:
340:
341:
342:
343:
344:
345:
346:
347:
348:
349:
350:
351:
352:
353:
354:
355:
356:
357:
358:
359:
360:
361:
362:
363:
364:
365:
366:
367:
368:
369:
370:
371:
372:
373:
374:
375:
376:
377:
378:
379:
380:
381:
382:
383:
384:
385:
386:
387:
388:
389:
390:
391:
392:
393:
394:
395:
396:
397:
398:
399:
400:
401:
402:
403:
404:
405:
406:
407:
408:
409:
410:
411:
412:
413:
414:
415:
416:
417:
418:
419:
420:
421:
422:
423:
424:
425:
426:
427:
428:
429:
430:
431:
432:
433:
434:
435:
436:
437:
438:
439:
440:
441:
442:
443:
444:
445:
446:
447:
448:
449:
450:

Sub MesUpload()
 
 
Dim sql         As String
    Dim rs          As ADODB.Recordset
    Dim adoCommand  As ADODB.Command
    Dim seqid As Long
    'Dim rs As ADODB.Recordset
    
On Error GoTo errorhandler
 
sql = " select seq_id "
sql = sql & " from e_cs_sequence_gen where id = 'SEQUENCE_A'"
 
Set rs = New Recordset
 
    rs.ActiveConnection = pubOraCn
    rs.CursorType = adOpenForwardOnly
    rs.LockType = adLockReadOnly
    rs.CursorLocation = adUseClient
    rs.Open sql, pubOraCn
    rs.ActiveConnection = Nothing
 
    If rs.RecordCount = 0 Then
        Exit Sub
    Else
        seqid = rs!seq_id
    End If
    
    rs.Close
    Set rs = Nothing
    
'********************************************
 
sql = " update e_cs_sequence_gen set seq_id = " & seqid & " + (select count(*) from eqp_import)"
sql = sql & " where id = 'SEQUENCE_A'"
 
Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
 
 
Set adoCommand = Nothing
'********************************************
sql = " commit"
'
Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
 
 
Set adoCommand = Nothing
 
'****************************************************************************************
 
sql = " insert into eqp_temp("
sql = sql & " select device_id, description, model, serial_number, owner, location,"
sql = sql & " category_1, category_2, subarea," & seqid & " + rownum "
sql = sql & " From EQP_import)"
 
Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
 
 
Set adoCommand = Nothing
'********************************************
sql = " commit"
'
Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
 
 
Set adoCommand = Nothing
 
 
 
'--Add to E_CS_OBJECT_DETAILS
'
sql = " MERGE INTO meslive.e_cs_object_details OBJ"
sql = sql & " USING ("
sql = sql & " SELECT device_id,"
sql = sql & " description,"
sql = sql & " seq_id"
sql = sql & " FROM meslive.eqp_temp) TEMP"
sql = sql & " ON (OBJ.object_id = TEMP.device_id)"
sql = sql & " WHEN MATCHED THEN"
sql = sql & " UPDATE SET OBJ.description = OBJ.description"
sql = sql & " WHEN NOT MATCHED THEN"
sql = sql & " INSERT (object_type, object_id,      version, description,      updated_by, updated_date, seq_id,      active, recno)"
sql = sql & " VALUES ('DEVICE',    TEMP.device_id, 0,       TEMP.description, 'SYSTEM',   systimestamp, TEMP.seq_id, 0,      0)"
 
Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
 
 
Set adoCommand = Nothing
 
'--Add to E_CS_DEVICE_DETAILS
'
sql = " MERGE INTO meslive.e_cs_device_details DET"
sql = sql & " USING ("
sql = sql & " SELECT device_id,"
sql = sql & " seq_id"
sql = sql & " FROM meslive.eqp_temp) TEMP"
sql = sql & " ON (DET.device_id=TEMP.device_id)"
sql = sql & " WHEN MATCHED THEN"
sql = sql & " UPDATE SET DET.seq_id = DET.seq_id " 'do nothing
sql = sql & " WHEN NOT MATCHED THEN"
sql = sql & " INSERT (device_id,      seq_id)"
sql = sql & " VALUES (TEMP.device_id, TEMP.seq_id)"
'
Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
 
 
Set adoCommand = Nothing
'--Add to SUBAREA
'
sql = " MERGE INTO meslive.e_cs_object_references REF"
sql = sql & " USING ("
sql = sql & " SELECT device_id,"
sql = sql & " subarea,"
sql = sql & " seq_id"
sql = sql & " From MESLIVE.eqp_temp"
sql = sql & " where subarea is not null) TEMP"
sql = sql & " ON (REF.object_id       = TEMP.device_id AND"
sql = sql & " REF.object_type     = 'DEVICE'       AND"
sql = sql & " REF.ref_object_id   = TEMP.subarea   AND"
sql = sql & " REF.ref_object_type = 'SUBAREA')"
sql = sql & " WHEN MATCHED THEN"
sql = sql & " UPDATE SET REF.active = REF.active"      '/* DO NOTHING */
sql = sql & " WHEN NOT MATCHED THEN"
sql = sql & " INSERT (ref_object_type, ref_object_id, ref_object_version, object_type, object_id     , version, seq_id,"
sql = sql & " ref_order,"
sql = sql & " active)"
sql = sql & " VALUES ('SUBAREA'      , TEMP.subarea,  0                 , 'DEVICE'   , TEMP.device_id, 0      , TEMP.seq_id,"
sql = sql & " (select max(ref_order) from e_cs_object_references where ref_object_type = 'SUBAREA' and ref_object_id = subarea),"
sql = sql & " 1)"
 
Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
 
 
Set adoCommand = Nothing
'--Add to DEVICE_CATEGORY 1
'
sql = " MERGE INTO meslive.e_cs_object_references REF"
sql = sql & " USING ("
sql = sql & " SELECT device_id,"
sql = sql & " category_1,"
sql = sql & " seq_id"
sql = sql & " From MESLIVE.eqp_temp"
sql = sql & " where category_1 is not null) TEMP"
sql = sql & " ON (REF.object_id       = TEMP.device_id  AND"
sql = sql & " REF.object_type     = 'DEVICE'        AND"
sql = sql & " REF.ref_object_id   = TEMP.category_1 AND"
sql = sql & " REF.ref_object_type = 'DEVICE_CATEGORY')"
sql = sql & " WHEN MATCHED THEN"
sql = sql & " UPDATE SET REF.active = REF.active"            '/* DO NOTHING */
sql = sql & " WHEN NOT MATCHED THEN"
sql = sql & " INSERT (ref_object_type  , ref_object_id , ref_object_version, object_type, object_id     , version, seq_id,"
sql = sql & " ref_order,"
sql = sql & " active)"
sql = sql & " VALUES ('DEVICE_CATEGORY', TEMP.category_1,  0               , 'DEVICE'   , TEMP.device_id, 0      , TEMP.seq_id,"
sql = sql & " (select max(ref_order) from e_cs_object_references where ref_object_type = 'DEVICE_CATEGORY' and ref_object_id = category_1),"
sql = sql & " 1)"
 
Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
 
 
Set adoCommand = Nothing
'--Add to DEVICE_CATEGORY 2
sql = " MERGE INTO meslive.e_cs_object_references REF"
sql = sql & " USING ("
sql = sql & " SELECT device_id,"
sql = sql & " category_2,"
sql = sql & " seq_id"
sql = sql & " From MESLIVE.eqp_temp"
sql = sql & " where category_2 is not null) TEMP"
sql = sql & " ON (REF.object_id       = TEMP.device_id  AND"
sql = sql & " REF.object_type     = 'DEVICE'        AND"
sql = sql & " REF.ref_object_id   = TEMP.category_2 AND"
sql = sql & " REF.ref_object_type = 'DEVICE_CATEGORY' )"
sql = sql & " WHEN MATCHED THEN"
sql = sql & " UPDATE SET REF.active = REF.active"           ' /* DO NOTHING */
sql = sql & " WHEN NOT MATCHED THEN"
sql = sql & " INSERT (ref_object_type  , ref_object_id , ref_object_version, object_type, object_id     , version, seq_id,"
sql = sql & " ref_order,"
sql = sql & " active)"
sql = sql & " VALUES ('DEVICE_CATEGORY', TEMP.category_2,  0               , 'DEVICE'   , TEMP.device_id, 0      , TEMP.seq_id,"
sql = sql & " (select max(ref_order) from e_cs_object_references where ref_object_type = 'DEVICE_CATEGORY' and ref_object_id = category_2),"
sql = sql & " 1)"
 
Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
 
 
Set adoCommand = Nothing
 
'--Add to E_CS_RESOURCE_DETAILS
sql = " insert into e_cs_resource_details (resource_type, resource_id, version, state, comments)"
sql = sql & " (select 'DEVICE',device_id, 0, 'AVAIL','New Equipment Listing' from eqp_temp"
sql = sql & " where device_id not in (select resource_id from e_cs_resource_details where"
sql = sql & " resource_type = 'DEVICE')) "
 
Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
 
 
Set adoCommand = Nothing
 
sql = " MERGE INTO meslive.e_cs_resource_details RES"
sql = sql & " USING ("
sql = sql & " SELECT device_id"
sql = sql & " FROM meslive.eqp_temp) TEMP"
sql = sql & " ON (RES.resource_id       = TEMP.device_id  AND"
sql = sql & " RES.resource_type     = 'DEVICE')"
sql = sql & " WHEN MATCHED THEN"
sql = sql & " UPDATE SET RES.state = RES.state "           '/* DO NOTHING */
sql = sql & " WHEN NOT MATCHED THEN"
sql = sql & " INSERT (resource_type, resource_id   , version, state  , comments)"
sql = sql & " VALUES ('DEVICE'     , TEMP.device_id, 0      , 'AVAIL', 'New Equipment Listing')"
 
 
Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
 
 
Set adoCommand = Nothing
 
'--Add ATTRIBUTES
 
sql = " MERGE INTO meslive.e_cs_attributes ATT"
sql = sql & " USING ("
sql = sql & " SELECT device_id,"
sql = sql & " model as value,"
sql = sql & " 'MODEL' as attribute_id"
sql = sql & " FROM meslive.eqp_temp) TEMP"
sql = sql & " ON (ATT.object_id = TEMP.device_id AND"
sql = sql & " ATT.attribute_id = TEMP.attribute_id)"
sql = sql & " WHEN MATCHED THEN"
sql = sql & " UPDATE SET ATT.value = TEMP.value"
sql = sql & " WHEN NOT MATCHED THEN"
sql = sql & " INSERT (ATT.object_type, ATT.object_id,  ATT.version, ATT.attribute_id,  ATT.value)"
sql = sql & " VALUES ('DEVICE',        TEMP.device_id,           0, TEMP.attribute_id, TEMP.value)"
'
 
Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
 
 
Set adoCommand = Nothing
 
sql = " MERGE INTO meslive.e_cs_attributes ATT"
sql = sql & " USING ("
sql = sql & " SELECT device_id,"
sql = sql & " SERIAL_NUMBER as value,"
sql = sql & " 'SERIAL NUMBER' as attribute_id"
sql = sql & " FROM meslive.eqp_temp) TEMP"
sql = sql & " ON (ATT.object_id = TEMP.device_id AND"
sql = sql & " ATT.attribute_id = TEMP.attribute_id)"
sql = sql & " WHEN MATCHED THEN"
sql = sql & " UPDATE SET ATT.value = TEMP.value"
sql = sql & " WHEN NOT MATCHED THEN"
sql = sql & " INSERT (ATT.object_type, ATT.object_id,  ATT.version, ATT.attribute_id,  ATT.value)"
sql = sql & " VALUES ('DEVICE',        TEMP.device_id,           0, TEMP.attribute_id, TEMP.value)"
 
Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
 
 
Set adoCommand = Nothing
 
 
sql = "MERGE INTO meslive.e_cs_attributes ATT"
sql = sql & " USING ("
sql = sql & " SELECT device_id,"
sql = sql & " owner as value,"
sql = sql & " 'OWNER' as attribute_id"
sql = sql & " FROM meslive.eqp_temp) TEMP"
sql = sql & " ON (ATT.object_id = TEMP.device_id AND"
sql = sql & " ATT.attribute_id = TEMP.attribute_id)"
sql = sql & " WHEN MATCHED THEN"
sql = sql & " UPDATE SET ATT.value = TEMP.value"
sql = sql & " WHEN NOT MATCHED THEN"
sql = sql & " INSERT (ATT.object_type, ATT.object_id,  ATT.version, ATT.attribute_id,  ATT.value)"
sql = sql & " VALUES ('DEVICE',        TEMP.device_id,           0, TEMP.attribute_id, TEMP.value)"
 
Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
 
 
Set adoCommand = Nothing
 
 
sql = " MERGE INTO meslive.e_cs_attributes ATT"
sql = sql & " USING ("
sql = sql & " SELECT device_id,"
sql = sql & " location as value,"
sql = sql & " 'LOCATION' as attribute_id"
sql = sql & " FROM meslive.eqp_temp) TEMP"
sql = sql & " ON (ATT.object_id = TEMP.device_id AND"
sql = sql & " ATT.attribute_id = TEMP.attribute_id)"
sql = sql & " WHEN MATCHED THEN"
sql = sql & " UPDATE SET ATT.value = TEMP.value"
sql = sql & " WHEN NOT MATCHED THEN"
sql = sql & " INSERT (ATT.object_type, ATT.object_id,  ATT.version, ATT.attribute_id,  ATT.value)"
sql = sql & " VALUES ('DEVICE',        TEMP.device_id,           0, TEMP.attribute_id, TEMP.value)"
 
Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
 
 
Set adoCommand = Nothing
 
 
 
 
 
 
sql = " commit"
 Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
  Set adoCommand = Nothing
  
  
sql = " delete from EQP_import"
 Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
    Set adoCommand = Nothing
    
    
    
sql = " commit"
 Set adoCommand = New ADODB.Command
 
    With adoCommand
        .ActiveConnection = pubOraCn
        .CommandText = sql
        .CommandType = adCmdText
        .Execute , sql
    End With
  Set adoCommand = Nothing
 
 
errorhandler:
    MsgBox Err.Description, vbCritical
    
End Sub
[+][-]02/06/09 02:53 AM, ID: 23568514Accepted Solution

View this solution now by starting your 30-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

About this solution

Zones: VB Database Programming, Microsoft Programming, Oracle 8.x
Sign Up Now!
Solution Provided By: choo_chu
Participating Experts: 2
Solution Grade: A
 
[+][-]02/06/09 05:45 AM, ID: 23569614Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]02/06/09 05:53 AM, ID: 23569668Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]02/08/09 11:36 PM, ID: 23587973Assisted Solution

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 30-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]02/08/09 11:45 PM, ID: 23588009Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]02/09/09 05:59 AM, ID: 23589980Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]02/10/09 12:19 AM, ID: 23598307Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20091111-EE-VQP-92 - Hierarchy / EE_QW_3_20080625