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