asked on

; 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.

Qlemo

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.

tmreiter

ASKER

Thanks, I'd actually already tried that as well; the errors change, but do not go away; see below:

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.

tmreiter

ASKER

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?

tmreiter

ASKER

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?

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tmreiter

ASKER

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

Of course "regular string" and @"verbatim string" is correct. Sorry for the typo above.