Extra characters at the end of expression & SQL

[MichaelCarroll]

Hello,

I have a grid with a button on the bottom which looks to see what row is checked. The code behind the button writes the row data to a SQL table.

If I try to write a SQL Insert Statement I get the error "Extra characters at the end of expression."

Here is the statement I was using:

dim sqlInsertStatement as c
sqlInsertStatement = <<%txt%
INSERT INTO co_staff_has_roles (Roles_Id_Roles, Co_Staff_Id_Staff)
VALUES (:Roles_Id_Roles, :Co_Staff_Id_Staff)
%txt%

However if I create a function and run the SQL Insert Statement there it works perfectly.

Thanks to all that had offered help on this,

Regards,

Michael Carroll

www.redrocksbluesky.com
[email protected]
435-275-5175

[Stan Mathews]

Re: Extra characters at the end of expression &amp; SQL

As shown your code will put a crlf() after the second first line. Flaunting my ignorance, is that proper in an Insert statement?

[Stan Mathews]

Re: Extra characters at the end of expression &amp; SQL

When I examine the xbasic an action script element uses internally for an sql insert I see.

dim sqlInsertStatement as c 
sqlInsertStatement = <<%txt%
INSERT INTO Customer  (FIRSTNAME, LASTNAME, PHONE, FAX) VALUES (:FIRSTNAME, :LASTNAME, :PHONE, :FAX)
%txt%

This leads me to believe that the crlf() would cause a problem. Try adding a line

strtran(sqlInsertStatement,crlf(),"")

just after the assignment.

[chadbrown]

Re: Extra characters at the end of expression &amp; SQL

dim sqlInsertStatement as c

rir=Roles_Id_Roles
csis=Co_Staff_Id_Staff

or

rir=datasubmited.roles
and so on

sqlInsertStatement = <<%txt%
INSERT INTO co_staff_has_roles Roles_Id_Roles= '{rir}', Co_Staff_Id_Staff='{csis}'
%txt%

This should work ''are for character fields

for me this is easier to look at

[MichaelCarroll]

Re: Extra characters at the end of expression &amp; SQL

Stan,

Unfortunately the error comes immediately after you click the OK button for the code form, and does not save the code.

Michael

When I examine the xbasic an action script element uses internally for an sql insert I see.

dim sqlInsertStatement as c 
sqlInsertStatement = <<%txt%
INSERT INTO Customer  (FIRSTNAME, LASTNAME, PHONE, FAX) VALUES (:FIRSTNAME, :LASTNAME, :PHONE, :FAX)
%txt%

This leads me to believe that the crlf() would cause a problem. Try adding a line

strtran(sqlInsertStatement,crlf(),"")

just after the assignment.

[MichaelCarroll]

Re: Extra characters at the end of expression &amp; SQL

Chad

The code window is choking on the <<%txt%. If I change it to <<%a% it does not throw the error. However I do like your code simplification.

Thanks,

Michael

dim sqlInsertStatement as c

rir=Roles_Id_Roles
csis=Co_Staff_Id_Staff

or

rir=datasubmited.roles
and so on

sqlInsertStatement = <<%txt%
INSERT INTO co_staff_has_roles Roles_Id_Roles= '{rir}', Co_Staff_Id_Staff='{csis}'
%txt%

This should work ''are for character fields

for me this is easier to look at

[chadbrown]

Re: Extra characters at the end of expression &amp; SQL

Pat Bremkamp is who I have been mentoring thru. I have learned alot of nice code in a very short time.

Thanks

[Pat Bremkamp]

Re: Extra characters at the end of expression &amp; SQL

Since my name was mentioned, if you use the {} around the variable, as above, then you also need to use (I don't tend to use such long names)

ins = evaluate_string(sqlInsertStatement)

to convert the variables to their values, then

cn.execute(ins)

Pat

[chadbrown]

Re: Extra characters at the end of expression &amp; SQL

Sorry Pat, I wasn't trying to quote your code simply trying to say that I have learned a lot from you in a short period of time.

[Pat Bremkamp]

Re: Extra characters at the end of expression &amp; SQL

No apology needed, just trying to make sure the approach was fully explained.

Pat

[MichaelCarroll]

Re: Extra characters at the end of expression &amp; SQL

Stan, Pat and Chad,

Thanks for your replies and your help.

Figured out how to make this work consistently pertaining to my original question: See the following post:

http://msgboard.alphasoftware.com/al...27&postcount=5

Regards,

Michael Carroll

www.redrocksbluesky.com
[email protected]
435-275-5175