I am working on a small project and I have run into an itchy problem with an SQL statement. The construction of the SQL statement is as below:

Me.StudInsertCommand.CommandText = _
"INSERT INTO Students (FirstName, LastName, MidName, SessionAdmitted, Level, " & _
"MatricNumber, DeptID, ProgID, FacultyID) VALUES ('" & firstTxt.Text & "', " & _
"'" & lastTxt.Text & "', " & _
"'" & midTxt.Text & "', " & _
"'" & sessAdTxt.Text & "', " & _
levelBox.Text & ", " & _
"'" & matricTxt.Text & "', " & _
deptBox.SelectedValue & ", " & _
progBox.SelectedValue & ", " & _
facultyBox.SelectedValue & ""

Me.StudInsertCommand.ExecuteNonQuery()


Now the above statement is in the Click event of a button. After all the necessary information has been entered and the add button is clicked, the VB.NET compiler/IDE indicates that there is an SQL syntax error. My database is implemented in MS Access and I am using an OleDbConnection and MS Jet. The resultant SQL statement from the above concatenation is below:

"INSERT INTO Students (FirstName, LastName, MidName, SessionAdmitted, Level, MatricNumber, DeptID, ProgID, FacultyID) VALUES ('Gill', 'Bates', 'Michael', '2006/2007', 300, '06BC0987', 19, 27, 5)"

As stated earlier, all this results in an SQL syntax error. Below is the StackTrace. Database programming really annoys me. Please help! I have been staring at this thing since last week and annoyingly, it may be a simple problem.

Unhandled Exception: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

Can anyone please help me take a look, because my eye cannot see anything. I am quite sure of the data types. Thanks a lot guys!

Nothing seems to be wrong with the code, although I must confess that I am a Java Programmer

Anyways is the SQL string that u displayed the actual one that is generated by the code or what YOU EXPECT to be generated from the code.

If it was actually generated from the code , then you can simply copy and paste the code into your SQL Manager and see if it executes on its own. If it doesn't execute then you really are in the shit! From what I can see the only possible source of error is the spaces between the commas in the insert values. But that shouldn't be a big deal!

If that fails I'm sure it would have to do with the structure of your table but I seriously doubt it reaches that far!

The SQL statement is what was generated. Someone gave me an idea as to what might be wrong, I'll try that and keep the thread updated.

Kanirip, thanks for checking it.

Try this and let me know if it works.

INSERT INTO STUDENTS set FirstName=’Gill’,lastname=’Bates’,Midname=’Micheal’,sessionadmited=’2006/2007’,Level=300,MatricNumber='06BC0987',
DeptID=19,ProgID=27, FacultyID=5


Use Val keyword or integer.trypass or Directcast to cast string to Integer.

Val(deptBox.SelectedValue) & ", " & _
Val(progBox.SelectedValue) & ", " & _
val(facultyBox.SelectedValue )& ")"



2k care