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
LVL 7
ayha1999Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EyalCommented:
use stored procedure to add lines to dataset.
there you should make a validation before the actual insert
0
ayha1999Author Commented:
pls provide me sample sp.

thanks
0
EyalCommented:
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

0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

EyalCommented:
ooops... found mistake and fixed it
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 dataset 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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EyalCommented:
another fix:
change
qty<@CurrentQuantity
to
qty>@CurrentQuantity
0
ayha1999Author Commented:
How can I execute the following line?

select @CurrentQuantity = count(*) from dataset where id=@ID
0
EyalCommented:
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
0
ayha1999Author Commented:
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.
       
0
EyalCommented:
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)
0
ayha1999Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.