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?
 
EyalConnect With a Mentor Commented:
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
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.