Click here to Skip to main content
15,904,153 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
See more:
I am using Asp.net 2008 and LINQ to SQL.
I have aproc:
SQL
create proc sp_InsertProduct
	@ID char(10),
	@ProductName nvarchar(50)
as
	if not exists(select * from PRODUCT where ID=@ID)
		insert into PRODUCT values(@ID,@ProductName)
go

I've drag all proc into DataContext.dbml.
I make a class named Connect:
C#
public string InsertProduct(string ID, string ProductName)
    {
        //
        DataContext dcon = new DataContext();
        if (dcon.sp_InsertProduct(ID, ProductName) == 0)
        {
            return "fail";
        }
        else
        {
            return "success";
        }
    }

Default.aspx.cs:
C#
protected void btnInsert_Click(object sender, EventArgs e)
{
        string msg=con.InsertProduct(txtID.Text.Trim(), txtProductName.Text.Trim());
        Label1.Text = msg;
        //re-load
        //LoadData();
}

I enter:
ID = "P"
ProductName = "Pages"
It works.
I enter ID = "P" again or ID = "Pn" ProductName = "Pen".I always get success.
I tried if (dcon.sp_InsertProduct(ID, ProductName) == 1) but It does not change.
What is the problem?
Posted

That should be obvious from your SP. Since you are checking if a product with the ID to be inserted already exists, the insert wont be done and thus the SP cannot fail. Everything is just dandy.

SQL
if not exists(select * from PRODUCT where ID=@ID) -- This checks if the product ID is not already present in the table


If you would want the SP to fail in case the ID was already present you could try throwing an exception via RAISERROR[^].

Regards,

Manfred
 
Share this answer
 
v3
Comments
giatuan2011 16-May-12 6:55am    
You're right.Use the try {}catch{} for failure results and success
Thank you.
Wendelius 16-May-12 16:49pm    
Good answer!
Hi ,
Try this
SQL
create proc sp_InsertProduct
    @ID char(10),
    @ProductName nvarchar(50)
as
BEGIN
    if not exists(select * from PRODUCT where ID=@ID)      
BEGIN
  insert into PRODUCT values(@ID,@ProductName)
END
END

Best Regards
M.Mitwalli
 
Share this answer
 
SQL
create proc sp_InsertProduct
	@ID char(10),
	@ProductName nvarchar(50)
as
begin
	if not exists(select * from PRODUCT where ID=@ID)
begin
		insert into PRODUCT(ID,ProductName) values(@ID,@ProductName)
end
End
go
 
Share this answer
 
Comments
giatuan2011 16-May-12 7:39am    
It doesn't work.Maybe I should use try catch
Mohamed Mitwalli 16-May-12 8:33am    
you should update your DBML file

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900