Check whether record has inserted

Question

I have a query (hypothetical example below)

if not exists(select productID from Products where supplierID=3)insert into Products(productName, price) values('Socks', 23)

What I am trying to do is insert a record in the Products table if there isn't any other record existing with the same supplierID. The query I have above works fine. What i would like to do after this query has executed is to retrieve the productIDif a record was found and use it to insert a record into another table, say invoices. I am trying to do this with count which isn't much help like so.(always resolves to the else statement.

int count=query.ExcecuteNonQuery();
if(count==1){
    return "found";
}
else{
    return "not found";
}

Anyone who can help with an effective solution for this?

UPDATE

So I tried this in Management Studio

declare @row int if not exists(select productID from Products where supplierID=3)insert into Products(productName, price) values('Socks', 23) select @rows=@@rowcount select @rows as rows

When I run this query in Management studio it returns 0 if no row was inserted and 1 if a row was inserted however when i try to replicate the query in my code and alert rows it displays 0 each time. Why could this be?

Possible duplicate http://stackoverflow.com/q/5228780/1513471 — believe me, Jul 15 '15 at 14:48
@LeandroSoares I am passing variables from ajax. This is just how I've been doing it so far. If you have a more effective method please share — Wairimu Murigi, Jul 15 '15 at 14:48
@Wairimu Murigi, With EF you don't need to use sql, it's called Code First, where a class (Model) represents a table: https://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/creating-an-entity-framework-data-model-for-an-asp-net-mvc-application — Leandro Soares, Jul 15 '15 at 14:51
@LeandroSoares Thanks for the link, however I know of code first and I have used it severally, the structure of the pages that I have require me to use javascript to save and retrieve data on some of those pages hence the ajax option... — Wairimu Murigi, Jul 15 '15 at 14:55
The approach is correct. You should show us the real query, along with the real data and the actual values involved with the execution, most likely data isn't what you think it is and as a result the `IF NOT EXISTS` is never true (I'm ready to bet hard cash that while building a real-world example to post here you'll figure out the issue) — Alex, Jul 15 '15 at 15:11
@Alex I have tested with both scenarios where I even confirm which operation was carried out by checking the database table. I included an `alert` to fire and display the returned data and it always evaluates to the else statement. in short the `if, else` statement doesn't give me the information i need — Wairimu Murigi, Jul 15 '15 at 15:21

score 1 · Accepted Answer · answered Jul 15 '15 at 14:55

1

Maybe, you can try with @@ROWCOUNT=0 then insert in other table. It returns 0 if your query does not find any row. But you must use it immediate after your query because it stores info only for last executed query.

I am not sure that this can help you but i would try to solve this situation on this way...

answered Jul 15 '15 at 14:55

MiroslavStojakovic

167
1
8

When I use this in management studio it works as it should but when i try to replicate the same in code it gives the same result where it finds a row or not. Let me update my question with what i tried to do – Wairimu Murigi Jul 15 '15 at 16:41
This query helped me to some extent. I finally arrived at a way to do it though it took me some bit of code and time to figure it out. Hence I will mark it as the answer. – Wairimu Murigi Jul 16 '15 at 14:20

Check whether record has inserted

1 Answers1