why i can't update to access 2003????

[kank]

I use below code to update to access 2003 from resetting password of login but when I use sql command to select pw, it still same pw, why????

   Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
        Dim sqlconn As New OleDbConnection
        Dim cmd As OleDbCommand = Nothing
        Dim transaction As OleDbTransaction
        Dim m As Match = regex.Match(Me.txtReset.Text)
        If Me.txtReset.Text = "" Then
            MsgBox("Password cannot be empty", MsgBoxStyle.Information)
            Me.txtReset.Focus()
            Exit Sub
        End If

        If Not m.Length = txtReset.Text.Length Then
            MsgBox("Password contains invalid characters", MsgBoxStyle.Information)
            Me.txtReset.Focus()
            Exit Sub
        End If

        'sqlconn.ConnectionString = connString
        sqlconn = New OleDbConnection(connString)
        sqlconn.Open()

        


        Try
            Dim sqltxt As String
            sqltxt = "UPDATE tblLogin SET pw = @inPassword, active = @act WHERE userId = @inUId"
            'Dim trans as new OleDBTransaction = sqlconn.BeginTransaction
            transaction = sqlconn.BeginTransaction()
            

            Dim crPW As String
            crPW = encryptor.Encrypt(txtReset.Text)

            cmd = New OleDbCommand(sqltxt, sqlconn)
            cmd.Connection = sqlconn
            cmd.Transaction = transaction

            cmd.Parameters.AddWithValue("@inUId", uid)
            cmd.Parameters.AddWithValue("@inPassword", encryptor.Encrypt(txtReset.Text))
            cmd.Parameters.AddWithValue("@act", True)

            cmd.CommandText = sqltxt
            cmd.ExecuteNonQuery()
            transaction.Commit()

            
            Dim cmd2 As OleDbCommand
            cmd2 = sqlconn.CreateCommand

            cmd2.CommandText = "select pw, active from tblLogin where userid ='admin'"
            'cmd.CommandText = sqltxt
            'cmd.CommandType = CommandType.Text
            rdr = cmd2.ExecuteReader
            rdr.Read()
            MessageBox.Show(rdr.GetString(0))
            MessageBox.Show(rdr.GetBoolean(1))


            MessageBox.Show("PW was reset successful!")
        Catch ex As SqlException
            'transaction.Rollback()
            sqlconn.Close()
            MessageBox.Show("Transaction unsuccesfull, it has been rolled back.")
            Exit Sub
        End Try
        transaction.Dispose()
        sqlconn.Close()
        fLogin.Show()
        Me.Hide()

Please help :-(

 

[JuggaloBrotha]

Here's what I see:
Access doesn't use @Params, it uses question marks.
You're resetting the PW for a specific user and then you're pulling only the admin users from the DB

Here's my take on roughly what you're trying to do:

    Private Sub ResetPWButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ResetPWButton.Click
        Select Case True
            Case CurrPasswordTextBox.Text.Trim = m_CurrPassword
                'Checks that the user knows existing PW
                MessageBox.Show("Incorrect current password", "Incorrect Current Password", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                CurrPasswordTextBox.Focus()
            Case NewPasswordTextBox.Text.Trim.Length = 0
                'Checks that a new PW is typed in
                MessageBox.Show("Must supply a new password", "New Password blank", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                NewPasswordTextBox.Focus()
            Case NewPasswordTextBox.Text.Trim = CurrPasswordTextBox.Text.Trim
                'Makes sure new PW isn't same as current PW
                MessageBox.Show("Must supply a different new password", "Same Password", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                NewPasswordTextBox.Focus()
            Case Else
                'All is good, change it in the DB
                Dim sqlconn As New OleDbConnection(m_ConnString)
                Dim UpdateCmd As New OleDbCommand("UPDATE tblLogin SET pw = ? WHERE userId = ?", sqlconn)
                Dim SelectCmd As New OleDbCommand("SELECT userId, pw, active FROM tblLogin WHERE userid = ?", sqlconn)
                Dim SqlReader As OleDbDataReader = Nothing
                Try
                    'Open the connection
                    sqlconn.Open()

                    'Establish the parameters
                    UpdateCmd.Parameters.Add("pw", OleDbType.VarChar)
                    UpdateCmd.Parameters.Add("userId", OleDbType.VarChar)
                    SelectCmd.Parameters.Add("userId", OleDbType.VarChar)

                    'Set paramter values
                    UpdateCmd.Parameters("pw").Value = encryptor.Encrypt(NewPasswordTextBox.Text.Trim)
                    UpdateCmd.Parameters("userId").Value = m_CurrUser
                    SelectCmd.Parameters("userId").Value = m_CurrUser

                    'Update the DB
                    UpdateCmd.ExecuteNonQuery()

                    'Verify the change
                    SqlReader = SelectCmd.ExecuteReader
                    If SqlReader.HasRows Then
                        While SqlReader.Read
                            MessageBox.Show(String.Format("User: {1}{0}Password: {2}{0}Active: {3}", Environment.NewLine, SqlReader("userId").ToString, SqlReader("pw").ToString, SqlReader("active").ToString))
                        End While
                    End If
                    SqlReader.Close()

                    'Close the connection
                    sqlconn.Close()
                Catch ex As Exception
                    MessageBox.Show(ex.ToString)
                Finally
                    'Clean up the reader
                    If SqlReader IsNot Nothing AndAlso Not SqlReader.IsClosed Then SqlReader.Close()
                    SqlReader = Nothing

                    'Make sure connection is closed
                    If sqlconn.State <> ConnectionState.Closed Then sqlconn.Close()

                    'Clean up objects
                    UpdateCmd.Dispose()
                    SelectCmd.Dispose()
                    sqlconn.Dispose()
                End Try
        End Select
    End Sub

 

[jmcilhinney]

Access doesn't use @Params, it uses question marks.

That's not strictly true. There are certain situations where you MUST use ? for parameters when working with Access. The Query Builder from the DataSet designer is one such place. In other situations you can use either approach. Writing your own inline SQL for an OleDbCommand is one such situation, which is what's happening here.

Personally, I prefer to use the @Name syntax because it makes for clearer code. The thing is, that syntax doesn't work with Access in the same way as it does with other databases, e.g. SQL Server. With a SqlCommand, anywhere @Name appears in the SQL code, the Value of the parameter named @Name will be substituted in. With an OleDbCommand, the parameter names are actually meaningless. They help the developer to keep things straight in their head but there is actually no specific connection between @Name in the SQL code and a parameter named @Name.

With an OleDbCommand, all that matters is the order that the parameters are added to the command and that is the issue here. Here's the original code:

sqltxt = "UPDATE tblLogin SET pw = [COLOR="#FF0000"]@inPassword[/COLOR], active = [COLOR="#00FF00"]@act[/COLOR] WHERE userId = [COLOR="#0000FF"]@inUId[/COLOR]"

            cmd.Parameters.AddWithValue("[COLOR="#0000FF"]@inUId[/COLOR]", uid)
            cmd.Parameters.AddWithValue("[COLOR="#FF0000"]@inPassword[/COLOR]", encryptor.Encrypt(txtReset.Text))
            cmd.Parameters.AddWithValue("[COLOR="#00FF00"]@act[/COLOR]", True)

The OP is presumably assuming that the names will ensure that the correct value will go to the correct place but it won't. The third parameter added to the command has the value True, so that will be substituted into the SQL code where the third @ place-holder is found in the SQL code, i.e. in the WHERE clause. You have no record where the userId id equal to True so the command actually affects zero records. If you test the value returned by ExecuteNonQuery that will be confirmed.

hat you need to do is change the order that you add the parameters to the command so that they are in the same order as they appear in the SQL code. That is the case whether you use @Name or ?. You don't have to do that with SqlClient but, really, why would you not anyway? Common sense should tell you that, if you have the choice between adding them in the same order as they appear in the SQL code or in a different order, adding them in the same order is the obvious choice.