user3280007 user3280007 - 4 months ago 19
Vb.net Question

VB Forms INSERT INTO statement, Changing Password Form

Hey I'm working on a school project and have run into some issues

I'm trying to make a change password form. I have an access table called "tblLogin" with the columns Username, Password, SecQuest, SecAns, Level.

Username is a public variable which belongs in my login form

The program is currently not changing the value of password in the tblLogin table

I assume it's a problem with my INSERT INTO Statement

Thank you in advance for any help you can provide!

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim sql As String

Dim Conn As New OleDb.OleDbConnection("PROVIDER = Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\Nelson\Documents\SystemDB\DB.accdb")
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim response As Integer

Try


Conn.Open()
'establishes communication with the database
sql = "SELECT Password FROM tblLogin WHERE Username = """ & LoginForm.username & """"
'declares SQL statement
da = New OleDb.OleDbDataAdapter(sql, Conn)
'runs sql statement on database
da.Fill(ds, "passwordcheck")
'fills in a dataset called "passwordcheck" with infomation selected by the sql statement

If TextBox1.Text = TextBox2.Text And TextBox3.Text = TextBox4.Text And TextBox1.Text = ds.Tables("passwordcheck").Rows(0).Item(0) And TextBox1.Text <> "" And TextBox2.Text <> "" And TextBox3.Text <> "" And TextBox4.Text <> "" Then
response = MessageBox.Show("Are you sure?", "Attention", MessageBoxButtons.YesNo)

If response = MsgBoxResult.Yes Then
Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("INSERT INTO tblLogin Password VALUES (?) WHERE Username = """ & LoginForm.username & """", Conn)
cmd.Parameters.Add("@Password", OleDb.OleDbType.VarChar).Value = TextBox3.Text
Conn.Close()
End If
Else
MessageBox.Show("Passwords entered are incorrect", "Attention")
End If

Catch ex As Exception
MessageBox.Show("Passwords entered are incorrect", "Attention")
End Try

End Sub

Answer

PASSWORD is a reserved keyword for MS-Access. If you want to use it for column names then enclose in sqare brackets. Said that, please use parameterized query for your commands.

Conn.Open()
'establishes communication with the database
sql = "SELECT [Password] FROM tblLogin WHERE Username = ?"
'declares SQL statement
da = New OleDb.OleDbDataAdapter(sql, Conn)
da.SelectCommand("@p1", LoginForm.username)
.....

Finally the INSERT clause doesn't require a WHERE statement (it is meaningless set a condition to select some rows for an INSERT)

Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("INSERT INTO tblLogin [Password] " & _
                                "VALUES (?) "

But looking at your code, then probably you need an UPDATE instead of an INSERT

Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("UPDATE tblLogin " & _ 
                                "SET [Password] = ? WHERE Username ?"
cmd.Parameters.Add("@Password", OleDb.OleDbType.VarChar).Value = TextBox3.Text
cmd.Parameters.Add("@Username", OleDb.OleDbType.VarChar).Value = TEXTBOXFORUSERNAME.Text

--- AND THE MOST IMPORTANT LINE IS ---

cmd.ExecuteNonQuery()

Without this no attempt is made to update the database table