Update 2 different Tables from one record?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 said:
Is this possible:

Read fields(rows/columns) from one sql database table (TableA). Then
Click to expand...
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.
Click to expand...
We would like to convert this Access Form to a Web Form using Visual
Studio.net and VB.net.
 
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 said:
Is this possible:

Read fields(rows/columns) from one sql database table (TableA). Then
Click to expand...
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.
Click to expand...
We would like to convert this Access Form to a Web Form using Visual
Studio.net and VB.net.
 
Even if the field in TableA is not the same field as in TableB? Example: "FinishDate" in TableA updates "NextPhase" in TableB.
 
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!" :-)

| 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
| >
| >
| > | > > 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
| >
| >
| >
 
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 said:
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!" :-)

| 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
| >
| >
| > | > > 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
| >
| >
| >
Click to expand...
 
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 said:
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] = '" &
Click to expand...
Forms!FieldLotProgress!LotCode & "' And [Complete] = -1")
MinIncomplete = DMin("[sortorder]", "[fieldprogresssub]", "[lotcode] = '"
Click to expand...
& 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 " &
Click to expand...
Forms!FieldLotProgress!LotCode & "?", vbQuestion + vbYesNo, "Grade Drive") =
vbYes Then
If IsNull(DLookup("[LotCode]", "FieldLotMeasurements", "[LotCode]
Click to expand...
= '" & Forms!FieldLotProgress!LotCode & "'")) Then
MsgBox "Please remember for future scheduling to input the
Click to expand...
driveway measurements before setting up grade schedule"
End If

sql = "UPDATE FieldGraderSchedule SET CalledIn = '" & Date & "'
Click to expand...
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 = '" &
Click to expand...
(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 & "'
Click to expand...
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 said:
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!" :-)

| 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
| >
| >
| > | > > 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
| >
| >
| >
Click to expand...
Click to expand...
 
Back
Top