How do I save the data from an Unbound Grid

[Turnbullca]

Hi All

Hopefully someone can point me in the rigth direction for something that should be simple but I cannot workout how to do it with an unbound grid.
I want be able to show a list of Students for a class (which are pulled from the SQL Student Table) with 5 radio buttons to the right hand side of those students - The Teacher would then choose one of the 5 options for each student and save the data back to a table that would be populated with student_id and the radio button choice.

Any help greatrfully received.
Thanks
Chris

[GGandhi]

Re: How do I save the data from an Unbound Grid

hello

i am not sure there is something like unbound grid, unbound dialog may be. as far as i know all the grids are bound to a table.
for your question on a grid:
create an editable grid based on the sql table. for the field that has five choices change the display to radio button and populate the choices. that is it. when you submit
the data will be saved properly.
if you meant unbound dialog, then you will need to write a code to insert the data into the table in the aftervalidate event of the dialog.

[Turnbullca]

Re: How do I save the data from an Unbound Grid

Govindan

Thanks for the reply.

I have the Grid which is Populated with a SQL Statement that has the field consequence as placeholder.

SELECT person_id, surname, chosen_forename, class_code, spacer, NULL AS consequence
FROM rs.rsp_stud_send_vln_ks2_header_row_with_class
ORDER BY surname, chosen_forename

I now need to Write person_id and consequence back to a table called consequence but I do not know what the code would look like but I think I would put it here onBeforeSQLCommandExecute. The write could by row or whole form.

Chris

[GGandhi]

Re: How do I save the data from an Unbound Grid

design an updatable grid as i said earlier and put a button and call it anything you like and on the on_click event of the button write an xbasic function.
the function will gather info from the grid
person_id = e._currentRowdataNew.person_id
consequence = c._currentrowDataNew.consequence.
then open a connection to the sql
set the arguments to the above
insert the data into the table via sql insert command.

if you need exact code i can post it in about one to two hours.

ps: the title of the thread should have been how to save data to another table.

[Turnbullca]

Re: How do I save the data from an Unbound Grid

gandhi

Thanks for the reply and I have made a note about the thread title.

I really would appreciate it if you could help me with the code as this is a new area for me.

My Connection is RSPortal

Table = rs.con_detail

Table has a PK of consequence_id which is an auto increment field

The fields in the table to write back are called consequence_id, person_id, consequence


I really appreciate your help.

Chris

[GGandhi]

Re: How do I save the data from an Unbound Grid

hello
place a button in the grid and call it Add to Consequence. in the on_click event define an ajax call back and the ajax call back add xbasic name addConseqence and create a prototype( look at the bottom) and then open the xbasic declaration and add the following code.

function addConsequence as c (e as p)
	
	dim person_id as n
	dim consequence as c
	
	person_id = e._currentRowDataNew.person_id
	consequence = e._currentRowDataNew.consequence
	
	dim cn as sql::connection
	dim result as l
	result = cn.open("::Name::RSPortal")
	if result = .f. then
	    addConsequence = "alert('Could not connect to database!');"
	    exit function
	end if
	
	dim sqlInsert as c
	sqlInsert = "INSERT INTO rs.con_detail(person_id,consequence) VALUES(:newPERSONID,:newCONSEQUENCE)"
	dim args as sql::arguments
	args.add("newPERSONID",person_id)
	args.add("newCONSEQUENCE",consequence)
	
	result = cn.Execute(sqlInsert,args)
	if result = .f. then
	    addConsequence = "alert('Record could not be inserted!');"
	    cn.close()
	    exit function
	end if
	addConsequence = "alert('Record successfully inserted!');"
	cn.Close()
	exit function

consequence_id is auto increment you don't have to insert it.
if you need further help let me know.

[Turnbullca]

Re: How do I save the data from an Unbound Grid

gandhi

That is fantastic and works well and I am so greatful for your time and patience.

The Priocess to what I am trying to achieve is this and an idea of how to achieve what is setup below would be great:-

The Teacher will bring up the grid for a particular class and if a student is naughty they will click in the 1 Radio Button and Save the record (This works well with your code - all I need to do is add a msgbox to say the record has been added). During the Rest of the Lesson the same student maybe naughtier and the Teacher would Click on the 2 Radio Button and Save the Record which I would want to replace the Original 1. The problem I have is that as we have the Grid is in Add mode so if the Teacher Saved the 2 I would then have 2 Records for that Lesson.

Also is possible to Save all the Dirty Records at once.

Is it possible to remove the builtin submit button on the status bar?

If I am asking too much please say so.


Thanks

Chris

[GGandhi]

Re: How do I save the data from an Unbound Grid

the message box should come up automatically, i have included that in my code.
since you are inserting into another table there will be many records for each students.
the problem comes because in one table the students exist and the new table they don't till you add them.
if the students table and consequence table contain students to begin with then you can update consequence where the student name is equal to name in the grid.
then my question comes why have two tables to begin with.
simply have one table and include all needed fields in the table and design the grids with as many fields as you need for that purpose and build another grid or report just with another set of fields.
this question comes to my mind since i do not understand the situation you have. please explain further i can definitely help or someone else will jump in.

[Turnbullca]

Re: How do I save the data from an Unbound Grid

Ican see the code for the alert but the msgbox doesn't come up but the record does save.

I only have one table - the student names for grid come from the student details sql view:-

With the following Code:

SELECT person_id, surname, chosen_forename, class_code, spacer, NULL AS consequence
FROM rs.rsp_stud_send_vln_ks2_header_row_with_class
ORDER BY surname, chosen_forename

You have to do it like this because the students have many lessons per week and you cannot pre create the records for 1600 students who have 25 lessons per week.

The Process is - The School Day is split up into 5 Lessons.

So the Student is Lesson one and they are naughty so the Teacher Clicks on 1 and then Saves. If the Student is naughty again teh Teacher would click on 2 and Save the Record. So for that lesson the Student should actually be on a Level 2 but we have the 2 Records. It maybe that when I do the report I just need to pick the Highest for each Lesson or run a stored procedure that deletes the Lowest.

On the Question of the Buttons - is it possible to not show the Default Submit button.

When teh Row has been Saved is it possible to Disable the On Click Button until that Row becomes Dirty.

Is it possible to have a custom button that saves the whole form at once.

Chris

[GGandhi]

Re: How do I save the data from an Unbound Grid

it can be done
my thinking process: please tell me we both are on the same page
the student list comes up and one student gets #1 click and the record is written to another table
then the next time one student is selected and the program checks to see if that student exist in the destination table, if exist then update that record.
if not create a new record for that student is that correct?

yes button can be activated once the field is dirty

submit button: take a look at properties, under heading record navigator/grid tool bar, the last one says customize click on that.
and expand the button on the right and select the radio button select editable
somewhere in the middle <td> grid submitbutton</td> delete that three lines
submit button should go away.

[Turnbullca]

Re: How do I save the data from an Unbound Grid

That is correct but it would only be for that session. Because the Student would go to the next lesson and the process would start again with a different teacher.

I will have a look at the button solution now.

Thank You

Chris

[Turnbullca]

Re: How do I save the data from an Unbound Grid

Sorted Button

Template was blank - inserted default template and deleted the bits that you mentiond and submit button has now gone.

[-Jinx-]

Re: How do I save the data from an Unbound Grid

If the function is exited does it actually send an alert? I've never tried it that way and have always used a seperate command I guess.

I like looking at other peoples code to see stuff I've missed and new ways of doing things.

I didn't test it but this is how I'd personally write it I think.

If you were to change the code like so would the message boxes work?

function addConsequence as c (e as p)
	
	dim person_id as n
	dim consequence as c
	dim vjscmd as c
	
	person_id = e._currentRowDataNew.person_id
	consequence = e._currentRowDataNew.consequence
	
	dim cn as sql::connection
	if cn.open("::Name::RSPortal") then
		dim sqlInsert as c
		sqlInsert = "INSERT INTO rs.con_detail(person_id,consequence) VALUES(:newPERSONID,:newCONSEQUENCE)"
		dim args as sql::arguments
		args.add("newPERSONID",person_id)
		args.add("newCONSEQUENCE",consequence)
			if cn.Execute(sqlInsert,args) then 
				vjscmd = "alert('Record successfully inserted!');"
			else 
				vjscmd = "alert('Execute Error: Error returned was: " + js_escape(cn.CallResult.text) + "');"
			end if
	else 
		vjscmd = "alert('Connection Error: Error returned was: " + js_escape(cn.CallResult.text) + "');"	
	end if
	
	args.Clear()
	cn.Close()

addConsequence = vjscmd

end function

On an unrelated note, I learned something new just the other day with rs.NextRow() and why you do a flag=rs.NextRow() and then "while flag" instead of a if rsNextRow() Then.....while rsNextRow().

It's getting scary because this coding is actually starting to be fun instead of just excruciating work...

[GGandhi]

Re: How do I save the data from an Unbound Grid

sorry i did not mention it. sometimes you take it granted. it was my error.
also
if the student misbehaves in one lesson but very good in another how do you track that? as far as i can see the destination table has person_id, consequence.

[GGandhi]

Re: How do I save the data from an Unbound Grid

A.H.

If the function is exited does it actually send an alert?

yes it does in my program every day.
i check to see if an item exist and then alert that item can not be added, or it adds one
and both times i get a message.
i modified my code to fit his.
i will read your code that seems well written.