使用sqlquery的问题 [英] Problem to using sqlquery

问题描述

我的表具有以下字段
BranchId产品名称库存

如果表具有给定的branchid，产品名，我想更新特定行的库存"字段

否则我要插入行

怎么做


请帮助我

My table has following field
BranchId Productname Stock

If the table has Given branchid,productname I want update Stock field for particular row

Else I want Insert the Row

How to do it


Please help me

推荐答案

尝试使用以下方法解决:

Try working it out with this:

IF EXISTS(SELECT * FROM YourTable WHERE BranchId = @branchID AND  Productname = @productName)
BEGIN
    UPDATE YourTable
    SET Stock = @stock
    WHERE BranchId = @branchID AND  Productname = @productName
END
ELSE
BEGIN
    INSERT INTO YourTable
    VALUES(@branchID, @productName, @stock)
END

C#代码可以是这样的:

C# code can be something like this:

SqlCommand cmd = yourConnection.CreateCommand();
cmd.CommandText = queryFromAbove;
cmd.Parameters.AddWithValue("@branchID", branchid);
cmd.Parameters.AddWithValue("@productName", productName);
cmd.Parameters.AddWithValue("@stock", stock);

try
{
   if(yourConnection.State != ConnectionState.Open)
   {
        yourConnection.Open()
   } 
   cmd.ExecuteNonQuery(); 
}
catch(SqlException sqlex)
{
    //handle your sql exception here
}
catch(exception ex)
{
    //handle your other exception here
}
finally
{
    cmd.Dispose();
}

您可能想做一个upsert，但是它在sqlserver中不存在.但请在此处查看: http://www.sergeyv. com/blog/archive/2010/09/10/sql-server-upsert-equivalent.aspx [

You would want to do an upsert, but it doesn''t exist in sqlserver. But have a look here: http://www.sergeyv.com/blog/archive/2010/09/10/sql-server-upsert-equivalent.aspx[^]

Good luck!

您可以编写以下查询以插入新行:

you can write following query for inserting new row:

ConnObj.Open()
        Dim mycommand As SqlCommand = New SqlCommand
        mycommand.Connection = ConnObj
        mycommand.CommandText = "insert into TableName" & " (branchId, productName) " & " VALUES (@branchId, @productName)"

        mycommand.Parameters.AddWithValue("@branchId", textBox1.Text)
        mycommand.Parameters.AddWithValue("@productName", textBox2.Text)
        mycommand.ExecuteNonQuery()
ConnObj.Close()

或者，如果您要更新任何一行，都可以执行以下操作:

or if you want to update any single row than you can do the following:

Dim myconn As New SqlConnection()
        myconn = New SqlConnection(ConnectionString)
        Dim mycom As SqlCommand = New SqlCommand()
        mycom.Connection = myconn
        myconn.Open()
        mycom.CommandText = "update TableName set productName= @productName where branchId='" + textbox1.Text + "'"
        mycom.Parameters.AddWithValue("@productName", textbox2.Text)
        mycom.ExecuteNonQuery()
        myconn.Close()

希望对您有帮助.

hope it will help you.

这篇关于使用sqlquery的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！