not to sure? updating question!

[jnash]

okay so ive got values in textboxes in my form, that i have taken from my database (mySQL)

i know need to update them (not delete, just replace some of the fields) however doing research on the internet has shown me maybe a few ways ado.net, adodb, im now so confused can anyone help me out with code please so i can get a start to this as its been bogging me for a good day now

thanks again

Jon

 

[jhomminga]

Sql needs some single quotes when working with text

Excellent!!! the executenonquery will work too.

Just add single quotes around the values in the update statement and make sure at least one space between words(WHERE).

Change
"UPDATE tblMember SET Firstname =" & txtFname.Text & "WHERE memberID =" & txtMemberID.Text & ")"

To
"UPDATE tblMember SET Firstname ='" & txtFname.Text & "' WHERE memberID ='" & txtMemberID.Text & "')"

 

[cjard]

If you want a good all-round set of tutorials about data access in .NET 2.0 and how it all hangs together (in a generic sense - you can practice the concepts using an acces database if you like, then apply them to mysql) read the DW2 link in my signature; it tells you most that you could want to know about data access in 2.0

 

[cjard]

To
"UPDATE tblMember SET Firstname ='" & txtFname.Text & "' WHERE memberID ='" & txtMemberID.Text & "')"

While mainly a personal opinion, I think most developers would agree that it is better to teach new programmers about database access using parameterized queries, rather than the ancient, insecure, type-unsafe method involving string-concatenation..

 

[jnash]

Excellent!!! the executenonquery will work too.

Just add single quotes around the values in the update statement and make sure at least one space between words(WHERE).

Change
"UPDATE tblMember SET Firstname =" & txtFname.Text & "WHERE memberID =" & txtMemberID.Text & ")"

To
"UPDATE tblMember SET Firstname ='" & txtFname.Text & "' WHERE memberID ='" & txtMemberID.Text & "')"

thanks

but im getting an error still it points at the executenonquery

#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

 

[jhomminga]

Whoops. Lose the last closing parantheses. Sorry I didn't catch that earlier. Your update statement doesn't need the parenthesis.

Just
UPDATE TABLENAME SET FIELD=VALUE WHERE FIELD=VALUE

 

[jnash]

Whoops. Lose the last closing parantheses. Sorry I didn't catch that earlier. Your update statement doesn't need the parenthesis.

Just
UPDATE TABLENAME SET FIELD=VALUE WHERE FIELD=VALUE

? i dont understand, i still need my text values in there!

 

[jhomminga]

Does it look like this now in your code?
"UPDATE tblMember SET Firstname ='" & txtFname.Text & "' WHERE memberID ='" & txtMemberID.Text & "')"

If so try...
"UPDATE tblMember SET Firstname ='" & txtFname.Text & "' WHERE memberID ='" & txtMemberID.Text & "''"

Remember, all we did is add single quotes to the inside of the double quotes and now removed the last parenthesis.

So in debug mode it should look like
UPDATE tblMember SET Firstname ='Jimmy' WHERE memberID ='JRH'

and if the memberid is a number then it should not be in quotes
UPDATE tblMember SET Firstname ='Jimmy' WHERE memberID =123

 

[jnash]

Thank you so much for your help guys, ive successfully sorted this part out however now when the program recieves the date of birth its changes it from 1984-12-12 to 12/12/1984 12:00???

 

[cjard]

I think you need to read up on how dates are stored in a computer.. The time is there because it has to be. It wasnt changed from your notation (actually, your notation is a string so it was changed in one sense), it simply exists because time, as a linear progression concept with a fractional component, means dates have to have a time.

To "remove" it you have to forma tthe date object into a string that doesnt have the time component on show. You cannot get rid of the time aspect, only hide it.