Update Data in a Database

[Nibor]

Hey guys,
I would like to create a button which can update the data in a mssql database.
Let's say I have a column in my database called "Password" and person x
lost his password and I want to reset it with this button. I have a field in my programm
called "fipw" in this field the person can write his new password.
The data is all in a datagrid and I just want to click on the entry in the datagrid
and then the button to update it. The problem is that it doesn't update in my mssql database
by the way I've coded the password column entrys with HashBytes('SHA1'...) and it works fine.
Here's my code. Maybe someone can help me to find the problem.

global gDB
on mouseUp
if the field "fipw" is empty
then
focus field "fipw"
answer "You have to put a password first to continue."
exit to top
end if
put field "fipw" into vPW
put the dghilitedline of group "dg1" into vreihe
put the dgDataOfLine[vrow] of group "dg1" into theDataA
put theDataA["UserID"] into vID
put "UPDATE FROM [Tablename] SET UserID= vIDWhere Passwort= '" & vPW' into vSQL
revExecuteSQL gDB,vSQL, "vPW"
end mouseUp

[Klaus]

Hi Nibor,

wrong and/or missing quotes and maybe too many unneccessary params!

To be sure please let you "answer" your SQL string before you execute it,
and get used to check "the result" after any database action!

Code: Select all

...
put "UPDATE YourTablenameHere SET UserID=" & vID && "where Passwort=" & vPW  into vSQL
revExecuteSQL gDB,vSQL
if the result begins with "revdberr" then
   ## something went wrong...
   answer the result
end if
...

Hint:
Do not use "THE" when addressing objects!
-> if fld "fipw" = empty...

Best

Klaus

[Nibor]

Hey
thanks for the quick answer.
I changed my code like you said it.
The problem is still there, in my database is still the old password.

The code looks like this

global gDB
on mouseUp
if field "fipw" = empty
then
focus field "fipw"
answer "You have to put a password first to continue."
exit to top
end if
put field "fipw" into vPW
put the dghilitedline of group "dg1" into vreihe
put the dgDataOfLine[vrow] of group "dg1" into theDataA
put theDataA["UserID"] into vID
put "UPDATE [Tablename] SET UserID=" & vID && "Where Passwort=" & vPW into vSQL
revExecuteSQL gDB,vSQL
if the result begins with "revdberr" then
answer the result
end if
end mouseUp

there is no error. But I checked "the result" and it says "0"
if I check vSQL or vID or vPW in the messagebox everything is ok.
But it doesn't update my data in the database

[Klaus]

Hi Nibor,

the result = 0 -> 0 rows updated, so your query failed.

If Passwort and UserID have spaces in them, you should single quote the values:
...
## I guess [Tablename] is replaced with the real name of your table, right? Wehe nicht!
put "UPDATE [Tablename] SET UserID=" & "'" & vID & "'" && "Where Passwort=" & "'" & vPW & "'" into vSQL
...

Best

Klaus

[Nibor]

Hey,
yeah of course "Tablename" is just a placeholder
thats the code at the moment still not updating

put "UPDATE [Tablename]] SET Password=" & "'" & vPW & "'" && "Where UserID=" & "'" & vID & "'" into vSQL

regards
Nibor

[Klaus]

Hi Nibor,

on the second look I found another error on your script:
...
put the dghilitedline of group "dg1" into vreihe
put the dgDataOfLine[vrow] of group "dg1" into theDataA
## vreihe <> vrow!
...
This may result in empty variables vPW and UserID, so the query fails!

Best

Klaus

[Nibor]

Hey,
well thats a translation mistake I made sorry.
In my original script its german.

[Klaus]

Ah, OK, maybe there are some invisible characters in the fields?

Sorry, I'm out of ideas...

[Nibor]

Hey,
a colleague just found the problem.

put "UPDATE [Tablename] SET Passwort=hashBytes('SHA1'," & "'" & vPW & "')" && "Where UserID=" & "'" & vID & "'" into vSQL

it was just the missing decode part I mentioned before
hashBytes('SHA1',....)



regards

Nibor

[Klaus]

Hi Nibor,

aha!

Hey,

you can call me Klaus!

Never heard of "hashByte(...)", is this a SQL function?


Best

Klaus

[Nibor]

Hey Klaus,
yeah hashBytes is a sql command to encode your stuff and SHA1 is a way you want to encode it with.
SHA stands for secure hash algorithm.
It's quite similar to MD5


regards

Nibor