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, Oct 15 '15 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, Oct 15 '15 at 19:11
`cmd.Connection = con;` can be moved outside of the if statement — Bennett Elder, Oct 16 '15 at 04:49

score 7 · Answer 1 · edited May 23 '17 at 11:58

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.

Answer can be improved by showing what parameterized queries looks like for this example... at least for the query part. — Bennett Elder, Oct 16 '15 at 04:54

If value exists then update, else insert value in database

1 Answers1