help with sql insert into statement

[kingcarol]

I am trying to follow the info here about Inserting a Row Into a SQL Database, p. 22: http://www.alphasoftware.com/whitepa...chitecture.pdf

I'm trying to just append an audit record about a deletion into my back end Access database. In the code below, the connection has already been opened, etc. The piece of code shown here produces a fatal error (which doesn't surprise me since I'm such an Alpha newbie). I've been studying everything I can find about INSERT INTO and am now throwing my appeal for help to the message board. Thanks.

'append an audit record about the Cost Type(PROD_CODE) deletion to the TblGLAudit
	'NOTE:  PROD_CODE value from TblProducts is appended to AP_PRODUCT_ID field in TblGLAudit
	
	dim sqlaudit as c
	sqlaudit = "INSERT INTO TblGLAudit(GL_JOURNAL, GL_DATE, GL_MAIN_ACCT_NUM, GL_DESCRIPTION, AP_PRODUCT_ID, GL_ENTRY_NOTES, AUDIT_DATE, USER_NAME)
	VALUES (:GL_JOURNAL, :GL_DATE, :GL_MAIN_ACCT_NUM, :GL_DESCRIPTION, :AP_PRODUCT_ID, :GL_ENTRY_NOTES, :AUDIT_DATE, :USER_NAME)"
	
	delete args 
	dim args as sql::arguments
	'set the value of the arguments.
	'NOTE:  If field is a numeric field, the code would have to be like this (per Selwyn):
	'args.add("whatCriteria", convert_type( FieldName, "N") )
	'ones I wonder about are the date and time fields...
	
	dim AuditValue as c
	AuditValue = e._currentRowDataOld.PROD_CODE
	dim AuditNote as c
	AuditNote = "User confirmed deletion of Cost Type: "+ AuditValue
	
	args.add("GL_JOURNAL", "NONE")
	args.add("GL_DATE",Date) 'today's date? is this right? need to convert to character?
	args.add("GL_MAIN_ACCT_NUM","0") 'GL_MAIN_ACCT_NUM is a text field
	args.add("GL_DESCRIPTION","Cost Type Deletion Request")
	args.add("AP_PRODUCT_ID",AuditValue)
	args.add("GL_ENTRY_NOTES", AuditNote)
	args.add("AUDIT_DATE",Now) 'date and time? is this right?  need to convert to character?
	args.add("USER_NAME","Alpha Test")
		
	'execute the audit append query with a flag to let us know if the query was executed successfully.
	flag = cn.Execute(sqlaudit,args)

[workaholic06]

Re: help with sql insert into statement

Hi Carol,

Can you post the error message you're getting?

Also, which event did you add this logic to?

Thanks,

Robert

[kingcarol]

Re: help with sql insert into statement

I tried adding this code to a much longer function that was already successfully deleting a record after looking up some criteria with sql statements to verify that the delete should occur. I am using a button I created for the delete and it's activated by the OnClick event.

After putting in the new code to append an audit record, I now get this error:

Fatal Error: Could not compile Xbasic: 'LookupInChildTablesThenDeleteIfNotFound'

[workaholic06]

Re: help with sql insert into statement

Sorry - editing..

I misread your note.

Can you show me the full xbasic for the onclick?

Thanks,

Robert

[Selwyn Rabins]

Re: help with sql insert into statement

I tried adding this code to a much longer function that was already successfully deleting a record after looking up some criteria with sql statements to verify that the delete should occur. I am using a button I created for the delete and it's activated by the OnClick event.

After putting in the new code to append an audit record, I now get this error:

Fatal Error: Could not compile Xbasic: 'LookupInChildTablesThenDeleteIfNotFound'

the error is telling you that there is a syntax error in this function that you have defined:

LookupInChildTablesThenDeleteIfNotFound

the easiest way to figure out what the error is, is as follows:

1. copy the function to the clipboard.
2. go to the a5 control panel (not the web control panel)
3. go to the code tab
4. create a new empty xbasic scrpt
5. paste the code from the clipboard.
6. on the toolbar you will see a button called 'check syntax' (7 from left). click that. it will tell you what the xbasic syntax error is.

[Selwyn Rabins]

Re: help with sql insert into statement

I am trying to follow the info here about Inserting a Row Into a SQL Database, p. 22: http://www.alphasoftware.com/whitepa...chitecture.pdf

I'm trying to just append an audit record about a deletion into my back end Access database. In the code below, the connection has already been opened, etc. The piece of code shown here produces a fatal error (which doesn't surprise me since I'm such an Alpha newbie). I've been studying everything I can find about INSERT INTO and am now throwing my appeal for help to the message board. Thanks.

'append an audit record about the Cost Type(PROD_CODE) deletion to the TblGLAudit
	'NOTE:  PROD_CODE value from TblProducts is appended to AP_PRODUCT_ID field in TblGLAudit
	
	dim sqlaudit as c
	sqlaudit = "INSERT INTO TblGLAudit(GL_JOURNAL, GL_DATE, GL_MAIN_ACCT_NUM, GL_DESCRIPTION, AP_PRODUCT_ID, GL_ENTRY_NOTES, AUDIT_DATE, USER_NAME)
	VALUES (:GL_JOURNAL, :GL_DATE, :GL_MAIN_ACCT_NUM, :GL_DESCRIPTION, :AP_PRODUCT_ID, :GL_ENTRY_NOTES, :AUDIT_DATE, :USER_NAME)"
	
	delete args 
	dim args as sql::arguments
	'set the value of the arguments.
	'NOTE:  If field is a numeric field, the code would have to be like this (per Selwyn):
	'args.add("whatCriteria", convert_type( FieldName, "N") )
	'ones I wonder about are the date and time fields...
	
	dim AuditValue as c
	AuditValue = e._currentRowDataOld.PROD_CODE
	dim AuditNote as c
	AuditNote = "User confirmed deletion of Cost Type: "+ AuditValue
	
	args.add("GL_JOURNAL", "NONE")
	args.add("GL_DATE",Date) 'today's date? is this right? need to convert to character?
	args.add("GL_MAIN_ACCT_NUM","0") 'GL_MAIN_ACCT_NUM is a text field
	args.add("GL_DESCRIPTION","Cost Type Deletion Request")
	args.add("AP_PRODUCT_ID",AuditValue)
	args.add("GL_ENTRY_NOTES", AuditNote)
	args.add("AUDIT_DATE",Now) 'date and time? is this right?  need to convert to character?
	args.add("USER_NAME","Alpha Test")
		
	'execute the audit append query with a flag to let us know if the query was executed successfully.
	flag = cn.Execute(sqlaudit,args)

here is how you can use an alpha five genie to help you generate an alphaDAO insert statement:

http://screencast.com/t/MDZhMDE4ZmEt

[kingcarol]

Re: help with sql insert into statement

Thanks, Selwyn, for the video. I'm going to go try that now. Wanted to a post a little Jing here because the screens in my Alpha are different than yours, and I had a little trouble finding the Xbasic Genie following your directions. Here's how I found it:

http://screencast.com/t/NzkxNmI1MTg

[Selwyn Rabins]

Re: help with sql insert into statement

Thanks, Selwyn, for the video. I'm going to go try that now. Wanted to a post a little Jing here because the screens in my Alpha are different than yours, and I had a little trouble finding the Xbasic Genie following your directions. Here's how I found it:

http://screencast.com/t/NzkxNmI1MTg

maybe my video was not clear, but it is actually simpler than your video indicates.

the first button on the toolbar is 'new xbasic script'

the second button is 'new action script'.

it is the second button that you need to click to create a new action script. once you are in the action script editor, you can then click the button to add new action.

[kingcarol]

Re: help with sql insert into statement

ok, I have generated code with the genie, copied what I needed, and am pasting it into the Xbasic function declaration. However, even if I comment out all the code I just pasted, I cannot save it. I get a message that says "Extra characters at end of expression." See Jing..

http://screencast.com/t/NWI1NjczMj

This code isn't tested, and I'm sure needs tweaking, but I can't save it in order to start testing... here's the code from the genie that I'm sure I'll probably need to alter some.

	'Specify that we are using Portable SQL syntax
	cn.PortableSQLEnabled = .t.
	
	'Dim a SQL arguments object, create arguments and set their values
	DIM args as sql::arguments
	
	args.add("GL_JOURNAL","NONE")
	if a5_eval_valid_expression("=Date()",local_variables()) then 
		args.add("GL_DATE",convert_type(a5_eval_expression("=Date()",local_variables()),"T"))
	end If
	args.add("GL_DESCRIPTION","User confirmed delete of Cost Type:  ")
	args.add("GL_MAIN_ACCT_NUM","0")
	if a5_eval_valid_expression("=e._currentRowDataOld.PROD_CODE",local_variables()) then 
		args.add("AP_PRODUCT_ID",convert_type(a5_eval_expression("=e._currentRowDataOld.PROD_CODE",local_variables()),"C"))
	end If
	if a5_eval_valid_expression("=Now()",local_variables()) then 
		args.add("AUDIT_DATE",convert_type(a5_eval_expression("=Now()",local_variables()),"T"))
	end If
	args.add("USER_NAME","Alpha Test")
	
	dim sqlInsertStatement as c 
	sqlInsertStatement = <<%txt%
	INSERT INTO TblGLAudit  (GL_JOURNAL, GL_DATE, GL_DESCRIPTION, GL_MAIN_ACCT_NUM, AP_PRODUCT_ID, AUDIT_DATE, USER_NAME) VALUES (:GL_JOURNAL, :GL_DATE, :GL_DESCRIPTION, :GL_MAIN_ACCT_NUM, :AP_PRODUCT_ID, :AUDIT_DATE, :USER_NAME)
	%txt%
	
	dim flag as l 
	flag = cn.Execute(sqlInsertStatement,args)

[Selwyn Rabins]

Re: help with sql insert into statement

OK, i see the problem.

You are using %txt% as your long string delimiter.
But the Grid component also uses this as its long string delimiter. (If you go to the Xbasic tab in the Grid Builder, you will see that).

Change your code to use some other long string delimiter.
e.g. instead of using %txt%, you could use %sqltxt%

We actually made a change yesterday to automatically fix up long string delimiter conflicts. That fix will be in the next update.

Thanks.

[kingcarol]

Re: help with sql insert into statement

Great! making that change lets me save the code. Now on to testing and tweaking it. Thanks, Selwyn

[kingcarol]

Re: help with sql insert into statement

AND success! I didn't have to tweak the code. It's appending the record to my audit table nicely...

here's the code that works. and I'm done for the day :) Thanks again, Selwyn.

'append an audit record about the Cost Type(PROD_CODE) deletion to the TblGLAudit
	'NOTE:  PROD_CODE value from TblProducts is appended to AP_PRODUCT_ID field in TblGLAudit
	
	'Specify that we are using Portable SQL syntax
	cn.PortableSQLEnabled = .t.
	
	'Dim an SQL arguments object, create arguments and set their values
	DIM args as sql::arguments
	
	args.add("GL_JOURNAL","NONE")
	if a5_eval_valid_expression("=Date()",local_variables()) then 
		args.add("GL_DATE",convert_type(a5_eval_expression("=Date()",local_variables()),"T"))
	end If
	args.add("GL_DESCRIPTION","User confirmed delete of Cost Type:  ")
	args.add("GL_MAIN_ACCT_NUM","0")
	if a5_eval_valid_expression("=e._currentRowDataOld.PROD_CODE",local_variables()) then 
		args.add("AP_PRODUCT_ID",convert_type(a5_eval_expression("=e._currentRowDataOld.PROD_CODE",local_variables()),"C"))
	end If
	if a5_eval_valid_expression("=Now()",local_variables()) then 
		args.add("AUDIT_DATE",convert_type(a5_eval_expression("=Now()",local_variables()),"T"))
	end If
	args.add("USER_NAME","Alpha Test")
		
	dim sqlInsertStatement as c 
	sqlInsertStatement = <<%sqltxt%
	INSERT INTO TblGLAudit  (GL_JOURNAL, GL_DATE, GL_DESCRIPTION, GL_MAIN_ACCT_NUM, AP_PRODUCT_ID, AUDIT_DATE, USER_NAME) VALUES (:GL_JOURNAL, :GL_DATE, :GL_DESCRIPTION, :GL_MAIN_ACCT_NUM, :AP_PRODUCT_ID, :AUDIT_DATE, :USER_NAME)
	%sqltxt%
	
	dim flag as l 
	flag = cn.Execute(sqlInsertStatement,args)
	
	'if audit append query could not execute, give the user a message and exit the function.
	if flag = .f. then 
		'there was an error - close the connection and exit
		cn.Close()
		dim msg as c 
		msg = "Could not append audit record, so delete will not occur. Please contact your program administrator for help.  Error reported was: " + cn.CallResult.text
		msg = js_escape(msg)
		dim jscmd as c 
		jscmd = "alert('" + msg + "');"
		DoDeleteAfterConfirm = jscmd
		exit function 
	end if