jerrym
Posts: 52
|
| Posted: 12/12/2006, 9:54 PM |
|
hi all
was wondering if someone could assist me in the code below, i am trying to loop thru the rows in an editable grid to fin out if column1 contains the value "A". if any row contains "A" then put this value in a textbox in the parent form. i have a parent form and an editable grid on the 1 screen. when i open the parent form, all related records from the child is loaded into the editable grid.
see code below. at the moment it doesnt work. would someone help thanks.
Dim strItem
Dim strItemFlag
Dim rs
Dim CASE_SQL
Set CASE_SQL=clsDBCASE_SQL
CASE_SQL.Open
rs = PunchlistDetails.Recordset
strItem = PunchlistDetails.DataSource.AorB.Value
If strItem in rs = "A" Then
Punchlist.DataSource.ItemFlag.Value = "A"
Else
Punchlist.DataSource.ItemFlag.Value="B"
End If
CASE_SQL.Close
Set CASE_SQL = nothing
|
 |
 |
Edd
Posts: 547
|
| Posted: 12/12/2006, 11:21 PM |
|
Jerry,
Stop and think what you have here.
Form A : Header Form,
Form B: Editable Grid.
Each form in ASP needs to be submitted independantly uless you do some extra code on the AfterUpdate event - so when you update the grid the header (despite what you changed) is just refreshed. Also what happens if the editable grid has more rows that fits on a page.
My personal preference is to use a trigger on the database. Easy simple and logically where the business rules should be.
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
jerrym
Posts: 52
|
| Posted: 12/14/2006, 8:21 PM |
|
hi ed
i think i agree with you that the trigger method is the correct way to handle my situation. however i'm new with triggers and was hoping you could look at this for me. somehow i dont think this will loop thru the rows in the inserted table. i'm using sql server 2005
Create Trigger trg
On PunchlistDetails
After Insert, Update
As
If Update (AorB)
Update Punchlist
Set ItemFlag = "A"
where PunchlistID IN (Select PunchlistID from Inserted)
|
|
|
|
Edd
Posts: 547
|
| Posted: 12/14/2006, 10:01 PM |
|
Hi Jerry
Try this - I think it is right
Create Trigger trg On PunchlistDetails
After Insert, Update
As
Begin
-- First reset all the records to blank that are to be updated.
Update Punchlist Set ItemFlag = ''
WHERE Punchlist.PunchlistID IN (Select Distinct Inserted.PunchlistID
from Inserted)
-- Now Update the 'A' records (Note I am guessing that the PunchlistDetails.Item flag is the correct name
-- Also we are not only looking at the inserted / updated records BUT all the item records that can afffect the outcome
Update Punchlist Set ItemFlag = "A"
WHERE Punchlist.PunchlistID IN (Select Distinct Inserted.PunchlistID
from Inserted inner Join PunchlistDetails ON Inserted.PunchlistID = PunchlistDetails.PunchlistID AND PunchlistDetails.ItemFlag ='A' )
-- Now Update the 'B' records that are currently blank
Update Punchlist Set ItemFlag = "B"
WHERE Punchlist Set ItemFlag ='' AND
Punchlist.PunchlistID IN (Select Distinct Inserted.PunchlistID from Inserted inner Join PunchlistDetails ON Inserted.PunchlistID = PunchlistDetails.PunchlistID AND PunchlistDetails.ItemFlag <>'A' )
End
If this works you will probably need a separate delete trigger as well - does the same thing just substituting 'deleted' for 'insered'
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
jerry
Posts: 16
|
| Posted: 12/15/2006, 10:52 PM |
|
Hi ed
many thanks for the reply. first let me clarify the following; in PunchlistDetails table i have 1 column called "AorB" - user only enters "A" or "B"
when the user opens the main Punchlist form with id=12, it also loads the Details form called PunchlistDetails which may have say 3 rows of data.
** lets say; for each of the 3 rows returned by the grid, the value in the 'AorB' column is B, A,B
the trigger i'm trying to code will loop through the 2 rows looking at the value in PunchlistDetails.AorB
so, in ** above the trigger will insert B in ItemFlag for 1st loop
A in ItemFlag for 2nd loop
THEN i want it to stop updating it to B in the 3rd loop
The biz rule is that as long as there's an 'A' value in column 'AorB' for any of the 3 rows it cannot update to 'B'
will your suggestion work for my scenario?
regards
jerry
|
|
|
|
Edd
Posts: 547
|
| Posted: 12/16/2006, 5:51 PM |
|
Jerry,
The trigger does the following
For a PunchList record that some details inserted OR updated, it first goes through and sets the "AorB" flag to blank(i.e. '') see below Section 1
It then goes through looking for any detail record that contains at least one A and updates the "AorB" flag to "A", if there is not "A" flag found it leaves it as Blank, see below Section 2
The 3rd pass then looks at the Master any detail records that have a "B" in them AND has a Master "AorB" flag still set to blank and sets the "AorB" flag to "B". see below Section 3
I figured this will satisfy your needs - try it and please excuse any typos.
The trigger should be amended (not that I know the master field's name) to:
Create Trigger trg On PunchlistDetails
After Insert, Update
As
Begin
-- Section 1
-- First reset all the records to blank that are to be updated.
Update Punchlist Set AorB = ''
WHERE Punchlist.PunchlistID IN (Select Distinct Inserted.PunchlistID
from Inserted)
-- Section 2
-- Now Update the 'A' records (Note I am guessing that the PunchlistDetails.Item flag is the correct name
-- Also we are not only looking at the inserted / updated records BUT all the item records that can afffect the outcome
Update Punchlist Set AorB = "A"
WHERE Punchlist.PunchlistID IN (Select Distinct Inserted.PunchlistID
from Inserted inner Join PunchlistDetails ON Inserted.PunchlistID = PunchlistDetails.PunchlistID AND PunchlistDetails.ItemFlag ='A' )
-- Section 3
-- Now Update the 'B' records that are currently blank
Update Punchlist Set AorB = "B"
WHERE Punchlist Set ItemFlag ='' AND
Punchlist.PunchlistID IN (Select Distinct Inserted.PunchlistID from Inserted inner Join PunchlistDetails ON Inserted.PunchlistID = PunchlistDetails.PunchlistID AND PunchlistDetails.ItemFlag <>'A' )
End
Cheers
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
jerry
Posts: 16
|
| Posted: 12/16/2006, 6:34 PM |
|
hi ed
the code you gave me the first time reflects the field/table structure than the 2nd lot of code. essentially you are spot on with the rules. so i used your code but rec'd these errors when using the 'parse' tool on sql server.
Msg 4145, Level 15, State 1, Procedure trg, Line 25
An expression of non-boolean type specified in a context where a condition is expected, near 'Set'.
it did not like the null value on the reset where you had ' '
and
it did not like 'A' in the where clause as it appeared in red; so i changed it to "A" the red disappeared.
|
|
|
|
Edd
Posts: 547
|
| Posted: 12/16/2006, 6:55 PM |
|
Sorry Jerry - I warned you about my typo errors.
Is the column "AorB" a bit field OR a char/varchar?
You should have no double quotes only single quotes
eg. Set AorB = "A"
should be Set AorB = 'A'
Be patient with me - I have to second guess some things - we are slowly getting there.
Cheers
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
jerry
Posts: 16
|
| Posted: 12/16/2006, 8:39 PM |
|
hi ed
the master form "Punchlist" has a field "ItemFlag" this is char
the details form "PunchlistDetails" has a field "AorB" this is char
what do you reckon about the msg error ?
|
|
|
|
Edd
Posts: 547
|
| Posted: 12/16/2006, 8:47 PM |
|
Jerry,
Section 3 - change to
Update Punchlist Set AorB = "B"
WHERE Punchlist.AorB ='' AND
Punchlist.PunchlistID IN (Select Distinct Inserted.PunchlistID from Inserted inner Join PunchlistDetails ON Inserted.PunchlistID = PunchlistDetails.PunchlistID AND PunchlistDetails.ItemFlag <>'A' )
End
I did too much copy and pasting
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
jerry
Posts: 16
|
| Posted: 12/16/2006, 9:06 PM |
|
hi ed
i'm getting this error msg now;
Msg 207, Level 16, State 1, Procedure trg, Line 13
Invalid column name 'A'.
Msg 207, Level 16, State 1, Procedure trg, Line 16
Invalid column name 'B'.
mate i really appreciate your patience and help, but i am at a lost here. could you have another go.
|
|
|
|
jerry
Posts: 16
|
| Posted: 12/16/2006, 11:55 PM |
|
hi ed
question on sql server triggers. as this is the very 1st one i'm doing, just need confirmation on this;
- i selected table expanded it then right click triggers folder and selected 'add new'
- template was then given for me to type in the code
- after typing in the code for the trigger
- i attempted to save it, it then asked for a file name and had a default file extension as .sql
- it saved it as a separate file in a projects folder ... is this right?
shouldnt it be saved as part of the database/table structure. the odd thing is that when i close and re-open the database and expanded the punchlistdetails table to show the previously entered trigger there was nothing there... ?
is something wrong here?
regards
jerry
|
|
|
|
Edd
Posts: 547
|
| Posted: 12/17/2006, 4:49 AM |
|
Jerry
Msg 207, Level 16, State 1, Procedure trg, Line 13
Invalid column name 'A'.
Msg 207, Level 16, State 1, Procedure trg, Line 16
Invalid column name 'B'.
Again single not double quotes line 13 and 16 and copy and pasting fault "A" and "B" should be 'A' and 'B'
Re triggers one you have the trigger script you run it to create it.
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
jerry
Posts: 16
|
| Posted: 12/17/2006, 1:28 PM |
|
ed
i copied the entire code in the sql window, here it is;
---start
Create Trigger trg On PunchlistDetails
After Insert, Update
As
Begin
Update Punchlist Set ItemFlag = ' '
WHERE Punchlist.PunchlistID IN (Select Distinct Inserted.PunchlistID from Inserted)
Update Punchlist Set ItemFlag = 'A'
WHERE Punchlist.PunchlistID IN (Select Distinct Inserted.PunchlistID from Inserted inner Join PunchlistDetails ON
Inserted.PunchlistID = PunchlistDetails.PunchlistID AND
PunchlistDetails.AorB = 'A')
Update Punchlist Set ItemFlag = 'B'
WHERE Punchlist.ItemFlag = ' ' AND
Punchlist.PunchlistID IN (Select Distinct Inserted.PunchlistID from Inserted inner Join PunchlistDetails ON
Inserted.PunchlistID = PunchlistDetails.PunchlistID AND PunchlistDetails.AorB <>'A' )
End
Go
--- end
i have a new error msg;
Msg 8197, Level 16, State 4, Procedure trg, Line 2
Object 'PunchlistDetails' does not exist or is invalid for this operation.
|
|
|
|
Edd
Posts: 547
|
| Posted: 12/17/2006, 6:34 PM |
|
try
Create Trigger PunchlistDetailstrg On dbo.PunchlistDetails
FOR INSERT, UPDATE
As
Begin
Update Punchlist Set ItemFlag = ' '
WHERE Punchlist.PunchlistID IN (Select Distinct Inserted.PunchlistID from Inserted)
Update Punchlist Set ItemFlag = 'A'
WHERE Punchlist.PunchlistID IN (Select Distinct Inserted.PunchlistID from Inserted inner Join PunchlistDetails ON
Inserted.PunchlistID = PunchlistDetails.PunchlistID AND
PunchlistDetails.AorB = 'A')
Update Punchlist Set ItemFlag = 'B'
WHERE Punchlist.ItemFlag = ' ' AND
Punchlist.PunchlistID IN (Select Distinct Inserted.PunchlistID from Inserted inner Join PunchlistDetails ON
Inserted.PunchlistID = PunchlistDetails.PunchlistID AND PunchlistDetails.AorB <>'A' )
End
Go
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
jerrym
Posts: 52
|
| Posted: 12/17/2006, 8:40 PM |
|
hi ed
new error msg based on your new code;
Msg 8197, Level 16, State 4, Procedure PunchlistDetailstrg, Line 2
Object 'dbo.PunchlistDetails' does not exist or is invalid for this operation.
|
|
|
|
Edd
Posts: 547
|
| Posted: 12/18/2006, 4:19 AM |
|
Jerry - I sent you a private message with my email address.
I would like to get the script of your tavle structure sent to me so I can sort this out once and for all.
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
jerrym
Posts: 52
|
| Posted: 12/18/2006, 4:49 AM |
|
hi ed
i sent the scripts the first time you requested it but did not zip it up. anyway got your 2nd request and have just sent again. thanks for your help.
|
|
|
|
jerry
Posts: 16
|
| Posted: 12/18/2006, 8:16 PM |
|
ed
did you get email ?
|
|
|
|
Edd
Posts: 547
|
| Posted: 12/18/2006, 10:19 PM |
|
Jerry - nothing has come through and I have checked my spam filter.
I will resend a private message and again include my email - I have been making some REAL DUMB typos this time of year - I wish it was to do with alcohol 
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
test
|
| Posted: 12/18/2006, 11:15 PM |
|
test
|
|
|
|
jerrym
Posts: 52
|
| Posted: 12/19/2006, 12:33 AM |
|
ed
you're a champion! geez you're good...
hey mate, thanks for your patience and assistance. its working brilliantly now.
jerry
|
|
|
|
