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
