Solved

VB Component Problem

Posted on 2004-08-20
18
330 Views
Last Modified: 2012-06-27
Hi There,

I am having a problem with a component that i have recently written. The component is to delete records from a db by passing a parameter to a stored procedure in Oracle. This is working fine when i am just passing one parameter but fails to work when i pass any more than one. I have tested the stored procedure in the SQL window passing in multiple parameters and that works fine so it can't be that. However, when i test the VB in a test harness i have written it falls over on any more than one value. The VB currently looks like this:

Public Sub VMWARE_USER_ID_DEL(ByVal i_VMWARE_USER_ID As String, ByRef o_oErr As Variant)
'----------------------------------------------------------------------
' Purpose: Update a category
'----------------------------------------------------------------------
  Dim dbCmd As ADODB.Command

  On Error GoTo ErrHandler
 
  If Not OpenConnection(o_oErr) Then
    Exit Sub
  End If
 
  Set dbCmd = New ADODB.Command
  With dbCmd
    .ActiveConnection = dbConn
    .CommandType = adCmdStoredProc
    .CommandText = "VMWARE_PACKAGE.VMWARE_USER_ID_DEL"
  End With
 
  'Input parameter
  dbCmd.Parameters.Append dbCmd.CreateParameter("VUT_VM_USER_ID", adVarChar, adParamInput, 6, i_VMWARE_USER_ID)
 
  'Output parameters
  dbCmd.Parameters.Append dbCmd.CreateParameter("o_oErr", adNumeric, adParamOutput, 10)
 
  'Execute Stored Procedure
  dbCmd.Execute , , adExecuteNoRecords
 
  'Check for an error returning from the Stored procedure
  lngError = NullsToZero(dbCmd.Parameters("o_oErr").Value)
  If lngError <> 0 Then
    Err.Raise vbObjectError + 1, , "SQLError : " & lngError
  End If
 
CleanUp:
  Set dbCmd.ActiveConnection = Nothing
  Set dbCmd = Nothing
  CloseConnection (lngError)
Exit Sub
 
ErrHandler:
  o_oErr = Err.Number
  RaiseError MODULE_NAME, "VMWARE_USER_ID_DEL"
  GoTo CleanUp

End Sub

I am testing this using the code:

Private Sub cmdDel_Click()
Dim o_oErr As Long

Set ObjVM = New CH_GEN_VMWARE.cVMWare

Call ObjVM.VMWARE_USER_ID_DEL(V55555, pErr)

Set ObjVM = Nothing

End Sub

The 'V55555' in the above is the parameter i pass in and this works fine. However, if i try to add any more the code falls over. Is there a way i can pass multiple values for the same parameter? I am fairly new to this so please excuse if this is a naive question.

Many thanks,

tom
0
Comment
Question by:tommydowns
18 Comments
 
LVL 15

Expert Comment

by:unknown_routine
Comment Utility
Your question is not naive but it is not very clear.

As the 1st step:

Dim o_oErr As Long <============= defined as long

Set ObjVM = New CH_GEN_VMWARE.cVMWare

Call ObjVM.VMWARE_USER_ID_DEL(V55555, pErr)  <=========pErrr is defined as Variant


Both parameters must be same type. try to define both o_O-err as long ,pErr

This is the 1st problem I see here.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
The stored procedure is defined to accept 1 value for the parameter, so if you want to pass several values:
* either modify the stored procedure as needed
* call the proc for each value you have to pass

This is the same for EVERY programming language, even VB ;-)

CHeers
0
 

Author Comment

by:tommydowns
Comment Utility
HI there,

Thanks very much for your comments. I will try to explain the problem i am getting a little more clearly.

I have an ASP page that is returning User ID values dynamically based on a query done against an Oracle db. These values are looped through and each returned next to a checkbox. As they are being returned dynamically all the text boxes have the same name - "checkbox1".  If a user selects this value it is stored in a variable in the ASP and passed across to a processing page where it is entered into the call to the VB. This is shown below:

Set myObj = Server.CreateObject("CH_GEN_VMWARE.cVMWare")

strDeleteID = Request.Form("checkbox1")
Call MyObj.VMWARE_USER_ID_DEL(strDeleteID, o_oErr)

strDeleteID here is all the values from the checkboxes that were ticked on the previous page. If i write this out in the asp using - Response.Write strDeleteID - the values are written out in a comma-delimited list so i assume this is how they are being passed to the component?

I have tested passing parameters to the stored procedure in a SQL window in oracle  as a comma delimited list and this works fine - i.e. it deletes the relevant values that i pass in.

However, when i pass more than one parameter to the VB Component it falls over. What i basically want the component to do is allow me to pass an unspecified number of paprameters to it which can then be passed into the Stored Procedure.

Does that make this any clearer? Apologies for being vague before.

Many thanks,

Tom
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
Yes, this makes things clear and confirms your misunderstanding/confusion about parameters and values.

Actually your oracle stored procedure VMWARE_PACKAGE.VMWARE_USER_ID_DEL
will have some statement like this:
DELETE sometable WHERE somefield IN ( yourparam )

Actually, your parameter will be interpreted as a single VALUE, not as a list. You cannot pass as string and assume that oracle will interprete it the same as you think it is.
Here how YOU think it should read like
Call MyObj.VMWARE_USER_ID_DEL('1,44,89,102', o_oErr)
-->  DELETE sometable WHERE somefield IN ( 1,44,89,102 )
But oracle really sees this:
-->  DELETE sometable WHERE somefield IN ( '1,44,89,102' )
BTW, exactly the way you passed the value, remember the ' ' around your values. The fact that you pass a parameter doesn't change this.

To overcome this, you either need
* to change your oracle stored procedure to split up the parameter VALUE and run the query for each of the subvalues you passed (delimited by the commas)
* to change your oracle stored procedure to run the query as a dynamic query, building your query string to that oracle finally will see what you expect it to see (see DBMS_SQL package reference)

* OR change you VB code to run the component with each item (subvalue) once
* or change your component to do the split, and build 1 (big) SQL string that runs the oracle procedure several times

Each of the above has it's pros and it's cons, none is PERFECT.
The first one has the drawback that the parameter size is limited
the second one has basically the same drawback + the negative impact of using dynamic sql
the third one will probably require more network traffic, as it runs several distinct sql requests over the network
the forth one has the problem of control in case of errors...

Good luck when doing the choice
CHeers

0
 
LVL 6

Expert Comment

by:PePi
Comment Utility
they way i would do it is i'll parse the comma-delimited values. after parsing the value i would
    Call MuObj.VMWARE_USER_ID_DEL passing the parsed values one at a time
loop through until all the comma-deleted values have been parsed and passed
0
 

Author Comment

by:tommydowns
Comment Utility
HI there,

Thank you very much for this advice. However, i am only familiar with the basics of stored procedures. The procedure you described does quite closely mirror the one that i have created. Currently it looks like this:

Procedure VMWARE_USER_ID_DEL(
i_VMWARE_USER_ID       IN VMWARE_USER_TABLE.VUT_VM_USER_ID%Type,

o_oErr                   OUT NUMBER
)
is
BEGIN

        o_oErr := 0;

DELETE FROM VMWARE_USER_TABLE WHERE VMWARE_USER_TABLE.VUT_VM_USER_ID IN (i_VMWARE_USER_ID);

        pl('Just finished Update');

EXCEPTION
  WHEN OTHERS
  THEN
  o_oErr := SQLCODE;
  SYSTEM.intranet_utils.intranet_log_errors
  ('Web PhoneBook',
  SYSTEM.intranet_utils.intranet_get_errmsg,
  'ERROR RETRIEVING DATA IN VMWARE_USER_ID_DEL.');
END VMWARE_USER_ID_DEL;

The values i basically pass into this are all going to look the same in the format V99999 - although obviously the numbers will change with each ID. As you correctly pointed out above - when i pass the parameters in the format ('V99999','V88888') it works fine in Oracle but notwhen i run it through the ASP. I would rather change the procedure than the component as this is proving a real pain to install on a fairly unreliable server....(had quite a day trying!)...do you know the mechanics of splitting the values up in the procedure?

Many thanks again. Your help with this is very much appreciated!

Tom
0
 
LVL 3

Expert Comment

by:PocketLintPPC
Comment Utility
If this works when
V55555 = "1234"
Call ObjVM.VMWARE_USER_ID_DEL(V55555, pErr)

Then try this to do many at once...
V55555 = "1234,3333,4444"
Call ObjVM.VMWARE_USER_ID_DEL(V55555, pErr)

I'm assuming you are trying something like..??
Call ObjVM.VMWARE_USER_ID_DEL(V55555,V66666,V77777 pErr)


Hope this helps some.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
Procedure VMWARE_USER_ID_DEL(
i_VMWARE_USER_ID      IN VARCHAR,

o_oErr                OUT NUMBER
)
is
DECLARE v_VMWARE_USER_ID VMWARE_USER_TABLE.VUT_VM_USER_ID%Type;
BEGIN

       o_oErr := 0;
   
      WHIL(E INSTR(i_VMWARE_USER_ID, ",") > 0)
      LOOP
           v_VMWARE_USER_ID := SUBSTR ( i_VMWARE_USER_ID, 1, INSTR(i_VMWARE_USER_ID, ",")-1 );
           i_VMWARE_USER_ID := SUBSTR ( i_VMWARE_USER_ID, INSTR(i_VMWARE_USER_ID, ",")+1 );
 
   
          DELETE FROM VMWARE_USER_TABLE WHERE VMWARE_USER_TABLE.VUT_VM_USER_ID IN (v_VMWARE_USER_ID);

      END LOOP;
     
      IF ( i_VMWARE_USER_ID <> '' )
      THEN
         DELETE FROM VMWARE_USER_TABLE WHERE VMWARE_USER_TABLE.VUT_VM_USER_ID IN (i_VMWARE_USER_ID);
      END IF;

       pl('Just finished Update');

EXCEPTION
  WHEN OTHERS
  THEN
  o_oErr := SQLCODE;
  SYSTEM.intranet_utils.intranet_log_errors
  ('Web PhoneBook',
  SYSTEM.intranet_utils.intranet_get_errmsg,
  'ERROR RETRIEVING DATA IN VMWARE_USER_ID_DEL.');
END VMWARE_USER_ID_DEL;

This is UNTESTED code, I just typed it in here...
It should give you an idea of how it should work
0
 

Expert Comment

by:mattcorc
Comment Utility
HI there,

Thanks very much for getting back to me regarding my problem. I know you said the code above is untested but i am trying to run it and am getting some errors. Firstly the line:

 DECLARE v_VMWARE_USER_ID VMWARE_USER_TABLE.VUT_VM_USER_ID%Type;

Returns an error unless i move it below the BEGIN line. Will this cause problems to move it there?

Secondly the line:

  WHILE(INSTR(i_VMWARE_USER_ID, ",") > 0)

Returns the error:

PLS-00103: Encountered the symbol "(" when expecting one of the following:

   constant exception <an identifier>

Have you any idea why this may be happening. Apologies for asking you to debug but i am unfamiliar with this use of sql

Many thanks,

Tom
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:tommydowns
Comment Utility
Apologies for the above. Was logged into my work account rather than my personal account. The above comment still applies though.

Thanks,

Tom
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
Indeed, the DECLARE needs to be after the BEGIN :-)

regarding the WHILE line, the outer brackets should be removed:

      WHILE  INSTR(i_VMWARE_USER_ID, ",") > 0
      LOOP
         <while body>
      END LOOP;

CHeers
0
 

Author Comment

by:tommydowns
Comment Utility
Hi there,

Thanks for the quick response. I'm now getting a few new errors.

The first is on the line LOOP and the error is:

PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:

   * & = - + ; < / > at in mod not rem <an exponent (**)>

Thes second is on the line:

 i_VMWARE_USER_ID := SUBSTR ( i_VMWARE_USER_ID, INSTR(i_VMWARE_USER_ID, ",")+1 );

And the error is:

PLS-00103: Encountered the symbol "=" when expecting one of the following:

   constant exception <an identifier>

And the final error is on the line:

 DELETE FROM VMWARE_USER_TABLE WHERE VMWARE_USER_TABLE.VUT_VM_USER_ID IN (v_VMWARE_USER_ID);

Error:

PLS-00103: Encountered the symbol "DELETE" when expecting one of the following:

   begin function package pragma procedure subtype type use

Some of these may be due to others though might they?

Thanks again for all the help AngelIII - this problem has been causing me major headaches for a good few days! :-)

Tom
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
I see now: i type "," instead of ',' in the INSTR() function as second argument...
Same as for the second usage of INSTR() function, replace it also there...
CHeers
0
 

Author Comment

by:tommydowns
Comment Utility
Hi there angelIII

Thanks for getting back to me. Sorry to be a real pain but that fix hasn't changed anything. I'm still getting all the same errors as before. Any other ideas what could be causing them?

Many thanks,

Tom
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
Here some code that should compile:

CREATE OR REPLACE PROCEDURE VMWARE_USER_ID_DEL(
i_vmware_user_id      IN VARCHAR2,
o_oerr                OUT NUMBER
)
IS
BEGIN
DECLARE
v_vmware_user_id NUMBER;
v_vmware_temp VARCHAR2(200) := i_vmware_user_id;
 BEGIN
      o_oerr := 0;

      WHILE (INSTR(v_vmware_temp, ',') > 0)
      LOOP
           v_vmware_user_id := SUBSTR ( v_vmware_temp, 1, INSTR(v_vmware_temp, ',')-1 );
           v_vmware_temp := SUBSTR ( v_vmware_temp, INSTR(v_vmware_temp, ',')+1 );

          DELETE FROM vmware_user_table WHERE vmware_user_table.vut_vm_user_id = TO_NUMBER(v_vmware_user_id);

      END LOOP;

      IF ( v_vmware_temp <> '' )
      THEN
         DELETE FROM vmware_user_table WHERE vmware_user_table.vut_vm_user_id = TO_NUMBER(v_vmware_temp);
      END IF;

       pl('Just finished Update');

EXCEPTION
  WHEN OTHERS
  THEN
  o_oerr := SQLCODE;
  SYSTEM.intranet_utils.intranet_log_errors
  ('Web PhoneBook',
  SYSTEM.intranet_utils.intranet_get_errmsg,
  'ERROR RETRIEVING DATA IN VMWARE_USER_ID_DEL.');
 
END;
END;
/

CHeers
0
 

Author Comment

by:tommydowns
Comment Utility
Hi there AngelIII

Thanks very much for all your help with this. That does compile but i am still having trouble getting this to work to delete multiple values. Using the above code it no longer deletes single values. As i am too unsure as to what is going on with the code i will award you the points now for all your help and try to find an alternative workaround? Let me know what you think and i will arrange this.

Many thanks,

Tom
0
 

Author Comment

by:tommydowns
Comment Utility
Hi again AngelIII

Just for your info....

The error i get in Event Viewer for this is:

Application uses a value of the wrong type for the current operation.

Not exactly sure what is springing this error but thought you may have some ideas? It may be something unrelated to the procedure.

Thanks again,

Tom
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
Now, with the new proc, you need to pass the single value also as varchar, but to locate the error, you should PRECISELY be able to locate the code line that raises the error.

I would check this first:
dbCmd.Parameters.Append dbCmd.CreateParameter("VUT_VM_USER_ID", adVarChar, adParamInput, 6, i_VMWARE_USER_ID)

Actually, the value 6 here indicates the size, which would indicate that you only can pass a string of 6 characters long to the proc, which is NOT true. give there a higer size, ie 200, and see if that helps.

dbCmd.Parameters.Append dbCmd.CreateParameter("VUT_VM_USER_ID", adVarChar, adParamInput, 200, i_VMWARE_USER_ID)

CHeers
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now