Click here to Skip to main content
14,303,059 members
Rate this:
Please Sign up or sign in to vote.
See more:
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
Posted
Comments
Oshtri Deka 27-Mar-12 6:00am
   
Why is this tagged with C# tag?
Liju Sankar 28-Mar-12 11:11am
   
are you using any Stored proc or any ORM tool ?
Rate this:
Please Sign up or sign in to vote.

Solution 1

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# 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();
}
   
v2
Comments
Amir Mahfoozi 27-Mar-12 6:00am
   
+5
Oshtri Deka 27-Mar-12 6:09am
   
Edit: I've added C# code.
Liju Sankar 28-Mar-12 11:10am
   
good once, my +5
Rate this:
Please Sign up or sign in to vote.

Solution 2

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!
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

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.
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 4

Use Merge statment...Its easy and do this for u

Regards
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100