I am having trouble getting my syntax right for a sqlexecute INSERT INTO command. Can someone please assist me? Here is the code:
db.SQLExecute(“INSERT INTO StudentInfo VALUES (FirstName = '” + StudentList.Cell(StudentList.ListIndex,0) + “’ OR LastName = '” + StudentList.Cell(StudentList.ListIndex,1) + “’ OR Age = '” + StudentList.Cell(StudentList.ListIndex,2) + “’ OR DOB = '” + StudentList.Cell(StudentList.ListIndex,3) + “’ OR School = '” + StudentList.Cell(StudentList.ListIndex,4) + “’ OR Teacher = '” + StudentList.Cell(StudentList.ListIndex,5) + “’ OR Grade = '” + StudentList.Cell(StudentList.ListIndex,6) + “’ OR DORMONTH = '” + StudentList.Cell(StudentList.ListIndex,7) + “’ OR DORDAY = '” + StudentList.Cell(StudentList.ListIndex,8) + “’ OR DORYEAR = '” + StudentList.Cell(StudentList.ListIndex,9) + “’ OR StudentID = '” + StudentList.Cell(StudentList.ListIndex,10) + “’”)
This is the error:
near “’’”: syntax error
INSERT syntax is typically something like this:
INSERT INTO table (col1, col2, col3) VALUES (val1, val2, val3)
Your syntax with the “OR” embedded is not something I’ve seen before.
INSERT INTO has 3 basic syntax
- The one Paul mentioned
- an extended version with multiple VALUE(val1,val2,val3) separated by comma
- a more complex version inserting data being read from one or more other tables
About the only place OR is used (most common usage) is in a WHERE statement
I have tried the following just to see what happens, but I still get an error. I am sure it has to do with my values, but I just can’t seem to get it tweaked just right. I am trying to pull the text from some specific fields to populate the database.
db.SQLExecute(“INSERT INTO StudentInfo (FirstName, LastName, Age, DOB, School, Teacher, Grade, DORMONTH, DORDAY, DORYEAR) VALUES (”+txtFirstName.text+", “+txtLastName.text+”)")
near “,”: syntax error
Sorry to be a burden.
put the sql statement into a MSG box and LOOK at it… does it look right?
If you think it does… then post it here… NOT the code (as you did above)… but the RESULT the would be going to the SQLExecute
If the two text fields only contain one value (first and last names), then you are missing age, dob, etc.
and you might need to insert some single quotes around “string” values
kinda hard to see… but its double quote single quote double quote + first name
Your statement is also saying that you want to insert FirstName, LastName, Age, DOB, School, Teacher, Grade, DORMONTH, DORDAY, DORYEAR but you’re only providing the first two values.
Aditionally to the points raised above, I’d strongly suggest using PreparedStatements!
Many thanks guys! Dave’s answer made easy work of my value issue.