I have been trying to resolve this problem for months and am completely frustrated. Here's the situation.

I took the book example and updated it to Visual Basic 2008 and then using the major functions I adapted them to my own project. My database contains a query named usp_UpdateEmployee with 30 fields . When I attempt to update thedatabase, using the code suggested in the book, it runs without any errors but nothing happens. Here's the code:

Private Sub SaveChanges()
  'Initialize a new instance of thedata access base class
   Using objData As New WDABase
   If intAdd = 0 Then
     objData.SQL = "usp_UpdateEmployee"
   Else
     objData.SQL = "usp_AddEmployee"
   End If
   'Initialize the command object
   objData.InitializeCommand()
   'Add the parameters to the Parameters collection
   If intAdd = 0 Then
    objData.AddParameter "@staffIndex",OleDbType.Integer, 4, txtStaffIndex.Text)
    End If
            objData.AddParameter("@EmployeeID", OleDbType.VarChar, 3, txtEmployeeID.Text)
            objData.AddParameter("@UserPwd", OleDbType.VarChar, 25, txtUserPwd.Text)
            objData.AddParameter("@LName", OleDbType.VarChar, 30, txtLName.Text)
            objData.AddParameter("@FName", OleDbType.VarChar, 30, txtFName.Text)
            objData.AddParameter("@DisplayName", OleDbType.VarChar, 65, txtDisplayName.Text)
            objData.AddParameter("@Address", OleDbType.VarChar, 50, txtAddress.Text)
            objData.AddParameter("@City", OleDbType.VarChar, 50, txtCity.Text)
            objData.AddParameter("@State", OleDbType.VarChar, 2, txtState.Text)
            objData.AddParameter("@Zip", OleDbType.VarChar, 10, txtZip.Text)
            objData.AddParameter("@Gender", OleDbType.VarChar, 1, txtGender.Text)
            objData.AddParameter("@Phone", OleDbType.VarChar, 14, txtPhone.Text)
            objData.AddParameter("@Cell", OleDbType.VarChar, 14, txtCell.Text)
            objData.AddParameter("@BirthDate", OleDbType.Date, 8, dtpBirthDate.Text)
            objData.AddParameter("@CanChange", OleDbType.Integer, 2, txtCanChange.Text)
            objData.AddParameter("@TSRequired", OleDbType.Integer, 2, txtTSRequired.Text)
            objData.AddParameter("@PBT", OleDbType.Integer, 2, txtPBT.Text)
            objData.AddParameter("@PBTBase", OleDbType.Double, 8, txtPBTBase.Text)
            objData.AddParameter("@StartDate", OleDbType.Date, 8, dtpStartDate.Text)
            objData.AddParameter("@NormalHours", OleDbType.Single, 4, txtWeekTotal.Text)
            objData.AddParameter("@HrsMonday", OleDbType.Single, 4, txtMonday.Text)
            objData.AddParameter("@HrsTuesday", OleDbType.Single, 4, txtTuesday.Text)
            objData.AddParameter("@HrsWednesday", OleDbType.Single, 4, txtWednesday.Text)
            objData.AddParameter("@HrsThursday", OleDbType.Single, 4, txtThursday.Text)
            objData.AddParameter("@HrsFriday", OleDbType.Single, 4, txtFriday.Text)
            objData.AddParameter("@HrsSaturday", OleDbType.Single, 4, txtSaturday.Text)
            objData.AddParameter("@HrsSunday", OleDbType.Single, 4, txtSunday.Text)
            objData.AddParameter("@Status", OleDbType.Integer, 2, txtStatus.Text)
            objData.AddParameter("@StatusDate", OleDbType.Date, 8, dtpStatusChangeDate.Text)
            objData.AddParameter("@Email", OleDbType.VarChar, 50, txtEmail.Text)

            objData.OpenConnection()
            intRowsAffected = objData.Command.ExecuteNonQuery
            MsgBox(intRowsAffected)
            objData.CloseConnection()

        End Using
    End Sub

The message box always shows with a 0 records affected. I am at a complete loss, please help.

Have stepped through this section of code in debug mode? What is the value of intAdd? Have you validated that your stored procedure is correct? This is a procedure and does not return a value based on any add or update of data. How does the message box gets its value to determine whether or not data was added or updated?

My suggestion would be to set a break point in this procedure and then capture all the parameters passed and then execute the stored procedure within a query window in SQL Server passing it the parameters being passed to this procedure.

Thearon

Thearon

Thank you for the response. In reference to your suggestions, I havestepped through every line of code, checked the value of each parameter and they all seemed OK.

to indicate that this is a new employee. the StaffIndex field in the database is an auto number in Access and rather then having a separate sub procedure for adding an employee as opposed to changing an existing employee I use it to select the SQL statement and to include the parameter needed for the "WHERE" in the update SQL statement but not needed in the "INSERT INTO" statement.

The message box is only a temporary indication to me that the query has resulted in one and only one record.

Milt

Thearon

In a continuing effort to get to the bottom of the problem I set up a query with only 3 fields. My Query is:

UPDATE tStaff SET Fname = [@Fname], Lname = [@Lname], DisplayName = [@DisplayName]
WHERE DisplayName = [@DisplayName];

My code now is:
   Private Sub SaveChanges()
        'Initialize a new instance of the data access base class
        Using objData As New WDABase
            objData.SQL = "usp_Update_Part1"
            'Initialize the command object
            objData.InitializeCommand()
            'Add the parameters to the Parameters collection
            'objData.AddParameter("@UserPwd", OleDbType.VarChar, 25, txtUserPwd.Text)
            objData.AddParameter("@LName", OleDbType.VarChar, 30, txtLName.Text)
            objData.AddParameter("@FName", OleDbType.VarChar, 30, txtFName.Text)
            objData.AddParameter("@DisplayName", OleDbType.VarChar, 65, txtDisplayName.Text)
            objData.OpenConnection()
            intRowsAffected = objData.Command.ExecuteNonQuery
            objData.CloseConnection()
        End Using
    End Sub

The result is still the saim. When I run it completes with no error mesage and the database is not updated.

Milt

Milt,

Please make sure you connection string is pointing to the correct database and that you have permissions to insert data. Again I would suggest that you run the stored procedure in a query window in the database to ensure that your stored procedure is functioning as expected.

Thearon

Thearon,

Thank you for trying to help. I triple checked to make sure that the database connection was correct and also double checked the Access query. In both cases they were correct. I'm at a complete loss.

Milt

Milt,

Can you post the query you are executing?

Thanks,
Thearon

Thearon

When I started having all this problem I created a small test procedure using only 3 fields. I am using the wuey as text and it is ss follows:

UPDATE tStaff SET Fname = [@Fname], Lname = [@Lname], DisplayName = [@DisplayName]
WHERE DisplayName = [@DisplayName];

Thanks for trying to help.

Milt

Milt,

You are using DisplayName as a parameter to your query. You are also updating that field in your query. Remove the update of DisplayName from your query and just leave it in the Where clause and see what happens.

Thearon

Thearon,

Still no luck.

Here's the query

UPDATE tStaff SET Fname = [@Fname], Lname = [@Lname], DisplayName = [@DisplayName]
WHERE staffIndex=@StaffIndex;

Here's the code:
'Initialize a new instance of the data access base class
Using objData As New WDABase
    objData.SQL = "usp_Update_Part1"

   'Initialize the command object
    objData.InitializeCommand()
   'Add the parameters to the Parameters collection
    objData.AddParameter("@staffIndex", OleDbType.Integer, 4, txtStaffIndex.Text)
    objData.AddParameter("@LName", OleDbType.VarChar, 30, txtLName.Text)
    objData.AddParameter("@FName", OleDbType.VarChar, 30, txtFName.Text)
    objData.AddParameter("@DisplayName", OleDbType.VarChar, 65,
        txtDisplayName.Text)

    objData.Command.ExecuteNonQuery()
    objData.CloseConnection()
End Using

Thearon, I sincerely appreciate the help your giving me, it speaks well of you and Wrox. I am, however, getting frustrated with Microsoft's changes to Visual Basic. In VB 6 I would have used a recordset and got this done with a lot less hassle and a lot less code.

Milt