Generating SQL within ASP.NET

[paulhudson]

I have an asp.net page that enables an administrator to reset users passwords. Having entered an unique username and a new password an SQL query is generated.

 

The NEW field in the database is set as Yes/No, if set to Yes then on logon the user is expected to change the password.

 

The following SQL line is generated:

 

UPDATE tbUsers SET new='1', password='xyz' WHERE Username='Under10'

 

But in trying to perform the update the following error occurs:

 

ERROR: Syntax error in UPDATE statement

 

I have tried running the SQL line from a query within the Access database and it works fine. The actual code that generates the SQL line is as follows:

 

sSQL = "UPDATE tbUsers SET new='1', password='" & NewPassword & "' WHERE Username='" & Username & "'"

 

This has me stumped - any suggestions would be greatly appreciated.

[HJB417]

try using

sSQL = "UPDATE tbUsers SET [new]='1', [password]='" & NewPassword & "' WHERE [username]='" & Username & "'"

 

Also, get in the habit of using parameters

sSQL = "UPDATE tbUsers SET [new]=@new, [password]=@NewPassword WHERE [username]=@Username"

or

sSQL = "UPDATE tbUsers SET [new]=?, [password]=? WHERE [username]=?"

there is no reason not to use parameters when a data provider supports it.

[paulhudson]

Thankyou! The square brackets worked a treat. I have gotten to using parameters but only when using stored procedures (or saved queries in Access). Is there any benefit in using them otherwise - excepting of course the adage of employing good practice?

 

Thankyou once again.

[HJB417]

using parameters stops sql injection attacks and some databases cache parameterized queries.