Hello,
I have a grid that displays a form for setting up a new user "staff". I then have a button that opens up a table called "roles" that that person plays.
[X] 1 Engineer
[ ] 2 Owner
[ ] 3 Technician
[X] 4 Spec 1
[ ] 5 Spec 2
I do not want to set this up as a field that stores comma delimitated information:
Engineer, Spec 1
This would be easy to do, however I have a more complex process that uses a recursive relationship that I need to perfect. So I am starting simple.
So I have three tables in a one to many and many to one recursive relationship:
Staff -|---< Staff_Has_Roles >---|- Roles
Staff
ID,First, Last
1, John, Smith
2, Mike, Carol
3, Jane, Smith
So to do this in MySQL I am going to insert data into the Staff_Has_Roles in the example above of:
I have selected the record for Jane Smith, and I have selected 1, Engineer, and 4, Spec 1 as my roles for her. So my table would look like this:
Staff_Has_Roles
staff, role
3, 1
3, 4
Or
INSERT INTO `Staff_Has_Roles` (`staff`, `role`)
VALUES (3, 1), (3, 4);
In A5 I create a script that looks like this (I stripped out the error handling for simplicity:
Var1 is what I want to contain the ID for the current staff record being edited
Var2 is what I want to pass the values that are selected from the roles grid.
'Insert a new record into a remote SQL database.
'DIM a connection variable
DIM cn as SQL::Connection
cn.PortableSQLEnabled = .t.
'Dim a SQL arguments object, create arguments and set their values
DIM args as sql::arguments
args.add("staff" , Var1")
args.add("roles" , Var2)
dim sqlInsertStatement as c
sqlInsertStatement = <<%txt%
INSERT INTO co_staff_has_roles (staff, roles) VALUES (:staff, :roles)
%txt%
'Now, close the connection
cn.close()
Now here are my questions?
How do I get the information from the selections I have made to the grid "roles" passed into an array variable?
How do I loop through this variable?
So I can then pass it to the A5 SQL code.
Thanks
Michael Carroll
FLOWSPEC LLC
www.flowspec.net
[email protected]
435-275-5175
I have a grid that displays a form for setting up a new user "staff". I then have a button that opens up a table called "roles" that that person plays.
[X] 1 Engineer
[ ] 2 Owner
[ ] 3 Technician
[X] 4 Spec 1
[ ] 5 Spec 2
I do not want to set this up as a field that stores comma delimitated information:
Engineer, Spec 1
This would be easy to do, however I have a more complex process that uses a recursive relationship that I need to perfect. So I am starting simple.
So I have three tables in a one to many and many to one recursive relationship:
Staff -|---< Staff_Has_Roles >---|- Roles
Staff
ID,First, Last
1, John, Smith
2, Mike, Carol
3, Jane, Smith
So to do this in MySQL I am going to insert data into the Staff_Has_Roles in the example above of:
I have selected the record for Jane Smith, and I have selected 1, Engineer, and 4, Spec 1 as my roles for her. So my table would look like this:
Staff_Has_Roles
staff, role
3, 1
3, 4
Or
INSERT INTO `Staff_Has_Roles` (`staff`, `role`)
VALUES (3, 1), (3, 4);
In A5 I create a script that looks like this (I stripped out the error handling for simplicity:
Var1 is what I want to contain the ID for the current staff record being edited
Var2 is what I want to pass the values that are selected from the roles grid.
'Insert a new record into a remote SQL database.
'DIM a connection variable
DIM cn as SQL::Connection
cn.PortableSQLEnabled = .t.
'Dim a SQL arguments object, create arguments and set their values
DIM args as sql::arguments
args.add("staff" , Var1")
args.add("roles" , Var2)
dim sqlInsertStatement as c
sqlInsertStatement = <<%txt%
INSERT INTO co_staff_has_roles (staff, roles) VALUES (:staff, :roles)
%txt%
'Now, close the connection
cn.close()
Now here are my questions?
How do I get the information from the selections I have made to the grid "roles" passed into an array variable?
How do I loop through this variable?
So I can then pass it to the A5 SQL code.
Thanks
Michael Carroll
FLOWSPEC LLC
www.flowspec.net
[email protected]
435-275-5175
Comment