'Specified cast is not valid' error on call to SQL Server stored procedure

Rex_C

I'm calling a stored procedure in a SQL Server database with the following code.

The idea is to add the relevant events to a list that is a property of the EventSchedule model, but the code is returning a

Specified cast is not valid

error. The stored procedure pulls data from a view I've built. The call stack shows the error is at line 96, not sure if that'll help anyone much. I think I may need another set of eyes to see what I'm missing here.

var command = new SqlCommand("GetEvents", conn)
{
    CommandType = CommandType.StoredProcedure
};

command.Parameters.Add(new SqlParameter("Id", SqlDbType.BigInt, 0, "id"));
command.Parameters[0].Value = schedule.LeagueId;

using (var reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        //line 96
        var _event = new Event
        {
            EventId = (Int64) reader["eventid"],
            HomeTeamId = (Int64) reader["home_team_id"],
            TeamName = (string) reader["team_name"],
            HomeTeamScore = (int) reader["home_team_score"],
            AwayTeamId = (Int64) reader["away_team_id"],
            AwayTeamName = (string) reader["Expr1"],
            AwayTeamScore = (int) reader["away_team_score"],
            WinningTeamId = (Int64) reader["winning_teamid"],
            EventStartDttm = (DateTime) reader["event_start_dttm"],
            CurrentDttm = (DateTime) reader["current_dttm"],
            Locked = (bool) reader["locked"]
        };

        schedule.Events.Add(_event);
    }
}

return schedule;

Models:

public class EventSchedule
{
    public Int64 UserId { get; set; }
    public Int64 LeagueId { get; set; }
    public string League { get; set; }
    public int Season { get; set; }
    public int Week { get; set; }
    public IList<Event> Events { get; set; }
}

public class Event
{
    public Int64 EventId { get; set; }
    public Int64 HomeTeamId { get; set; }
    public string TeamName { get; set; }
    public int? HomeTeamScore { get; set; }
    public Int64 AwayTeamId { get; set; }
    public string AwayTeamName { get; set; }
    public int? AwayTeamScore { get; set; }
    public Int64 WinningTeamId { get; set; }
    public DateTime EventStartDttm { get; set; }
    public DateTime CurrentDttm { get; set; }
    public bool Locked { get; set; }
}

Stored proc:

ALTER PROCEDURE [dbo].[GetEvents]
    @Id int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
       eventid, 
       home_team_id, 
       team_name, 
       home_team_score, 
       away_team_id, 
       Expr1, 
       away_team_score, 
       winning_teamid, 
       event_start_dttm, 
       current_dttm, 
       locked 
    FROM 
       leagueScheduleForCurrentWeek 
    WHERE 
       id = @Id
END
Scott Chamberlain

Your problem comes from the fact that one of your casts are failing, most likely one of the nullable types from your model. Change from a cast to using as for the nullable types, this will cause DbNull.Value (which is what is being returned by your reader) to be come the null value you want.

    var _event = new Event
    {
        EventId = (Int64) reader["eventid"],
        HomeTeamId = (Int64) reader["home_team_id"],
        TeamName = (string) reader["team_name"],
        HomeTeamScore = reader["home_team_score"] as int?, //here
        AwayTeamId = (Int64) reader["away_team_id"],
        AwayTeamName = (string) reader["Expr1"],
        AwayTeamScore = reader["away_team_score"] as int?, //and here
        WinningTeamId = (Int64) reader["winning_teamid"],
        EventStartDttm = (DateTime) reader["event_start_dttm"],
        CurrentDttm = (DateTime) reader["current_dttm"],
        Locked = (bool) reader["locked"]
    };

If your problem still exists then your model does not match your data table. you will need to go through your types to find out which one does not match. You can make this easier on yourself by breaking out the assignment out from the constructor.

    var _event = new Event();

    _event.EventId = (Int64) reader["eventid"];
    _event.HomeTeamId = (Int64) reader["home_team_id"];
    _event.TeamName = (string) reader["team_name"];
    _event.HomeTeamScore = reader["home_team_score"] as int?;
    _event.AwayTeamId = (Int64) reader["away_team_id"];
    _event.AwayTeamName = (string) reader["Expr1"];
    _event.AwayTeamScore = reader["away_team_score"] as int?;
    _event.WinningTeamId = (Int64) reader["winning_teamid"];
    _event.EventStartDttm = (DateTime) reader["event_start_dttm"];
    _event.CurrentDttm = (DateTime) reader["current_dttm"];
    _event.Locked = (bool) reader["locked"];

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Stored procedure in SQL Server; syntax error

From Dev

Syntax error with SQL Server stored procedure

From Dev

Linq result Int error: Specified cast is not valid

From Dev

Error with SQL Server Stored Procedure & C#

From Dev

Insert throw error : Specified cast is not valid

From Dev

How to call sql server stored procedure with input parameters in Qt

From Dev

Specified cast is not valid error at runtime, but not in debugger

From Dev

How to capture error in SQL Server stored procedure?

From Dev

SQL Server : create and call stored procedure

From Dev

Why is Setting a SQL Server Bit to "false" in a stored procedure definition valid?

From Dev

Stored Procedure error SQL Server 2014

From Dev

Call SQL server stored procedure with JPA 2.1 annotations

From Dev

Boolean cast Specified cast is not valid error

From Dev

Call SQL Server stored procedure with parameters using C#

From Dev

Connection string for Access to call SQL Server stored procedure

From Dev

Call Stored Procedure with Null Data Parameter in SQL Server

From Dev

Error in SQL Server stored procedure

From Dev

How to call sql server stored procedure with input parameters in Qt

From Dev

ASP parameters to call a stored procedure in SQL Server

From Dev

call a sql function stored in a separate db server from a stored procedure

From Dev

SQL Server : create and call stored procedure

From Dev

Stored Procedure error SQL Server 2014

From Dev

SQL server create stored procedure syntax error

From Dev

SQL Server stored procedure update error

From Dev

Stored procedure :The specified cast from a materialized 'System.String' type to a nullable 'System.Single' type is not valid

From Dev

MSDTC on server '' is unavailable. sql server link server error when call stored procedure

From Dev

SQL Server Stored Procedure Syntax Error on Value

From Dev

SQL query get error "Specified cast is not valid."

From Dev

SQL server : stored procedure

Related Related

  1. 1

    Stored procedure in SQL Server; syntax error

  2. 2

    Syntax error with SQL Server stored procedure

  3. 3

    Linq result Int error: Specified cast is not valid

  4. 4

    Error with SQL Server Stored Procedure & C#

  5. 5

    Insert throw error : Specified cast is not valid

  6. 6

    How to call sql server stored procedure with input parameters in Qt

  7. 7

    Specified cast is not valid error at runtime, but not in debugger

  8. 8

    How to capture error in SQL Server stored procedure?

  9. 9

    SQL Server : create and call stored procedure

  10. 10

    Why is Setting a SQL Server Bit to "false" in a stored procedure definition valid?

  11. 11

    Stored Procedure error SQL Server 2014

  12. 12

    Call SQL server stored procedure with JPA 2.1 annotations

  13. 13

    Boolean cast Specified cast is not valid error

  14. 14

    Call SQL Server stored procedure with parameters using C#

  15. 15

    Connection string for Access to call SQL Server stored procedure

  16. 16

    Call Stored Procedure with Null Data Parameter in SQL Server

  17. 17

    Error in SQL Server stored procedure

  18. 18

    How to call sql server stored procedure with input parameters in Qt

  19. 19

    ASP parameters to call a stored procedure in SQL Server

  20. 20

    call a sql function stored in a separate db server from a stored procedure

  21. 21

    SQL Server : create and call stored procedure

  22. 22

    Stored Procedure error SQL Server 2014

  23. 23

    SQL server create stored procedure syntax error

  24. 24

    SQL Server stored procedure update error

  25. 25

    Stored procedure :The specified cast from a materialized 'System.String' type to a nullable 'System.Single' type is not valid

  26. 26

    MSDTC on server '' is unavailable. sql server link server error when call stored procedure

  27. 27

    SQL Server Stored Procedure Syntax Error on Value

  28. 28

    SQL query get error "Specified cast is not valid."

  29. 29

    SQL server : stored procedure

HotTag

Archive