Update statement syntax 1

[dwg23]

Hello,
I am attempting to write an update statement to allow users to change thier password and am really struggling with the syntax in the statement.

Here is my code if someone could help me out with the syntax and wording. Also could someone recomend a good book on how to put all the " and ' in the right places?

<%Sql = "UPDATE tbl_operators_users "SET password="& spassword where oid = " & soperatorcode &" and username = " & susername'" %>

 

[espar]

is that your exact code cause there's some crazy " ing going on

"UPDATE tbl_operators_users SET password="& spassword & " where oid = " & soperatorcode & " and username = " & susername

your sql syntax seems fine

 

[kenhutchings]

Not far off...

<%Sql = "UPDATE tbl_operators_users SET password = " & spassword & " where oid = " & soperatorcode & " and username = " & susername & " %>

If the database field you are inserting into is numerical then use:

" & numnamehere & "

if the database field you are inserting into is text then use:

'" & txtnamehere & "'

Youll need to trap apostrophes in text fields using the replace method like so:

'" & replace(request("txtnamehere"),"'","''") & "'

Hope this helps

 

[espar]

lol - well he could use an int for a username

 

[Tarwn]

When I was starting out the way I would test my statements to make sure the syntax looked write (and executed) was to Response.Write them to the screen. This allowed me to see if all the quotes I needed were there, I hadn't accidentally lost a space in between two concatenations, things like that. Added bonus is that you could then drop it in Query Analyzer or Access's Query window and run it to make sure it worked as expected.

 

[DotNetGnat]

username and password are both reserved words...so use square braces [] to wrap them...

-DNG

 

[dwg23]

Hello All,
And thank you for all of your input. And again if someone can recomend a book that will teach this dense person how to properly write a SQL statement I would be very happy.

Ken,
I put your code into the page adding '" & password & "'
as it is a text field and get this when I response.write, response.end the SQL statement.

Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/op/user_cp.asp, line 28

Sql = "UPDATE tbl_operators_users SET password = '" & spassword & "' where oid = " & soperatorcode & " and username = " & susername & "

So I added a " to the end of the statement and I get this

UPDATE tbl_operators_users SET password = '222222' where oid = MED and username =

Tarwin,
I have tried response.write and dropping into the Query Analyzer but I do not understand what it is telling me other than I have an error.

Espar,
I get the same results as above when I use your code.

 

[gmmastros]

Run this in Query Analyzer and post the results back here.

Select  Column_Name, Data_Type 
From    Information_Schema.Columns 
Where   Table_Name = 'tbl_operators_users'
        And Column_Name In ('password','oid','username')

Generally speaking, you need apostrophe's to surround your data unless the data type is bit, tinyint, smallint, int or bigint.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dwg23]

gmmastros,
I hope I did this right. I input this into the Analyzer.
Select Column_Name, Data_Type
From Information_Schema.Columns
Where Table_Name = 'tbl_operators_users'
And Column_Name In ('password','oid','username')
and the result was.
(0 row(s) affected)

Thanks
Dave

 

[gmmastros]

Make sure you are connected to the correct database. Also, make sure the tablename and columnnames are spelled correctly.

The result of this query should be 3 records. Each record should have 2 columns. The first column would be the column name and the 2nd column would be the data type.

Assuming each column is varchar or nvarchar, then you would probably want to use...

Sql = "UPDATE tbl_operators_users SET password = '" & spassword & "' where oid = '" & soperatorcode & "' and  username = '" & susername & "'"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dwg23]

gmmastros,
That did the trick! Thank You very much!
It fails in the Query Analyzer but it do work on the page and I am not going to argue with results.

Thanks again.
Dave