Using dataadapter to update a database table.

[mike55]

Hi

 

I am attempting to user dataadapter.updatecommand to update a row in a database table.

 

I have created a dataset which contains all the rows in the database table except one. The dataset does contain data.

 

The code that I am using for the update is:

                           Dim tempDs As New DataSet
                           Dim cmd As New SqlCommand
                           cmd.Connection = cnConn
                           cmd.CommandType = CommandType.Text
                           cmd.CommandText = "SELECT * FROM Members"
                           Dim daDataAdapter2 As New SqlDataAdapter
                           daDataAdapter2.SelectCommand = cmd
                           daDataAdapter2.Fill(tempDs, "Members")

daDataAdapter2.UpdateCommand = New SqlCommand("UPDATE Members SET Custom_ID = @CID, Surname = @SName, Forename = @FName, FullName = @Full, DOB = @Dob, " & _
                                                                                                                                       "Title = @Title, MobileNumb = @MNumb, PhoneNumb = @PNumb, Addr1 = @Addr1, Addr2 = @Addr2, Town = @Town, " & _
                                                                                                                                       "County = @County, Role = @Role, Guardian1 = @G1, Guardian2 = @G2, Guardian1_Phone = @G1Numb, Guardian2_Phone = @G2Numb, " & _
                                                                                                                                       "Email = @Email, Active = @Active, AdditionalInformation = @Additional, CodePrefix = @Code, Country = @Country, PostCode = @PCode, " & _
                                                                                                                                       "InternationalNumber = @International, Custom1 = @Custom1, Custom2 = @Custom2, Custom3 = @Custom3, " & _
                                                                                                                                       "Custom4 = @Custom4, Custom5 = @Custom5, Custom6 = @Custom6, @Custom7 = @Custom7, " & _
                                                                                                                                       "Custom8 = @Custom8, Custom9 = @Custom9, Custom10 = @Custom10 " & _
                                                                                                                                       "WHERE Member_ID = @MID", cnConn)

daDataAdapter2.UpdateCommand.Parameters.Add(New SqlParameter("@MID", SqlDbType.BigInt))
daDataAdapter2.UpdateCommand.Parameters("@MID").Direction = ParameterDirection.Input
daDataAdapter2.UpdateCommand.Parameters("@MID").SourceColumn = dstMemberData.Tables(0).Rows(0).Item("Member_ID") 

' Repeated for each of the parameters mentioned above.

ProcessMemberData = daDataAdapter2.Update(dstMemberData, "0")

 

The code is executing without any errors, unfortunately no data is being updated. I would appreciate it if anyone could suggest where I am going wrong.

 

Mike55.