; expected error in SQL statement

I'm working on my first C# project and have hit a very frustrating snag; I'm trying to run the SQL statements below (one or the other based on the If/else condition), but am getting an "; expected" error at the end of the first statement (bolded).  Note that:
    1)  the various "ID" variables are small integers, the IsTopMenu is a bit (boolean until imported into SQL Server);  
    2)  the code below is kind of a mess because I'm trying to trouble shoot-it; previously the code in the If block was similar to the code in the Else block; it didn't generate any errors but also didn't produce any results.
    3)  Using the syntax in the Else block, SQL Server Profiler showed that the variable names rather than the values were being passed to the statement, so I tried to fix that as in the If block (converting the the string variables in the Else block to the integers in the If block), but that's when the errors started...
    4) I tried to make the problem go away by just truncating the statement, so that it ended after '" + iService + "', but the error then simply moved to the new end of the statement.

 if(Convert.ToInt32(lbFormation.GetItemText(lbFormation.SelectedValue)) == 0)
                {
                    sUnitSQL =  @"SELECT [tblUnitList].UID, [tblUnitList].UnitDesc, [tblUnitList].SideID, [tblUnitList].FormTypeID 
                                FROM [tblUnitList] 
                                WHERE [tblUnitList].SideID = '" + iSide + "' AND [tblUnitList].ServiceID = '" + iService + "' AND [tblUnitList].IsTopMenu = [b]"1" 
                                ORDER BY UID";[/b]
                }
                else
                {
                    sUnitSQL = @"SELECT [tblUnitList].UID, [tblUnitList].UnitDesc, [tblUnitList].SideID, [tblUnitList].FormTypeID 
                                FROM [tblUnitList] 
                                WHERE [tblUnitList].SideID = ' + sSide + ' AND [tblUnitList].ServiceID = ' + sService + ' AND [tblUnitList].IsTopMenu = ' + 1 + '
                                    AND  [tblUnitList].FormTypeID = ' + SFormType + '
                                ORDER BY UID";
                }

Open in new window


Would greatly appreciate any thoughts; I've been googling for a couple of hours but can't seem to find a resolution.
tmreiterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
This should be the first case:
                    sUnitSQL =  @"SELECT [tblUnitList].UID, [tblUnitList].UnitDesc, [tblUnitList].SideID, [tblUnitList].FormTypeID 
                                FROM [tblUnitList] 
                                WHERE [tblUnitList].SideID = '" + iSide + "' AND [tblUnitList].ServiceID = '" + iService + "' AND [tblUnitList].IsTopMenu = '1' 
                                ORDER BY UID";

Open in new window

So your changes were almost correct.
tmreiterAuthor Commented:
Thanks, I'd actually already tried that as well; the errors change, but do not go away; see below:
C
The red underlining actually shows three separate errors:  
(1) a "newline in constant" error at the end of the WHERE clause;
(2) in the last line, it shows that "the type or namespace ORDER could not be found"; and
(3) at the very end (under UID), it says that "; expected" (even though there is a ; right there!).

I'm sort of at a loss at this point--any ideas?  One question--could Visual Studio be "confused" about the changes I've made to the SQL statement?  I'll try a bit later nuking and retyping the whole statement to see if that fixes the errors.
tmreiterAuthor Commented:
Weird...deleted and manually retyped SQL statement, but got the same errors...  

On a whim, I deleted the linebreak between the last AND clause and the ORDER BY clause, and the errors went away:

 if(Convert.ToInt32(lbFormation.GetItemText(lbFormation.SelectedValue)) == 0)
                {
                    sUnitSQL = @"SELECT [tblUnitList].UID, [tblUnitList].UnitDesc, [tblUnitList].SideID, [tblUnitList].FormTypeID
                                FROM [tblUnitList]
                                WHERE [tblUnitList].SideID = '" + iSide + "' AND [tblUnitList].ServiceID = '" + iService + "' AND [tblUnitList].IsTopMenu = '1' ORDER BY UID";
                }
                else
                {
                    sUnitSQL = @"SELECT [tblUnitList].UID, [tblUnitList].UnitDesc, [tblUnitList].SideID, [tblUnitList].FormTypeID 
                                FROM [tblUnitList] 
                                WHERE [tblUnitList].SideID = ' + sSide + ' AND [tblUnitList].ServiceID = ' + sService + ' AND [tblUnitList].IsTopMenu = ' + 1 + '
                                    AND  [tblUnitList].FormTypeID = ' + SFormType + '
                                ORDER BY UID";
                }

Open in new window


This "worked" (although the SQL statement still doesn't yield any results), but it would be helpful to understand what the problem was with the original statement?
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

tmreiterAuthor Commented:
As mentioned in my previous post, I've gotten the errors to go away, but the SQL statement doesn't provide any results (zero rows according to SQL Server Profiler).  

To troubleshoot, first I eliminated the WHERE clause and then replaced the SELECT clause with "SELECT *", and still got zero rows, so something seems fundamentally wrong.  Meanwhile, other SQL statements in this C# application are working fine.  The referenced table and columns exist.

But I guess I should open this as a new question, or at least re-title this one?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You cannot use line breaks in regular string literals, enclosed in ", but in verbatim string literals starting with ". Whenever you end a verbatim string with a double quote, and some other string, you need to continue with @" again if using line breaks.

That much about the syntax errors. Print out the result of the SQL string, run it in a SQL tool, and try to get it working there before using it in your application.
And a SELECT * does never change the number of rows, only if columns. The where clause determines what rows are selected.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tmreiterAuthor Commented:
You cannot use line breaks in regular string literals, enclosed in ", but in verbatim string literals starting with ". Whenever you end a verbatim string with a double quote, and some other string, you need to continue with @" again if using line breaks.

Ok, thanks, I'd have never figured that out...  That closes out this question, I'll award you the points.

And a SELECT * does never change the number of rows, only if columns. The where clause determines what rows are selected.
heh, yeah, I know, but the other SQL statements which are working on this form use SELECT * so I just wanted to check if there was a problem with the column names somehow...

Print out the result of the SQL string, run it in a SQL tool, and try to get it working there before using it in your application.
By "SQL tool" do you mean SQL Server Management Studio?  If so I'll google around and figure out how to do that.

My previous experience with both programming and databases was both very limited and restricted to Access/VBA, so C#/SQL Server is a bit of a slog for me.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Of course "regular string" and @"verbatim string" is correct.  Sorry for the typo above.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.