• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • 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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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