Home All Groups Group Topic Archive Search About

Stored procedure handling 1 parent record and multiple child recor

microsoft.public.dotnet.languages.vb
Author
16 Mar 2006 6:46 AM
alee
I am writing a stored procedure to handle Order and OrderDetail tables. I
created a tempTable physically on the database, and insert the OrderDetails
into the tempTable first, then I try to use the tempTable in a select
statement to insert the all the records to the OrderDetails. If there is any
record failed, rollback all the transaction. But when I run the program, it
generates error message saying that the column name of number of supplied
items does not match table definition.

I have checked the table. It has exactly the same number of columns. The
only difference is the tempTable has no POID, which is an auto number
generated after the Order table has inserted. I use the @POID as the
parameter to pass in the value into the OrderDetail with other columns from
tempTable.

I don't know what goes wrong. Please help me!

Thanks

Anita



create proc trans
    @OrderDate smalldatetime,
    @shipDate smalldatetime,
    @CustID int,
    @SupID int,
    @FavUnfav real,
    @ShipTo char(35),
    @ShipAddress char(50),
    @ShipCity char(15),
    @ShipProv char(10),
    @ShipPC char(10),
    @ShipPhone char(10),
    @AccntPO char(20),
    @POID int OUTPUT

as
begin transaction
insert into tblPO
    values(@OrderDate,@shipDate,@CustID,@SupID,@FavUnfav,@ShipTo,
    @ShipAddress,@ShipCity,@ShipProv,@ShipPC,@ShipPhone,@AccntPO)    set @POID
=@@identity

declare @poDetailInsert varchar(500)
et @poDetailInsert = 'insert into tblPODetail(POID, productID, qtyorder,
qtyship, price, CommissionRate) select '
            + convert(varchar,@POID) + 'ProductID, qtyorder, qtyship, price,
CommissionRate from tempPODetail'

IF @@TRANCOUNT <> 0
BEGIN
   PRINT 'A transaction needs to be rolled back'
  ROLLBACK TRAN
END
else
    commit transaction

delete from tempPODetail
exec(@poDetailInsert)

go

Author
17 Mar 2006 1:31 AM
Jeff Jarrell
the simple way to avoid this is to ALWAYs use column lists...

    insert into SOMETABLE (col1,col2)  <----
       select @col1,@col2

Show quoteHide quote
"alee" <a***@discussions.microsoft.com> wrote in message
news:039F253A-6672-4F1E-8AF4-9C62688340FF@microsoft.com...
>I am writing a stored procedure to handle Order and OrderDetail tables. I
> created a tempTable physically on the database, and insert the
> OrderDetails
> into the tempTable first, then I try to use the tempTable in a select
> statement to insert the all the records to the OrderDetails. If there is
> any
> record failed, rollback all the transaction. But when I run the program,
> it
> generates error message saying that the column name of number of supplied
> items does not match table definition.
>
> I have checked the table. It has exactly the same number of columns. The
> only difference is the tempTable has no POID, which is an auto number
> generated after the Order table has inserted. I use the @POID as the
> parameter to pass in the value into the OrderDetail with other columns
> from
> tempTable.
>
> I don't know what goes wrong. Please help me!
>
> Thanks
>
> Anita
>
>
>
> create proc trans
> @OrderDate smalldatetime,
> @shipDate smalldatetime,
> @CustID int,
> @SupID int,
> @FavUnfav real,
> @ShipTo char(35),
> @ShipAddress char(50),
> @ShipCity char(15),
> @ShipProv char(10),
> @ShipPC char(10),
> @ShipPhone char(10),
> @AccntPO char(20),
> @POID int OUTPUT
>
> as
> begin transaction
> insert into tblPO
> values(@OrderDate,@shipDate,@CustID,@SupID,@FavUnfav,@ShipTo,
> @ShipAddress,@ShipCity,@ShipProv,@ShipPC,@ShipPhone,@AccntPO) set @POID
> =@@identity
>
> declare @poDetailInsert varchar(500)
> et @poDetailInsert = 'insert into tblPODetail(POID, productID, qtyorder,
> qtyship, price, CommissionRate) select '
> + convert(varchar,@POID) + 'ProductID, qtyorder, qtyship, price,
> CommissionRate from tempPODetail'
>
> IF @@TRANCOUNT <> 0
> BEGIN
>   PRINT 'A transaction needs to be rolled back'
>  ROLLBACK TRAN
> END
> else
> commit transaction
>
> delete from tempPODetail
> exec(@poDetailInsert)
>
> go
Author
17 Mar 2006 3:56 AM
alee
Hi Jeff,
Thanks very much for replying my question. I have used the column lists as
you advice. But it still generates the same error message. Please help. Here
is the code:

create proc trans
    @OrderDate smalldatetime,
    @shipDate smalldatetime,
    @CustID int,
    @SupID int,
    @FavUnfav real,
    @ShipTo char(35),
    @ShipAddress char(50),
    @ShipCity char(15),
    @ShipProv char(10),
    @ShipPC char(10),
    @ShipPhone char(10),
    @AccntPO char(20),
    @POID int OUTPUT

as
begin transaction
insert into tblPO
    values(@OrderDate,@shipDate,@CustID,@SupID,@FavUnfav,@ShipTo,
    @ShipAddress,@ShipCity,@ShipProv,@ShipPC,@ShipPhone,@AccntPO)
    set @POID =@@identity

declare @poDetailInsert varchar(500)
set @poDetailInsert = 'insert into tblPODetail(POID, productID, qtyorder,
qtyship, price, CommissionRate) select '
    --        + convert(varchar,@POID) + 'ProductID, qtyorder, qtyship, price,
CommissionRate from tempPODetail'

IF @@TRANCOUNT <> 0
BEGIN
  PRINT 'A transaction needs to be rolled back'
  ROLLBACK TRAN
END
else
    commit transaction

delete from tempPODetail
exec(@poDetailInsert)

go


Show quoteHide quote
"Jeff Jarrell" wrote:

> the simple way to avoid this is to ALWAYs use column lists...
>
>     insert into SOMETABLE (col1,col2)  <----
>        select @col1,@col2
>
> "alee" <a***@discussions.microsoft.com> wrote in message
> news:039F253A-6672-4F1E-8AF4-9C62688340FF@microsoft.com...
> >I am writing a stored procedure to handle Order and OrderDetail tables. I
> > created a tempTable physically on the database, and insert the
> > OrderDetails
> > into the tempTable first, then I try to use the tempTable in a select
> > statement to insert the all the records to the OrderDetails. If there is
> > any
> > record failed, rollback all the transaction. But when I run the program,
> > it
> > generates error message saying that the column name of number of supplied
> > items does not match table definition.
> >
> > I have checked the table. It has exactly the same number of columns. The
> > only difference is the tempTable has no POID, which is an auto number
> > generated after the Order table has inserted. I use the @POID as the
> > parameter to pass in the value into the OrderDetail with other columns
> > from
> > tempTable.
> >
> > I don't know what goes wrong. Please help me!
> >
> > Thanks
> >
> > Anita
> >
> >
> >
> > create proc trans
> > @OrderDate smalldatetime,
> > @shipDate smalldatetime,
> > @CustID int,
> > @SupID int,
> > @FavUnfav real,
> > @ShipTo char(35),
> > @ShipAddress char(50),
> > @ShipCity char(15),
> > @ShipProv char(10),
> > @ShipPC char(10),
> > @ShipPhone char(10),
> > @AccntPO char(20),
> > @POID int OUTPUT
> >
> > as
> > begin transaction
> > insert into tblPO
> > values(@OrderDate,@shipDate,@CustID,@SupID,@FavUnfav,@ShipTo,
> > @ShipAddress,@ShipCity,@ShipProv,@ShipPC,@ShipPhone,@AccntPO) set @POID
> > =@@identity
> >
> > declare @poDetailInsert varchar(500)
> > et @poDetailInsert = 'insert into tblPODetail(POID, productID, qtyorder,
> > qtyship, price, CommissionRate) select '
> > + convert(varchar,@POID) + 'ProductID, qtyorder, qtyship, price,
> > CommissionRate from tempPODetail'
> >
> > IF @@TRANCOUNT <> 0
> > BEGIN
> >   PRINT 'A transaction needs to be rolled back'
> >  ROLLBACK TRAN
> > END
> > else
> > commit transaction
> >
> > delete from tempPODetail
> > exec(@poDetailInsert)
> >
> > go
>
>
>



Post Other interesting topics
exe/dll as scheduled task
Icons in Application Menus
Setting Foreground Color Property at Row Level in Datagrid
Access, OLE & VB.NET
How To Cancel Edits on a control?
Serial Date
How Many SQL Connections Should I Use?
vb.net to c# conversion help please
Name 'ADODB' is not declared Error
Converting Base 2 Numbers to Base 10