How to update multiple records that the data is based on another table?

[jayz24]

Hi everybody! I'm new in this forum and new in vb.net, a beginner. I have a problem, could anyone help me by sending me a code for a multiple update that the data is based on the other table.
I'm using VS 2005 with VB.NET as my language

Here's the scenario:

I 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 hope that you could help me with this. Thank you so much.

Sincerely,
Jayz

[MartinLiss]

I moved your thread to this forum which is for VB.Net. Forum Feedback is meant for questions/comments/concerns about VB Forums itself.

[jayz24]

hi marty,

okey then. thanks.

-jayz

[jmcilhinney]

I'm not 100% sure what you're asking for. I can't really see how those two tables are related for a start. A little information about how your treating the data in code would be good too. Are you performing two queries to populate two DataTables or a single query with a join? Are you asking how to retrieve the data, save the changes or both?

I'd recommend that you start by checking out the ADO.NET sections of some of the tutorials in my signature to get a feel for it.

[jayz24]

Below is my code for updating "STARTS" table. I'm using datalist. So I want to add in this Update event, the updates of the "THROUGHPUT" table, so everytime I update the "STARTS" table it will 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=WEBSPML01\WEBSPML01;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