asked on

Query

I have a table and data as follows.

table1
id qty
1 5
2 2

and a dataset as follows.
id item
1 item1
1 item2
1 item3
1 item4
1 item5
2 item6
2 item6

here qty for id=1 in the table1 is 5 and the no. of items in the dataset is 5. how can I prevent users adding another item into the dataset with id=1 or id=2 (qty is 2 for id=2 in the table.) I am using asp.net 4 and sql 2005. the sql connection string, commands and dataset are already created.

please help.

ayha

Eyal

use stored procedure to add lines to dataset.
there you should make a validation before the actual insert

ayha1999

ASKER

pls provide me sample sp.

thanks

Eyal

something like that...

CREATE PROCEDURE ProcedureName
	@ID int, --<--change this to the real field type
	@Name nvarchar(4000)--<--change this to the real field type
AS
BEGIN
	
	SET NOCOUNT ON;
	
	declare @CurrentQuantity int
	
	select @CurrentQuantity = count(*) from table1 where id=@ID
	
	if exists(select * from table1 where ID=@ID and qty<@CurrentQuantity)
    bagin
		insert into dataset (id,item) values ()
	end
END
GO

Open in new window

ASKER CERTIFIED SOLUTION

Eyal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eyal

another fix:
change
qty<@CurrentQuantity
to
qty>@CurrentQuantity

ayha1999

ASKER

How can I execute the following line?

select @CurrentQuantity = count(*) from dataset where id=@ID

Eyal

it will run in the stored procedure

if you will run
select count(*) from dataset where id=1
the result will be assigned to @CurrentQuantity

ayha1999

ASKER

I am not even able to create the sp

select @CurrentQuantity = count(*) from dataset where id=@ID <--- dataset is an invalid object

I am trying the dataset set loop as

int i=0;
foreach (DataRow obDr in objDT.Rows)
{
cmdCheck.Parameters["@Id"].Value = obDr["id"].ToString ();
cmdCheck.ExecuteNonQuery ();
i += 1;
}

pls check.

Eyal

I don't know your database structure so change the names according to the real names

first try to create the procedure in the database
after that check it through sql management studio
and finally implement it in the code (from the snippet you gave here I don't understand what objDT contains)

ayha1999

ASKER

the dataTable contains objDT is the name of the datatable (memory table)
id item
1 item1
1 item2
1 item3
1 item4
1 item5
2 item6
2 item6

I am trying to compare contents of the sql table with the datable created in the code. I think the question was not clear to you.
I cannot create sp because the datatable name can be used these.