# Duplicate entry checking with Azure database

**URL:** <https://forum.inductiveautomation.com/t/duplicate-entry-checking-with-azure-database/9747>\
**Category:** Ignition\
**Created:** [February 25, 2015, 9:23pm UTC](https://forum.inductiveautomation.com/t/duplicate-entry-checking-with-azure-database/9747 "2015-02-25T21:23:50Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![dfarnes](https://avatars.discourse-cdn.com/v4/letter/d/ea666f/32.png) [@dfarnes](https://forum.inductiveautomation.com/u/dfarnes)\
**Post date:** [February 25, 2015, 9:23pm UTC](https://forum.inductiveautomation.com/t/duplicate-entry-checking-with-azure-database/9747/1 "2015-02-25T21:23:50Z")

</div>

I have a short script which saves a product name to a Azure SQL database. My question is how to test for and disallow duplicate names. I know I can put a constraint on the database which will throw an exception if a duplicate is entered. Also, I know I can use a “try … except” block to catch the exception. What I don’t know is how to identify that particular exception so it can be handled. Any ideas would be most appreciated! We are using Ignition Ver. 7.7.2, and here the script I’m using, which works fine except for the need to check for dups.

[code]varIng = event.source.parent.getComponent(‘Ingredient’).text  
varIngID = event.source.parent.parent.SEL\_ING

if (varIng == “”):  
system.gui.messageBox(“Ingredient name cannot be blank”)

else:  
if varIngID \> 0:  
#edit Ingredient  
varIngID = system.db.runPrepUpdate(“UPDATE Ingredients SET INGREDIENT = ?  
WHERE Ingrd\_id = ?”, [varIng, varIngID])

```
	else:
			# Add Ingredient
			varIngID = system.db.runPrepUpdate("INSERT INTO Ingredients (INGREDIENT) VALUES (?)",
			[varIng], getKey=1)

```

system.db.refresh(event.source.parent.parent.getComponent(‘Ing\_Tbl’),‘data’)  
[/code]

---

<div class="post-metadata">

**Author:** ![JGJohnson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jgjohnson/32/29582_2.png) [@JGJohnson](https://forum.inductiveautomation.com/u/JGJohnson)\
**Post date:** [February 25, 2015, 10:29pm UTC](https://forum.inductiveautomation.com/t/duplicate-entry-checking-with-azure-database/9747/2 "2015-02-25T22:29:01Z")

</div>

You could use a stored procedure to handle it all for you and then take appropriate action based on the return value.

---

<div class="post-metadata">

**Author:** ![SeanT](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@SeanT](https://forum.inductiveautomation.com/u/SeanT)\
**Post date:** [February 25, 2015, 11:06pm UTC](https://forum.inductiveautomation.com/t/duplicate-entry-checking-with-azure-database/9747/3 "2015-02-25T23:06:12Z")

</div>

You can use

```auto
SELECT COUNT(*) 
FROM Ingredients
WHERE Ingrd_id = x
```

and use your result to decide whether to INSERT or UPDATE.

Alternatively, search for UPSERT - bunch of articles out there explaining how you can do it in one transaction.

---

<div class="post-metadata">

**Author:** ![dfarnes](https://avatars.discourse-cdn.com/v4/letter/d/ea666f/32.png) [@dfarnes](https://forum.inductiveautomation.com/u/dfarnes)\
**Post date:** [February 26, 2015, 8:50pm UTC](https://forum.inductiveautomation.com/t/duplicate-entry-checking-with-azure-database/9747/4 "2015-02-26T20:50:01Z")

</div>

Thank you both for your reply’s! And yes, I’m fairly new to SQL (but learning quickly).

SeanT - Either your code example or an UPSERT looks like what I’m looking for. My question now: would it be best to do this as a stored procedure as JGJohnson suggests, or it is possible to put it into a script behind a button? Adding it behind a button would be my preference, but I don’t see a good way to do that.

Again, thanks for all the assistance!

---

<div class="post-metadata">

**Author:** ![SeanT](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@SeanT](https://forum.inductiveautomation.com/u/SeanT)\
**Post date:** [February 27, 2015, 2:01pm UTC](https://forum.inductiveautomation.com/t/duplicate-entry-checking-with-azure-database/9747/5 "2015-02-27T14:01:32Z")

</div>

Azure is a cloud service right? Knowing that, and knowing that upsert is not too complicated, I’d probably lean toward keeping the query in Ignition.