Check value from one table in sql and if exists then increment the value in other table

Posted by Xperiment under ASP.NET on 9/4/2010 | Points: 10 | Views : 2257 | Status : [Member] | Replies : 5
Hi, I need some help with asp.net, ado.net & sql
I have 2 sql tables. One stores the book information and other table stores the number of copies. what i want is, I want to first check if the book of same name is already present and if book is already present then it should increment the values in copy column of copies table.

for example, first table contains books information with columns like its name,publisher,edition and second table has name and number of copies. so when I add any book information, then it should check booktable based on book name and if same book is present then it should increment value in copies table. this is what i have written but its not working


SqlConnection additemconn = new SqlConnection(strconn);
additemconn.Open();
SqlCommand checkcomm = new SqlCommand("if EXISTS(select Bname from Books where Bname='" + txtItemname.Text + "') Update Bookcopies set BooksCopies=BooksCopies + 1 where Bname='" + txtItemname.Text + "' End if");
checkcomm.Connection = additemconn;
checkcomm.ExecuteNonQuery();


SqlConnection additemconnadd = new SqlConnection(strconn);
additemconn.Open();
SqlCommand additemcomm = new SqlCommand("Insert into Books values ('" + txtItemname.Text + "','" + txtitemauthor.Text + "','" + lstitemflagged.Text + "','" + txtitempublisher.Text + "','" + txtitemedition.Text + "','" + txtitemdate.Text + "','" + txtItemId.Text + "') ");

additemcomm.Connection = additemconnadd;
additemcomm.ExecuteNonQuery();





Responses

Posted by: T.saravanan on: 9/4/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Xperiment,

Just small changes in your code

SqlCommand checkcomm = new SqlCommand("if EXISTS(select Bname from Books where Bname='" + txtItemname.Text + "') begin Update <BookCopies TableName> set BooksCopies=BooksCopies + 1 where Bname='" + txtItemname.Text + "' End ");

In my suggestion use procedure for this type of insertion and updation.

Cheers :)

Thanks,
T.Saravanan

Xperiment, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Xperiment on: 9/4/2010 [Member] Starter | Points: 25

Up
0
Down
Hey mate, thanks.. it working now.. i was stucked at this since 1 week. thanks again.

Xperiment, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Xperiment on: 9/5/2010 [Member] Starter | Points: 25

Up
0
Down
hi, there is one more syntax problem. Its adding to copies table but only when there is an item in Copies table. so the problem is whenever we are inserting it for first time, the copies table values remains empty as there are no earlier records in it. so after the statement I added else statement but its not working, as i am not sure about the syntax.
this is what I wrote. please rectify. thanks


 SqlCommand checkcomm = new SqlCommand("if EXISTS(select Bname from Books where Bname='" + txtItemname.Text + "') begin Update Bookcopies set BooksCopies=BooksCopies + 1 where BookName='" + txtItemname.Text + "' Else  Insert into BooksCopies values('"+txtItemname.Text+"',"+1+") End");


Xperiment, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Shankul2784 on: 9/5/2010 [Member] Bronze | Points: 25

Up
0
Down
Hi,

in your statement just use the begin & end for the if & else. you will achieve your goal.

Thanks & Regards,

Shailesh

Xperiment, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Xperiment on: 9/5/2010 [Member] Starter | Points: 25

Up
0
Down
Thanks mate. working now. btw there is mistake in fields. so I am putting here correct codes.

SqlCommand checkcomm = new SqlCommand("If exists (Select CdName from CdCopies where CdName='"+txtItemname.Text+"') begin update CdCopies set CdCopies=CdCopies +1 where CdName='"+txtItemname.Text+"' end Else begin  Insert into CdCopies values('"+txtItemname.Text+"',"+1+") end");


Xperiment, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response