hello! I have a code as shown below.. the problem is, the code adding new user is working fine but the code for changing password will not work.. it give error.. I need help?? Somebody? Thanks in advanced..


Private Sub Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Update.Click
Dim connection As New OleDbConnection(My.Settings.ConnectionString)
Dim cmd As New OleDbCommand
cmd.Connection = connection
cmd.Connection.Open()
cmd.CommandText = "Update Users set User_Name='" & TextBox1.Text & "',Password='" & TextBox2.Text & "'where User_Name='" & TextBox1.Text & "'"
cmd.ExecuteNonQuery()
End Sub
End Class



Private Sub AddButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddButton.Click
Dim connection As New OleDbConnection(My.Settings.ConnectionString)
Dim com As New OleDbCommand
com.Connection = connection
com.Connection.Open()
com.CommandText = "Insert into Users values('" & TextBox1.Text & "','" & TextBox2.Text & "')"
com.ExecuteNonQuery()
MsgBox("User Created Successfully....")
End Sub

It appears that you must be running into one of the"gotcha's" that we all run into once in a while. I think that in your system, the word "Password" is reserved -- at least that's all I can figure out. I created a small Microsoft Access database to test your code, and Access doesn't like "Password". Change the line to something like this ("Password" becomes "_Password"):

cmd.CommandText = "Update Users SET User_Name='" & TextBox1.Text & "', _Password='" & TextBox2.Text & "' where User_Name = '" & TextBox1.Text & "'"

You must also change the column name in the database to "_Password" (or whatever you choose) to match the SQL statement.

One more thing. Since you're opening the database connection, you really should close it.

Cmd.Connection.Close()

And

Com.Connection.Close()

thanks!! Now the password can be change.. but there is another problem.. I can't figure out the reasons..
now the login form is not working and the delete user is also not working.. before it was working fine.. and what do you mean by that the "password" is reserved? My codes for login form and delete user form are as below;

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Try
If (connection.State = ConnectionState.Closed) Then connection.Open()
cmd = New OleDbCommand("select * from Users where User_Name='" & TextBox1.Text & "' and _Password='" & TextBox2.Text & "'", connection)

Dim x As Integer
Dim xx As String
xx = cmd.ExecuteScalar().ToString
x = Convert.ToInt32(xx)

If x = 1 Then
Me.Hide()
Main.Show()

ElseIf x = 1 Then
Me.Hide()
Main.Show()

Else
MsgBox("Invalid User ID/Password!!", MsgBoxStyle.Critical, "Error")
TextBox1.Focus()


End If

connection.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub



Private Sub DelButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DelButton.Click

Dim connection As New OleDbConnection(My.Settings.ConnectionString)
Dim cm As New OleDbCommand
cm.Connection = connection
cm.Connection.Open()

cm.CommandText = "DELETE from Users WHERE _Password='" & TextBox2.Text & "'"
cm.ExecuteNonQuery()
MsgBox("User Deleted...", MsgBoxStyle.Information)
cm.Connection.Close()
End Sub

I'm not sure why your logon form is not working. It looks OK to me. Also, the Delete function looks OK, also.



What do you mean by that the "password" is reserved?


I mean that the database system, itself, uses the word "password" to mean something special. It's not "a password" that's reserved, it's the word, "password". The writers of the database don't allow reserved words to be used as a column headings. They "reserve" them. Here's a reference:

http://kb2.adobe.com/cps/180/tn_18050.html

"Certain words are reserved database words. If you use these words in your SQL statements or in CFML code, for instance, they can cause unexpected behavior. This article provides links to reserved database word lists for various databases. "