Problem to using sqlquery

Question

0.00/5 (No votes)

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 26-Mar-12 23:48pm

devausha

Add a Solution

Comments

Oshtri Deka 27-Mar-12 6:00am

Why is this tagged with C# tag?

[no name] 28-Mar-12 11:11am

are you using any Stored proc or any ORM tool ?

4 solutions

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!

Posted 27-Mar-12 0:00am

E.F. Nijboer

Solution 3

you can write following query for inserting new row:

VB

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:

VB

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.

Posted 27-Mar-12 0:06am

ShivKrSingh

Updated 27-Mar-12 0:07am

v2

Solution 4

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

Regards

Posted 28-Mar-12 5:49am

mhwasim

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Oshtri Deka · Accepted Answer · 2012-03-26T23:57:00

Try working it out with this:

SQL

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:

C#

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();
}