ADO.NET dataAdapter,update Error

[Crookshanks]

Hello there,

I have problems with updating the data in the data source (Accessdatabase). I am writing an eventhandler that should reset the password of a user.

daUsers = data-Adapter
dsUsers = dataset
drUsers = the current row

Private Sub cmdReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdReset.Click
response = MsgBox("Weet U zeker dan u het wachtwoord wilt resetten?", MsgBoxStyle.YesNo)
If response = MsgBoxResult.No Then Exit Sub

drUsers.Item("Password") = InitialPassword 'change record
Try
Me.daUsers.Update(Me.dsUsers.Users)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub

When I try to save the change to the actual database I've got the following exception error. Could someone please explain why I am receiving this?

Errormessage:
SystemInvalidOperationsException: Update requieres a valid update command when passed dataRow collection with modified rows.

What am I doing wrong?

Kind regards,

 

[Bhavna13]

Do you have a sqlclient.sqlcommand object assigned to the daUsers.UpdateCommand property? if u havent created the DA using the wizard then you need to create the command manually.

hope this helps.

 

[Crookshanks]

Yes Thanks. I found out that the error had to do with the command. I am now trying to figure out how to manually assign such a command. Perhaps you could help me with an example based on my program code? Well any way. Thanks.

 

[Bhavna13]

I recently did something similar except I used the wizard to configure my dataAdapter. This is the code the it generated for the update command for my table..

Mine was the employees table and had 4 fields : EId(primary key), Name, TelNumber, password. I know its long but it works...


Friend WithEvents SqlUpdateCommand5 As System.Data.SqlClient.SqlCommand
Me.SqlUpdateCommand5 = New System.Data.SqlClient.SqlCommand

Me.daEmp.UpdateCommand = Me.SqlUpdateCommand5

Me.SqlUpdateCommand5.CommandText = "UPDATE Employee SET Eid = @Eid, Name = @Name, TelNumber = @TelNumber, Password = " & _
"@Password WHERE (Eid = @Original_Eid) AND (Name = @Original_Name OR @Original_Na" & _
"me IS NULL AND Name IS NULL) AND (Password = @Original_Password OR @Original_Pas" & _
"sword IS NULL AND Password IS NULL) AND (TelNumber = @Original_TelNumber OR @Ori" & _
"ginal_TelNumber IS NULL AND TelNumber IS NULL); SELECT Eid, Name, TelNumber, Pas" & _
"sword FROM Employee WHERE (Eid = @Eid)"
Me.SqlUpdateCommand5.Connection = Me.SqlConnection2
Me.SqlUpdateCommand5.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Eid", System.Data.SqlDbType.NVarChar, 3, "Eid"))
Me.SqlUpdateCommand5.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Name", System.Data.SqlDbType.NVarChar, 50, "Name"))
Me.SqlUpdateCommand5.Parameters.Add(New System.Data.SqlClient.SqlParameter("@TelNumber", System.Data.SqlDbType.NVarChar, 50, "TelNumber"))
Me.SqlUpdateCommand5.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Password", System.Data.SqlDbType.VarChar, 10, "Password"))
Me.SqlUpdateCommand5.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Eid", System.Data.SqlDbType.NVarChar, 3, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Eid", System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand5.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Name", System.Data.SqlDbType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Name", System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand5.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Password", System.Data.SqlDbType.VarChar, 10, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Password", System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand5.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_TelNumber", System.Data.SqlDbType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "TelNumber", System.Data.DataRowVersion.Original, Nothing))

 

[edderic]

Like this :

Dim commbld As OleDbCommandBuilder = New OleDbCommandBuilder(adapterRelaties)
adapterRelaties.Update(datasetRelaties, "Relaties")
datasetRelaties.AcceptChanges()



Eric De Decker
Visit The Belgium "Visual Basic Group" at

http://www.vbgroup.org