How to update multiple records with 2 tables involves?

[jayz24]

Hi everyone! I'm new in this forums and new in the line of programming..A Beginner.. I have a problem, I don't know how to populate a multiple updates. Anyone who have codes for this?

Here's the scenario:

have 2 tables, one is table named as “STARTS” with field name avail,uph,yearly_starts.(52*7*24*uph*avail). Other table is named as “THROUGHPUT” with field name process_name,step_yield(%),yearly_throughput.

Uph = 1420
Avail = .8
yearly_starts = (52*7*24*uph*avail)

The scenario of getting the yearly throughput is here:

process_name----step_yield(%)----yearly_throughput
0-----------------------100----------------9924096-->which is the yearly_starts * step_yield
1-----------------------99.8---------------9904248-->which is the yearly_throughput of process_name 0 * step_yield
2-----------------------94-----------------9309993-->which is the yearly_throughput of process_name 1 * step_yield
3-----------------------97-----------------9030693-->which is the yearly_throughput of process_name 2 * step_yield
.
.
23 and so on..........................................


I have a update command for the "STARTS" table, and I would like to add the update code for the "THROUGHPUT" table inside this code. So everytime that the "STARTS" table updates, it also update the "THROUGHPUT" table.

Protected Sub Update_Click(ByVal sender As Object, ByVal e As DataListCommandEventArgs) Handles DataList1.UpdateCommand


Dim calc As New mcCalc6()

Dim tavail As TextBox = CType(e.Item.FindControl("txtavail"), TextBox)
Dim tuph As TextBox = CType(e.Item.FindControl("txtuph"), TextBox)
Dim tystarts As TextBox = CType(e.Item.FindControl("txtystarts"), TextBox)

If tystarts.Text <> "" Then

tystarts.Text = calc.evaluate(52 * 7 * 24 * tavail.Text * tuph.Text)

End If


Dim tbox As TextBox
Dim avail As String
Dim uph As String
Dim ystarts As String
Dim info As String
Dim lbl As Label



tbox = CType(e.Item.FindControl("txtavail"), TextBox)
avail = tbox.Text
tbox = CType(e.Item.FindControl("txtuph"), TextBox)
uph = tbox.Text
tbox = CType(e.Item.FindControl("txtystarts"), TextBox)
ystarts = tbox.Text
tbox = CType(e.Item.FindControl("txtid"), TextBox)
ID = tbox.Text
lbl = CType(e.Item.FindControl("lblinfo"), Label)
info = lbl.Text


'---updates the database--

Dim strConn As String = "data source=WEBSPM\WEBSPM;Initial Catalog=cost;User ID=password;Password=password"
Dim conn As New SqlConnection(strConn)
Dim strUpdate As String
Dim cmdUpdate As SqlCommand
Dim intRecordCount As Integer

strUpdate = "UPDATE starts SET avail='" & avail & "',uph='" & uph & "', yearly_starts='" & ystarts & "' where id='" & ID & "'"
cmdUpdate = New SqlCommand(strUpdate, conn)

conn.Open()

intRecordCount = cmdUpdate.ExecuteNonQuery()

conn.Close()
lbl.Text = intRecordCount & " " & "records updated!"


End Sub


I hope that you could help me with this. Thank you so much.

Sincerely,
Jayz

[bplacker]

open up another dataadapter for the second table, write another update query, and execute it at the same time. You cannot execute multiple table updates on one data adapter, it doesn't know what to do with that.