Proper Syntax?

[vb89]

When I run my code I seem to get a 'SQL command not properly ended '
Here is my code:

'WHERE CLAUS (FILTERS)
 
  tWhereSQL = " WHERE main.active_record <> 'R' "&_
  				" AND main.game_type_id ="& iGameType &_ 
  				" AND main.split_number = -1" &_
				" AND main.league_id IN (1,6)" &_
				" AND main.season = "& iSeason &_
  				" AND main.team_id_1032 = " & iID &_
				" AND gs.team_id = main.team_id" &_
				" AND gs.game_type_id = main.game_type_id" &_
				" AND gs.game_type_desc = main.game_type_desc" &_
				" AND gs.league_id = main.league_id" &_
				" AND gs.league_name = main.league_name" &_
				" AND gs.team_id_1032 = main.team_id_1032" &_
				" AND gs.team_name = main.team_name" &_
				" AND gs.team_nickname = main.team_nickname" &_
				" AND gs.team_abbrev = main.team_abbrev" &_
				" AND gs.season_id = main.season_id"
				
  tWhereOppSQL = " WHERE main.active_record <> 'R' "&_
  				" AND main.game_type_id ="& iGameType &_ 
  				" AND main.split_number = -1" &_
				" AND main.league_id IN (1,6)" &_
				" AND main.season = "& iSeason &_
  				" AND main.team_id_1032 = " & iID &_
				" AND gs.team_id = main.team_id" &_
				" AND gs.game_type_id = main.game_type_id" &_
				" AND gs.game_type_desc = main.game_type_desc" &_
				" AND gs.league_id = main.league_id" &_
				" AND gs.league_name = main.league_name" &_
				" AND gs.team_id_1032 = main.team_id_1032" &_
				" AND gs.team_name = main.team_name" &_
				" AND gs.team_nickname = main.team_nickname" &_
				" AND gs.team_abbrev = main.team_abbrev" &_
				" AND gs.season_id = main.season_id"
				
  tDataSQL = tDataSQL & tWhereSQL & tWhereOppSQL

My question is this code properly formated:
"tDataSQL = tDataSQL & tWhereSQL & tWhereOppSQL "

When I run the output in Querly Analyzer:

WHERE main.active_record <> 'R'
   AND main.game_type_id = 1
   AND main.split_number = -1
   AND main.league_id IN (1, 6)
   AND main.season = 2007
   AND main.team_id_1032 = 8
   AND gs.team_id = main.team_id
   AND gs.game_type_id = main.game_type_id
   AND gs.game_type_desc = main.game_type_desc
   AND gs.league_id = main.league_id
   AND gs.league_name = main.league_name
   AND gs.team_id_1032 = main.team_id_1032
   AND gs.team_name = main.team_name
   AND gs.team_nickname = main.team_nickname
   AND gs.team_abbrev = main.team_abbrev
   AND gs.season_id = main.season_id
 WHERE main.active_record <> 'R' 
 ...

where the line " WHERE main.active_record <> 'R' " starts for the second function it states "SQL command not properly ended

 

[jmeckley]

tDataSQL = tDataSQL & tWhereSQL & tWhereOppSQL

this doesn't make sense as your sql will end up looking like

select ...
from ...
where ...
where ... //this is invalid

AND main.game_type_id ="& iGameType &_

you should be using parameterized queries, not sql injection to construct commands.

command.CommandText = "... AND main.game_type_id =@gametype";
IDbParameter p = command.CreateParameter("gametype");
p.Value = iGameType;

I would also recommend a StringBuilder over concatenated strings. this reduces the memory stack when constructing strings.

Jason Meckley
Programmer
Specialty Bakers, Inc.