ASP: conditional update or insert record

Tweet

There are situations when we need to insert or update a record in a table according to its presence. The condition is: if the record is already in the table, then update it, otherwise insert it.

Here we will see how to do it.

Some assumptions

To achive our goal, the first task is to determine which is the condition upon which we decide to update or insert the record.
I will show you an example, that will apply to a specific situation, however, after understanding the logic, you can think of something related to your personal goal.
In the following example, we have a table (table1) with 3 columns: id, field1 and field2. We have a form submitting data. Then there's another table (table2), linked to the first table through the id field. The columns of this second table are: id and field3.
The first table (table1) might have records linked to the second table (table2), while table2 already contains records. That is a classic situation in relational databases. The point here is that we know that a unique record is present in table2, and we want to insert a new unique record in table1 if it's not already present, otherwise we need to update it.
I hope that the above explanation is clear enough.

The condition

In our situation, the condition is the id column. We know that it is present in table2, but we have to determine if it's present in table1.
When we create the form, we need to query the database, including the records from table2 and what is present in table1.
Something like:

SELECT table2.id, table2.field3, table1.id AS chk, table1.field1, table1.field2
FROM table2 LEFT OUTER JOIN table1 ON table2.id = table1.id
WHERE table2.id = 1

As you can see, we get the id from table2 (and we know it's there) and from table1, giving it the name "chk". That will be our condition: if chk is null, it means that the record is not present in table1.
Said that, we can build our conditional query (insert or update).

The query

I will show you all the necessary code to perform our conditional query. What might change in your case is the condition explained above. Remember that everything revolves around that condition. You might need to find a way of creating yours, however, once you have that, you're almost done with your work.
Ok, let's see the code:

Dim editCmd

Set editCmd = Server.CreateObject ("ADODB.Command")
editCmd.ActiveConnection = yourconnectionstring

Dim chk
Dim id
Dim field1
Dim field2
chk = request.Form("chk")
id = request.Form("id")
field1 = Request.Form("field1")
field2 = Request.Form("field2")
if chk <>"" then
   editCmd.CommandText = "UPDATE dbo.table2 SET field1 = '" & field1 &"', field2 = '" & field2 & "' WHERE id = '" & id&"'"
else
   editCmd.CommandText = "INSERT INTO dbo.table2 (id, field1, field2) VALUES ('" & id & "', '" & field1 & "', '" & field2 & "')"
end if
editCmd.Prepared = true
editCmd.Execute
editCmd.ActiveConnection.Close

Remember to change yourconnectionstring with your database connection string.
As you can see, the result is quite simple. Basically we change the editCmd.CommandText (which is our query string), according to the presence of chk (which is our condition variable).

I hope I made myself clear enough. As usual, if you need more help, let me know by commenting the post.

Have a nice day!