Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeDevelopment and...Development and...SQL and SQL Ser...SQL and SQL Ser...SQL Severity Code 16 but INSERT is WorkingSQL Severity Code 16 but INSERT is Working
Previous
 
Next
New Post
3/8/2015 4:18 PM
 

I'm getting a severity 16 back from SQL Server which I was unaware of because the insert is working. 

In a nutshell, from SSMS, this code can be called and there is no severity 16 returned.  All 0's are returned.  The insert happens, the profile entries are made and can be viewed on the child portal.  But somehow when the SPROC is called via C#, only then does a 16 severity error code get returned.

 It did turn out 2 fields, Default Value and Validation Expression were the only fields not explicitly being set NULL so this was changed to NULL AS DefaultValue, etc. for these.  And, Created By User was set to 1.  All other fields had values being set and all explicitly NOT NULL attributes are being passed values.

The point this was being called in code was after the portal and user in question were created.  So, since email notifications were made next plus a few other actions, I put the calling code last  in the Create Portals event.  The idea was maybe something in the DB isn't finishing so let's have everything finish first, then set up the profile entries.  Still got an error 16.

This code ...

                            if (chairPayTo.Visible)
                            {
                                //calls the data access method to make the settings for Pay to, then add them
                                CreateSkinMessage("Pay To Settings Code returned is "
                                    + dataAccess.ABFR_COPY_PROFILE_PAYTO_SETTINGS(intPortalId).ToString());
                                //if (dataAccess.ABFR_COPY_PROFILE_PAYTO_SETTINGS(intPortalId) == 0)
                                //    chairPayTo.AddChairPayToInfo(ui);
                            }

... is calling this method in my data access project:

        public int ABFR_COPY_PROFILE_PAYTO_SETTINGS(int childPortalID)
        {
            int insertSuccess = -1;
            using (SqlConnection sqlConnection1 = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "ABFR_COPY_PROFILE_PAYTO_SETTINGS";
                    cmd.Parameters.AddWithValue("PortalID", childPortalID);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = sqlConnection1;
                    sqlConnection1.Open();
                    insertSuccess = cmd.ExecuteNonQuery();
                }
            }
            return insertSuccess;
        }

Since the insert was working I had no concern about the return val never being == 0.  But when the data wasn't ending up in the profile ... since this code is on a dynamic recompile, the error was trapped by sending it to a skin message:

Here is the stored procedure being called, with code added to trap @@ERROR at every possible point and print the values out.  When the sproc is executed in SSMS, the insert happens and all 0 codes (success) are returned.  Note, this code is a copy of ABFR_COPY_PROFILE_PAYTO_SETTINGS  but with the error trapping code inserted.  My code calls the SPROC named above:

IF  EXISTS (SELECT * FROM sys.objects
WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[AA_TEMP_ABFR_COPY_PROFILE_PAYTO_SETTINGS]')
AND type in (N'P'))
DROP PROCEDURE [dbo].[AA_TEMP_ABFR_COPY_PROFILE_PAYTO_SETTINGS]
GO

CREATE PROCEDURE dbo.AA_TEMP_ABFR_COPY_PROFILE_PAYTO_SETTINGS
(
    @PortalId            INT
)

AS
    BEGIN
            DECLARE @ERROR INT;
            DECLARE @ROWS_START    INT;
            DECLARE @ROWS_TEMP    INT;
            DECLARE @ROWS_END    INT;

            -- GET STARTING ROW COUNT IN PROP DEF
            SET @ROWS_START = (SELECT COUNT(*) FROM dbo.ProfilePropertyDefinition);
            SET @ERROR = @@ERROR;
            PRINT 'THIS IS @@ERROR AFTER PROFILE PROP DEF COUNT'
            PRINT @ERROR

            SELECT PPD.*
                INTO #TEMP_PPD   

            FROM [dbo].[ProfilePropertyDefinition] PPD

            WHERE [PropertyCategory] = 'Chairperson'
                AND PPD.PortalId = 0;
            SET @ERROR = @@ERROR;
            PRINT 'THIS IS @@ERROR AFTER INSERT PORTAL 0 VALUES INTO TEMP TABLE'
            PRINT @ERROR

            -- GET THE TEMP TABLE COUNT
            SET @ROWS_TEMP = (SELECT COUNT(*) FROM #TEMP_PPD);
            SET @ERROR = @@ERROR;
            PRINT 'THIS IS @@ERROR AFTER TEMP ROWS COUNT'
            PRINT @ERROR

            INSERT INTO [dbo].[ProfilePropertyDefinition]
                SELECT
                @PortalId   
                , ModuleDefID
                , Deleted
                , DataType
                , DefaultValue
                , PropertyCategory
                , PropertyName
                , Length
                , Required
                , ValidationExpression
                , ViewOrder
                , Visible
                , CreatedByUserID
                , CreatedOnDate
                , LastModifiedByUserID
                , LastModifiedOnDate
                , DefaultVisibility
                , ReadOnly

            FROM #TEMP_PPD;
            SET @ERROR = @@ERROR;
            PRINT 'THIS IS @@ERROR AFTER INSERT'
            PRINT @ERROR

            DROP TABLE #TEMP_PPD;
            SET @ERROR = @@ERROR;
            PRINT 'THIS IS @@ERROR AFTER DROP TEMP TABLE'
            PRINT @ERROR

            -- GET FINAL PPD COUNT
            SET @ROWS_END = (SELECT COUNT(*) FROM dbo.ProfilePropertyDefinition);
            SET @ERROR = @@ERROR;
            PRINT 'THIS IS @@ERROR AFTER PROFILE PROP DEF INSERT'
            PRINT @ERROR

            IF @ROWS_END = @ROWS_START + @ROWS_TEMP
                SET @ERROR = @@ERROR
            ELSE
                SET @ERROR = @@ERROR;

            --SET @ERROR = @@ERROR;
            PRINT 'THIS IS @@ERROR AFTER IF BLOCK'
            PRINT @ERROR

            RETURN @ERROR
    END   

 

 
New Post
3/8/2015 5:06 PM
 
maybe the print fails?
btw, you should not rely on the order of fields in the table, but provide the column list for insert.

Cheers from Germany,
Sebastian Leupold (Microsoft MVP)

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
New Post
3/8/2015 8:05 PM
Accepted Answer 
Actually, problem solved.  In this case 16 has nothing to do with the severity code but is the number of rows affected by the query.  I should have read the manual first ... RTFM.
 
New Post
3/8/2015 8:13 PM
 
yes, that's correct. However you should still provide the column names, especially as you are manipulation core tables, bypassing DNN API, which are subject to change without notification.

Cheers from Germany,
Sebastian Leupold (Microsoft MVP)

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
New Post
3/8/2015 10:02 PM
 

That base is covered.  The SELECT has every column name in it, it's not SELECT * INTO ... etc.  Thanks for the head's up and the very quick replies.

 

 
Previous
 
Next
HomeHomeDevelopment and...Development and...SQL and SQL Ser...SQL and SQL Ser...SQL Severity Code 16 but INSERT is WorkingSQL Severity Code 16 but INSERT is Working


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.

Content Layout

Subscribe to DNN Digest

Subscribe to DNN Digest

DNN Digest is our monthly email newsletter. It highlights news and content from around the DNN ecosystem, such as new modules and themes, messages from leadership, blog posts and notable tweets. Keep your finger on the pulse of the ecosystem by subscribing.  


Copyright 2017 by DNN Corp Terms of Use Privacy
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out