PDA

View Full Version : Button that invokes an SQL statememnt


ABC123

rrwebster3
02-04-2014, 08:09 AM
I am trying to create an inquiry screen that starts out with buttons.

the first button would clear a work file, the second button would prompt for a date range and then fill the work file then the other buttons would generate reports from the workfile.

I am struggling with how to write the code behind the button that executes the SQL statements. No problem with the sql or anything none Alpha related as I'm a 30+ year developer.... just unsure of the way to have sql execute from a button.

this would be a Alpha Anywhere Web app.

Could someone post some simple examples of how to do this please?

Thank you in advance!

Robert

Peter.Greulich
02-04-2014, 12:39 PM
This link will get you started using xbasic with SQL. Very good info there:

http://www.downloads.alphasoftware.com/Tutorials/UsingXbasicWithSQLTables.html

Here is some sample generic code:



''Concise SQL example:
''=========================
Dim SQL as c
Dim conn as SQL::connection
Dim args as SQL::Arguments
Dim rs as SQL::ResultSet

args.add("PartNum",vBomPartNum)
args.add("Part",vBomPart)
args.add("Qty",vQty)

SQL = "INSERT INTO Product_Trans (PtPartNum, PtPart, PtQty) VALUES (:PartNum, :Part, :Qty)"

conn.open("::Name::XXXXXXX")
flag = conn.execute(SQL, args)
conn.close()

vText = conn.callResult.text
vTitle1 = "FINI"
vMsg1 = vText
ui_msg_box(vTitle1, vMsg1,UI_INFORMATION_SYMBOL)

rrwebster3
02-06-2014, 04:40 PM
That is terrific... I actually have it working except my parameters are hard coded... how do I get values entered into a testbox to be used for the arguments?


args.add("PartNum",:testbox1)
args.add("Part",:testbox2)
args.add("Qty",:textbox3)

I've tried the above but it doesn't work.

Peter.Greulich
02-06-2014, 04:55 PM
It all depends upon context. Since I don't know the context you could put a debug(1) statement in your code and then run your component in working preview. You will see any values there. You can copy variable names back to your code. Once you see it in the debugger it will make more sense to you.

rrwebster3
02-06-2014, 05:22 PM
37602

This is where the variables are. Nothing shows up in the debug view.

Peter.Greulich
02-06-2014, 06:43 PM
Oops. Sorry. Put the letter e in the 1st column of the debugger and then expand it.

rrwebster3
02-07-2014, 07:31 AM
This was all I needed to do:


args.add("group",e.datasubmitted.Group)
args.add("PERIOD",e.datasubmitted.Period)
args.add("YEAR",e.datasubmitted.Year)

rrwebster3
02-07-2014, 09:34 AM
How do I create a named connection on my server? If I use a string it disables the user id's password?

Peter.Greulich
02-07-2014, 02:12 PM
In the web profile click on alias.