SQL Server values are changed only while program is running

[dangerousd28]

Hi, im currently working with an SQL Server 2008 database and im trying to change values in the database usign visual basic.

The following code runs correctly and I have tested changing passwords and have been able to logoff and then back on again using the new password. The problem i am facing is that as soon as the application exits the database returns to its origional values so the next time i run the program the passwords are back to the old values.

If anyone has any ideas as to how to make the changes to the database permenant i would much appreciate it

    Public Function ChangeUserPassword(ByVal newPass As String) As Boolean
        newPassword = PasswordHasher.HashPassword(newPass)
        GenerateSelectSqlCommandFromDetails()
        If (ConfirmUser()) Then
            GenerateUpdateSqlCommandFromDetails()
            Try
                sqlCommand.Connection.Open()
                sqlCommand.ExecuteNonQuery()
                sqlCommand.Connection.Close()
                Return True
            Catch ex As Exception
                MessageBox.Show("There was a problem writing your new password to the database")
            Finally
                sqlCommand.Connection.Close()
            End Try
            Return False
        Else
            Return False
        End If


    End Function

[Begginnerdev]

What is your command text?

Do you have an update statement?

I am assuming that confirm user is returning true?
A function that checks to see if the user is in the database?

[poojavb]

the below thing I had done in my change password code...check if this helps u....
this code I had written in a button click event....

'Open connection

        If txtUName.Text = "" Or txtOldPwd.Text = "" Or txtNewPwd.Text = "" Or txtCNPwd.Text = "" Then
            MsgBox("All fields are mandatory")
        Else
            If txtNewPwd.Text <> txtCNPwd.Text Then
                MsgBox("Password and Confirm Password should be same")
                txtCNPwd.Text = ""
                txtNewPwd.Text = ""
                txtNewPwd.Focus()
            Else
                Try
                    Dim myCommand As SqlCommand
                    myCommand = New SqlCommand("Select Username, password from userdetails where Username='" + txtUName.Text + "' and password='" + txtOldPwd.Text + "'", Connection)
                    Dim reader As SqlDataReader = myCommand.ExecuteReader
                    Dim value As Boolean
                    value = reader.Read
                    reader.Close()
                    If value = True Then
                        Try
                            Dim myCommand2 As SqlCommand
                            myCommand2 = New SqlCommand("update userdetails set password='" + txtNewPwd.Text + "' where username='" + txtUName.Text + "'", Connection)
                            myCommand2.ExecuteNonQuery()
                            MsgBox("Password Changed Successfully")
                            Me.Close()
                        Catch ex As Exception
                            MsgBox("Error in select query: " + ex.Message)
                        End Try
                    Else
                        MsgBox("Old password is incorrect")
                        txtCNPwd.Text = ""
                        txtNewPwd.Text = ""
                        txtOldPwd.Text = ""
                        txtOldPwd.Focus()
                    End If
                Catch ex As Exception
                    MsgBox("Error in select query: " + ex.Message)
                End Try
            End If
        End If

       'close connection

[dangerousd28]

The code i have for creating the command text is

    Private Sub GenerateUpdateSqlCommandFromDetails()
        Dim commandText As String = ("UPDATE userAccount SET ")
        commandText += ("password=" + Chr(39) + newPassword + Chr(39) + " WHERE ")
        commandText += ("Username=" + Chr(39) + userName + Chr(39))
        Me.SetSqlCommandText(commandText)
    End Sub

That generates a commandtext such as
"UPDATE userAccount SET password='SomeHashedPassword' WHERE Username='SomeUsername'"

The commands do seem to be changing the passwords during run time as i can change a password from test1 to test2 and when logging off only test2 will let me log back on. The issue is that as soon as the application exits the database reverts back to test1

Edit: ConfirmUser checks that the username matches the users old password. It is returning true I have stepped through the code and it does successfully update the password to the new one. It just reverts the new password back to the old one as soon as the program is closed

[Begginnerdev]

Are you sure it is actually changing the value?

Write a small sub to go out and retrive the value as soon as it is changed.

Just tell the sub to get the username and pass where username = username

Then post that to a message box to verify it actually changed.

Call this right after the execnonquery before closing, just to verify it is actually executing.

Private Sub CheckIfChanged(username As String, YourConnection as SqlConnection)

    Dim sqlCom As New SqlCommand("SELECT username,password FROM userAccount WHERE username='" & username & "'", YourConnection)
    Dim da As New SqlDataAdapter(sqlCom)
    Dim ds As New DataSet
    Dim dt As New DataTable

    da.Fill(ds, "table1")
    dt = ds.Tables("table1")

    If dt.Rows.Count > 0 Then
        MsgBox("Username:" & dt.Rows(0).Item(0).ToString & " Password:" & dt.Rows(0).Item(0).ToString)
    End If

End Sub

[dangerousd28]

I added your code to check the passwords after changing them and the message box is displaying the new password.

[Begginnerdev]

Ok, next question. Do you have a query that executes when your application exits?

Also, is your username/password checked against active directory when you first log in?

If so, there is your problem.

[adam_k]

After you exit your app, is the value different in your table? (check directly)
If it is you are saving it on or before exit.
If it isn't and you find it different when trying to login (again check directly in your table) then you are changing it in startup.
Either way if you can drop your table (script it appropriately before you drop it of course) and debug your program. You'll get an error when you update your table. Of course if the value changes on exit, drop the table just before exit.

[dangerousd28]

To exit the program i just call Application.Exit() i only have 2 areas where querys are run.
The first is the logon screen where I run a select query to ensure that the username/password are correct.
The second is on a seperate form for changing passwords.
Im not quite sure what you mean about the Active DIrectory but if google is anything to go by then I dont think the passwords are checked against it.

[Begginnerdev]

Are you using SQL server's integrated security, or manually logging on?

[dangerousd28]

Im just using the integrated security. The database was setup using windows authentication rather than an sql username/password

[dangerousd28]

I think i have found something that may be the cause but im not 100% sure on how i would go about fixing it.

When the application is run VS appears to be creating a copy of the database in the /bin folder.

Having looked at this copy all of the changes have been made to it and are saved.

I cant find anyway to get these changes from the /bin copy of the database to the original.

[Begginnerdev]

Is the SQL Server install on the same PC or on the network?

[dangerousd28]

The server is installed on the same pc.

The problem was having the database as part of the Visual Basic project. Each time i ran the program it was copying the database from the project to the bin folder overwritting any changes that had been made. Im still not sure how you would send changes from the bin copy used during runtime to the one stored in the project so i removed the database from both the project and the /bin folder and now have it stored in its own folder.

This seems to be working for now. Because the database is no longer in the solution visual studio isnt copying it to the bin folder all the changes are working each time the program restarts.