CODEWITHSUNDEEP
c#sql-servervisual-studio-2013asp.net-mvc-5
Wairimu Murigi
Wairimu Murigi

Reputation: 2267

Check whether record has inserted

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?

Upvotes: 0

Views: 588

Answers (1)

MiroslavStojakovic
MiroslavStojakovic

Reputation: 167

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...

Upvotes: 1

Related Questions