# Miscellaneous > Database Programming >  Tornado-Need two ways Update records

## paul mancuso

Is it possible, using Tornado, to update records in two ways?
1. by using dbEditGridUpdate=true.
2. by using the standard update.

Currently, when dbEditGridUpdate=true the update button automatically presents the full screen edit mode. what I need are two buttons, one for full screen edit, another for single record edit.  Ideally,  the update button on every row should enable single record update and the update button (stand alone) should call the full screen.

How do I accomplish this?

----------


## Frank

Make two custom buttons one for grid and one for single Edit. When one of them is clicked, detected that in the URL via Request.Querystring. Modify the properties (disable or enable gridedit property).


Frank

----------


## paul mancuso

Frank,

Thanks for the direction on this.  The custom button has been programmed, however how do I position the new button within the other navigation button set.  In other words, I need to place the custom button next to the Update Button, it seems to default to the first position....

----------


## Frank

I'll look into the positioning of the custom buttons an dalso the row edit approach.

The rwoedit and gridedit mix is complicated. I think allowing a button insert next to the update button is easier.



Frank

----------


## paul mancuso

Frank,

I'm unsure how to proceed using the request.querystring function.  What string do I test for if the update button is pressed and the custom button as well?

----------


## Frank

As for custom buttons, you can put anyting like MyBut=Grid or MyBut=Single. Inspectt he querystring and you can extract them using resquest.querystring("MyBut")

But let me look at using an insert tag next to the update button. Itis a lot easier


Frank

----------


## Frank

I got a good solution w/o any code penalty on my part. I'll allow a new "gridupdate" or "gridadd" button in addition to the regular ones. once that is clicked, the gridedit switch will be set to true. No need to specify dbEditGridUpdate = true. So when yo specify ni=b5,update,gridupdate... both will appear. I'll wait for my web programmer to make the new buttons and then do the final test. 

Looks like this one is a pretty cool feature.. hope it works out.

Frank

----------


## Frank

Looks like it is working. Email me when you are ready to test that.

Frank

----------


## paul mancuso

Frank,

Your solution sounds excellent and it is a very nice addition to an already excellent product.

Paul

----------


## paul mancuso

Frank,

My agencys email is currently down so I'll post my findings here.  The new dll does work.  It included the gridupdate button which called the full screen edit mode.  The normal update button is as usual, single record update. This is good.  

I did note a problem, the update and gridupdate modes did not allow dropdowns, calendars etc. but the add mode did.

To take this to the final level, it would be nice if the data elements could be selected individually between the two edit modes.

I'll wait to hear from you...

Paul

----------


## Frank

>>...the data elements could be selected individually between the two edit modes.

explain more about this.

I'll look into the dropdown problem.


FK

----------


## Frank

I use the following code and drodown works for both normal and grid update. Only "gridUpdate and Normal Update is implemented at this time. Explain more about the problem ->

		Dim Mydb As New tornado.z()
		With Mydb
			.dbQP = "U=1|M=Grid!sysindex=true|S=plain|D=Nwind|Q=Orders  |TH=Tit=2 Update Styles| gdf=0,1,2,3| ni=b5,update,gridupdate| bm=Orders;0"
			.dbCommonTables = "index=First,FullName|sql=Select Firstname,Firstname & ' ' & Lastname from employees; index=EID|sql=Select Employeeid from employees"
			.dbEditUpdateFlds = "1,fi=2|ty=SB|val=EID|Tex=FullName"
			.ASPdbNET()
		End With

FK

----------


## paul mancuso

Frank,

I'm still working to get the dropdown fields working.  Can you explain the following syntax:

.dbEditUpdateFlds = "1,fi=2|ty=SB|val=EID|Tex=FullName"

I can't find where the 1 in 1,fi=2 is documented.

I'm sure I'll figure this out, however let me explain further my idea of user selected data elements based on gridupdate or update mode.

It would be a nice Tornado feature for the programmer to be able to select independently which fields are to be in gridupdate vs. update.  In my case I'd like to only allow certain fields to be updateable if the gridupdate mode is called. Likewise for the update mode.

I see a new function called .dbGridUpdateFlds = fi=2|ty-SB|val=EID|tex=FullName 
and
.dbEditUpdateFlds = fi=4|ty=textarea|val=memo|tag=col=20

this would allow greater flexability in reducing the screens real estate during full screen edit mode (GridUpdate) as well.

What do ya think?

----------


## Frank

OH.. You mean you would like to have a different set of Update flds for normal and grid update !!

Frank

----------


## paul mancuso

Yes!!!!!!

----------


## Frank

This is possible.. I'll look into that.

Frank

----------


## Frank

Modification made.. contact me to get a test DLL

FK

----------


## paul mancuso

Frank,

The new .dll was waiting in my mailbox when I arrived in the office today.  It's been installed and preliminary testing looks very good.

Again, you have come through with an excellent modification in a very short time.  Now Tornado has extreme editing muscle.  What other product could do this?

I'll be working with the new .dll this week and will report back with my findings.  Again, thanks Paul

----------


## Frank

Thanks for the suggestion and debugging !!

FK

----------


## paul mancuso

Frank,

Have been using the new .dbGridUpdateFlds command with no problems.

However, there should be seperate functionality for the .dbEditGridAdd.
If .dbEditGridAdd=true the system defaults to fill screen edit mode. With the inclusion of the new .dbGridUpdateFlds a companion for the non full screen Add mode is appropriate.

What do ya think?

----------


## Frank

I did not do the Edit Add... you think it needs the same treatment?

Frank

----------


## paul mancuso

Yep!  We now can split the data elements between single record mode and full screen mode.  In a one to many relationship you may just need to add a single record in full screen edit mode or you may need to add a master record in single add mode.  Splitting up that functionality makes sense to me...

----------


## Frank

OK.. I'll do the same to Add then...


FK

----------


## Frank

I added the GridAddFlds to the C# version. Look for that when it is released. 


Frank

----------


## paul mancuso

Frank,

I've been out of the office since last week, when you posted the latest message.  What is your timeline for release of the version containing the GridAddFlds? And, thanks for the quick turn around.

----------


## Frank

are you in need of this function.. I am shooting for next month to get testers.. If you need that ina  hurry , I'll put that in the VB version.


FK

----------


## paul mancuso

It seems I'm always in a hurry when it comes to project deadlines.  The sooner the better.... thanks....

----------


## Frank

OK.. I'll do it sometime today fo ryou.


FK

----------


## paul mancuso

Thank you very much....!!!!

----------


## paul mancuso

Frank,

Do you have a timeline for completion of the new .dll containing the .dbGridAddFlds modification?

----------


## Frank

did I send you the dll already ?

FK

----------


## paul mancuso

No, but I've been waiting to hear from you regarding it....

----------


## Frank

OK.. try again... let me know when you get it...

----------


## paul mancuso

OK, Frank, I have the latest tornado.dll.  Now I need the function name and syntax to use it...

----------


## Frank

should be the same as gridupadteflds. just change the update to add. 

<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
   Dim X As New tornado.z()
   X.dbQP = "U=1|S=15|D=Nwind|ni=b5,update,gridupdate,add,grid  add|Q=Orders|Th=Title=Grid Edit| bm=orders;0+1|gdf=0,3,4,5,7"
   X.dbEditUpdateFlds = "fi=0|type=RONOUPDATE,4,5,7"
   X.dbEditGridUpdateFlds = "fi=0|type=RONOUPDATE,5,7"
   X.dbEditAddFlds = "0,4,5,7"
   X.dbEditGridAddFlds = "fi=0,5"   
   X.dbDebug = "EditAction"
   X.ASPdbNET()
End Sub
</script>   


FK

----------


## paul mancuso

Frank,

Here's the error I'm getting:
Compiler Error Message: BC30456: 'dbEditGridAddFlds' is not a member of 'Tornado.z'.

Looks like I do not have the latest Tornado.dll.

The version as reported by TornadoInfo is: ver 4.4.30.11

----------


## Frank

I looked into the latest normal and grid edit implementation and it really needs the corresponding user defeined edit SQL to go with the two modes. Otherwsie, the fields edited in the two modes mustbe the same in which is too restrictive. OK.. now it is done and I'll send you the DLL and test code (bottom of Edit manual page). Confirm the new dbEditGridUPdateSQL and dbEditGridAddSQL for me...


Frank

----------


## paul mancuso

Frank,

Wow! The SQL functions are done, thanks. I'm just back from visiting clients in Michigan and will get to testing the new SQL functions today.

Did you get a chance to post the syntax needed to save data to multiple files?

talk to you later... Paul

----------


## Frank

I added the example to the end of the edit  manual page - edit two tables.

Frank

----------


## paul mancuso

Frank,

Is the new file posted to the FTP site we used last time?  Paul

----------


## Frank

OH.. I just did that.. I cannot delete the old dll file (access denied) but I did upload the newest dll and hope that it'll overwrite the old one.

let me know..

Frank

----------


## paul mancuso

Frank,

I downloaded the new .dll from the FTP site.  Thank you.  I'll test out the new functions as soon as I get the multi-table edit/add working. 

Having looked at your example I don't see what I'm doing wrong.  Here's the error the system is giving.

System.Data.OleDb.OleDbException: Invalid column name 'LocPin'. at System.Data.OleDb.OleDbDataReader.NextResults(IMul  tipleResults imultipleResults, OleDbConnection connection, OleDbCommand command) at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern  al(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at Tornado.RS_OleDB.Execute_SQLs(String which, String sql, Int32 recs, Int64& LRecs)

(313 : 7) Error 



Edit SQL = INSERT INTO mpoProject (MPOPin,MPOName,mpoNameCode,CreatedDate,FedYear,Co  unty,mpoAgency,ObligateDate,AirQuality,IsActive,On  StateSystem,Safety,Remarks,LocPin,ProjectBegin,Pro  jectEnd,MilePostBegin,MilePostEnd,Distance,PolDist  Code,Sponsor,GISData) VALUES ('111999','UCTC','1','6/21/2004','2004','Ulster','City',Null,'AQ Exempt','False','False','False',Null,'111999',Null  ,Null,Null,Null,Null,Null,Null,Null)

And, here's my code:

DIM addSql as string = ""
                addSql =          "INSERT INTO mpoProject (mpoProject.mpoPin"
                addSql = addSql & "mpoProject.DOTAuth,mpoProject.mpoName,"
                addSql = addSql & "mpoProject.WorkType,mpoProject.IsActive,"
                addSql = addSql & "mpoProject.Safety,mpoProject.FunctionClass,"
                addSql = addSql & "mpoProject.OnStateSystem,mpoProject.Let_Date,  "
                addSql = addSql & "mpoProject.County,mpoProject.AirQuality,"
                addSql = addSql & "mpoProject.ProjectManager,mpoProject.mpoAgenc  y,"
                addSql = addSql & "mpoProject.FedYear,mpoProject.CreatedBy,"                
                addSql = addSql & "mpoProject.UpDatedBy,mpoProject.CreatedDate,"
                addSql = addSql & "mpoProject.UpDatedDate,mpoProject.mpoNameCode  ,"                
                addSql = addSql & "mpoProject.Sponsor,mpoProject.AirQuality,"                
                addSql = addSql & "mpoProject.Remarks,mpoProject.Description)"
                addSql = addSql & "VALUES ('mpoPin','DOTAuth','mpoName','WorkType','IsActive  ',"
                addSql = addSql & "'Safety','FunctionClass','OnStateSystem',#Let_Dat  e#,'FedYear'"
                addSql = addSql & "'CreatedBy','UpDatedBy',#CreateDate#,#UpdateDate#  ,"
                addSql = addSql & "'mpoNameCode','Sponsor',"
                addSql = addSql & "'AirQuality','Remarks','Description');"
                addSql = addSql & "INSERT INTO ProjectLocation "
                addSql = addSql & "(ProjectLocation.LocPin,ProjectLocation.Location,  "
                addSql = addSql & "ProjectLocation.Distance,ProjectLocation.MilePost  Begin,"
                addSql = addSql & "ProjectLocation.MilePostEnd,ProjectLocation.Map_N  umber,"
                addSql = addSql & "ProjectLocation.CountyName,ProjectLocation.Corrid  or,"
                addSql = addSql & "ProjectLocation.ProjectBegin,ProjectLocation.Proj  ectEnd,"
                addSql = addSql & "ProjectLocation.PolDistCode,ProjectLocation.GISDa  ta)"
                addSql = addSql & "VALUES ('LocPin','Location','Distance','MilePostBegin','M  ilePostEnd',"
                addSql = addSql & "'Map_Number','CountyName','Corridor','ProjectBegi  n','ProjectEnd',"
                addSql = addSql & "'PolDistCode','GISDate')"
             .dbEditAddSQL = addSql


I can't say for sure but the SQL Debug does not contain 2 INSERT INTO statements, which are present in the .dbEditAddSQL function. It would seem to me 2 INSERT INTO statements should be there, to differentiate between the 2 tables. 

Have tried every trick I know,  Some help would be greatly appreciated.

Paul

----------


## Frank

did you try the example in the manual page (as follows)? 


<script language='vb' runat='server'>
Sub Page_Load(Source as Object, E as EventArgs)
   Dim SQL As String = "SELECT Addresses.ID, Addresses.Address, FLname.LastName, FLname.FirstName FROM Addresses"
   SQL &= " INNER JOIN FLname ON Addresses.ID = FLname.ID  ORDER BY Addresses.ID"

   Dim AddSQL As String = "(| :Wink: INSERT INTO FLname (ID, LastName, FirstName) VALUES ('[[0]]', '[[2]]', '[[3]]');"
   AddSQL &= "INSERT INTO Addresses (ID, Address) VALUES ('[[0]]', '[[1]]')"

   Dim UpSQL As String = "(| :Wink: UPDATE FLname SET LastName='[[2]]', FirstName='[[3]]' WHERE ID='{{0}}';"
   UpSQL &= "UPDATE Addresses SET Address='[[1]]' WHERE ID='{{0}}'"

   Dim X As New tornado.z()
   X.dbQP = "U=1|M=Grid!RowEdit=true|D=MultipleEdit|NI=b5,add,  update|PS=-1|TH=Ti= 2 Tables
   X.dbSQL = SQL
   X.dbDebug = "ActiveSQL, EditAction, EditSetup"
   X.dbNameMap = "fi=0|Al=ID|Fo=Addresses.ID, fi=1|Al=Address|Fo=Addresses.address, fi=2|Al=First|Fo=FLname.firstname,fi=3|Al=Last|Fo=  FLname.lastname"
   X.dbEditAddFlds = "0,1,2,3"
   X.dbEditUpdateFlds = "0,1,2,3"
   X.dbEditUPDATESQL = UpSQL
   X.dbEditADDSQL = AddSQL
   X.dbBookMark = "FLname;0"
   X.ASPdbNET()
End Sub
</script>

----------


## paul mancuso

Yes, I did. And I don't see how that code will work either.  Here's the error it produces.

Format of the initialization string does not conform to specification starting at index 0. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.

Source Error: 


Line 20: X.dbEditADDSQL = AddSQL
Line 21: X.dbBookMark = "FLname;0"
Line 22: X.ASPdbNET()
Line 23: End Sub
Line 24: </script>

Whatever, this means....
Also, to what database do the tables FLName and Address belong.  Maybe you should send to me, too.  I know saving to multi tables works with aspDB Classic because I was there and did that.  Did you look at the debug SQL string I posted?  Should that string have 2 INSERT INTO statements? The behavior seems to indicate that the first data element of the second table 
(ProectLocation)is unknown. Strange, it's present in the SQL statement! What do you think?

----------


## Frank

did you check out the special example - edit two tables section in the Edit manual page. I tried that and works OK.

Frank

----------


## paul mancuso

Frank,

We have been testing these new functions and have come up with a recommendation.  the .dbEditGridAdd function needs a parameter telling it how many rows to default to.  Currently the default is the number of rows shown on the screen grid.  In most cases, in add mode, a single record would be the default. In edit mode, the number of rows edit should be the number of rows shown on the grid. 

Also, it would be a good idea to include in the manual, exactly how the functions .dbeditadd and .dbeditgridadd are related.  We needed to isolate them 
using IF/Then statement(s).  

We need this capability ASAP.  

thanks, Paul

----------


## Frank

try intercept the gridedit buttonand change the pagesize.

>>how the functions .dbeditadd and dbeditgridadd are related ??

Explain more.. one function is normal add andone is grid add.

FK

----------


## paul mancuso

I'll try the isolation thing..

As for the .dbEditAdd and .dbEditGridAdd. Without using an IF/Then statement to seperate them the UPDATE/INSERT sql string is not correct.
That is, if the functions are not isolated the INSERT SQL will default to the .dbSQL string.  
The  .dbEditGridUpdate appears to work correctly with out isolation.

Programming with the If/Then fixes the issue but it should be resolved at the system level so the applications programmer can simple use the .dbEditAddSQL and .dbEditGridAddSQL functions as designed....

Paul

----------


## Frank

MMMnn.. still not clear. Please exaplin more before I ask for a NWIND sample. Describe the case when it is NOT working.


FK

----------


## paul mancuso

OK,

I created a NWind example, actually SQL Server example and sent it to your email address.  You'll need to change the DNS for your system..

Paul

----------


## Frank

I looked at the test file again and I see the gridaddsql is not correct - fixed. Please try the patched dll (in your ftp dir).
ALso, let me know how it goes in controlling the Edit Add "Rows". I see the reson there is "legi". If it is difficult to set the pages size when interccept the "gridadd" click then I can add a property to set the "GridAddRows". 


FK

----------

