How to automatically save the record?

[kingcarol]

Re: How to automatically save the record?

P.S. After you execute the update query, then your function should probably refresh your grid so the checkbox shows that it is checked.
functionName = "{grid.Object}.refresh();"

[betty]

Re: How to automatically save the record?

Well I must be stupid. This does not work either. No error message. Just doesn't work.

function AfterInsertRecord as v (e as p)
'debug(1)

'connect to the SQL database
dim cn as sql::Connection
dim flag as l
flag = cn.open("::name::conn")
if flag = .f. then
AfterInsertRecord = "alert('Could not connect to database');"
exit function
end if

'SQL Query (fields that will be written to)
dim sqlUpdate as C
sqlUpdate = <<%sql%
update journal
(posted)
values
(:posted)
%sql%

'map fields for the first record
dim args as sql::Arguments
args.add("posted",.t.)

'execute the sql using arguments first transaction
flag = cn.execute(sqlUpdate,args)
if flag = .f. then
dim errorTxt as C
errorTxt = js_escape(cn.CallResult.text)
cn.close()
AfterInsertRecord = "alert('Could not update SQL statement. Error reported was: "+errorTxt+" ');"
exit function
end if

dim rowsUpdated as N
rowsUpdated = cn.CallResult.rowsAffected
cn.close()

end function

[kingcarol]

Re: How to automatically save the record?

NOT STUPID! This stuff is tough!!! (at least I think so...)

First, you said your grid is read only, so I don't think using the AfterInsertRecord would work. I don't think you can insert a record on a read only grid. Perhaps you need to use the OnChange event of the field you have just changed on the client side. (Or if it is a check box, maybe the OnClick event?)

Second, I think your query statement might need to be something more like below (but I don't know what type of back end database you have so I may not be right), and it seems like you would need a where clause to identify the record you are updating based on a primary key field (using an argument that gets the primary key field value). You would put your primary key field name where the question marks are:

update journal set posted = :posted where ?????? = :???????

For the where, seems like you would have to use a primary key field from your grid to match the primary key in the table you are updating, .

[kingcarol]

Re: How to automatically save the record?

Betty, for some reason I can't get a clear picture of what you are trying to do. I think it would help whoever might want to help you here if you do a video demonstrating your grid and identifying its structure.

[betty]

Re: How to automatically save the record?

Aaahhh .. I have satellite here .. it doesn't play well with video and if I download or upload too much they throttle me and then I have no internet. To try to make it simpler .. I have two tables; a journal and a ledger. I am using a mySQL database with alphadao. This is what I want to do (and I can do all of it except save the record at step #3; whether I use a grid or detail view):

1. Enter a record in the journal
2. When I save that record, write two splits to the ledger.
3. Mark the journal record as posted and save that change to that record.

I know that the sql statement that I want to use is 'update journal posted = true" (ie. done). For what seems like such a simple thing .. yes .. it seems a bit convoluted. I probably make it worse than it needs to be because I have tried two dozen things and have gotten the same result no matter what I do; I can create the journal entry, I can create the ledger entries, I can update the posted value to true .. but I cannot save that updated record. I think that what I am trying to do is a common thing that everybody does; but I cannot find an answer to it.

I have looked at your work and you do a wonderful job; I cannot imagine that it has been so tough for you.

Maybe I will try to do a video after 2am when Hughes lets up on the fair access threshold.

[Davidk]

Re: How to automatically save the record?

Hey Betty,

Give this a try. I've tried to duplicate your situation. I have a read only grid, but I haven't attached any linked grids or detail grids - that shouldn't matter. The Post button (my button) updates a MySQL table and then refreshes the current row. As you say, your journal entry and ledger entries are going in ok so maybe this is just the last piece. Here is the code. It really isn't any different than yours. This is an Ajax Callback running a function. Let me know if this is close... don't worry... we'll get it. I don't know what you're using in your MySQL table for the Posted field. I'm using a type of TinyInt(1).

function PostJournal as c (e as p)
'This function will handle the Ajax callback. 
'It must return a string of Javscript commands that will be executed in the Browser.	

dim cn as sql::Connection 
dim args as sql::Arguments
dim sqlSelect as c
dim flag as l

flag = cn.open("::Name::YOURCONNECTIONSTRING","YOURSQLLOGIN","YOURPASSWORD")

sqlSelect = "UPDATE tblorders SET Posted ='1' WHERE OrderId='" + e._currentRow + "'"
flag = cn.Execute(sqlSelect)
'You can check the flag here to make sure it updated ok

'And now, refresh just the row you are on
PostJournal = "{grid.Object}.refreshRow("+e._currentRow+");"

cn.Close()

end function

[kingcarol]

Re: How to automatically save the record?

I'm glad you did that, David. I kept wishing I could find time to write some sample code for Betty, and was going to include the refresh, but my plate is way too full at year end. I'm pulling for her!

[betty]

Re: How to automatically save the record?

Well that worked .. thank you