Re: ASP & SQL - Very urgent please!

From: Jim Kennedy <kennedy-family_at_attbi.com>
Date: Tue, 19 Mar 2002 05:29:48 GMT
Message-ID: <g1Al8.66106$af7.47318_at_rwcrnsc53>


Also use bind variables. That won't solve this problem - Mike is probably correct.
Jim
"Mike & Jen" <mhuneycu_at_tampabay.rr.com> wrote in message news:m%wl8.187134$Dl4.21557836_at_typhoon.tampabay.rr.com...
> Judith,
> I suspect that the problem is that you are doing an insert into member
> which is firing a the trigger which does an
> insert into member which fires the trigger. Basically you are in an
> infinant loop (or not so infinant, because Oracle
> prevents that). Recursive SQL are SQL statements which are not directly
> executed by you but are called based on triggers or some other event such
 as
> extent allocation.
>
> Mike H. Sr.
> "Judith" <judithlemo_at_hotmail.com> wrote in message
> news:d95531b6.0203181231.416a81fc_at_posting.google.com...
> > Hi Everybody,
> >
> > I have a problem with inserting records into an oracle database, could
> > you please help?
> > This is the error that I am getting and I have no idea what the
> > problem is. Please advise!
> >
> > Technical Information (for support personnel)
> >
> > Error Type:
> > Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> > [Oracle][ODBC][Ora]ORA-00036: maximum number of recursive SQL levels
> > (50) exceeded ORA-06512: at "MJ864.MAKESEQUENCE", line 2 ORA-04088:
> > error during execution of trigger 'MJ864.MAKESEQUENCE' ORA-06512: at
> > "MJ864.MAKESEQUENCE", line 2 ORA-04088: error during execution of
> > trigger 'MJ864.MAKESEQUENCE' ORA-06512: at "MJ864.MAKESEQUENCE", line
> > 2 ORA-04088: error during execution of trigger 'MJ864.MAKESEQUENCE'
> > ORA-06512: at "MJ864.MAKESEQUENCE", line 2 ORA-04088: error during
> > execution of trigger 'MJ864.MAKESE
> > /~mj864/newmember/acknowledge.asp, line 71
> >
> >
> > The line 71 that this refers to is
> >
> > MyConn.Execute "INSERT INTO MEMBER (memberID, membership_type,
> > firstName, middleName, lastName, address, city, postcode, telephone,
> > e_mail, date_of_birth, start_date, interest, objective, password,
> > verify_password, paymentmethod, personal_trainer_id, membership_id)
> > VALUES
>
 ('"&Request.Form("memberID")&"','"&Request.Form("membership_type")&"','"&Req
>

 uest.Form("firstName")&"','"&Request.Form("middleName")&"','"&Request.Form("
>

 lastName")&"','"&Request.Form("address")&"','"&Request.Form("city")&"','"&Re
>

 questForm("postcode")&"','"&Request.Form("telephone")&"','"&Request.Form("e_
>

 mail")&"','"&Request.Form("date_of_birth")&"','"&Request.Form("start_date")&
> "','"&Request
> >
>

 Form("interest")&"','"&Request.Form("objective")&"','"&Request.Form("passwor
>

 d")&"','"&Request.Form("verify_password")&"','"&Request.Form("Paymentmethod"
> )&"','"&Request.Form("personal_trainer_id")&"','"&Request.Form("membership
 _i
> d")&"')"
> >
> > I suspect the problem is with the trigger, and below is the trigger
> > that I am using.
> >
> > create or replace trigger makeSequence
> > before insert on member
> > for each row
> > begin
> > insert into member values (NEWMEMBERSEQ.nextval, :new.MEMBERSHIP_TYPE,
> > :new.FIRSTNAME, :new.MIDDLENAME, :new.LASTNAME,
> > :new.ADDRESS, :new.CITY, :new.POSTCODE, :new.TELEPHONE, :new.E_MAIL,
> > :new.DATE_OF_BIRTH, :new.START_DATE, :new.INTEREST,
> > :new.OBJECTIVE, :new.CURRENT_LEVEL, :new.STATUS, :new.PASSWORD,
> > :new.VERIFY_PASSWORD, :new.PAYMENTMETHOD, :new.PERSONAL_TRAINER_ID,
> > :new.MEMBERSHIP_ID);
> > end;
> > /
> >
> > Your help will be much appreciated.
> >
> > Many thanks,
> >
> > Judith
> >
>
>
Received on Tue Mar 19 2002 - 06:29:48 CET

Original text of this message