Having a problem inserting products

I am trying to write a bit of code that I can pass a brand name to. If the brand name exists I want to return the brandid to the calling middle tier. If the brand id does not exist I want to insert and then return the new brand id. The code below works unless the brand does not exist. Then it inserts, and I get an application exception. Next time I run the code it continues on until the next time it has to do an insert. So the inserts are working, but getting the value back is resulting in an application excetio.

Middle Tier Function (

privatestaticint GetBrandForProduct(clsProduct o)

{

int brandid = -1;// If the brand name comes in blank use the first word of the overstock product

o.BrandName = o.BrandName.Trim();

// if we do not have a brand for this productif (o.BrandName.Length == 0)return -1;Database db =CommonManager.GetDatabase();;try

{

// Get the brand id for this brand name// If it does not exist we will add it and STILL return a brand idobject obj = db.ExecuteScalar("BrandIDGetOrInsert", o.BrandName);string catid = obj.ToString(); *** FAILING LINE ***returnConvert.ToInt32(obj.ToString());

}

catch (Exception ex)

{

throw ex;return -1;

}

return brandid;

}

Stored Procedure: --------------------------------------------------

ALTER

PROCEDURE [dbo].[BrandIDGetOrInsert]-- Add the parameters for the stored procedure here

@.brandnameparm

varchar(50)

AS

BEGIN

-- SET NOCOUNT ONSELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm)-- If we found a record, exit

if@.@.rowcount> 0return-- We did not find a record, so add a new one.

begin

insertinto brands(Brandname)values(@.brandnameparm)

end

SELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm)

END

Hi Dear,

You have not mention that What is the Exception Message? I will be in better position to answer if you share Exception Message also...

but the One thing that seems wrong in your SP is

if@.@.rowcount> 0return

-- We did not find a record, so add a new one.

begin

insertinto brands(Brandname)values(@.brandnameparm)

end

@.@.rowcount return 0 if select statement didn't find any result......

so i think this check will be like this

if@.@.rowcount = 0return

-- We did not find a record, so add a new one.

begin

insertinto brands(Brandname)values(@.brandnameparm)

end

change this thing in your Store Procedure , if problem doesn't solve ..then post the Exception message...

Thank You

Best Regards,

Muhammad AKhtar Shiekh

|||

Why would I do that? If the rowcount > 0 then I am happy with the first select and I want the SP to exit. It will have returned the brandid that I need. If the rowcount = 0 then I want to do the insert.

The ASP.NET codes an 'object not defined' exception. The brandid is not being returned after the insert - possible two rows are being returned also which I think an executescalar would not be happy with. How do I get only row to return in either case?

|||

patrick24601:

Why would I do that? If the rowcount > 0 then I am happy with the first select and I want the SP to exit. It will have returned the brandid that I need. If the rowcount = 0 then I want to do the insert.

That's what i am saying but there is contradiction in Your SP...it is doing this

if@.@.rowcount> 0return

-- We did not find a record, so add a new one.

begin

insertinto brands(Brandname)values(@.brandnameparm)

end

It is actually inserting when RowCount is greater then 0 ( Not equal to 0)

......

patrick24601:

The ASP.NET codes an 'object not defined' exception. The brandid is not being returned after the insert - possible two rows are being returned also which I think an executescalar would not be happy with. How do I get only row to return in either case?

You can try this code,

ALTER

PROCEDURE [dbo].[BrandIDGetOrInsert]-- Add the parameters for the stored procedure here

@.brandnameparm

varchar(50)ASBEGIN-- SET NOCOUNT ONIFnotexists(SELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm))begininsertinto brands(Brandname)values(@.brandnameparm)endelseSELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm)END

Thanks

Best Regards,

Muhammad AKhtar Shiekh

|||

sorry SP is no correct in above post, Remove the else part in the sp

ALTERPROCEDURE [dbo].[BrandIDGetOrInsert]

-- Add the parameters for the stored procedure here

@.brandnameparm

varchar(50)ASBEGIN-- SET NOCOUNT ONIFnotexists(SELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm))begininsertinto brands(Brandname)values(@.brandnameparm)endSELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm)END|||

i think the problem lies with this:

if (o.BrandName.Length == 0)return -1;

Database db =CommonManager.GetDatabase();

;

Notice the stray ; also the if statement is missing some braces

if (o.BrandName.Length == 0)
{
return -1;
}

Thats what looks wrong to me