Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VB Component Problem

Posted on 2004-08-20
18
Medium Priority
?
346 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
ID: 11851740
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11851751
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
ID: 11851956
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11852200
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
ID: 11852325
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
ID: 11852326
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
ID: 11862281
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11867578
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
ID: 11869174
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
 

Author Comment

by:tommydowns
ID: 11869184
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11869308
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
ID: 11869484
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11869825
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
ID: 11870209
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11870635
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
ID: 11879099
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
ID: 11879168
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 11879494
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses

926 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