在sql中检查一个表中的值，如果存在，则在另一个表中递增该值 [英] Check value from one table in sql and if exists then increment the value in other table

问题描述

我需要有关asp.net，ado.net& sql
我有2个sql表.一个存储书籍信息，另一个表存储份数.我想要的是，我首先要检查是否存在相同名称的书，如果已经存在该书，则应该增加副本表的副本列中的值.
例如，第一个表包含书籍信息，其名称，发布者，版本等列均在其中，第二个表则包含名称和副本数.因此，当我添加任何图书信息时，它都应该根据图书名称检查图书桌，如果存在相同的图书，则应该增加副本表中的值.这是我写的，但是没用

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

<pre><br />
SqlConnection additemconn = new SqlConnection(strconn);<br />
            additemconn.Open();<br />
            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");<br />
            checkcomm.Connection = additemconn;<br />
            checkcomm.ExecuteNonQuery();<br />
            <br />
            <br />
            SqlConnection additemconnadd = new SqlConnection(strconn);<br />
            additemconn.Open();<br />
            SqlCommand additemcomm = new SqlCommand("Insert into Books values (''" + txtItemname.Text + "'',''" + txtitemauthor.Text + "'',''" + lstitemflagged.Text + "'',''" + txtitempublisher.Text + "'',''" + txtitemedition.Text + "'',''" + txtitemdate.Text + "'',''" + txtItemId.Text + "'') ");<br />
            <br />
            additemcomm.Connection = additemconnadd;<br />
            additemcomm.ExecuteNonQuery();</pre>

推荐答案

对于您的问题，您可能想要一些类似
的sql

For your question perhaps you want some sql like

IF EXISTS(select Bname from Books where Bname=''NEWBOOKNAME'') BEGIN
  Update Bookcopies set BooksCopies=BooksCopies + 1 where Bname=''NEWBOOKNAME''
END ELSE BEGIN
 Insert into Books .....
End

在一个SQL中.

可能是您可以编写存储过程来代替.并使用参数来避免注入.

in one SQL.

May be you can write a stored procedure instead. and use parameters to avoid injection.

如果您在if..else查询块中取消了终止的if，您的代码将运行良好. sql中没有End If.它只有一个End

Your code will work good if you take off the terminating if in your if..else query block. There is no End If in sql..its only an End

首先，请使用storedproc而不是内联查询.其次，删除End If

Firstly, use the storedproc rather than inline queries. Secondly, remove the End If

这篇关于在sql中检查一个表中的值，如果存在，则在另一个表中递增该值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！