Casting makes no sense.

[tshad]

I have some code where I am moving an integer to an integer and I am getting a casting error. But I am casting it as an integer - which it is.

int surveyTestAnswerID = 0;

surveyTestAnswerID = (int)dbReader["SurveyTestAnswerID"];

I get an error here as:

Specified cast is not valid ????

The value of dbReader["SurveyTestAnswerID"] is 23697 which is an integer. The stored procedure has the value coming from the Scope_Identity() of the value which is an integer.

How can this not be casted correctly???

Thanks,

Tom

 

[tshad]

I did a dbReader["SurveyTestAnswerID"].GetType() and the value was a decimal.

How can that be?

Here is the Sql statement that is being returned:

Select SurveyTestAnswerID=Scope_Identity()

SurveyTestAnswerID is an int and the PK of the table.

Where did the decimal come from???

Thanks,

Tom

 

[jbenson001]

How are you passing back the Scope_Identity() from your stored procedure? Post some code.

 

[tshad]

ALTER PROCEDURE [dbo].[AddSurveyTestAnswer] 
 (
	@QuestionID Int,
	@SortOrder Int,
	@Answer varChar(250)
) 
AS 
declare @AnswerID Int

if @SortOrder <> -1
begin
	insert into SurveyTestAnswers (SurveyTestQuestionID,SortOrder,Answer)  
	values(@QuestionID,@SortOrder,@Answer)
end
else
begin
	insert into SurveyTestAnswers (SurveyTestQuestionID,Answer,SortOrder)  
	Select @QuestionID,@Answer,coalesce(max(SortOrder), -1) + 1
	From SurveyTestAnswers where SurveyTestQuestionID = @QuestionID
end

Select SurveyTestAnswerID=Scope_Identity()

The Table is:

CREATE TABLE [dbo].[SurveyTestAnswers](
	[SurveyTestAnswerID] [int] IDENTITY(1,1) NOT NULL,
	[SurveyTestQuestionID] [int] NOT NULL,
	[SortOrder] [tinyint] NULL,
	[Answer] [varchar](250) NULL,
	[Correct] [bit] NULL,
	[AnswerTime] [int] NULL,
	[CreateDate] [smalldatetime] NULL
)

As you can see the SurveyTestAnswerID is an int and an identity field. So why is it passing back a decimimal?

Thanks,

Tom

 

[MarkSweetland]

I don't think it's a problem with SQL, but an issue how you're accessing the data through the datareader. Can you post that code?


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[tshad]

I am not sure how the way the DataReader is set up matters since the return is from a SELECT statement and not through an output parameter. Then that would make sense

Here is the code.

When I change the other code to Convert.ToInt32(dbReader["SurveyTestAnswerID"]), it works. It is converting the decimal to int.

     dbReader = dbo.GetDataReader("AddSurveyTestAnswer");
    if (dbReader.Read())
    {
        surveyTestAnswerID = Convert.ToInt32(dbReader["SurveyTestAnswerID"]);
    }

...

public SqlDataReader GetDataReader(string storedProcName)
{
    SqlDataReader returnReader = null;
    ProcedureName = storedProcName;

    if (myConnection.State == ConnectionState.Open)
    {
        myConnection.Close();
    }
    myConnection.Open();

    myCommand = BuildQueryCommand();

    myCommand.CommandType = CommandType.StoredProcedure;

    // Connection will be closed automatically when the DataReader is closed.
    returnReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

    return returnReader;
}

The BuildQuery method and AddReturnParameter:

private SqlCommand BuildQueryCommand()
{
    SqlCommand command = new SqlCommand(ProcedureName, myConnection);
    command.CommandType = CommandType.StoredProcedure;

    if ((Parameters != null))
    {
        foreach (SqlParameter parameter in Parameters)
        {
            command.Parameters.Add(parameter);
        }
    }
    AddReturnParameter();

    return command;
}

public void AddReturnParameter()
{
    Parameters.Add(database.SetParameter("@Return", SqlDbType.Int, 0));
    Parameters.Find(c => c.ParameterName == "@Return").Direction = ParameterDirection.ReturnValue;
}

Thanks,

Tom