UpdateDatabase

[TommyF]

I ma using the following code to try and update my database

myDataAdaptor.UpdateCommand = New OleDbCommand("UPDATE tblMembers SET (dteJoinDate,txtMemberTitle,txtMemberFirstName,txtMemberMiddleName,txtMemberLastName,dteMemberDOB,txtAddress_HouseNumber,txtAddress_Street,txtAddress_TownVillage,txtAddress_County,txtAddress_PostalCode,txtContact_HomePhone,txtContact_MobilePhone,hypContact_Email,curCurrentMembership,memAdditionalComments)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ", myConnection)

myDataAdaptor.UpdateCommand.CommandType = CommandType.Text

myDataAdaptor.UpdateCommand.Parameters.Add("@dteJoinDate", OleDbType.Date, 8, "dteJoinDate")
myDataAdaptor.UpdateCommand.Parameters.Add("@txtMemberTitle", OleDbType.Char, 5, "txtMemberTitle")
myDataAdaptor.UpdateCommand.Parameters.Add("@txtMemberFirstName", OleDbType.Char, 25, "txtMemberFirstName")
myDataAdaptor.UpdateCommand.Parameters.Add("@txtMemberMiddleName", OleDbType.Char, 25, "txtMemberMiddleName")
myDataAdaptor.UpdateCommand.Parameters.Add("@txtMemberLastName", OleDbType.Char, 25, "txtMemberLastName")
myDataAdaptor.UpdateCommand.Parameters.Add("@dteMemberDOB", OleDbType.Date, 8, "dteMemberDOB")
myDataAdaptor.UpdateCommand.Parameters.Add("@txtAddress_HouseNumber", OleDbType.Char, 25, "txtAddress_HouseNumber")
myDataAdaptor.UpdateCommand.Parameters.Add("@txtAddress_Street", OleDbType.Char, 75, "txtAddress_Street")
myDataAdaptor.UpdateCommand.Parameters.Add("@txtAddress_TownVillage", OleDbType.Char, 50, "txtAddress_TownVillage")
myDataAdaptor.UpdateCommand.Parameters.Add("@txtAddress_County", OleDbType.Char, 50, "txtAddress_County")
myDataAdaptor.UpdateCommand.Parameters.Add("@txtAddress_PostalCode", OleDbType.Char, 7, "txtAddress_PostalCode")
myDataAdaptor.UpdateCommand.Parameters.Add("@txtContact_HomePhone", OleDbType.Char, 11, "txtContact_HomePhone")
myDataAdaptor.UpdateCommand.Parameters.Add("@txtContact_MobilePhone", OleDbType.Char, 11, "txtContact_MobilePhone")
myDataAdaptor.UpdateCommand.Parameters.Add("@hypContact_Email", OleDbType.WChar, 25, "hypContact_Email")
myDataAdaptor.UpdateCommand.Parameters.Add("@curCurrentMembership", OleDbType.Currency, 25, "curCurrentMembership")
myDataAdaptor.UpdateCommand.Parameters.Add("@memAdditionalComments", OleDbType.Char, 25, "memAdditionalComments")


Dim newRow As DataRow = myDataSet.Tables("tblMembers").Rows(0)

newrow("dteJoinDate") = Me.txtdteJoined.Text
newRow("txtMemberTitle") = Me.cmbTitle.Text
newRow("txtMemberFirstName") = Me.txtFirstName.Text
newRow("txtMemberMiddleName") = Me.txtMiddleName.Text
newRow("txtMemberLastName") = Me.txtLastName.Text
newRow("dteMemberDOB") = Me.txtDOB.Text
newRow("txtAddress_HouseNumber") = Me.txtAddressHouseNumber.Text
newRow("txtAddress_Street") = Me.txtAddressStreet.Text
newRow("txtAddress_TownVillage") = Me.txtAddressTownVillage.Text
newRow("txtAddress_County") = Me.txtAddressCounty.Text
newRow("txtAddress_PostalCode") = Me.txtAddressPostalCode.Text
newRow("txtContact_HomePhone") = Me.txtHomePhone.Text
newRow("txtContact_MobilePhone") = Me.txtMobile.Text
newRow("hypContact_Email") = Me.txtEmail.Text
newRow("curCurrentMembership") = Format(Me.txtCurMembership.Text, "Standard")
newRow("memAdditionalComments") = Me.txtAdditionalComments.Text

myDataAdaptor.Update(myDataSet, "tblMembers")
myDataSet.Tables("tblMembers").AcceptChanges()

It stops on the line myDataAdaptor.Update(myDataSet, "tblMembers")

I think the problem is that I am not telling it what row in the dataset to update.

I have a lngMemberID which ID of the record I need to update but I am not sure how to select the correct record.

DO I have to loop through the dataset?

 

[PankajBanga]

VALUES(@dteJoinDate,@txtMemberTitle,@txtMemberFirstName...)

http://home.iprimus.com.au/pankajbanga

 

[TommyF]

Thanks for the reply but it still stops in the same place

 

[TommyF]

Solved the problem I had to change my SQL command to

myDataAdaptor.UpdateCommand = New OleDbCommand("UPDATE tblMembers SET dteJoinDate = @dteJoinDate,txtMemberTitle = @txtMemberTitle,txtMemberFirstName =@txtMemberFirstName,txtMemberMiddleName = @txtMemberMiddleName,txtMemberLastName = @txtMemberLastName,dteMemberDOB = @dteMemberDOB,txtAddress_HouseNumber = @txtAddress_HouseNumber," & _
"txtAddress_Street = @txtAddress_Street,txtAddress_TownVillage = @txtAddress_TownVillage,txtAddress_County = @txtAddress_County,txtAddress_PostalCode = @txtAddress_PostalCode,txtContact_HomePhone = @txtContact_HomePhone,txtContact_MobilePhone = @txtContact_MobilePhone," & _
"hypContact_Email = @hypContact_Email,curCurrentMembership = @curCurrentMembership,memAdditionalComments = @memAdditionalComments WHERE lngmemberID = " & Me.lvwMembers.SelectedItems(0).Tag, myConnection)