Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-06-17 : 08:10:31
|
| Gary Taylor writes "Dear Team,We are currently developing an application in VB6 that accesses an SQL database on SQL Server 2000 that was upsized from Access 2000. While we have a few amendments to make regarding the use of " and ' we are reciving a number of invalid syntax errors on our ADODB sql strings e.g.Run-time error '-2147217900 (80040e14)Line 1: Incorrect syntax near 'lel_course_id'This is generated from the SQL string:sqlstring = "SELECT [student&course].lel_student_id, [student&course].lel_course_id, course.course_title, course.course_summary, course.course_mode, course.course_e_type FROM (course INNER JOIN [student&course] ON course.lel_course_id = [student&course].lel_course_id) INNER JOIN how_allocated ON course.lel_course_id = how_allocated.lel_course_id WHERE ((([student&course].lel_student_id)=" & var_lel_student_id & ") AND (([student&course]![lel_course_id])=[course]![lel_course_id]) AND (([course]![course_mode])<>""blend-only-ins"" And ([course]![course_mode])<>""blend-only-e"") AND ([how_allocated]![how]=""ind"" Or [how_allocated]![how]=""g""));"When we add a watch to the variable sqlstring, it tells us that the value is:"SELECT [student&course].lel_student_id, [student&course].lel_course_id, course.course_title, course.course_summary, course.course_mode, course.course_e_type FROM (course INNER JOIN [student&course] ON course.lel_course_id = [student&course].lel_cours_"This is 253 characters long and a truncated version of our string.Could you tell us why this is? We have a number of complicated sql strings using inner joins..............is there a limit to the number of characters used in an sql statement? If so is there a work around, or is there a patch or add on to correct this problem? Everything worked fine on Access 2000, this problem has only appeared since upsizing the database to SQL Server 2000.Many thanks in advance.Best regards,John Rae and Gary Taylor" |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-06-17 : 08:37:54
|
| What is sqlstring stored in? What else has changed apart from upsizing? If sqlstring is not stored on the DBMS, then this is a VB6 coding issue. I'd check the dimensioned length of sqlstring, to be honest.-------Moo. |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-06-17 : 14:55:25
|
| I think the watch window is not showing you the complete string, but anyway the problem seems to be here:sqlstring = "SELECT [student&course].lel_student_id, [student&course].lel_course_id, course.course_title, course.course_summary, course.course_mode, course.course_e_type FROM (course INNER JOIN [student&course] ON course.lel_course_id = [student&course].lel_course_id) INNER JOIN how_allocated ON course.lel_course_id = how_allocated.lel_course_id WHERE ((([student&course].lel_student_id)=" & var_lel_student_id & ") AND (([student&course]![lel_course_id])=[course]![lel_course_id]) AND (([course]![course_mode])<>""blend-only-ins"" And ([course]![course_mode])<>""blend-only-e"") AND ([how_allocated]![how]=""ind"" Or [how_allocated]![how]=""g""));" The TableName!ColumnName qualifier works only in Access. For SQL Server, you must use TableName.ColumnName. Also replace all the double quotes inside the sqlstring with single quotes. SQL Server will probably yell about that too. And you wont need to escape single quotes like double quotes (by doubling them)sqlstring = "SELECT [student&course].lel_student_id, [student&course].lel_course_id, course.course_title, course.course_summary, course.course_mode, course.course_e_type FROM (course INNER JOIN [student&course] ON course.lel_course_id = [student&course].lel_course_id) INNER JOIN how_allocated ON course.lel_course_id = how_allocated.lel_course_id WHERE ((([student&course].lel_student_id)=" & var_lel_student_id & ") AND (([student&course].[lel_course_id]=[course].[lel_course_id]) AND (([course].[course_mode])<>'blend-only-ins' And ([course].[course_mode])<>'blend-only-e') AND ([how_allocated].[how]='ind' Or [how_allocated].[how]='g'));" Owais |
|
|
|
|
|
|
|
|
Line 1: Incorrect syntax near 'lel_course_id' - problem with sql statement length?