Fairly new to this but I have been trying to create a ASP.NET website to archive files. I want to capture the Windows username so when the user inserts a file, a record is kept of who inserted it. I have a table of users that will be using the site, giving the username a reference for records. When I am testing in a development environment everything works correctly and I can see the record created. However, on the server no record is being created and I get this returned to me:
'The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Person_BoxArchive_LastUpdate". The conflict occurred in database
"BoxManagement", table "dbo.Person", column 'PersonID'.###-1###'
I have tried enabling Windows Authentication in IIS and tried using these:
string userName = HttpContext.Current.User.Identity.Name.Replace(".", " ");
WindowsIdentity identity = HttpContext.Current.Request.LogonUserIdentity;
The code I am using to pass the username down to the stored procedure is:
string userName = Environment.UserName.Replace(".", " ");
int userID = -1;
DataTable database = new DataTable();
using (SqlConnection con = new SqlConnection(dbString))
using (SqlCommand cmd = new SqlCommand("GetUserID", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@userName", Environment.UserName);
con.Open();
using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
rdr.Read();
userID = rdr.GetInt32(rdr.GetOrdinal("PersonID"));
rdr.Close();
}
}
And the stored procedure is just
SELECT PersonID FROM Person WHERE WindowsName = @userName
Not sure I am looking in the right places but hopefully, this is enough to be pointed in the right direction.
EDIT:
This is the code I am using to insert the file:
DataTable database = new DataTable();
string dbString = ConfigurationManager.ConnectionStrings["connArchiveDatabase"].ConnectionString;
using (SqlConnection con = new SqlConnection(dbString))
using (SqlCommand cmd = new SqlCommand("dbo.InsertAccountFile", con))
{
try
{
int FileType = Int32.Parse(ddlInAccFileType.SelectedValue);
string policyNumber = "";
DateTime closedPolicy = new DateTime(1900, 01, 01);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@boxID", ddlInAccBox.Text);
cmd.Parameters.AddWithValue("@policyNumber", policyNumber);
cmd.Parameters.AddWithValue("@fileReference", tbInAccReference.Text);
cmd.Parameters.AddWithValue("@archivedbyID", userID);
cmd.Parameters.AddWithValue("@dateArchived", archivedDate);
cmd.Parameters.AddWithValue("@closedPolicy", closedPolicy);
cmd.Parameters.AddWithValue("@closedFile", tbInAccClosedDate.Text);
cmd.Parameters.AddWithValue("@filetypeID", FileType);
cmd.Parameters.AddWithValue("@comment", tbInAccComment.Text);
cmd.Parameters.AddWithValue("@expectedDestruction", destructionDate);
cmd.Parameters.AddWithValue("@lastupdateID", userID);
cmd.Parameters.AddWithValue("@updatedDate", updateDate);
SqlParameter archiveParameter = new SqlParameter
{
ParameterName = "@boxArchiveID",
SqlDbType = SqlDbType.Int,
Direction = ParameterDirection.Output
};
SqlParameter messageParameter = new SqlParameter
{
ParameterName = "@returnMessage",
SqlDbType = SqlDbType.VarChar,
Size = 255,
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(archiveParameter);
cmd.Parameters.Add(messageParameter);
con.Open();
cmd.ExecuteNonQuery();
string returnMessage = messageParameter.Value.ToString();
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('" + returnMessage + "###" + archiveParameter.Value.ToString() + "###" + "')", true);
}
catch (Exception ex)
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('" + ex.Message.ToString() + "')", true);
return;
}
finally
{
con.Close();
}
}
ALTER PROCEDURE [dbo].[InsertAccountFile]
@boxID INT,
@policyNumber VARCHAR(255),
@fileReference VARCHAR(255),
@archivedbyID INT,
@dateArchived SMALLDATETIME,
@closedPolicy SMALLDATETIME,
@closedFile SMALLDATETIME,
@filetypeID INT,
@comment VARCHAR(255),
@expectedDestruction SMALLDATETIME,
@lastupdateID INT,
@updatedDate SMALLDATETIME,
@boxArchiveID INT OUTPUT,
@returnMessage VARCHAR(255) OUTPUT
AS
BEGIN
SET NOCOUNT ON
IF NOT EXISTS(SELECT * FROM BoxArchive WHERE BoxID = @boxID AND FileReference = @fileReference)
BEGIN
BEGIN TRY
INSERT INTO BoxArchive (PolicyNumber, FileReference, ArchivedByID, DateArchived, DatePolicyClosed, ClosedFileDate, BoxID, FileTypeID, Comment, ExpectedDestructionDate,
LastUpdateID, LastUpdateDate)
VALUES (@policyNumber, @fileReference, @archivedbyID, @dateArchived, @closedPolicy, @closedFile, @boxID, @filetypeID, @comment, @expectedDestruction,
@lastupdateID, @updatedDate)
SET @returnMessage = 'Success.'
SET @boxArchiveID = IDENT_CURRENT('BoxArchive')
END TRY
BEGIN CATCH
SET @returnMessage = ERROR_MESSAGE()
SET @boxArchiveID = -1
END CATCH
END
ELSE
BEGIN
SET @returnMessage = 'Error: "' + @fileReference + '" already exists in this box.'
SET @boxArchiveID = -1
END
userName in debugging is correctly returning my name, also userID knows my ID which in this case is 8. On the server it seems to come back with -1.
