Hi,
I wrote a code to export my data in gridview to excel. But at runtime it throws an exception saying "syntax error in INSERT TO" on execution of the command. I stepped through the code to see the output of the sql statement and I see nothing wrong with it. Can anyone assist me quickly on this please? Very many thanks in advance.
NB: The table creates perfectly, but the INSERT statement gives the exception.
My Code:
public static void ExportStudentsToXls(string filepath, List<Student> students)
{
using (ExcelHelper helper = new ExcelHelper(filepath))
{
helper.Hdr = "YES";
helper.Imex = "0";
Dictionary<string, string> tableDefinition = new Dictionary<string, string>();
tableDefinition.Add("StudentID", "int");
tableDefinition.Add("RegNo", "varchar(50)");
tableDefinition.Add("Surname", "varchar(100)");
tableDefinition.Add("Firstname", "varchar(100)");
tableDefinition.Add("Middlename", "varchar(100)");
tableDefinition.Add("LevelID", "int");
tableDefinition.Add("Sex", "varchar(10)");
tableDefinition.Add("DOB", "decimal");
tableDefinition.Add("Religion", "varchar(50)");
tableDefinition.Add("Nationality", "varchar(100)");
tableDefinition.Add("StateOfOrigin", "varchar(100)");
tableDefinition.Add("ClassID", "int");
tableDefinition.Add("ParentID", "int");
tableDefinition.Add("Address", "varchar(150)");
tableDefinition.Add("HomePhone", "varchar(20)");
tableDefinition.Add("MobilePhone", "varchar(20)");
tableDefinition.Add("Email", "varchar(255)");
tableDefinition.Add("NextofKin", "varchar(100)");
tableDefinition.Add("KinAddress", "varchar(200)");
tableDefinition.Add("BloodGroup", "varchar(50)");
tableDefinition.Add("Genotype", "varchar(50)");
tableDefinition.Add("Disability", "varchar(50)");
tableDefinition.Add("SessionID", "int");
tableDefinition.Add("TermAdmitted", "varchar(50)");
tableDefinition.Add("LevelAdmitted", "varchar(50)");
tableDefinition.Add("ClassAdmitted", "varchar(50)");
tableDefinition.Add("DateAdmitted", "decimal");
tableDefinition.Add("Hobbies", "varchar(50)");
tableDefinition.Add("Picture", "varchar(50)");
tableDefinition.Add("DateRegistered", "decimal");
tableDefinition.Add("Status", "varchar(50)");
tableDefinition.Add("Deleted", "varchar(5)");
helper.WriteTable("Students", tableDefinition);
string decimalQuoter = (CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator.Equals(",") ? "\"" : String.Empty);
foreach (var student in students)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO [Students] (StudentID, RegNo, Surname, Firstname, Middlename, LevelID, Sex, DOB, Religion, Nationality, StateOfOrigin, ClassID, ParentID, Address, HomePhone, MobilePhone, Email, NextofKin, KinAddress, BloodGroup, Genotype, Disability, SessionID, TermAdmitted, LevelAdmitted, ClassAdmitted, DateAdmitted, Hobbies, Picture, DateRegistered, Status, Deleted) VALUES(");
sb.Append(student.StudentID); sb.Append(",");
sb.Append('"'); sb.Append(student.RegNo); sb.Append("\",");
sb.Append('"'); sb.Append(student.Surname); sb.Append("\",");
sb.Append('"'); sb.Append(student.Firstname); sb.Append("\",");
sb.Append('"'); sb.Append(student.Middlename); sb.Append("\",");
sb.Append(student.LevelID); sb.Append(",");
sb.Append('"'); sb.Append(student.Sex); sb.Append("\",");
sb.Append(decimalQuoter); sb.Append(student.DOB.Value.ToOADate()); sb.Append(decimalQuoter); sb.Append(",");
sb.Append('"'); sb.Append(student.Religion); sb.Append("\",");
sb.Append('"'); sb.Append(student.Nationality); sb.Append("\",");
sb.Append('"'); sb.Append(student.StateOfOrigin); sb.Append("\",");
sb.Append(student.ClassID); sb.Append(",");
sb.Append(student.ParentID); sb.Append(",");
sb.Append('"'); sb.Append(student.Address); sb.Append("\",");
sb.Append('"'); sb.Append(student.HomePhone); sb.Append("\",");
sb.Append('"'); sb.Append(student.MobilePhone); sb.Append("\",");
sb.Append('"'); sb.Append(student.Email); sb.Append("\",");
sb.Append('"'); sb.Append(student.NextofKin); sb.Append("\",");
sb.Append('"'); sb.Append(student.KinAddress); sb.Append("\",");
sb.Append('"'); sb.Append(student.BloodGroup); sb.Append("\",");
sb.Append('"'); sb.Append(student.Genotype); sb.Append("\",");
sb.Append('"'); sb.Append(student.Disability); sb.Append("\",");
sb.Append(student.SessionID); sb.Append(",");
sb.Append('"'); sb.Append(student.TermAdmitted); sb.Append("\",");
sb.Append('"'); sb.Append(student.LevelAdmitted); sb.Append("\",");
sb.Append('"'); sb.Append(student.ClassAdmitted); sb.Append("\",");
sb.Append(decimalQuoter); sb.Append(student.DateAdmitted.Value.ToOADate()); sb.Append(decimalQuoter); sb.Append(",");
sb.Append('"'); sb.Append(student.Hobbies); sb.Append("\",");
sb.Append('"'); sb.Append(student.Picture); sb.Append("\",");
sb.Append(decimalQuoter); sb.Append(student.DateAdmitted.Value.ToOADate()); sb.Append(decimalQuoter); sb.Append(",");
sb.Append('"'); sb.Append(student.Status); sb.Append("\",");
sb.Append('"'); sb.Append(student.Deleted); sb.Append("\",");
sb.Append(")");
helper.ExecuteCommand(sb.ToString());
}
}
|