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();
}
}
}
