Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • Last Modified:

Stored Procedure Problem

My table sturcuture is below...

the coloums names are:
CatID, LabgID, EnglishID, ParentCategoryID, CategoryName, Show, OrderBy, PopRating

I want to write a stored procedure to add cateogies if they are a different language...
The english information (catID: 22,108) is present for all the categories ( I don't list them here since there are too many...)

the parameters to input should be the englishID of the Category and the CategoryName.

I wrote a procedure but it doesn't seem to be working... (Posted under the table)

Any help would be great!
thanks!
318	FRE	1	0	Automobile	               0	1	NULL
327	FRE	2	318	Accessoires	               0	NULL	NULL
328	FRE	6	0	Éducation	                        0	NULL	NULL
329	FRE	3	0	VĂȘtements Et Accessoires         0	NULL	NULL
22	ENG	6	0	Education	                        0	5	NULL
108	ENG	69	22	Academic	                        0	1        NULL
 
 
ALTER PROCEDURE [dbo].[usp_EnterLangFree]
	-- Add the parameters for the stored procedure here
	@LangId char(3), 
	@EnglishID int,
	@CategoryName nvarchar(MAX)
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @ParentCategoyID int
 
	SELECT DISTINCT @ParentCategoyID=ParentCategoyID FROM Categories WHERE EnglishID=@EnglishID AND LanguageID = @LangId
	
	IF (@ParentCategoyID = null) 
		BEGIN
		Return 1;
		DECLARE @HoldID1 int
		DECLARE @HoldID2 int
 
		SELECT DISTINCT @HoldID1=ParentCategoyID FROM Categories WHERE EnglishID=@EnglishID AND LanguageID = 'ENG'
		
		SELECT DISTINCT @HoldID2=EnglishID FROM Categories WHERE CategoryID=@HoldID1 AND LanguageID = 'ENG'
		
		SELECT DISTINCT @ParentCategoyID=CategoryID FROM Categories WHERE EnglishID=@HoldID2 AND LanguageID = @LangId
	
 
	END
 
 
	IF EXISTS (
		SELECT 1
		FROM dbo.Categories
		WHERE EnglishID = @EnglishID
		AND LanguageID = @LangId
		)
		BEGIN
		--Category already addded. Do an update.
			UPDATE Categories SET CategoryName=@CategoryName WHERE EnglishID=@EnglishID AND LanguageID = @LangId
	END
	ELSE
		BEGIN
		-- Insert into Users Table
		INSERT INTO Categories ( 
			LanguageID, 
			EnglishID,
			ParentCategoyID, 
			CategoryName, 
			Show
		)
		VALUES (
			@LangId, 
			@EnglishID,
			@ParentCategoyID,	
			@CategoryName, 
			'0'
		)
    END
END

Open in new window

0
copyPasteGhost
Asked:
copyPasteGhost
  • 4
  • 3
1 Solution
 
Daniel WilsonCommented:
You're not telling me in what way it's failing.  Still, here are a few pointers / improvements.  If these don't get it, please tell me a little bit more about where it's failing.
ALTER PROCEDURE [dbo].[usp_EnterLangFree]
        -- Add the parameters for the stored procedure here
        @LangId char(3), 
        @EnglishID int,
        @CategoryName nvarchar(MAX)
 
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        DECLARE @ParentCategoyID int
 
        SELECT @ParentCategoyID= Max(ParentCategoyID) FROM Categories WHERE EnglishID=@EnglishID AND LanguageID = @LangId
--                            ^^^  I Changed Distinct to MAX b/c MAX is guaranteed to return a single value
        
        IF (@ParentCategoyID IS null) 
--                           ^^^ To compare to NULL, use IS, not =
                BEGIN
                Return 1;
                DECLARE @HoldID1 int
                DECLARE @HoldID2 int
 
                SELECT @HoldID1=MAX(ParentCategoyID) FROM Categories WHERE EnglishID=@EnglishID AND LanguageID = 'ENG'
                
                SELECT @HoldID2=Max(EnglishID) FROM Categories WHERE CategoryID=@HoldID1 AND LanguageID = 'ENG'
                
                SELECT @ParentCategoyID=Max(CategoryID) FROM Categories WHERE EnglishID=@HoldID2 AND LanguageID = @LangId
        
 
        END
 
 
        IF EXISTS (
                SELECT 1
                FROM dbo.Categories
                WHERE EnglishID = @EnglishID
                AND LanguageID = @LangId
                )
                BEGIN
                --Category already addded. Do an update.
                        UPDATE Categories SET CategoryName=@CategoryName WHERE EnglishID=@EnglishID AND LanguageID = @LangId
        END
        ELSE
                BEGIN
                -- Insert into Users Table
                INSERT INTO Categories ( 
                        LanguageID, 
                        EnglishID,
                        ParentCategoyID, 
                        CategoryName, 
                        Show
                )
                VALUES (
                        @LangId, 
                        @EnglishID,
                        @ParentCategoyID,       
                        @CategoryName, 
                        '0'
                )
    END
END

Open in new window

0
 
copyPasteGhostAuthor Commented:
Thank you,

I have tried your sugesstions and I am getting the same problem..

The problem was @ParentCategoyID was always ending up null..


I have now isolated the problem to the @langID parameter. for some reason it's empty, all the time.

Any ideas why? I'm passing it in my application, also if i run tests from within SQL management studio as well as visual studio I'm still getting the empty value.

Thanks,
Ghost
0
 
copyPasteGhostAuthor Commented:
I have succeed in getting it working by doing this..


As you can see it's hardcoded to 'FRE'. I would like it to be dynamic.

Thanks
ALTER PROCEDURE [dbo].[usp_EnterLangFree]
	-- Add the parameters for the stored procedure here
	@LangId nvarchar(MAX), 
	@EnglishID int,
	@CategoryName nvarchar(MAX)
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @ParentCategoyID int;
	DECLARE @HoldID1 int;
    DECLARE @HoldID2 int;
	
	SELECT @HoldID1=MAX(ParentCategoyID) FROM Categories WHERE EnglishID=@EnglishID AND LanguageID = 'ENG'
	
	SELECT @HoldID2=Max(EnglishID) FROM Categories WHERE CategoryID=@HoldID1 AND LanguageID = 'ENG'
	
	SELECT @ParentCategoyID=Max(CategoryID) FROM Categories WHERE EnglishID=@HoldID2 AND LanguageID = 'FRE'
 
	IF EXISTS (
		SELECT 1
		FROM dbo.Categories
		WHERE EnglishID = @EnglishID
		AND LanguageID = 'FRE'
		)
		BEGIN
		--Category already addded. Do an update.
			UPDATE Categories SET CategoryName=@CategoryName WHERE EnglishID=@EnglishID AND LanguageID = 'FRE'
	END
	ELSE
		BEGIN
		-- Insert into Users Table
		INSERT INTO Categories ( 
			LanguageID, 
			EnglishID,
			ParentCategoyID, 
			CategoryName, 
			Show
		)
		VALUES (
			'FRE', 
			@EnglishID,
			@ParentCategoyID,	
			@CategoryName, 
			'0'
		)
    END
END

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Daniel WilsonCommented:
>>if i run tests from within SQL management studio as well as visual studio I'm still getting the empty value.

So, if in Management Studio you say:

Exec usp_EnterLangFree 'Eng', 22, 108

you're getting behavior as though @LangID is NULL?  that should not be ...


>>I'm passing it in my application

Could you post that part of your app code?
0
 
copyPasteGhostAuthor Commented:
Here you go.


As you can see the value is getting passed....but then just idissapears....

thanks.
bool passed = DBobj.runStoredProcInsert("usp_EnterLangFree", new string[,] { 
                { "@LangId", ddlLanguage.SelectedValue },
                { "@EnglishID", txtEnglishCode.Text },
                { "@CategoryName",txtLangFree.Text}});
 
 
public bool runStoredProcInsert(string procName, string[,] paramList) {
            try {
                openDB();
                SqlCommand cmd = new SqlCommand(procName, con);
 
                cmd.CommandType = CommandType.StoredProcedure;
 
                if (paramList != null) {
                    for (int i = 0; i < (paramList.Length / 2); i++) {
                        cmd.Parameters.Add(new SqlParameter(paramList[i, 0], paramList[i, 1]));
                    }
                }
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                closeDB();
            }
            catch (Exception ex) {
                throw new Exception(ex.ToString(), ex);
                return false;
            }
            return true;
        }

Open in new window

enterlangfree.JPG
0
 
Daniel WilsonCommented:
that looks good ... though if we eliminate the DB-side problems I'll look harder at it.

What happens in management studio if you


Exec usp_EnterLangFree 'Eng', 22, 108

(with the right parameters put in instead of mine)
0
 
copyPasteGhostAuthor Commented:
wow...it works....

both in my application and in management studio.

Thanks so much!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now