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