Help With Construction Of Troublesome Sql Statement In Vb.net!

A Member? Please Login  
type your username and password to login
Date: October 15, 2008, 10:59 PM
250358 members and 149127 Topics
Latest Member: Mainfrrame
Nairaland [Nigerian Forum] Home Help Search Who is currently online? Login Register
Nairaland Forum  |  Technology  |  Programming  |  Help With Construction Of Troublesome Sql Statement In Vb.net!
Pages: (1) Go Down Send this topic Notify of replies
Author Topic: Help With Construction Of Troublesome Sql Statement In Vb.net!  (Read 94 views)
davidt (m)
Help With Construction Of Troublesome Sql Statement In Vb.net!
« on: June 02, 2008, 09:04 AM »

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!


kanirip (m)
Re: Help With Construction Of Troublesome Sql Statement In Vb.net!
« #1 on: June 02, 2008, 10:37 AM »

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

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!
davidt (m)
Re: Help With Construction Of Troublesome Sql Statement In Vb.net!
« #2 on: June 02, 2008, 10:59 AM »

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.

luckyCO
Re: Help With Construction Of Troublesome Sql Statement In Vb.net!
« #3 on: June 02, 2008, 11:59 AM »

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
 Join The New It User Group-9ija-it-pros. Hosted By The Culminis Alliance.  3 Nigerians, 368 Others Compete For Microsoft Laurel  Do You Want To Work In Ibm, Microsoft,tcs, Infosys, Nucleus Software?  Page 2
Pages: (1) Go Up Send Topic to Friend by E-mail Reply 
Google
 
Web www.nairaland.com
Sections: TV/Movies (2) Music/Radio (2) Celebrities Job Talk Jobs/Vacancies (2) Career Talk Romance Books Politics Sports Fashion Travel
Health Schooling Religion General(2) Business Webmaster Programming Computers Phones Cars & Trucks

Links: Page1 Page2 Page3 Page4 Page5 Page6 Page7 Page8 Page9 Page10

Nairaland is owned by Oluwaseun Osewa
Nairaland Forum | Powered by SMF 1.0.12.
© 2001-2005, Lewis Media. All Rights Reserved.