Multiple Insert

Hello,

I am trying to enter data.
I have a loop to insert data into 3 tables.
the data entered to the first table only once based on the action.

Ex:
on error resume next
if action="dir" then
strqry1="insert into issuemain(formid,trdate) values(...)
conn.beginTrans
conn.execute strqry1
if Err.number <> 0 then checkerror 'procedure
executeIssueDet 'procedure to insert into sub table
conn.commitTrans
else
conn.beginTrans
executeIssueDet
conn.commitTrans
end if

response.write "Operation succeeded"
conn.close

sub executeIssueDet()
for j=0 to ubound(arr1)
indno=arr1(j)
strQry2="insert into issuedetails(...) values(...)
strQry3="update inventory set issuedqty=issuedqty+" & curqty & " where
invindex=" & indno
conn.execute strQry2
if Err.number<>0 then checkerror
conn.execute strQry3
if err.number <> 0 then checkerror
next

end sub

sub checkerror()
response.write "ERROR"
Err.clear
conn.RollbackTrans
conn.close
response.end
end sub



If any query is wrong(data type mismatch or any)
its not running checkerror procedure. It must rollback all executions had
before. Its not doing. The first insert(strQry1) is having data and rest no
data(cos some error in the qry). What could be the problem.

Re: Multiple Insert

On Wed, 19 Jan 2005 17:01:02 -0800, Rajani
wrote:

Remove or comment out the On Error Resume Next line. Response.Write
queries.

Jeff


> I am trying to enter data.
>I have a loop to insert data into 3 tables.
>the data entered to the first table only once based on the action.
>
>Ex:
>on error resume next
> if action="dir" then
> strqry1="insert into issuemain(formid,trdate) values(...)
> conn.beginTrans
> conn.execute strqry1
> if Err.number <> 0 then checkerror 'procedure
> executeIssueDet 'procedure to insert into sub table
> conn.commitTrans
> else
> conn.beginTrans
> executeIssueDet
> conn.commitTrans
> end if
>
>response.write "Operation succeeded"
> conn.close
>
> sub executeIssueDet()
> for j=0 to ubound(arr1)
> indno=arr1(j)
> strQry2="insert into issuedetails(...) values(...)
> strQry3="update inventory set issuedqty=issuedqty+" & curqty & " where
>invindex=" & indno
> conn.execute strQry2
> if Err.number<>0 then checkerror
> conn.execute strQry3
> if err.number <> 0 then checkerror
> next
>
> end sub
>
> sub checkerror()
> response.write "ERROR"
> Err.clear
> conn.RollbackTrans
> conn.close
> response.end
> end sub
>
>
>
>If any query is wrong(data type mismatch or any)
> its not running checkerror procedure. It must rollback all executions had
>before. Its not doing. The first insert(strQry1) is having data and rest no
>data(cos some error in the qry). What could be the problem.
>

Re: Multiple Insert

No, i want to check whether its rollback or not. So i only gave wrong data.
Its not coming to checkerror procedure. Some insert statements are executing
and its not doing rollback if any statement in the loop got error. If this
happens like this, wrong data will be updated or inserted to the database. Is
my way is correct to insert?




"Jeff Cochran" wrote:

> On Wed, 19 Jan 2005 17:01:02 -0800, Rajani
> wrote:
>
> Remove or comment out the On Error Resume Next line. Response.Write
> queries.
>
> Jeff
>
>
> > I am trying to enter data.
> >I have a loop to insert data into 3 tables.
> >the data entered to the first table only once based on the action.
> >
> >Ex:
> >on error resume next
> > if action="dir" then
> > strqry1="insert into issuemain(formid,trdate) values(...)
> > conn.beginTrans
> > conn.execute strqry1
> > if Err.number <> 0 then checkerror 'procedure
> > executeIssueDet 'procedure to insert into sub table
> > conn.commitTrans
> > else
> > conn.beginTrans
> > executeIssueDet
> > conn.commitTrans
> > end if
> >
> >response.write "Operation succeeded"
> > conn.close
> >
> > sub executeIssueDet()
> > for j=0 to ubound(arr1)
> > indno=arr1(j)
> > strQry2="insert into issuedetails(...) values(...)
> > strQry3="update inventory set issuedqty=issuedqty+" & curqty & " where
> >invindex=" & indno
> > conn.execute strQry2
> > if Err.number<>0 then checkerror
> > conn.execute strQry3
> > if err.number <> 0 then checkerror
> > next
> >
> > end sub
> >
> > sub checkerror()
> > response.write "ERROR"
> > Err.clear
> > conn.RollbackTrans
> > conn.close
> > response.end
> > end sub
> >
> >
> >
> >If any query is wrong(data type mismatch or any)
> > its not running checkerror procedure. It must rollback all executions had
> >before. Its not doing. The first insert(strQry1) is having data and rest no
> >data(cos some error in the qry). What could be the problem.
> >
>
>

Re: Multiple Insert

Rajani wrote:
> Hello,
>
> I am trying to enter data.
> I have a loop to insert data into 3 tables.

What database? Type and version?

> the data entered to the first table only once based on the action.
>
> Ex:
> on error resume next
> if action="dir" then
> strqry1="insert into issuemain(formid,trdate) values(...)
> conn.beginTrans
> conn.execute strqry1
> if Err.number <> 0 then checkerror 'procedure

There is nothing here to prevent the next line from executing if an error
occurred. You need to use "else" to correctly process this ... oh wait! you
use response.end in your checkerror procedure. This should work, but it's
not very clear to somebody reading the code. I would prefer something like

begin transaction
execute statement
if err<>0 then
checkerror 'remove response.end from procedure
else
execute second statement
if err <> 0 then
checkerror
else
commit transaction
end if
end if

>
> If any query is wrong(data type mismatch or any)
> its not running checkerror procedure. It must rollback all
> executions had before.

"All" the executions? Even the ones that were committed?

> Its not doing. The first insert(strQry1) is
> having data and rest no data(cos some error in the qry). What could
> be the problem.

I don't know. If the database was SQL Server, I would prefer to handle this
in a stored procedure rather than using ADO's transaction control.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.