C# adding data to a SQL table “Specified cast is not valid.” – Mysql

C# adding data to a SQL table with ASP.NET throws an error

System.InvalidCastException: "Specified cast is not valid"

This is for a foreign key column with a data type of bigint.

I set my type in C# to be Int64, after int definition threw an error but still get the error.

Is this because I can’t add to a column that has foreign keys? Do I need to cascade?

This is my datacontroller:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using SchoolDb.Models;
using MySql.Data.MySqlClient;
using System.Diagnostics;

namespace SchoolDb.Controllers
{
    public class CoursesDataController : ApiController
    {
        private SchoolDbContext School = new SchoolDbContext();

        /// <summary>
        /// returns list of courses in the system
        /// </summary>
        /// <example>GET api/CoursesData/ListCourses</example>
        /// <returns>a list of courses</returns>
        [HttpGet]
        [Route("api/CourseData/ListCourses/{SearchKey?}")]
        public IEnumerable<Course> ListCourses(string SearchKey = null)
        {
            //create an instance of a connection
            MySqlConnection Conn = School.AccessDatabase();

            //open the connection between server and database
            Conn.Open();

            string query = "Select * from classes where lower(classname) like lower(@key) or classid like (@key)";
            Debug.WriteLine("the search key is " + query);

            // establish a new command query for our database
            MySqlCommand cmd = Conn.CreateCommand();

            // SQL query
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@key", "%" + SearchKey + "%");
            cmd.Prepare();

            // gather result set of query into variable
            MySqlDataReader ResultSet = cmd.ExecuteReader();

            // create an empty list of courses
            List<Course> Courses = new List<Course> { };

            while (ResultSet.Read())
            {
                // access column information by the db column name as an index
                int ClassId = (int)ResultSet["classid"];
                string ClassCode = (string)ResultSet["classcode"];
                Int64 TeacherId = (Int64)ResultSet["teacherid"];
                DateTime StartDate = (DateTime)ResultSet["startdate"];
                DateTime FinishDate = (DateTime)ResultSet["finishdate"];
                string ClassName = (string)ResultSet["classname"];

                Course NewCourse = new Course();
                NewCourse.ClassId = ClassId;
                NewCourse.ClassCode = ClassCode;
                NewCourse.TeacherId = TeacherId;
                NewCourse.StartDate = StartDate;
                NewCourse.FinishDate = FinishDate;
                NewCourse.ClassName = ClassName;

                // add the course info to the list
                Courses.Add(NewCourse);
            }

            Conn.Close();

            // return the final list of courses
            return Courses;
        }

        /// <summary>
        /// returns a single instance of a course
        /// </summary>
        /// <param name="id">class id</param>
        /// <returns>info on a particular course based on classid input</returns>
        [HttpGet]
        public Course FindCourse(int id)
        {
            Course NewCourse = new Course();

            // create an instance of a connection
            MySqlConnection Conn = School.AccessDatabase();

            // open the connection between server and database
            Conn.Open();

            // establish a new command query for our database
            MySqlCommand cmd = Conn.CreateCommand();

            // SQL query
            cmd.CommandText = "Select * from classes where classid = " + id;

            // gather result set of query into variable
            MySqlDataReader ResultSet = cmd.ExecuteReader();

            while (ResultSet.Read())
            {
                // access column information by the db column name as an index
                int ClassId = (int)ResultSet["classid"];
                string ClassCode = (string)ResultSet["classcode"];
                Int64 TeacherId = (Int64)ResultSet["teacherid"];
                DateTime StartDate = (DateTime)ResultSet["startdate"];
                DateTime FinishDate = (DateTime)ResultSet["finishdate"];
                string ClassName = (string)ResultSet["classname"];

                NewCourse.ClassId = ClassId;
                NewCourse.ClassCode = ClassCode;
                NewCourse.TeacherId = TeacherId;
                NewCourse.StartDate = StartDate;
                NewCourse.FinishDate = FinishDate;
                NewCourse.ClassName = ClassName;
            }

            return NewCourse;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <example>POST: /api/CoursesData/DeleteCourse/3</example>
        [HttpPost]
        public void DeleteCourse(int id)
        {
            // create an instance of a connection
            MySqlConnection Conn = School.AccessDatabase();

            // open the connection between server and database
            Conn.Open();

            // establish a new command query for our database
            MySqlCommand cmd = Conn.CreateCommand();

            // SQL query
            cmd.CommandText = "Delete from classes where classid=@id";
            cmd.Parameters.AddWithValue("@id", id);
            cmd.Prepare();

            cmd.ExecuteNonQuery();

            Conn.Close();
        }

        [HttpPost]
        public void AddCourse(Course NewCourse)
        {
            // create an instance of a connection
            MySqlConnection Conn = School.AccessDatabase();

            // open the connection between server and database
            Conn.Open();

            // establish a new command query for our database
            MySqlCommand cmd = Conn.CreateCommand();

            // SQL query
            cmd.CommandText = "insert into classes (classcode, teacherid, startdate, finishdate, classname) value (@ClassCode, @TeacherId, @StartDate,@FinishDate,@ClassName)";
            cmd.Parameters.AddWithValue("@ClassCode", NewCourse.ClassCode);
            cmd.Parameters.AddWithValue("@TeacherId", NewCourse.TeacherId);
            cmd.Parameters.AddWithValue("@StartDate", NewCourse.StartDate);
            cmd.Parameters.AddWithValue("@FinishDate",NewCourse.FinishDate );
            cmd.Parameters.AddWithValue("@ClassName", NewCourse.ClassName);
            cmd.Prepare();

            cmd.ExecuteNonQuery();

            Conn.Close();
        }
    }
}