Click here to Skip to main content
14,668,418 members
Home » Discussions » Database
   

Database

 
GeneralRe: how to create a sum query between multiple tables in Microsoft access Pin
Mycroft Holmes20-Aug-18 12:57
professionalMycroft Holmes20-Aug-18 12:57 
QuestionStored Procedure execution from Entity Framework should return 0 or -1 if insert or update fails Pin
indian1437-Aug-18 14:27
Memberindian1437-Aug-18 14:27 
AnswerRe: Stored Procedure execution from Entity Framework should return 0 or -1 if insert or update fails Pin
Mycroft Holmes7-Aug-18 14:37
professionalMycroft Holmes7-Aug-18 14:37 
GeneralRe: Stored Procedure execution from Entity Framework should return 0 or -1 if insert or update fails Pin
indian1437-Aug-18 14:39
Memberindian1437-Aug-18 14:39 
GeneralRe: Stored Procedure execution from Entity Framework should return 0 or -1 if insert or update fails Pin
Mycroft Holmes7-Aug-18 16:38
professionalMycroft Holmes7-Aug-18 16:38 
GeneralRe: Stored Procedure execution from Entity Framework should return 0 or -1 if insert or update fails Pin
indian1438-Aug-18 6:38
Memberindian1438-Aug-18 6:38 
GeneralRe: Stored Procedure execution from Entity Framework should return 0 or -1 if insert or update fails Pin
Richard Deeming8-Aug-18 0:27
mveRichard Deeming8-Aug-18 0:27 
GeneralRe: Stored Procedure execution from Entity Framework should return 0 or -1 if insert or update fails Pin
indian1438-Aug-18 6:42
Memberindian1438-Aug-18 6:42 
I am not sure but its calling the SP in the following way:
        public virtual int Usp_Add_LookupRecord(Nullable<int> lookupTableId, string type, string description, Nullable<int> foreignKeyId, string createdBy, string modifiedBy, Nullable<bool> isValid)
        {
            var lookupTableIdParameter = lookupTableId.HasValue ?
                new ObjectParameter("LookupTableId", lookupTableId) :
                new ObjectParameter("LookupTableId", typeof(int));

            var typeParameter = type != null ?
                new ObjectParameter("Type", type) :
                new ObjectParameter("Type", typeof(string));

            var descriptionParameter = description != null ?
                new ObjectParameter("Description", description) :
                new ObjectParameter("Description", typeof(string));

            var foreignKeyIdParameter = foreignKeyId.HasValue ?
                new ObjectParameter("ForeignKeyId", foreignKeyId) :
                new ObjectParameter("ForeignKeyId", typeof(int));

            var createdByParameter = createdBy != null ?
                new ObjectParameter("CreatedBy", createdBy) :
                new ObjectParameter("CreatedBy", typeof(string));

            var modifiedByParameter = modifiedBy != null ?
                new ObjectParameter("ModifiedBy", modifiedBy) :
                new ObjectParameter("ModifiedBy", typeof(string));

            var isValidParameter = isValid.HasValue ?
                new ObjectParameter("IsValid", isValid) :
                new ObjectParameter("IsValid", typeof(bool));

            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("Usp_Add_LookupRecord", lookupTableIdParameter, typeParameter, descriptionParameter, foreignKeyIdParameter, createdByParameter, modifiedByParameter, isValidParameter);
        }

And when I changed the stored procedure to return -3 is record exists, it is returning me -1, here is my SP:
ALTER PROCEDURE [dbo].[Usp_Add_LookupRecord] (@LookupTableId int,
    @Type varchar(100), @Description varchar(500), @ForeignKeyId int, @CreatedBy varchar(500), @ModifiedBy varchar(500), @IsValid bit<br />
)
AS
BEGIN
    declare @LookupTableName varchar(100)=(select top 1 LookupTableName FROM [dbo].[ListOfLookupTables] WHERE PkListOfLookupTablesId=@LookupTableId)
    declare @InsertedId int=0, @FKProgramTypeLKPId int = (select top 1 PKProgramTypeLKPId from ProgramTypeLKP where Type='Mental Health')

    IF (@LookupTableName='ProgramTypeLKP')
    BEGIN
        IF NOT EXISTS(SELECT Top 1 1 FROM dbo.ProgramTypeLKP WHERE [Type]=@Type AND ISNULL(IsValid, 1)=1)
        BEGIN
            INSERT INTO dbo.ProgramTypeLKP ([Type], [Description], CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, IsValid)
            VALUES(LEFT(@Type, 15), LEFT(@Description, 500), GETDATE(), LEFT(@CreatedBy, 500), GETDATE(), LEFT(@ModifiedBy, 500), @IsValid)
            SET @InsertedId=SCOPE_IDENTITY()
        END
        ELSE
        BEGIN
            RETURN -3
        END
    END
    ELSE IF (@LookupTableName='AddressTypeLKP')
    BEGIN
        IF NOT EXISTS(SELECT Top 1 1 FROM dbo.AddressTypeLKP WHERE [Description]=@Type AND ISNULL(IsValid, 1)=1)
        BEGIN
            INSERT INTO dbo.AddressTypeLKP ([Description], FKProgramTypeLKPId, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, IsValid)
            VALUES(LEFT(@Description, 20), @FKProgramTypeLKPId, GETDATE(), LEFT(@CreatedBy, 30), GETDATE(), LEFT(@ModifiedBy, 30), @IsValid)
            SET @InsertedId=SCOPE_IDENTITY()
        END
        ELSE
        BEGIN
            RETURN -3
        END
    END

    RETURN @InsertedId
END

I don't understand why is it returning the -1 instead of -3, is there any way to get the result -3?

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

-- modified 8-Aug-18 13:02pm.
GeneralRe: Stored Procedure execution from Entity Framework should return 0 or -1 if insert or update fails Pin
Richard Deeming8-Aug-18 8:23
mveRichard Deeming8-Aug-18 8:23 
GeneralRe: Stored Procedure execution from Entity Framework should return 0 or -1 if insert or update fails Pin
indian1439-Aug-18 6:05
Memberindian1439-Aug-18 6:05 
QuestionDATABASE Maintainance Pin
ricardobrooks6-Aug-18 3:12
Memberricardobrooks6-Aug-18 3:12 
AnswerRe: DATABASE Maintainance Pin
Victor Nijegorodov6-Aug-18 8:01
MemberVictor Nijegorodov6-Aug-18 8:01 
AnswerRe: DATABASE Maintainance Pin
Eddy Vluggen6-Aug-18 8:15
professionalEddy Vluggen6-Aug-18 8:15 
GeneralRe: DATABASE Maintainance Pin
CHill606-Aug-18 22:01
mveCHill606-Aug-18 22:01 
Questionrecovery method for hard drive Pin
Member 139376004-Aug-18 4:08
MemberMember 139376004-Aug-18 4:08 
AnswerRe: recovery method for hard drive Pin
Victor Nijegorodov4-Aug-18 20:34
MemberVictor Nijegorodov4-Aug-18 20:34 
QuestionNeed to drop a schema in which there are some objects exist Pin
indian1431-Aug-18 6:28
Memberindian1431-Aug-18 6:28 
AnswerRe: Need to drop a schema in which there are some objects exist Pin
Mycroft Holmes1-Aug-18 15:03
professionalMycroft Holmes1-Aug-18 15:03 
AnswerRe: Need to drop a schema in which there are some objects exist Pin
CHill602-Aug-18 2:26
mveCHill602-Aug-18 2:26 
QuestionRe: Need to drop a schema in which there are some objects exist Pin
CHill606-Aug-18 1:40
mveCHill606-Aug-18 1:40 
GeneralLegacy software: can't load MSJTER35.DLL Pin
Keith Sheppard227-Jul-18 23:26
MemberKeith Sheppard227-Jul-18 23:26 
GeneralRe: Legacy software: can't load MSJTER35.DLL Pin
Victor Nijegorodov27-Jul-18 23:45
MemberVictor Nijegorodov27-Jul-18 23:45 
GeneralRe: Legacy software: can't load MSJTER35.DLL Pin
Keith Sheppard22-Aug-18 1:23
MemberKeith Sheppard22-Aug-18 1:23 
Questioninner join results Pin
Member 1386716325-Jul-18 18:12
MemberMember 1386716325-Jul-18 18:12 
AnswerRe: inner join results Pin
Richard Deeming26-Jul-18 1:50
mveRichard Deeming26-Jul-18 1:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.