Why does only the first INSERT stat

[raydenl]

Why does only the first INSERT statement work???


CREATE PROCEDURE insert_inventory
@item_name varchar(20),
@description varchar(100),
@notes varchar(255),
@amount varchar(8)
AS
DECLARE @item_id int
DECLARE @transaction_date datetime

IF (@item_name = '') SET @item_name = NULL
IF (@description = '') SET @description = NULL
IF (@notes = '') SET @notes = NULL
IF (@amount = '') SET @amount = NULL

SET @item_id = IDENT_CURRENT('inventory')
SET @transaction_date = GETDATE()

INSERT INTO inventory (item_name, item_description, notes) VALUES (@item_name, @description, @notes)

INSERT INTO expenditure (item_id, transaction_date, amount) VALUES (@item_id, @transaction_date, CAST(@amount AS money))

 

[iker3000]

Where are you calling this procedure from?
Do you get any error messages when executing it.

(I'm not sure whether I understand your code correctly, but it seems to me, that you would like to insert a row into the inventory table, and than insert a row into the expenditure table whith the @item_ID, the row in the inventory table got. In this case you should place the row

SET @item_id = IDENT_CURRENT('inventory')

between the two inserts.)

Iker

 

[raydenl]

Oh you are right about putting that between the two INSERT statements, but the 2nd INSERT still does not execute, nothing is inserted into the 'expenditure' table.

I am executing it from VB6. If I take out the 1st INSERT statement the 2nd one works pefectly.

???

 

[raydenl]

I get NO errors

 

[iker3000]

Are you using ADO? We had some similar problems earlier, because ADO only runs the script until the first result is returned (say, the first insert). There is a command like NextRecordset, or something like this, have a look at it.

Iker

 

[raydenl]

Yeah ADO. Cheers, I will have a look.

 

[raydenl]

It inserts into the expenditure table if I enter an amount, but the amount field is NOT NULL, so I need it to complain if nothing is entered in the amount field, but at present it just fails to insert the whole row if amount is NULL

 

[raydenl]

Sweet, I found the problem, I needed to put:

SET NOCOUNT ON

Now it works, I just dont know why

CREATE PROCEDURE insert_inventory
@item_name varchar(20),
@description varchar(100),
@notes varchar(255),
@amount varchar(8)
AS
SET NOCOUNT ON
DECLARE @item_id int
DECLARE @transaction_date datetime

IF (@item_name = '') SET @item_name = NULL
IF (@description = '') SET @description = NULL
IF (@notes = '') SET @notes = NULL
IF (@amount = '') SET @amount = NULL

SET @transaction_date = GETDATE()

INSERT INTO inventory (item_name, item_description, notes) VALUES (@item_name, @description, @notes)
SET @item_id = @@IDENTITY
INSERT INTO expenditure (item_id, transaction_date, amount) VALUES (@item_id, @transaction_date, CAST(@amount AS money))

 

[iker3000]

Well, this is also a solution. The first insert statement returns something like (1 rows affected). SET NOCOUNT OFF prevents messages like this to be sent.

Iker