Problem using proc to insert

Question

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 16-May-12 0:35am

giatuan2011

Add a Solution

3 solutions

Solution 2

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

Posted 16-May-12 0:58am

Mohamed Mitwalli

Solution 3

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

Posted 16-May-12 1:19am

Vasim889

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Manfred Rudolf Bihy · Accepted Answer · 2012-05-16T00:44:00

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