When I update the pollQuestion and pollName column that contain double quotes using the stored proc below, it saves the double quotes properly. However, for columns option1,...,option9, the stored proc doesn't save the double quotes or any characters after the double quotes. It's as though SQL Server truncates the string prematurely at the double quotes. I have a hunch that QUOTED_IDENTIFIER has something to do with this problem but it does NOT explain the inconsistency I have with pollQuestion and pollName and option1,...,option9.
Question: Why does SQL Server 2005 update columns option1,...,option9 with strings truncated at the double quotes? (Ex: Saving "Hello \" World" on option1 would be saved as "Hello ")
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_Poll_UpdateQandAs2]
@POLL_ID INT,
@NAME VARCHAR(140) = NULL,
@QUESTION VARCHAR(300),
@OPT1 VARCHAR(150),
@OPT2 VARCHAR(150),
@OPT3 VARCHAR(150)= NULL,
@OPT4 VARCHAR(150)= NULL,
@OPT5 VARCHAR(150)= NULL,
@OPT6 VARCHAR(150)= NULL,
@OPT7 VARCHAR(150)= NULL,
@OPT8 VARCHAR(150)= NULL,
@OPT9 VARCHAR(150)= NULL,
@VOTES1 INT = 0,
@VOTES2 INT = 0,
@VOTES3 INT = 0,
@VOTES4 INT = 0,
@VOTES5 INT = 0,
@VOTES6 INT = 0,
@VOTES7 INT = 0,
@VOTES8 INT = 0,
@VOTES9 INT = 0
AS
UPDATE tTREpoll2 SET
pollName = @NAME,
pollQuestion = @QUESTION,
option1 = @OPT1,
option2 = @OPT2,
option3 = @OPT3,
option4 = @OPT4,
option5 = @OPT5,
option6 = @OPT6,
option7 = @OPT7,
option8 = @OPT8,
option9 = @OPT9,
votes1 = @VOTES1,
votes2 = @VOTES2,
votes3 = @VOTES3,
votes4 = @VOTES4,
votes5 = @VOTES5,
votes6 = @VOTES6,
votes7 = @VOTES7,
votes8 = @VOTES8,
votes9 = @VOTES9
WHERE pollID = @POLL_ID
UPDATE: People are guessing that it's the code that's calling the stored procedure is the culprit. I doubt it is but here's the c# that does it.
public static void UpdatePoll(PollObj savedPoll)
{
Database db = DatabaseFactory.CreateDatabase("ControlPanelSqlServer");
using (DbCommand cmd = db.GetStoredProcCommand("sp_Poll_UpdateQandAs2"))
{
db.AddInParameter(cmd, "POLL_ID", DbType.String, savedPoll.PollID);
db.AddInParameter(cmd, "QUESTION", DbType.String, savedPoll.Question);
db.AddInParameter(cmd, "NAME", DbType.String, savedPoll.Name);
for (int i = 0; i < MAX_NUMBER_OF_CHOICES; i++)
{
if (i < savedPoll.Answers.Count)
{
db.AddInParameter(cmd, "OPT" + (i + 1), DbType.String, savedPoll.Answers[i].Option);
db.AddInParameter(cmd, "VOTES" + (i + 1), DbType.Int32, savedPoll.Answers[i].NumVotes);
}
else
{
db.AddInParameter(cmd, "OPT" + (i + 1), DbType.String, null);
db.AddInParameter(cmd, "VOTES" + (i + 1), DbType.Int32, 0);
}
}
if (db.ExecuteNonQuery(cmd) <= 0)
{
throw new Exception("Could not save changes with given the Poll_ID:" + savedPoll.PollID);
}
}
}
Update #2
I found the problem and it isnt the code I've posted. The database actually works and does what I want it to do. The problem i had is the way how one of my aspx pages read the saved results. It didn't HtmlEncode the saved data to an input tag properly so the double quotes ended up delimiting the input tag at the double quotes.