Reputation: 29
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.
Upvotes: 1
Views: 1592
Reputation: 46434
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.
Upvotes: 7