Access Update Stored Procedure Problem

[spaulding]

I'm building a membership registration program for my son's school and they are limited to Access as their database. I've got a datagrid to display member information, but it's got some problem. When I try edit, the datagrid expands to show the edit textboxes, but when I change the data and push update, the data doesn't change in the database. I get no error messages and the text of a label changes as I've programmed in the update subroutine so the sub is firing, but the data remains the same. I've tried executing the stored procedure (update query) directly in Access and it works fine. So I think I'm leaving out a step somewhere in the following code. I'd really appreciate another pair of eyes looking at it. Thanks

The Update Sub Code

Sub UpdateItem(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
Dim txtFirstName As TextBox = e.Item.Cells(2).Controls(0)
Dim txtMI As TextBox = e.Item.Cells(3).Controls(0)
Dim txtLastName As TextBox = e.Item.Cells(4).Controls(0)
Dim txtEMail As TextBox = e.Item.Cells(5).Controls(0)
Dim txtRoomID As TextBox = e.Item.Cells(6).Controls(0)
Dim txtDormPhone As TextBox = e.Item.Cells(7).Controls(0)
Dim txtCellPhone As TextBox = e.Item.Cells(8).Controls(0)
Dim txtClassYear As TextBox = e.Item.Cells(9).Controls(0)
Dim txtOutfit As TextBox = e.Item.Cells(10).Controls(0)
Dim txtUIN As TextBox = e.Item.Cells(11).Controls(0)
Dim txtStaff As TextBox = e.Item.Cells(12).Controls(0)
Dim txtConclave As TextBox = e.Item.Cells(13).Controls(0)
Dim txtPaid As TextBox = e.Item.Cells(14).Controls(0)
Dim txtPaymentMethod As TextBox = e.Item.Cells(15).Controls(0)
Dim txtPaymentNumber As TextBox = e.Item.Cells(16).Controls(0)
Dim iMemberID As Int16 = dgMemberList.DataKeys(e.Item.ItemIndex)
Dim strSql As String
Dim cmdDataEnter As OleDbCommand
Dim prmFirstName As New OleDbParameter
Dim prmMI As New OleDbParameter
Dim prmLastName As New OleDbParameter
Dim prmEMail As New OleDbParameter
Dim prmRoomId As New OleDbParameter
Dim prmDormPhone As New OleDbParameter
Dim prmCellPhone As New OleDbParameter
Dim prmClassYear As New OleDbParameter
Dim prmOutfit As New OleDbParameter
Dim prmUIN As New OleDbParameter
Dim prmStaff As New OleDbParameter
Dim prmConclave As New OleDbParameter
Dim prmPaid As New OleDbParameter
Dim prmPaymentMethod As New OleDbParameter
Dim prmPaymentNumber As New OleDbParameter
Dim prmMemberID As New OleDbParameter

lblMessage.Text = "Update This:" & iMemberID

strSql = "Execute UpdateMember"
cmdDataEnter = New OleDbCommand(strSql, OleDbConnection1)
Try
With prmFirstName
.ParameterName = "FirstName"
.OleDbType = OleDbType.VarChar
.Size = 50
.Value = txtFirstName.Text
End With
cmdDataEnter.Parameters.Add(prmFirstName)

With prmMI
.ParameterName = "MI"
.OleDbType = OleDbType.VarChar
.Size = 50
.Value = txtMI.Text
End With
cmdDataEnter.Parameters.Add(prmMI)

With prmLastName
.ParameterName = "LastName"
.OleDbType = OleDbType.VarChar
.Size = 50
.Value = txtLastName.Text
End With
cmdDataEnter.Parameters.Add(prmLastName)

With prmEMail
.ParameterName = "Email"
.OleDbType = OleDbType.VarChar
.Size = 50
.Value = txtEMail.Text
End With
cmdDataEnter.Parameters.Add(prmEMail)

With prmRoomId
.ParameterName = "RoomID"
.OleDbType = OleDbType.VarChar
.Size = 50
.Value = txtRoomID.Text
End With
cmdDataEnter.Parameters.Add(prmRoomId)

With prmDormPhone
.ParameterName = "DormPhone"
.OleDbType = OleDbType.VarChar
.Size = 50
.Value = txtDormPhone.Text
End With
cmdDataEnter.Parameters.Add(prmDormPhone)

With prmCellPhone
.ParameterName = "CellPhone"
.OleDbType = OleDbType.VarChar
.Size = 50
.Value = txtCellPhone.Text
End With
cmdDataEnter.Parameters.Add(prmCellPhone)

With prmClassYear
.ParameterName = "ClassYear"
.OleDbType = OleDbType.VarChar
.Size = 50
.Value = txtClassYear.Text
End With
cmdDataEnter.Parameters.Add(prmClassYear)

With prmOutfit
.ParameterName = "Outfit"
.OleDbType = OleDbType.VarChar
.Size = 50
.Value = txtOutfit.Text
End With
cmdDataEnter.Parameters.Add(prmOutfit)

With prmUIN
.ParameterName = "UIN"
.OleDbType = OleDbType.VarChar
.Size = 50
.Value = txtUIN.Text
End With
cmdDataEnter.Parameters.Add(prmUIN)

With prmStaff
.ParameterName = "Staff"
.OleDbType = OleDbType.VarChar
.Size = 50
.Value = txtStaff.Text
End With
cmdDataEnter.Parameters.Add(prmStaff)

With prmConclave
.ParameterName = "Conclave"
.OleDbType = OleDbType.VarChar
.Size = 50
.Value = txtConclave.Text
End With
cmdDataEnter.Parameters.Add(prmConclave)

With prmPaid
.ParameterName = "Paid"
.OleDbType = OleDbType.VarChar
.Size = 50
.Value = txtPaid.Text
End With
cmdDataEnter.Parameters.Add(prmPaid)

With prmPaymentMethod
.ParameterName = "PaymentMethod"
.OleDbType = OleDbType.VarChar
.Size = 50
.Value = txtPaymentMethod.Text
End With
cmdDataEnter.Parameters.Add(prmPaymentMethod)

With prmPaymentNumber
.ParameterName = "PaymentNumber"
.OleDbType = OleDbType.VarChar
.Size = 50
.Value = txtPaymentNumber.Text
End With
cmdDataEnter.Parameters.Add(prmPaymentNumber)

With prmMemberID
.ParameterName = "MemberID"
.OleDbType = OleDbType.BigInt
.Size = 50
.Value = iMemberID
End With
cmdDataEnter.Parameters.Add(prmMemberID)

OleDbConnection1.Open()
cmdDataEnter.ExecuteNonQuery()
OleDbConnection1.Close()

dgMemberList.EditItemIndex = -1
BindData("LastName")

Catch ex As Exception
lblMessage.Text = "Error Occurred.<P>" & ex.ToString

End Try

End Sub

The query Sql Code

PARAMETERS MemberID Long, FirstName Text ( 255 ), MI Text ( 255 ), LastName Text ( 255 ), EMail Text ( 255 ), RoomID Text ( 255 ), DormPhone Text ( 255 ), CellPhone Text ( 255 ), ClassYear Text ( 255 ), Outfit Text ( 255 ), UIN Text ( 255 ), Staff Text ( 255 ), Conclave Text ( 255 ), Paid Text ( 255 ), PaymentMethod Text ( 255 ), PaymentNumber Text ( 255 );
UPDATE MemberInfo SET MemberInfo.FirstName = FirstName, MemberInfo.MI = MI, MemberInfo.LastName = LastName, MemberInfo.EMail = Email, MemberInfo.RoomID = RoomID, MemberInfo.DormPhone = DormPhone, MemberInfo.CellPhone = CellPhone, MemberInfo.ClassYear = ClassYear, MemberInfutfit = Outfit, MemberInfo.UIN = UIN, MemberInfo.Staff = Staff, MemberInfo.Conclave = Conclave, MemberInfo.Paid = Paid, MemberInfo.PaymentMethod = PaymentMethod, MemberInfo.PaymentNumber = PaymentNumber
WHERE (((MemberInfo.MemberID)=[MemberID]));

 

[Mike555]

Hi. The first thing that I notice is that your subroutine does not have a 'Handles' statement which specifies when it should execute.

Give this a try...
Sub UpdateItem(ByVal sender As Object, ByVal e As DataGridCommandEventArgs) Handles dgMemberList.UpdateCommand

 

[spaulding]

Mike555 Thanks, I appreciate the help. Should have been doing that for consistency, but that wasn't the problem.

The problem was in the order I built the parameters. I built the Primary Key (memberID) parameter last in the subroutine, but listed it first in the Parameter list of the stored procedure. The database must update them in order instead of by name, because as soon as I moved that parameter section to the top of the list, it works as expected. Hate when I do that!