locked
ASP.net, C#, ExecuteReader, {"Specified cast is not valid."}, how to get GUID RRS feed

  • Question

  • User1045460610 posted

    I get an error reading the GUID from the column {"Specified cast is not valid."} The documentation shows that the type is reader.GetInt32(0). On this line below "ecordID = reader.GetInt32(0), //instrctr_id_num //{"Specified cast is not valid."}" I get the error {"Specified cast is not valid." The code works when I test with an Integer column. How do I read the GUID column in the Execute Reader?

    The guid in a query looks like this

    RecordID CourseCode CourseTitle
    61E56201 - 09C6 - 46DD - 8E26 - 0FCE97BB8233
       is 45 characters

    The column type in the database is

    [ScheduleDaysID] [uniqueidentifier] ROWGUIDCOL NOT NULL,

    using Newtonsoft.Json;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Web;
    using System.Web.Security;
    using System.Web.Services;
    using System.Web.UI;
    using System.Web.UI.WebControls;

    public partial class updatecourses : System.Web.UI.Page
    {

    protected void Page_LoadComplete(object sender, EventArgs e)
    {

    {
    SqlConnection con = new SqlConnection(Database.ConnectionString);
    try
    {
    var returnUrl = Request.Params["ReturnUrl"];
    var hostID = Request.Params["HostID"];
    if (string.IsNullOrWhiteSpace(hostID))
    {
    if (!string.IsNullOrWhiteSpace(returnUrl))
    Response.Redirect(returnUrl);

    throw new Exception("Variable \"HostID\" not found in query params");
    }

    con.Open();

    var cmd = new SqlCommand("select * from dbo.FWK_User where HostID = @hostId", con);

    cmd.Parameters.AddWithValue("hostId", hostID);
    var dr = cmd.ExecuteReader();
    if (!dr.Read())
    Response.Redirect($"studentcourse.aspx?hostId={hostID}"); // won't need to redirect, instructor will click link on web site for Faculty (Instrcutor) or student
    dr.Close();

    var query = @"select distinct" +
    " flt.instrctr_id_num as HostID" +
    ",sd.scheduledaysid as RecordID" +
    ",sd.status" +
    ",sd.minutes" +
    ",sm.crs_cde as CourseCode" +

    ",sm.SHORT_CRS_TITLE_1 as CourseTitle" +
    ",sm.yr_cde,sm.trm_cde" +
    ",sd.startTime as StartTime" +
    ",sd.startTime" +
    ",flt.instrctr_id_num as InstructorID" +
    ",n.First_Name" +

    ", CONCAT(n.LAST_NAME, ', ', n.FIRST_NAME) AS InstructorName" +
    " from net.dbo.cheduleDays as sd" +
    " inner join net.dbo.section as s" +
    " on sd.sectionid = s.SectionID " +
    "inner JOIN net.dbo.LMS_Course AS c " +
    "WITH (NOLOCK) ON s.CourseID = c.CourseID " +
    "inner join tmprd.dbo.section_master as sm" +
    " on c.CourseCode + ' ' + s.NAME = sm.crs_cde " +
    "and left(s.coursekey,4) = sm.yr_cde " +
    "and substring(s.CourseKey,6,2) = sm.trm_cde " +
    "inner join tmprd.dbo.student_crs_hist as sch " +
    "on sm.crs_cde = sch.crs_cde " +
    "and sm.yr_cde = sch.yr_cde " +
    "and sm.trm_cde = sch.trm_cde " +
    "inner join tmprd.dbo.faculty_load_table " +
    "as flt on sm.crs_cde = flt.crs_cde " +
    "and sm.yr_cde = flt.yr_cde " +
    "and sm.trm_cde = flt.trm_cde " +
    "inner join TmPrd.dbo.NAME_MASTER " +
    "as n on n.id_num = flt.instrctr_id_num " +
    "where " +

    "sd.startdate <= @Now " +
    "and sd.enddate >= @Now " +

    "and flt.INSTRCTR_ID_NUM = @HostID " +
    "and sm.crs_cde not like 'ONSO%'" +
    "AND sm.CRS_CDE NOT LIKE 'CLIN 7303 001' " +
    "AND sm.CRS_CDE NOT LIKE 'CLIN 7203 001' " +
    "AND sm.CRS_CDE NOT LIKE 'CLIN 8103 001' " +

    "and sm.LOC_CDE = 'main'";

    cmd = new SqlCommand(query, con);

    cmd.Parameters.AddWithValue("HostID", hostID);
    cmd.Parameters.AddWithValue("RecordID", txtRecordID.Text);
    cmd.Parameters.AddWithValue("now", DateTime.Now);dr = cmd.ExecuteReader();
    if (dr.Read())
    {
    // display data in textboxes
    txtRecordID.Text = dr["RecordID"].ToString();
    txtInstructorID.Text = dr["InstructorID"].ToString();
    txtInstructorName.Text = dr["InstructorName"].ToString();
    txtCourseCode.Text = dr["CourseCode"].ToString();
    txtCourseTitle.Text = dr["CourseTitle"].ToString();
    txtStartTime.Text = dr["StartTime"].ToString();
    txtAttendanceCode.Text = dr["AttendanceCode"].ToString();

    }
    else
    {
    lblMsg3.Text = "You do not have a class in the next hour.";

    }
    dr.Close();
    }
    //catch (Exception ex)
    catch (Exception)
    {
    //lblMsg.Text = "Error --> " + ex.Message;
    }
    finally
    {
    con.Close();
    }

    if (string.IsNullOrWhiteSpace(txtAttendanceCode.Text))
    {

    string numbers = "1234567890";

    string characters = numbers;

    int length = 5;
    string otp = string.Empty;
    for (int i = 0; i < length; i++)
    {
    string character = string.Empty;
    do
    {

    int index = new Random().Next(0, 5);
    character = characters.ToCharArray()[index].ToString();
    } while (otp.IndexOf(character) != -1);
    otp += character;
    }

    txtAttendanceCode.Text = otp;
    }
    }

    }

    protected void BtnGenerateCode_Click(object sender, EventArgs e)
    {
    {

    string numbers = "1234567890";

    string characters = numbers;

    int length = 5;
    string otp = string.Empty;
    for (int i = 0; i < length; i++)
    {
    string character = string.Empty;
    do
    {

    int index = new Random().Next(0, 5);
    character = characters.ToCharArray()[index].ToString();
    } while (otp.IndexOf(character) != -1);
    otp += character;
    }

    txtAttendanceCode.Text = otp;
    }

    }

    [WebMethod]
    public static string GetCourseTitles(string instructorId)
    {
    var result = new List<dynamic>();
    SqlConnection con = new SqlConnection(Database.ConnectionString);
    try
    {
    con.Open();

    var cmd = new SqlCommand("select distinct" +

    " sd.scheduledaysid as RecordID " + //guid, need to change course guid, not instructor ID
    ",sm.crs_cde as CourseCode" + //1 coursecode is second column
    ",sm.SHORT_CRS_TITLE_1 as CourseTitle" + //2 title is third column
    " from inet.dbo.LMS_ScheduleDays as sd" +
    " inner join net.dbo.lms_section as s" +
    " on sd.sectionid = s.SectionID" +
    " inner JOIN net.dbo.Course AS c" +
    " WITH (NOLOCK) ON s.CourseID = c.CourseID" +
    " inner join tmprd.dbo.section_master as sm" +
    " on c.CourseCode + ' ' + s.NAME = sm.crs_cde" +
    " and left(s.coursekey,4) = sm.yr_cde" +
    " and substring(s.CourseKey,6,2) = sm.trm_cde" +
    " inner join tmprd.dbo.student_crs_hist as sch" +
    " on sm.crs_cde = sch.crs_cde" +
    " and sm.yr_cde = sch.yr_cde" +
    " and sm.trm_cde = sch.trm_cde" +
    " inner join tmprd.dbo.faculty_load_table" +
    " as flt on sm.crs_cde = flt.crs_cde" +
    " and sm.yr_cde = flt.yr_cde" +
    " and sm.trm_cde = flt.trm_cde" +
    " inner join TmPrd.dbo.NAME_MASTER" +
    " as n on n.id_num = flt.instrctr_id_num " +

    "where " +

    "flt.instrctr_id_num = @instructorId " + //courses for current instructor
    "and sm.crs_cde not like 'ONSO%' " +
    "AND sm.CRS_CDE NOT LIKE 'CLIN 7303 001' " +
    "AND sm.CRS_CDE NOT LIKE 'CLIN 7203 001' " +
    "AND sm.CRS_CDE NOT LIKE 'CLIN 8103 001' " +
    "and sm.LOC_CDE = 'main'", con);

    cmd.Parameters.AddWithValue("instructorId", instructorId);
    var reader = cmd.ExecuteReader();
    while (reader.Read()) //reader, reading through results, adding records to dropdownlist
    {
    result.Add(new
    {

    RecordID = reader.GetInt32(0), //instrctr_id_num //{"Specified cast is not valid."}

    CourseCode = reader.GetString(1),
    Title = reader.GetString(2)
    });
    }
    reader.Close();
    }


    //catch (Exception ex)
    catch (Exception)
    {
    //lblMsg4.Text = "Error --> " + ex.Message;
    }

    finally
    {
    con.Close();
    }
    return JsonConvert.SerializeObject(result); //not returning result to web page, browserLink doesn't seem to run
    }


    protected void Page_Load(object sender, EventArgs e)
    {

    }


    protected void btnAdd_Click(object sender, EventArgs e)
    {
    SqlConnection con = new SqlConnection(Database.ConnectionString);
    try
    {
    con.Open(); //

    SqlCommand cmd = new SqlCommand("insert into Reporting.dbo.InstructorCourse(RecordID,InstructorID,CourseID,CourseCode,CourseTitle,AttendanceCode) values(@RecordID,@InstructorID,@CourseID,@CourseCode,@CourseTitle,@AttendanceCode)", con);

    cmd.Parameters.AddWithValue("@RecordID", txtRecordID.Text); //scheduledaysid guid, dropdownlist changes value
    cmd.Parameters.AddWithValue("@InstructorID", txtInstructorID.Text);
    cmd.Parameters.AddWithValue("@CourseID", txtRecordID.Text); //reserved for unique value, dropdownlist changes value
    cmd.Parameters.AddWithValue("@CourseCode", txtCourseCode.Text); //like CHS 6102 001 L, dropdownlist changes value
    cmd.Parameters.AddWithValue("@CourseTitle", txtCourseTitle.Text); //dropdownlist changes value
    cmd.Parameters.AddWithValue("@AttendanceCode", txtAttendanceCode.Text);

    int count = cmd.ExecuteNonQuery();
    if (count == 1)
    lblMsg3.Text = "Attendance Code [" + txtCourseCode.Text + "] has been recorded!";
    else
    lblMsg3.Text = "Could not add Attendance!";
    }
    catch (Exception ex)
    {
    lblMsg3.Text = "Error --> " + ex.Message;
    }
    finally
    {
    con.Close();
    }
    }
    }

    Tuesday, April 9, 2019 3:29 PM

Answers

All replies