Add user and database insert simultaniously

[RaEd.]

I've used Grids to insert information into my database and I've used Dialogs to add new users to the security system, but I can't seem to find a way to do both in one action.

Is there some way I can add empty fields to a Grid to configure the ulink variable and password whilst adding the username to a database, or alternatively, have a Dialog connect to a database to perform an insert whilst configuring the security by scripting events?

Thanks!

[christappan]

Re: Add user and database insert simultaniously

Is there some way I can add empty fields to a Grid to configure the ulink variable and password whilst adding the username to a database, or alternatively, have a Dialog connect to a database to perform an insert whilst configuring the security by scripting events?
Thanks!

Both are possible; the former I struggled with and couldn't get to work (others have), but the latter is straightforward--just use the same DBF or SQL xbasic commands to make a table insert that you would anywhere else. If SQL:

DIM cn as SQL::Connection
DIM args as SQL::arguments
cn.open("[Your Named Connection")
	
'Specify that we are using Portable SQL syntax
cn.PortableSQLEnabled = .t.
	
args.add("Value1",currentform.controls.fieldname1)
args.add("Value2",currentform.controls.fieldname2)

cn.execute("INSERT INTO [tablename] ([Col1],[Col2]) VALUES (:Value1,:Value2)",args)

cn.close()

[RaEd.]

Re: Add user and database insert simultaniously

Sorry it's taken a while to get back to you, I've been pretty busy with some other stuff!
Hopefully I'll be able to have a go at implementing this today/monday. I'll tell you how it works out.

Thanks

[Pat Bremkamp]

Re: Add user and database insert simultaniously

Here is an example that I created some time ago using MySQL. If I was doing it again nowdays, I'd probably do it as a stored program, but this should give you some ideas. This uses a grid as shown in the pic which is based on the "signup_dummy" table, and this is in the AfterInsertRecord event. It creates entries in several tables as well as creating a user record and loggin in the person who signed up:

function AfterInsertRecord as v (e as p)
'==== assign session vars ====
e.rv.session.goto = "signup.a5w"
e.rv.session.__protected__fullname =alltrim(e.DataSubmitted.firstname)+" "+alltrim(e.DataSubmitted.lastname)

dim cnx as SQL::connection
dim args as SQL::arguments
dim crs as SQL::ResultSet
'==== create the arguments
args.add("argDummyid",e.lastIdentityValue)	'signup dummy table id
args.add("argFormalname",alltrim(e.DataSubmitted.formalname))
args.add("argShortname",alltrim(e.DataSubmitted.shortname))
args.add("argMailadr1",alltrim(e.DataSubmitted.mailadr1))
args.add("argMailadr2",alltrim(e.DataSubmitted.mailadr2))
args.add("argCity",alltrim(e.DataSubmitted.City))
args.add("argState",alltrim(e.DataSubmitted.State))
args.add("argZip",alltrim(e.DataSubmitted.Zip))
args.add("argCountry",alltrim(e.DataSubmitted.country))
args.add("argWebsite",alltrim(e.DataSubmitted.website))
args.add("argSwitchboard",remspecial(e.DataSubmitted.switchboard))
args.add("argFirstname",alltrim(e.DataSubmitted.firstname))
args.add("argLastname",alltrim(e.DataSubmitted.lastname))
args.add("argFullname",alltrim(e.DataSubmitted.firstname)+" "+alltrim(e.DataSubmitted.lastname))
args.add("argEmail",alltrim(e.DataSubmitted.email))
args.add("argPhone",remspecial(e.DataSubmitted.phone))
args.add("argTitle",alltrim(e.DataSubmitted.title))
args.add("argUsrid",alltrim(e.DataSubmitted.userid))
args.add("argPassword",alltrim(e.DataSubmitted.user_password))
args.add("argCurrentcy",alltrim(e.DataSubmitted.currency))
'==== create the client record
dim ClInsert as c = ""
ClInsert=<<%a%
INSERT INTO clients SET
name = :argFormalname,
shortname = :argShortname,
switchboard = :argSwitchboard,
website = :argWebsite,
cont_name = :argFullname,
cont_email = :argEmail,
cont_phone = :argPhone,
currency = :argCurrency,
startdate = curdate(),
lastupdateby = :argFullname,
active = 1
%a%
if .not. cnx.open("::name::egw")
	session.ErrorMsg="Could not connect to the database. Please try again later."
	goto ErrorOut
end if
'==== insert the client record ==== 
if cnx.Execute(ClInsert,args) = .f.
	session.ErrorMsg="Could not create your client record. Please try again."
	goto ErrorOut
end if
delete ClInsert
'==== get the client id number ====
if cnx.Execute("select last_insert_id() as cidnum from clients")
	crs=cnx.ResultSet
	dim session.__protected__clid as n = int(crs.data("cidnum"))
	args.add("argClid",int(crs.data("cidnum")))
else
	session.ErrorMsg="Could not create your client record. Please try again."
	goto ErrorOut
end if

'==== update the client id in the signup_dummy table =============
dim SdUpdate as c = ""
SdUpdate = "UPDATE signup_dummy SET cl_id=:argClid WHERE idcode=:argDummyid"
cnx.Execute(SdUpdate,args)
delete SdUpdate

'==== create a location =======================================
dim LoInsert as c = ""
args.add("argLouuid",strtran(api_uuidcreate(),"-",""))
LoInsert=<<%b%
INSERT INTO locations SET
lo_fullname = :argFormalname,
lo_shortname = :argShortname,
lo_switchboard = :argSwitchboard,
lo_active = 1,
lo_startdate = curdate(),
lo_website = :argWebsite,
cont_name = :argFullname,
cont_email = :argEmail,
cont_phone = :argPhone,
cl_id = :argClid,
lo_currency = :argCurrency,
lo_mailattention = :argFullname,
lo_mailadr = :argMailadr1,
lo_mailadr2 = :argMailadr2,
lo_mailcity = :argCity,
lo_mailstate = :argState,
lo_mailzip = :argZip,
lo_mailcountry = :argCountry,
lo_lastupdateby =  :argFullname,
lo_uuid = :argLouuid
%b%
if cnx.Execute(LoInsert,args) = .f.
	session.ErrorMsg="Could not create your location record. Go to Profile to create locations."
	goto ErrorOut
end if
delete LoInsert
'==== get the location id number ==============================
if cnx.Execute("select last_insert_id() as lidnum from locations")
	crs=cnx.ResultSet
	dim session.__protected__loid as n = int(crs.data("lidnum"))
	args.add("argLoid",int(crs.data("lidnum")))
else
	session.ErrorMsg="Could not create a location record. Go to Profile to create locations."
	goto ErrorOut
end if

'==== create an employee ======================================
dim EmInsert as c = ""
emuuid = strtran(api_uuidcreate(),"-","")
args.add("argEmuuid",emuuid)
EmInsert=<<%c%
INSERT INTO employees SET
em_uuid = :argEmuuid,
em_firstname = :argFirstname,
em_lastname = :argLastname,
em_haslogin = 0,
em_phone = :argPhone,
em_email = :argEmail,
em_title = :argTitle,
cl_id = :argClid,
lo_id = :argLoid,
ct_adjustments = 1,
em_lastupdateby = :argFullname
%c%
if cnx.Execute(EmInsert,args)
	dim session.__protected__ulink as c = emuuid
else
	session.ErrorMsg="Could not create your employee record. Go to Profile to create one."
	goto ErrorOut
end if
delete EmInsert
cnx.close()

'==== create a user record ====================================
dim UserValue as p
UserValue.email = e.DataSubmitted.email
UserValue.groups = "Admins,Managers,Employees"
UserValue.redirpage = "my_account.a5w"
UserValue.ulink = session.__protected__ulink
UserValue.userid = e.DataSubmitted.userid
UserValue.password = e.DataSubmitted.user_password
dim output as p
dim output.controls as p
dim output.controls.guid.value as c 
dim result as p
result = a5ws_Save_WebUser_Values(output,uservalue)
if result.errors = .T. then
	session.ErrorMsg = result.error_text
	goto ErrorOut
end if
user_guid = output.controls.guid.value
dim UserValue.guid as c
UserValue.guid = output.controls.guid.value 
result = a5ws_Get_WebUser_Values(output,uservalue)
if result.errors = .T. then
	session.ErrorMsg = result.error_text
	goto ErrorOut
end if 
user_id = output.controls.userid.value
dim vLoginResult as p 
vLoginResult=a5ws_login_user(UserValue.userid,UserValue.password)
if vLoginResult.error=.f.
	if eval_valid("session.ErrorMsg")
		delete session.ErrorMsg
	end if
	e.rv.session.goto = "signup_payment.a5w"
end if
end

ErrorOut:
e.rv.session.goto="signup.a5w"
cnx.close()

end function

[RaEd.]

Re: Add user and database insert simultaniously

Hey, only just got back around to upgrading my new user component!
If anyone's still following this, just got a quick question or two.

Firstly, if I was to implement this in a Dialog, what would be the best event to use? I'm not overly familiar with system events but I'm reading up on them now.
Also, would I be better adding my own submit button and using the onClick event instead? If so how would I go about that?

I'll keep fiddling with it in the meantime!

[chadbrown]

Re: Add user and database insert simultaniously

Pats example is in a grid. Using the after insert event. And works very well I use this on my site.

[RaEd.]

Re: Add user and database insert simultaniously

I'll build a grid and give Pats implementation a go.
Back shortly with questions/results!

[RaEd.]

Re: Add user and database insert simultaniously

It'd actually be really handy to see your Table design for that implementation, Pat. Performing this action through a grid means that I need to make some changes to the database.

Also, a couple questions:

• I've no idea what the following line does.
  

  args.add("argDummyid",e.lastIdentityValue)

  What does e.lastIdentityValue return?
  
• Where is the AfterInsertRecord event? Does it have a different name now? I can't find it D:

Thanks!

[Pat Bremkamp]

Re: Add user and database insert simultaniously

If you are using an SQL back end, then e.lastIdentityValue give you the record id of the record that was just created.

In my example, I'm using a "dummy" table as the basis for the grid. Then, in the AfterInsertRecord event, I copy those values to the various active tables.

[RaEd.]

Re: Add user and database insert simultaniously

I've tweaked the solution and put it into my AfterInsertRecord and it's working great!... except that users aren't being added to Users and Groups :(
There are still a couple lines that I don't fully understand. I'll show what I've got and point out some problem lines, and hopefully the solution will present itself.

function AfterInsertRecord as v (e as p)
	'==== assign session vars ====

'''* I'm pretty sure both of these aren't used at any point in this function, but I'd like to be certain before I remove them! *'''
	e.rv.session.goto = "new_seller.a5w"
	e.rv.session.__protected__userid =alltrim(e.DataSubmitted.userid)
	
	dim cnx as SQL::connection
	dim args as SQL::arguments
	dim crs as SQL::ResultSet
	'==== create the arguments ====

'''* I still don't fully understand how this next line is to be used.  I assume it's exclusion is what's causing my problems, but I've no idea how to implement it *'''
	'args.add("argDummyid",e.lastIdentityValue)	'signup dummy table id

	args.add("argForename",alltrim(e.DataSubmitted.forename))
	args.add("argSurname",alltrim(e.DataSubmitted.surname))
	args.add("argUserid",alltrim(e.DataSubmitted.userid))
	args.add("argAsmid",alltrim(e.DataSubmitted.asmid))
	args.add("argPassword",alltrim(e.DataSubmitted.password))

	'==== create an employee ====
	dim TblInsert as c = ""
	TblInsert=<<%c%
	INSERT INTO sql_sellers SET
	forename = :argForename,
	surname = :argSurname,
	userid = :argUserid,
	asmid = :argAsmid,
	password = :argPassword
	%c%
	if cnx.Execute(TblInsert,args)

'''* is __protected__ulink a predefined variable name or should this be the ulink field that I defined myself (__session_userid)? *'''
		dim session.__protected__ulink as c = e.DataSubmitted.userid

	else
		session.ErrorMsg="Could not create your employee record. Go to Profile to create one."
		goto ErrorOut
	end if
	delete TblInsert
	cnx.close()
	
	'==== create a user record ====================================
	dim UserValue as p
	UserValue.userid = e.DataSubmitted.userid
	UserValue.password = e.DataSubmitted.password
	UserValue.groups = "User"
	UserValue.redirpage = "home1.a5w"
	UserValue.ulink = e.DataSubmitted.userid
	dim output as p
	dim output.controls as p
	dim output.controls.guid.value as c 
	dim result as p
	result = a5ws_Save_WebUser_Values(output,UserValue)
	if result.errors = .T. then
		session.ErrorMsg = result.error_text
		goto ErrorOut
	end if

'''* How is user_guid used?  It doesn't seem to have a purpose *'''
	user_guid = output.controls.guid.value

	dim UserValue.guid as c
	UserValue.guid = output.controls.guid.value 
	result = a5ws_Get_WebUser_Values(output,UserValue)
	if result.errors = .T. then
		session.ErrorMsg = result.error_text
		goto ErrorOut
	end if

'''* Like user_guid, user_id doesn't seem to have a purpose.  Am I missing something? *'''
	user_id = output.controls.userid.value
	
	ErrorOut:
		e.rv.session.goto="new_seller.a5w"
	cnx.close()
	
end function

Any comment on the mentioned lines will help me to understand this process better.
Thanks for all the help so far!

[RaEd.]

Re: Add user and database insert simultaniously

Could the = create an employee = code not be using in a Dialog event? I just had a go implementing it but it's throwing up all kinds of errors. I'll keep at it but the original solution definately seems the more viable options at the moment.

Also I'm a little confused as to when Dialog events take place. When it says the form is "run", does it mean when the form is first opened, or when the Submit button is pressed?

[RaEd.]

Re: Add user and database insert simultaniously

Despite using a grid, I was writing lines to add field values to a grid so all of that came out.

I used this to add a new user record to the web security

	dim UserValue as p
	dim UserValue.Userid as c
	dim UserValue.Password as c
	dim UserValue.Groups as c
	dim UserValue.RedirPage as c
	dim UserValue.Ulink as c
	UserValue.Userid = e.DataSubmitted.asmid
	UserValue.Password = e.DataSubmitted.password
	UserValue.Groups = "ASM"
	UserValue.RedirPage = "home.a5w"
	UserValue.Ulink = e.DataSubmitted.asmid
	
	dim output as p
	dim output.controls as p
	dim output.controls.guid.value as c 

	dim result as p
	result = a5ws_Save_WebUser_Values(output,UserValue)

	if result.errors = .T. then
	    error_message = result.error_text
	    end
	end if

Thanks everyone for the assistance! Hope this thread can be good help to anyone else.
Also if you're ever having trouble understanding how to add users, check out the alpha wiki:
A5WS_Save_WebUser_Values