Update 2 different Tables from one record?

[gthompson]

Is this possible:

Read fields(rows/columns) from one sql database table (TableA). Then edit/update the same 'field' in TableA; and in TableB edit/update a different field - all at the same time?

This is the current situation in an Access Form using Sql for the backend. We would like to convert this Access Form to a Web Form using Visual Studio.net and VB.net.

Please help. Thanks,
gthompson

[Kevin Spencer]

At the same exact time? No. You will need to do 2 separate UPDATE
statements. However, you can certainly do them one after the other.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

"gthompson" <gt*******@thebbpcompanies.com(donotspam)> wrote in message
news:07**********************************@microsof t.com...

Is this possible:

Read fields(rows/columns) from one sql database table (TableA). Then edit/update the same 'field' in TableA; and in TableB edit/update a
different field - all at the same time?
This is the current situation in an Access Form using Sql for the backend. We would like to convert this Access Form to a Web Form using Visual
Studio.net and VB.net.
Please help. Thanks,
gthompson

[Steve C. Orr [MVP, MCSD]]

You could call one Stored Procedure that would in turn execute two Update
statements (one for each table.)

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net
"gthompson" <gt*******@thebbpcompanies.com(donotspam)> wrote in message
news:07**********************************@microsof t.com...

Is this possible:

Read fields(rows/columns) from one sql database table (TableA). Then edit/update the same 'field' in TableA; and in TableB edit/update a
different field - all at the same time?
This is the current situation in an Access Form using Sql for the backend. We would like to convert this Access Form to a Web Form using Visual
Studio.net and VB.net.
Please help. Thanks,
gthompson

[gthompson]

Even if the field in TableA is not the same field as in TableB? Example: "FinishDate" in TableA updates "NextPhase" in TableB.

"Steve C. Orr [MVP, MCSD]" wrote:

You could call one Stored Procedure that would in turn execute two Update
statements (one for each table.)

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net
"gthompson" <gt*******@thebbpcompanies.com(donotspam)> wrote in message
news:07**********************************@microsof t.com...

Is this possible:

Read fields(rows/columns) from one sql database table (TableA). Then

edit/update the same 'field' in TableA; and in TableB edit/update a
different field - all at the same time?

This is the current situation in an Access Form using Sql for the backend.

We would like to convert this Access Form to a Web Form using Visual
Studio.net and VB.net.

Please help. Thanks,
gthompson

[Kyril Magnos]

If using a stored procedure, sure.

CREATE PROCEDURE UpdateTables
(
@Value1 varchar(100),
@Value2 varchar(100),
@Value3 varchar(100)
)
AS
UPDATE Table1
SET Value1 = @Value2

UPDATE Table2
SET Value2 = @Value1

UPDATE Table3
SET Value3 = @Value3

GO

--
HTH

Kyril Magnos
"I'm not a developer anymore, I'm a software engineer now!" :-)

"gthompson" <gt*******@thebbpcompanies.com(donotspam)> wrote in message
news:2E**********************************@microsof t.com...
| Even if the field in TableA is not the same field as in TableB? Example:
"FinishDate" in TableA updates "NextPhase" in TableB.
|
| "Steve C. Orr [MVP, MCSD]" wrote:
|
| > You could call one Stored Procedure that would in turn execute two
Update
| > statements (one for each table.)
| >
| > --
| > I hope this helps,
| > Steve C. Orr, MCSD, MVP
| > http://Steve.Orr.net
| >
| >
| > "gthompson" <gt*******@thebbpcompanies.com(donotspam)> wrote in message
| > news:07**********************************@microsof t.com...
| > > Is this possible:
| > >
| > > Read fields(rows/columns) from one sql database table (TableA). Then
| > edit/update the same 'field' in TableA; and in TableB edit/update a
| > different field - all at the same time?
| > >
| > > This is the current situation in an Access Form using Sql for the
backend.
| > We would like to convert this Access Form to a Web Form using Visual
| > Studio.net and VB.net.
| > >
| > > Please help. Thanks,
| > > gthompson
| >
| >
| >

[gthompson]

Thanks - I'll give it a try.

"Kyril Magnos" wrote:

If using a stored procedure, sure.

CREATE PROCEDURE UpdateTables
(
@Value1 varchar(100),
@Value2 varchar(100),
@Value3 varchar(100)
)
AS
UPDATE Table1
SET Value1 = @Value2

UPDATE Table2
SET Value2 = @Value1

UPDATE Table3
SET Value3 = @Value3

GO

--
HTH

Kyril Magnos
"I'm not a developer anymore, I'm a software engineer now!" :-)

"gthompson" <gt*******@thebbpcompanies.com(donotspam)> wrote in message
news:2E**********************************@microsof t.com...
| Even if the field in TableA is not the same field as in TableB? Example:
"FinishDate" in TableA updates "NextPhase" in TableB.
|
| "Steve C. Orr [MVP, MCSD]" wrote:
|
| > You could call one Stored Procedure that would in turn execute two
Update
| > statements (one for each table.)
| >
| > --
| > I hope this helps,
| > Steve C. Orr, MCSD, MVP
| > http://Steve.Orr.net
| >
| >
| > "gthompson" <gt*******@thebbpcompanies.com(donotspam)> wrote in message
| > news:07**********************************@microsof t.com...
| > > Is this possible:
| > >
| > > Read fields(rows/columns) from one sql database table (TableA). Then
| > edit/update the same 'field' in TableA; and in TableB edit/update a
| > different field - all at the same time?
| > >
| > > This is the current situation in an Access Form using Sql for the
backend.
| > We would like to convert this Access Form to a Web Form using Visual
| > Studio.net and VB.net.
| > >
| > > Please help. Thanks,
| > > gthompson
| >
| >
| >

[gthompson]

The current sql UPDATE is being used in an Access form:

Private Sub Finish_AfterUpdate()
Me.Complete = -1
DoCmd.RunCommand acCmdSaveRecord
Dim MinIncomplete, MaxComplete, sql
MaxComplete = DMax("[SortOrder]", "[fieldProgressSub]", "[LotCode] = '" & Forms!FieldLotProgress!LotCode & "' And [Complete] = -1")
MinIncomplete = DMin("[sortorder]", "[fieldprogresssub]", "[lotcode] = '" & Forms!FieldLotProgress!LotCode & "' And [Complete] = 0 And [SortOrder] > '" & MaxComplete & "'")
If Not IsNull(MinIncomplete) Then
Forms!FieldLotProgress!NextPhase = MinIncomplete
Else
Forms!FieldLotProgress!NextPhase = 0
End If
If Me.Type = "Insp Soil" Then
If MsgBox("Does the drive get graded with the floor on this lot " & Forms!FieldLotProgress!LotCode & "?", vbQuestion + vbYesNo, "Grade Drive") = vbYes Then
If IsNull(DLookup("[LotCode]", "FieldLotMeasurements", "[LotCode] = '" & Forms!FieldLotProgress!LotCode & "'")) Then
MsgBox "Please remember for future scheduling to input the driveway measurements before setting up grade schedule"
End If

sql = "UPDATE FieldGraderSchedule SET CalledIn = '" & Date & "' WHERE Lotcode = '" & Forms!FieldLotProgress!LotCode & "' AND Phase = 'Shade/Grade' OR Lotcode = '" & Forms!FieldLotProgress!LotCode & "' AND Phase = 'Grade Floor' OR Lotcode = '" & Forms!FieldLotProgress!LotCode & "' AND Phase = 'Grade Drive/Walk' OR Lotcode = '" & Forms!FieldLotProgress!LotCode & "' AND Phase = 'Shade Soil' "
DoCmd.RunSQL sql

sql = "UPDATE FieldGraderSchedule SET Tons = '" & (DSum("[SquareFeet]", "FieldLotMeasurements", "[LotCode] = '" & Forms!FieldLotProgress!LotCode & "'") * 0.0046) * (DLookup("[SubThickness]", "EstimatingSubdivision", "[SubdivCode] = '" & DLookup("[SubdivCode]", "EstimatingLot", "[LotCode] = '" & Forms!FieldLotProgress!LotCode & "'") & "'")) & "' WHERE Lotcode = '" & Forms!FieldLotProgress!LotCode & "' AND Phase = 'Grade Drive/Walk' "
DoCmd.RunSQL sql

Else
sql = "UPDATE FieldGraderSchedule SET CalledIn = '" & Date & "' WHERE Lotcode = '" & Forms!FieldLotProgress!LotCode & "' AND Phase = 'Shade/Grade' OR Lotcode = '" & Forms!FieldLotProgress!LotCode & "' AND Phase = 'Grade Floor' OR Lotcode = '" & Forms!FieldLotProgress!LotCode & "' AND Phase = 'Shade Soil' "
DoCmd.RunSQL sql

End If
End If
If Me.Type = "Insp PrePour" Then
DoCmd.OpenForm "FieldSuperCheckList"
End If
End Sub

Can I do something similar to this in Visual Studio.net?
"Kyril Magnos" wrote:

If using a stored procedure, sure.

CREATE PROCEDURE UpdateTables
(
@Value1 varchar(100),
@Value2 varchar(100),
@Value3 varchar(100)
)
AS
UPDATE Table1
SET Value1 = @Value2

UPDATE Table2
SET Value2 = @Value1

UPDATE Table3
SET Value3 = @Value3

GO

--
HTH

Kyril Magnos
"I'm not a developer anymore, I'm a software engineer now!" :-)

"gthompson" <gt*******@thebbpcompanies.com(donotspam)> wrote in message
news:2E**********************************@microsof t.com...
| Even if the field in TableA is not the same field as in TableB? Example:
"FinishDate" in TableA updates "NextPhase" in TableB.
|
| "Steve C. Orr [MVP, MCSD]" wrote:
|
| > You could call one Stored Procedure that would in turn execute two
Update
| > statements (one for each table.)
| >
| > --
| > I hope this helps,
| > Steve C. Orr, MCSD, MVP
| > http://Steve.Orr.net
| >
| >
| > "gthompson" <gt*******@thebbpcompanies.com(donotspam)> wrote in message
| > news:07**********************************@microsof t.com...
| > > Is this possible:
| > >
| > > Read fields(rows/columns) from one sql database table (TableA). Then
| > edit/update the same 'field' in TableA; and in TableB edit/update a
| > different field - all at the same time?
| > >
| > > This is the current situation in an Access Form using Sql for the
backend.
| > We would like to convert this Access Form to a Web Form using Visual
| > Studio.net and VB.net.
| > >
| > > Please help. Thanks,
| > > gthompson
| >
| >
| >

[Steve C. Orr [MVP, MCSD]]

Yes this code should work in VB.NET will little code modification required.
You'll need to change the DoCmd lines to use ADO.NET commands instead, And
you'll need to change your form references to point to your webforms instead
(of course) but that's about it.

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net
"gthompson" <gt*******@thebbpcompanies.com(donotspam)> wrote in message
news:72**********************************@microsof t.com...

The current sql UPDATE is being used in an Access form:

Private Sub Finish_AfterUpdate()
Me.Complete = -1
DoCmd.RunCommand acCmdSaveRecord
Dim MinIncomplete, MaxComplete, sql
MaxComplete = DMax("[SortOrder]", "[fieldProgressSub]", "[LotCode] = '" & Forms!FieldLotProgress!LotCode & "' And [Complete] = -1") MinIncomplete = DMin("[sortorder]", "[fieldprogresssub]", "[lotcode] = '" & Forms!FieldLotProgress!LotCode & "' And [Complete] = 0 And [SortOrder] >
'" & MaxComplete & "'") If Not IsNull(MinIncomplete) Then
Forms!FieldLotProgress!NextPhase = MinIncomplete
Else
Forms!FieldLotProgress!NextPhase = 0
End If
If Me.Type = "Insp Soil" Then
If MsgBox("Does the drive get graded with the floor on this lot " & Forms!FieldLotProgress!LotCode & "?", vbQuestion + vbYesNo, "Grade Drive") =
vbYes Then If IsNull(DLookup("[LotCode]", "FieldLotMeasurements", "[LotCode] = '" & Forms!FieldLotProgress!LotCode & "'")) Then MsgBox "Please remember for future scheduling to input the driveway measurements before setting up grade schedule" End If

sql = "UPDATE FieldGraderSchedule SET CalledIn = '" & Date & "' WHERE Lotcode = '" & Forms!FieldLotProgress!LotCode & "' AND Phase =
'Shade/Grade' OR Lotcode = '" & Forms!FieldLotProgress!LotCode & "' AND
Phase = 'Grade Floor' OR Lotcode = '" & Forms!FieldLotProgress!LotCode & "'
AND Phase = 'Grade Drive/Walk' OR Lotcode = '" &
Forms!FieldLotProgress!LotCode & "' AND Phase = 'Shade Soil' " DoCmd.RunSQL sql

sql = "UPDATE FieldGraderSchedule SET Tons = '" & (DSum("[SquareFeet]", "FieldLotMeasurements", "[LotCode] = '" &
Forms!FieldLotProgress!LotCode & "'") * 0.0046) * (DLookup("[SubThickness]",
"EstimatingSubdivision", "[SubdivCode] = '" & DLookup("[SubdivCode]",
"EstimatingLot", "[LotCode] = '" & Forms!FieldLotProgress!LotCode & "'") &
"'")) & "' WHERE Lotcode = '" & Forms!FieldLotProgress!LotCode & "' AND
Phase = 'Grade Drive/Walk' " DoCmd.RunSQL sql

Else
sql = "UPDATE FieldGraderSchedule SET CalledIn = '" & Date & "' WHERE Lotcode = '" & Forms!FieldLotProgress!LotCode & "' AND Phase =
'Shade/Grade' OR Lotcode = '" & Forms!FieldLotProgress!LotCode & "' AND
Phase = 'Grade Floor' OR Lotcode = '" & Forms!FieldLotProgress!LotCode & "'
AND Phase = 'Shade Soil' " DoCmd.RunSQL sql

End If
End If
If Me.Type = "Insp PrePour" Then
DoCmd.OpenForm "FieldSuperCheckList"
End If
End Sub

Can I do something similar to this in Visual Studio.net?
"Kyril Magnos" wrote:

If using a stored procedure, sure.

CREATE PROCEDURE UpdateTables
(
@Value1 varchar(100),
@Value2 varchar(100),
@Value3 varchar(100)
)
AS
UPDATE Table1
SET Value1 = @Value2

UPDATE Table2
SET Value2 = @Value1

UPDATE Table3
SET Value3 = @Value3

GO

--
HTH

Kyril Magnos
"I'm not a developer anymore, I'm a software engineer now!" :-)

"gthompson" <gt*******@thebbpcompanies.com(donotspam)> wrote in message
news:2E**********************************@microsof t.com...
| Even if the field in TableA is not the same field as in TableB? Example: "FinishDate" in TableA updates "NextPhase" in TableB.
|
| "Steve C. Orr [MVP, MCSD]" wrote:
|
| > You could call one Stored Procedure that would in turn execute two
Update
| > statements (one for each table.)
| >
| > --
| > I hope this helps,
| > Steve C. Orr, MCSD, MVP
| > http://Steve.Orr.net
| >
| >
| > "gthompson" <gt*******@thebbpcompanies.com(donotspam)> wrote in message | > news:07**********************************@microsof t.com...
| > > Is this possible:
| > >
| > > Read fields(rows/columns) from one sql database table (TableA). Then | > edit/update the same 'field' in TableA; and in TableB edit/update a
| > different field - all at the same time?
| > >
| > > This is the current situation in an Access Form using Sql for the
backend.
| > We would like to convert this Access Form to a Web Form using Visual
| > Studio.net and VB.net.
| > >
| > > Please help. Thanks,
| > > gthompson
| >
| >
| >