update problem

  • Thread starter Thread starter JFB
  • Start date Start date
J

JFB

Hi,
I have two tables with one relation in a dataAdapter, I pass all the records
to a couple datagrids.
After add, update and delete records.... I'm trying to update the
dataAdapter with the all changes.
I read in my book that I need to do it in particular order:
1.Submit New rows for both tables.
2.Submit Modified rows for both tables.
3.Submit Deletes rows for both tables.
I have a Update bottom in my vb.net form with the above code.
Can someome help me to fix the error after I try to do an update "Update
requieres a
Valid Update command when passed data row ...."
Also if I delete something it said... "update requieres a Valid Delete
Command...."
Do I need to create an add, update and delete command?
Do you have an example?
Tks in advance
JFB
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click

Dim tblPersons As DataTable = hSet.Tables("Persons")

Dim tblNewPersons As New DataTable

Dim tblHouses As DataTable = hSet.Tables("Houses")

Dim tblNewHouses As New DataTable

Dim daPersons As New SqlDataAdapter

Dim intOrdersModified As Integer

Try

'Update changes for New Records

If hSet.HasChanges(DataRowState.Added) Then

tblNewPersons = tblPersons.GetChanges(DataRowState.Added)

intOrdersModified = hAdapter.Update(tblNewPersons)

tblNewHouses = tblHouses.GetChanges(DataRowState.Added)

hAdapter.Update(tblNewHouses)

End If

'Update changes for modified Records

If hSet.HasChanges(DataRowState.Modified) Then

tblNewPersons = tblPersons.GetChanges(DataRowState.Modified)

intOrdersModified = hAdapter.Update(tblNewPersons)

tblNewHouses = tblHouses.GetChanges(DataRowState.Modified)

hAdapter.Update(tblNewHouses)

End If

'Update changes for Records deleted

If hSet.HasChanges(DataRowState.Deleted) Then

tblNewPersons = tblPersons.GetChanges(DataRowState.Deleted)

intOrdersModified = hAdapter.Update(tblNewPersons)

tblNewHouses = tblHouses.GetChanges(DataRowState.Deleted)

hAdapter.Update(tblNewHouses)

End If

Catch ex As Exception

MsgBox(ex.ToString)

End Try

End Sub
 
If you use a CommandBuilder and you have a Keyed table, it will generate the
commands for you. Otherwise, you'll need to either write them yourself or
use the DataAdapter Configuration wizard to do it for you. Check out Bill
Vaughn's article on Weaning Developers from the CommandBuilder on
www.betav.com -> Articles -> MSDN.
I can't tell from the code below but it's one of two things..either the
delete and update command objects don't have the commandtext set or it's
incorrect.

HTH,

Bill
 
Tks for you reply William,
I have a command builder to fill the data set with the code below at the
time when I load my form, at the same time I fill the two datagrids.
Do I need to rewrite the command or create a updata command?
Tks
JFB

Dim hSet As New DataSet

Dim conn As New SqlConnection(connectionString)

Dim cmdSQL As New SqlCommand("Select customerID as ID, lastName as [Last
Name],firstName as [First Name],Phone,Cellular,Email from persons where
status = 1 order by lastName", conn)

Dim hAdapter As New SqlDataAdapter(cmdSQL)

Sub populate()


Try

'Fill the DataSet with customer table

conn.Open()

hAdapter.Fill(hSet, "persons")

'fill dataSet with houses table

cmdSQL.CommandText = "Select customerID as ID, ContactName as [Contact
Name],Address1, Address2,City,State,zipcode as [Zip
Code],Phone,Fax,Cellular,Email,status as Active from houses where status =
1"

hAdapter.SelectCommand = cmdSQL

hAdapter.Fill(hSet, "houses")

'Set relation between persons and houses table

hSet.Relations.Add("personHouses", hSet.Tables("persons").Columns("ID"),
hSet.Tables("houses").Columns("ID"))

Catch ex As Exception

MsgBox(ex.ToString)

Return

Finally

conn.Close()

End Try

End Sub
 
Also I have primary keys in both tables
Tks

William Ryan said:
If you use a CommandBuilder and you have a Keyed table, it will generate the
commands for you. Otherwise, you'll need to either write them yourself or
use the DataAdapter Configuration wizard to do it for you. Check out Bill
Vaughn's article on Weaning Developers from the CommandBuilder on
www.betav.com -> Articles -> MSDN.
I can't tell from the code below but it's one of two things..either the
delete and update command objects don't have the commandtext set or it's
incorrect.

HTH,

Bill
Click to expand...
 
Back
Top