Changing password recorded from Employees database

[Saiph0902]

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Data.OleDb.OleDbCommand
Public Class frmChangePassword
    Public cn As New OleDbConnection
    Public cmd As New OleDbCommand
    Public da As New OleDbDataAdapter
    Public str As String
    Public dlgRes As DialogResult
    Private Sub btnOkay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOkay.Click
        Try
            cmd.Connection = cn
            cmd.CommandText = "UPDATE Employees SET Password ='" & txtNewPassword.Text & "'" & _
            "SecretQuestion1 ='" & cmbNewSecretQ.Text & ", SecretAnswer1 ='" & txtNewSecretA.Text & "'" & _
            "WHERE Username='" & frmForgotPassword.txtUsername.Text & "'"
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show("Unable to change password due to " & ex.Message, "Changing password")
        End Try
    End Sub
    Private Sub frmChangePassword_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= C:\Documents and Settings\James\My Documents\MSN Pharmacy\msn_pharmacy.mdb"
        cn.Open()
    End Sub
End Class

I get a Syntax Error on UPDATE Statement

 

[jmcilhinney]

First up, don't use string concatenation to build SQL code. Follow the Blog link in my signature to learn why and how to use Parameters In ADO.NET.

Once you've fixed that, which will get rid of one of the syntax errors, you will also need to wrap the 'Password' column name in brackets because it is a keyword in Access.

 

[Saiph0902]

But I use OleDb actually.

 

[jmcilhinney]

That has no bearing on what I said in my previous post. If you had bothered to read the information I have already provided, you would have found this about 2/3 of the way down that blog post:

Now, let’s look at how using parameters differs amongst ADO.NET providers and data sources. First up, let’s look using OleDb and an Access database. The code should look almost exactly the same, except using OleDb classes instead of SqlClient classes.

 

[Saiph0902]

Imports SystemImports System.Data
Imports System.Data.OleDb
Imports System.Data.OleDb.OleDbCommand
Public Class frmChangePassword
    Public cn As New OleDbConnection
    Public cmd As New OleDbCommand
    Public da As New OleDbDataAdapter
    Public str As String
    Public dlgRes As DialogResult
    Private Sub btnOkay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOkay.Click
        Try
            Dim str As String = "UPDATE Employees SET [Password] =@[Password], SecretQuestion1 = @SecretQuestion1, SecretAnswer1 = @SecretAnswer1"
            Dim cmd As New OleDbCommand(str)
            With cmd.Parameters
                .AddWithValue("@[Password]", Me.txtNewPassword.Text)
                .AddWithValue("@SecretQuestion1", Me.cmbNewSecretQ.Text)
                .AddWithValue("@SecretAnswer1", Me.txtNewSecretA.Text)
            End With
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show("Unable to change password due to " & ex.Message, "Changing password")
        End Try
    End Sub
    Private Sub frmChangePassword_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= C:\Documents and Settings\James\My Documents\MSN Pharmacy\msn_pharmacy.mdb"
        cn.Open()
    End Sub
End Class

Altered as told, but now I get an error "Connection property not initialized"

 

[jmcilhinney]

That would be because you haven't initialised the Connection property. You are creating the OleDbCommand object and the OleDbConnection object, but you never associate the two.

 

[Saiph0902]

So how do I initialize it? (In code as much as possible, don't give me riddle-esque statements).

 

[jmcilhinney]

Read the documentation for the OleDbCommand class.

 

[momoaal]

if i want to learn ... at least i have to ... read .