Re: Exception: Syntax Error in INSERT TO. - C# Discussion Boards

Re: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code

Luc Pattyn11-Jul-12 4:46

Luc Pattyn

11-Jul-12 4:46

. As Dave said, look at the details provided by the stack trace.
And don't use release builds when there are still big issues in your code; with a debug build, the stack trace would provide actual line numbers rather than offsets.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

Exception: Syntax Error in INSERT TO.

kaycey9011-Jul-12 2:37

kaycey90

11-Jul-12 2:37

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

Re: Exception: Syntax Error in INSERT TO.

Pete O'Hanlon11-Jul-12 2:42

Pete O'Hanlon

11-Jul-12 2:42

kaycey90 wrote:
sb.Append('"'); sb.Append(student.Deleted); sb.Append("\",");
sb.Append(")");

You have a , just before your closing ). You don't need to put that in. BTW, why did you not consider using sb.AppendFormat instead? You could have saved yourself a lot of sb.Append work there.

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

Re: Exception: Syntax Error in INSERT TO.

BobJanova11-Jul-12 2:43

BobJanova

11-Jul-12 2:43

Use a parameterised query! And if that's too hard (it's not), at least escape the input strings. You're going to break things when you meet Mr. O'Brien. (Not to mention Bobby Tables.)

SQL doesn't use double quotes, it uses single quotes. But I will re-emphasise, building a query string like this is the wrong way.

Re: Exception: Syntax Error in INSERT TO.

PIEBALDconsult11-Jul-12 3:30

PIEBALDconsult

11-Jul-12 3:30

That deserves more than a 5.

Re: Exception: Syntax Error in INSERT TO.

Richard Andrew x6411-Jul-12 5:25

Richard Andrew x64

11-Jul-12 5:25

I've heard of Bobby Tables, but could you provide a link to Mr. O'Brien?

The difficult we do right away...
...the impossible takes slightly longer.

Re: Exception: Syntax Error in INSERT TO.

BobJanova11-Jul-12 6:29

BobJanova

11-Jul-12 6:29

That's not a comic reference, just the first Irish name that came to mind. Irish surnames are the source of most real world accidental query failures of this kind.

Re: Exception: Syntax Error in INSERT TO.

Richard Andrew x6411-Jul-12 8:18

Richard Andrew x64

11-Jul-12 8:18

Oh I see. The apostrophe.

The difficult we do right away...
...the impossible takes slightly longer.

Re: Exception: Syntax Error in INSERT TO.

Pete O'Hanlon11-Jul-12 8:35

Pete O'Hanlon

11-Jul-12 8:35

My post was just above and the first Irish name that came to mind was O'Brien?

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

Re: Exception: Syntax Error in INSERT TO.

BobJanova11-Jul-12 23:21

BobJanova

11-Jul-12 23:21

Haha sorry Pete, I don't usually read the column of poster names when I'm perusing a thread!

Re: Exception: Syntax Error in INSERT TO.

PIEBALDconsult11-Jul-12 3:37

PIEBALDconsult

11-Jul-12 3:37

I want to further emphasize what the others said. Use a parameterized query every time. It's safer and it's quicker if you reuse the command many times:

Set the command and add the parameters
Loop
  Set the paramater values
  Execute the command
Endloop

And what's with the Dictionary? Confused | :confused:

There's no guarantee which order the columns will be in when you create the table. A List of Tuples might be a better idea.

Passing an object through different window classes in Windows Forms

nstk11-Jul-12 0:55

nstk

11-Jul-12 0:55

In a Windows Forms application, I have a class Students and then I create a new object student inside the class Form1 which represents the main window of my application.

So far so good, but I need a second window which will show the user some of the student's data. Therefore I create a second class derived from Form, let's say
StudentsData : Form
and then I let a new object of that class be created inside the Form1 class, and again through ShowDialog() I let that new Window be visible.

What I do not succeed is to pass the student's data into that window. I think the real question should be, how do I make an object persist through different other classes/objects?

Any help would be appreciated.

Re: Passing an object through different window classes in Windows Forms

Pete O'Hanlon11-Jul-12 1:02

Pete O'Hanlon

11-Jul-12 1:02

If all you are trying to do is to pass the Students class through to your second form, you simply need to make sure you have added this into your StudentsData form before you call ShowDialog. Now, you could do this by passing it in a constructor in StudentsData, or you could simply expose a property that accepts StudentsData. Depending on what you are trying to achieve with this, I would choose the property because you are probably going to want to be reading the contents of this after your dialog has closed.

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

Re: Passing an object through different window classes in Windows Forms

nstk11-Jul-12 3:47

nstk

11-Jul-12 3:47

I have a main Form
public partial class Form1 : Form
a Data Form
public partial class Data : Form
and a class Students in another file with
public string Name
{ get; set; }

Now on Form1 I create
Students stud = new Students();
and then I give
stud.Name = "hisname";

Then by clicking a button on Form 1 the following happens

Data datawindow = new Data();
datawindow.ShowDialog();

When Data Form loads I want to get in a text box
txtName.Text = stud.Name;

But the object stud is not available on Data form.

Re: Passing an object through different window classes in Windows Forms

DaveyM6911-Jul-12 4:02

DaveyM69

11-Jul-12 4:02

In Data class:

private Students students;

public Students Students
{
    get{ return students; }
    set
    {
        students = value;
        txtName.Text = students.Name;
    }
}

In your Button Click handler in Form1:

Data datawindow = new Data();
datawindow.Students = stud;
datawindow.ShowDialog();

Dave

Binging is like googling, it just feels dirtier.
Please take your VB.NET out of our nice case sensitive forum.
Astonish us. Be exceptional. (Pete O'Hanlon)

BTW, in software, hope and pray is not a viable strategy. (Luc Pattyn)

Re: Passing an object through different window classes in Windows Forms

nstk12-Jul-12 1:36

nstk

12-Jul-12 1:36

Thanks for your help.

Unfortunately I am getting an error, and I think I have seen it before when I was trying to solve that problem, maybe because I have a class Students on a file students.cs

The error is:
Inconsistent accessibility: property type 'Studentstest.Students' is less accessible than property 'Studentstest.Data.Students'

Where Studentstest is the name of the project containing the following classes:
Form1.cs, Data.cs and Students.cs

Re: Passing an object through different window classes in Windows Forms

nstk12-Jul-12 1:44

nstk

12-Jul-12 1:44

OK, I found this
http://msdn.microsoft.com/en-us/library/532wtsbc%28v=vs.90%29.aspx
and corrected accordingly.

Thanks again.

modified 12-Jul-12 7:52am.

Re: Passing an object through different window classes in Windows Forms

BobJanova11-Jul-12 2:41

BobJanova

11-Jul-12 2:41

There's two common approaches I use for modal dialogs. The first is to write a method in StudentsDataForm (yes I just renamed your class Smile | :)

) which takes the Student, shows the form and returns a modified version:

public Student EditStudent(Student input){
 this.firstNameTextBox.Text = input.FirstName;
 // ... etc to copy data into other controls
 // you could also use data binding
 if(DialogResult.OK == ShowDialog()){
  Student result = new Student();
  result.FirstName = firstNameTextBox.Text;
  // etc
  return result;
 } else return input;
}

There's also a variant of this where you update the input object in place, and return the DialogResult, which you can use if you always want the data object to be updated by the form when OK is used.

The second way is to have a property in the second form:

public Student Student {
 get {
  Student student = new Student();
  student.FirstName = firstName.Text;
  // etc
  return student;
 }
 set {
  firstName.Text = value.FirstName;
  // etc
 }
}

Then you push the data into the form, and use the data from it if the user pressed OK:

studentDataForm.Student = someStudent;
if(DialogResult.OK == studentDataForm.ShowDialog(this)){
 // Do something with studentDataForm.Student to store the changed data
}

Re: Passing an object through different window classes in Windows Forms

nstk14-Jul-12 7:40

nstk

14-Jul-12 7:40

Hello and thanks for your help.

Of course it works that way, but assuming I have 10 different objects in the Main Form and I need their data in several other Forms of my programme, let's say 6 new forms, do I have to create similar objects of all those 10 in everyone of the 6 Forms?

Isn't there a way to make an object available in the whole programme?

modified 14-Jul-12 15:27pm.

Data to and from object?

Ronnie_B11-Jul-12 0:55

Ronnie_B

11-Jul-12 0:55

Hi. I'm new to C# so sorry if my questions will be little simple or not so clear.

I have a DLL that I I'm using as a COM reference in my project.

In the DLL there is a method I'd like to use:

STDMETHOD(CS7ProSim::ReadOutputImage)( long StartIndex,
long ElementsToRead,
ImageDataTypeConstants DataType,
VARIANT* pData)

My problem is the "VARIANT* pData" and how to access the returned data in it. The result I want from the method is a byte array[1024].

I declared this variable
public partial class Form1 : Form
{
byte [] QB = new byte[1024]; //Define the data area

Then I'm using this code...

//Set up variables
Object pData = QB;
//Setup of method datatype
ImageDataTypeConstants pdtConst;
pdtConst = ImageDataTypeConstants.S7Byte; //Read in byte form
//Read from-to
int StartByte = 0; //Start on byte 0
int ReadSize = 1024; //Read 1024 bytes

//Call the method
ps.ReadOutputImage(StartByte, ReadSize, pdtConst, ref pData); //Read the whole image of 1024 bytes

The method will return the correct data in the pData array. But I don't want to have it in a object but in an array.

My questions are...
-Do I really have to create the new pData object or can I somehow use the QB directly in the method call?

-If I use pData as I do now, how can I copy the values in pData back to QB?

If I "hover" above the "pData" I can see it's a array like QB and it has the values from the method, but how to get those values back to QB?

Thanks.

Shared memory Applicaion <->Service

Sebastian_G11-Jul-12 0:07

Sebastian_G

11-Jul-12 0:07

Hello,
how can a service read a shared memory file "MemoryMappedFile" from a user application.
I have a closed Source application that have a public Shared memory file with data.
In a normal test application i can read the Memory Mapped File.

In a service the same failed. I can only read Shared memory data from applications that shared with the filename: "Global\\filename", not segments with "filename".

There is a chance to read Shared Memory Files without the "Global\\" mark in a windows Service from a normal application.

Sebastian

Can I Apply Autocomplete to the master page in asp.net

Nitin_Kadam10-Jul-12 22:50

Nitin_Kadam

10-Jul-12 22:50

in my website there is a problem regarding the master page i can apply the autocomplete ajax externder to the master page then code is not work properly, the main thing is that can i use autocomplete externder to the master page in asp.net

Nitin Kadam

Re: Can I Apply Autocomplete to the master page in asp.net

DaveyM6911-Jul-12 0:03

DaveyM69

11-Jul-12 0:03

Although you may be coding in C#, the ASP.NET forum[^] may be better suited for your question.

Using Dotnet Login code in HTML doc

ASPnoob10-Jul-12 21:35

ASPnoob

10-Jul-12 21:35

Hi all,
If I want to have a login option on my home page without using javascript what are my choices. I can create a login page using asp.net but the problem is my home page would have an extension of .aspx and not .com or .net. I know that you could use a login page that is separate from the home page and then link to it but there has to be a way to have login on the home page. Please point me in the right direction, thanks in advance.

Re: Using Dotnet Login code in HTML doc

AmitGajjar10-Jul-12 22:08

AmitGajjar

10-Jul-12 22:08

I can create a login page using asp.net but the problem is my home page would have an extension of .aspx and not .com or .net.

- You can set default page as Login.aspx in your site and you can access your website using .com and your pagename will not appear. You can also use URL Redirection if you do not want to display .aspx extension.

Hope this will solve your problem

Thanks
-Amit.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.