If value exists then update, else insert value in database

Question

I having a problem that if a value in 4 of my textbox - ID, Type of Room, Rate, Extra Charge; if Type of Room is exist in the database then update, and if not exist then insert to database.

public void existRoomType()
{
    con.Open();
    string typetable = "tblRoomType";
    string existquery = "SELECT*FROM tblRoomType WHERE RoomType = '" + txtRoomType.Text + "'";
    da = new SqlDataAdapter(existquery, con);
    da.Fill(ds, typetable);
    int counter = 0;
    if (counter < ds.Tables[typetable].Rows.Count)
    {
        cmd.Connection = con;
        string edittypequery = "UPDATE tblRoomType SET RoomType = '" + txtRoomType.Text + "', RoomRate = '" + txtRateOfRoom.Text + "', ExtraCharge = '" + txtExtraCharge.Text + "', CancelFee = '" + txtCancelFee.Text + "', MaxOccupant = " + txtMaxOccupants.Text + "" +
            "WHERE TypeID = '" + txtTypeID.Text + "'";
        cmd.CommandText = edittypequery;
        cmd.ExecuteNonQuery();

        MessageBox.Show("Type of Room is added.", "Room Type Management", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    else
    {
        cmd.Connection = con;
        string addtypequery = "INSERT INTO tblRoomType VALUES ('" + txtTypeID.Text + "','" + txtRoomType.Text + "','" + txtRateOfRoom.Text + "','" + txtExtraCharge.Text + "','" + txtCancelFee.Text + "'," + txtMaxOccupants.Text + ")";
        cmd.CommandText = addtypequery;
        cmd.ExecuteNonQuery();

        MessageBox.Show("Type of Room is edited.", "Room Type Management", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    con.Close();
}

If I change the condition if statement from counter < ds.Tables[typetable].Rows.Count to counter > ds.Tables[typetable].Rows.Count, I can add value but I can't edit/update in the database.

I presume you're using Microsoft SQL Server--please confirm that, as the syntax differs between SQL implementations. — STW
– STW, Commented Oct 15, 2015 at 18:25
You need to read up on sql injection, this is a textbook example. You need to use parameterized queries. And don't do things like select * to check for the existence of a row. Use EXISTS. — Sean Lange
– Sean Lange, Commented Oct 15, 2015 at 19:11
cmd.Connection = con; can be moved outside of the if statement — user3269584
– user3269584, Commented Oct 16, 2015 at 4:49

Community · Accepted Answer · 2017-05-23 11:58:38Z

6

What you're looking for is an "UPSERT" statement. An upsert combines an insert and update statement, and will perform the relevant action. It's been available since MS SQL 2003, but wasn't fully baked until SQL Server 2008, where the MERGE function was introduced.

Here's a code sample, taken from another answer. This article is also referenced by that answer as providing a good introduction to using the MERGE statement.

MERGE 
   member_topic AS target
USING 
   someOtherTable AS source
ON 
   target.mt_member = source.mt_member 
   AND source.mt_member = 0 
   AND source.mt_topic = 110
WHEN MATCHED THEN 
   UPDATE SET mt_notes = 'test'
WHEN NOT MATCHED THEN 
   INSERT (mt_member, mt_topic, mt_notes) VALUES (0, 110, 'test')
;

The benefit of this approach is that it only requires a single SQL query, whereas your current approach requires two queries. It also avoids mixing languages, which is generally good for maintainability.

You should also be using Parameterized Queries to pass your variable values to SQL. This will give you protection against SQL injection.

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Oct 15, 2015 at 18:11

STW

46.8k17 gold badges109 silver badges170 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3269584 Over a year ago

Answer can be improved by showing what parameterized queries looks like for this example... at least for the query part.

Collectives™ on Stack Overflow

If value exists then update, else insert value in database

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related