Incorrect syntax near the keyword 'THEN'.

Hi All,
I'm trying to put an if statement in my SP, which incidently is comparing
two dates, thats by the by, because I cant even get this simple if
statement
to run with out error:
IF (9 > 8) THEN
PRINT '9 is greater than 8'
END IF
Also tried:
IF (9 > 8)
PRINT '9 is greater than 8'
END IF
And:
IF (9 > 8)
PRINT '9 is greater than 8'
END
Help! Whats wrong here?
Simon.
---
I am using the free version of SPAMfighter for private users.
It has removed 2976 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!Forgot to add the error messages for the 2nd and 3rd attempts:

> IF (9 > 8)
> PRINT '9 is greater than 8'
> END IF
Incorrect syntax near the keyword 'END'.

> IF (9 > 8)
> PRINT '9 is greater than 8'
> END
Incorrect syntax near the keyword 'END'.|||do you have SQL Server Books Online? The help on IF is pretty clear -
it's not like VB [if you don't have it, you need to get it]
Simon Harris wrote:
> Hi All,
> I'm trying to put an if statement in my SP, which incidently is comparing
> two dates, thats by the by, because I cant even get this simple if
> statement
> to run with out error:
> IF (9 > 8) THEN
> PRINT '9 is greater than 8'
> END IF
> Also tried:
> IF (9 > 8)
> PRINT '9 is greater than 8'
> END IF
> And:
> IF (9 > 8)
> PRINT '9 is greater than 8'
> END
> Help! Whats wrong here?
> Simon.
>
> ---
> I am using the free version of SPAMfighter for private users.
> It has removed 2976 spam emails to date.
> Paying users do not have this message in their emails.
> Try www.SPAMfighter.com for free now!
>|||Thanks Trey, I do have SQL Books online (Forgot about it...Too used to
Googling!) after reading that it makes sense, and works.
Thank you!

Incorrect syntax near LEFT

Hi guys;

I am trying to create a stored procedure in my database with dynamic filter, how ever i cant even let my query work.

When i run my stored procedure i get this errorIncorrect syntax near 'LEFT'

I dnt knw wats wrong with my code.

Please help me...
below is my stored procedure..

Thanks is advance.

ALTER PROCEDURE dbo.GetClassByCustomFilter
@.pcCustomFilterNVARCHAR(500)=''
AS
DECLARE @.sSqlString nvarchar(1024)
SET @.sSqlString = 'SELECT R.cRoomNo, P.cFirstName, P.cLastName, P.cMiddleName, U.cCode AS cSubjectCode, U.cName AS cSubjectName, '
SET @.sSqlString = @.sSqlString + 'U.cDescription AS cSubjectDescription, B.cCode AS cSection, H.cName AS cRecurenceName, C.iClassID, C.iInstructorID, C.iSubjectid, C.iEnrollmentID, '
SET @.sSqlString = @.sSqlString + 'C.iRecordTypeID, C.iBlockSectionID, C.cCode, C.cType, C.iRoomID, C.cRecurrence, CAST(CONVERT(nvarchar, GETDATE(), 1) + '' '' + CONVERT(nvarchar, '
SET @.sSqlString = @.sSqlString + 'C.tStartTime,8) AS datetime) AS tStartTime, CAST(CONVERT(nvarchar, GETDATE(), 1) + '' '' + CONVERT(nvarchar, C.tEndTime,8) AS datetime) '
SET @.sSqlString = @.sSqlString + 'AS tEndTime, C.fUnits, C.nAllowed, C.nMaxAllowed, C.mNotes, C.tCreated, C.tEdited, C.iEditedBy, C.iCreatedby, C.adGUID '
SET @.sSqlString = @.sSqlString + 'FROM dbo.PERSONALINFO P INNER JOIN dbo.INSTRUCTORS I ON P.iPersonalInfoId = I.iPersonalInfoID RIGHT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.CLASSES C INNER JOIN dbo.BLOCKSECTIONS B ON C.iBlockSectionID = B.iBlockSectionId INNER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.SUBJECTS U ON C.iSubjectid = U.iSubjectID LEFT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.SCHEDULERECURRENCE H ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID LEFT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.ROOMS R ON C.iRoomID = R.iRoomId WHERE (C.cType <> ''0'')'
IF LEN(@.pcCustomFilter) > 0
BEGIN
SET@.sSqlString = @.sSqlString + ' AND ' + @.pcCustomFilter
END
EXEC sp_executesql @.sSqlString

Well, thanks for reminding me why I hate complex dynamic SQL strings.

Thisa line is causing you problems:

SET @.sSqlString = @.sSqlString + 'dbo.SCHEDULERECURRENCE H ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID LEFT OUTER JOIN '

Note the:ON C.cRecurrence = H.cRecurrenceON I.iInstructorID = C.iInstructorID (two ON clauses for a single JOIN clause).|||Thanks for the reply .

The Query is correct.

I get this error because the lenght of the variable@.sSqlString is only 1024 but my query string is longer than that.

Regards|||Well, be that as it may, having to ON statements for a single JOIN clause will give you a syntax error...|||Of course it will. But the SQL statement below has 6JOIN and 6ON.

enewe thanks 4 the time.

Regards.|||The following, cut and pasted from you original post, is what I'm referencing:

SET @.sSqlString = @.sSqlString + 'dbo.SUBJECTS U ON C.iSubjectid = U.iSubjectID LEFT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.SCHEDULERECURRENCE H ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID LEFT OUTER JOIN '

Note the 'ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID' of the second line. That will cause issues.

However, I'm glad you got it working...|||Of course it wont work bcoz its just a part of the statement, not the whole statement. :) jst Kid'N. But I am 101% sure that the SQL Statement below will work because I just copied it from Visual Studio .NET View Designer.

8)

incorrect syntax near ;

Hi,

I am trying to test a login form and I get this error message and can't find out why. Istarted out with the Login control, but since I have to try it on the ISP's server, I can't use the SQL Server Managment Studio's integrated authentication. So, I converted the login control to a template and assigned a handler for the login button:

protectedvoid LoginButton_Click(object sender,EventArgs e)

{

String usrname = lpLogin.UserName.ToString(); //lpLogin is the <ASP:Login ...>

String conString ="Data Source=mylocalserver\\SQLEXPRESS;Initial Catalog=LPRU;Integrated Security=True";

String selQuery ="SELECT [Password], [FirstName], [LastName] FROM [lpUserInfo] WHERE ([UserID] ='" + usrname +"';";

SqlConnection con =newSqlConnection(conString);

SqlCommand cmd =newSqlCommand(selQuery, con);

con.Open();

SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); // <--it says "syntax error near ';' " on this line, I tried it without CommandBehavior

while (rdr.Read())

{

Label1.Text= rdr.GetString(0) + rdr.GetString(1); // for testing purposes, trying to print out first name and last name

}

rdr.Close();

con.Close();

}

Is there a way of using SQLServer 2000, used by my ISP, and take advantage of .net 2.0's login control, roles, membership, ...? By just using a connection string?

I think you have an unwanted ";" in the line code

String selQuery ="SELECT [Password], [FirstName], [LastName] FROM [lpUserInfo] WHERE ([UserID] ='" + usrname +"';";

You should change it to this,maybe it can work well.

String selQuery ="SELECT [Password], [FirstName], [LastName] FROM [lpUserInfo] WHERE ([UserID] ='" + usrname;

wish this help you

|||

In the immortal words of Homer Simpson, "DOH!".. (I know your working in C#, but that doesn't mean a semi-colon is good for everything)

look at your line:

String selQuery ="SELECT [Password], [FirstName], [LastName] FROM [lpUserInfo] WHERE ([UserID] ='" + usrname +"';";

And then look at this line:

String selQuery ="SELECT [Password], [FirstName], [LastName] FROM [lpUserInfo] WHERE ([UserID] ='" + usrname +"')";

Don't you just hate it when that happens... For the record, the queryis executed on the line where you get the exception rather than where you make the assignment.

|||

Jason,

You got rid of the offending semicolon, but you still have to close the single quote and close parenthesis around 'usrname'

|||

NoBullMan:

String selQuery ="SELECT [Password], [FirstName], [LastName] FROM [lpUserInfo] WHERE ([UserID] ='" + usrname +"';";

You missed a ')' at the end of the query string, which you can easily check in Query Analyzer (or any where you can parse T-SQL statement) BTW, if there is a single quote in the usrname, the query string will be broken, unless you replace every single quote in the usrname with 2 single quotes; and such concatenated queries may lead to SQL Injection, so always useParameterized Queries.

|||

Thank you guys. I am from php/MySQL background and the ';' at the end of the query doesn't cause problems in MySQL. I appreciate your help.

|||T-SQL in SQL Server also accepts ';'sql

In VB Express 2005, I cant save any record in database of sql server.

I was using the msdn tutorial regarding sql database for vb2005 express. In that tutorial, I followed the steps as listed. But the problem is that the save button in my application during run time, it does not work. Furthermore, the code for for saving a record during run time does not work as well.

I have reinstall sql 2005 server and vb2005 express but of no use.

I have winxp with system of pentium 4.

What I should do to solve this dilemma.

Faisal.

You may want to check with this group of people:

http://www.tutorialized.com/tutorials/Visual-Basic/Database-Related/1

Buck Woody

In VB Express 2005, I cant save any record in database of sql server.

I was using the msdn tutorial regarding sql database for vb2005 express. In that tutorial, I followed the steps as listed. But the problem is that the save button in my application during run time, it does not work. Furthermore, the code for for saving a record during run time does not work as well.

I have reinstall sql 2005 server and vb2005 express but of no use.

I have winxp with system of pentium 4.

What I should do to solve this dilemma.

Faisal.

You may want to check with this group of people:

http://www.tutorialized.com/tutorials/Visual-Basic/Database-Related/1

Buck Woody