# Miscellaneous > Database Programming >  Nulls replaced by blank problem

## alarabie

I have a problem with Tornado version 4.3.25.10.

When adding a record  in an Access database, it puts a blank in all the fields that were not entered on the template instead of nulls.

When you try to update these newly added records it causes the edit masks like SSN and phones numbers to fail because they contain a blank when they should be null and not be checked for edit rules.

This is a new behavior, the DLL I am using in production is from last August and it does not replace the nulls with blanks.

----------


## Frank

Use the following (ACCESS) test code and when you add a new record, it'll display the EditAction like ->

Edit Action SQL = INSERT INTO employees (LastName,FirstName,Title,TitleOfCourtesy) VALUES ('doe','JOhn','sales',Null)

There is no "blank" but instead Null is used.

Dim Add As New tornado.z()
		With Add
			.dbQP = "U=7|D=nwind|Q=employees|ps=-1|gdf=0,1,2,3,4,5|ni=b5,add|bm=employees;0| de=EditAction"
			.dbEditAddFlds = "1,2,3,4"
			.ASPdbNET()
		End With


FK

----------


## alarabie

OK, values are null, but under update if you tab or click in the field it inserts a blank which causes the edit rules to fail.

----------


## Frank

OK.. try this code. add a record with some blanks and the update that. Click the blank input and then update. The debug will show ->

Edit Action SQL = UPDATE employees SET LastName='doe',FirstName='John',Title='sales',Titl  eOfCourtesy=Null WHERE (EmployeeID=13)


Dim Add As New tornado.z()
		With Add
			.dbQP = "U=7|Mode=ty=dh!sysind=t|D=nwind|Q=employees|p  s=-1|gdf=1|ni=b5,add,update|bm=employees;0| de=EditAction"
			.dbEditAddFlds = "1,2,3,4"
			.dbEditUpdateFlds = "fi=0|Ty=RONOUPDATE,1,2,3,4"
			.ASPdbNET()
		End With

show me the key sequence to break the update.

FK

----------


## alarabie

The update is updating with a null, but if I tab in the my SSN or phone number field, the edit rules fail as not meeting the standard edit mask, and the behavior is that the cursor is in the second character of any field you tab or click into. The edit rules do not fail unless you tab thru the field or click in the field, it appears that setting the focus on any blank field forces that behavior of setting the cursor on the second character in the field. 

Your example code does not behave like this, it may be the fact that I am using a custom template for updating, I'll create a custom template for your example and see if I can break it.

----------


## Frank

you are talking about edit rules so you are using Validation also like SS. Please modify the code so that we can break it. Also, you mentioned that when you tab into a field, it goes to the second character? This is new to me.

FK

----------


## alarabie

Change your test code to use the attached update template, you will see the difference in behavior. Create a new record and leave the title blank, update the record and you will see that the cursor goes to the second character when the focus is on the title field.

----------


## Frank

Blank space in INPUT problem Confirmed and fixed... Will check it out more and send you the patch DLL.


Frank

----------


## alarabie

The new DLL fixes the blank problem, but has other issues, this dll does not work with an embedded grid.

Here is the code I took from your web site and it also does not work, it does not make a clickable key in the embedded grid.

Sub Page_Load(Source as Object, E as EventArgs)
   Dim pop As String = "<A HREF='/site/tor/manual04/code/eg_MagicCellAdvancedDetail.aspx?Details=^0^' Target='Details' onClick=""window.open('','Details', 'width=500, height=500, left=400, top=400, directories=no, menubar=no, toolbar=no, status=no, resizable=yes')"">^0^</A>"
   Dim E63 As New Tornado.Z()
   with E63
      .dbUnit = 60
      .dbSkin= 15
      .dbMode = "Grid"
      .dbGridDisplayFlds = "CustomerID, CompanyName, ContactName"
      .dbDSN = "Nwind.mdb"
      .dbEmbeddedGridMagicCell = "(;![]^|~/+{})fi=OrderID!mac=" & pop
      .dbGridMagicCell = "(;![]^|~/+{})field=CompanyName|macro=^1^<br>^4^<br>^5^, ^7^<p>^2^<br>^4^<br>^9^!field=ContactName|Macro=SQ  L:SELECT OrderID,OrderDate,RequiredDate,ShippedDate from orders where customerID= '^0^'"
      .dbNameMap = "field=CustomerID|alias=ID,field=CompanyName|alias  =Customer,field=ContactName|alias=Orders"
      .dbEmbeddedGridNameMap = "field=OrderID|alias=ID,field=OrderDate|alias=Orde  red,field=RequiredDate|alias=Required,field=Shippe  dDate|alias=Shipped"
      .dbSQL = "SELECT CustomerID,CompanyName,ContactName,ContactTitle,Ad  dress,City,Region,PostalCode, Country,Phone,Fax FROM Customers"
      .dbTextHolder = "Title=Tornado Demo - Advanced Master with embedded Charting Details|subtitle=Can you image you can do this with 30 lines of code om Notepad and without knowing too much about ASP or ASP.NET?"
      .ASPdbNET()
   End With
End Sub

----------


## Frank

The master slave embedded grid exampl ehas been update as follows. One file instead of two. But the old way should still work. I'll look into that ->

<body onLoad="self.focus()">
<script language="vb" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
   Dim myself = Request.ServerVariables("SCRIPT_NAME")
   If Request.QueryString("details") = "" Then
      Dim pop As String = "<A HREF='" & myself & "?Details=#0#' Target='Details' onClick=""window.open('','Details', 'width=500, height=500, left=400, top=400, directories=no, menubar=no, toolbar=no, status=no, resizable=yes')"">#0#</A>"
   Dim E63 As New tornado.z()
      With E63
         .dbQP = "U=60| S=ty=gold| M=G| D=Nwind| GDF=CustomerID,CompanyName,ContactName"
         .dbEmbeddedGridMagicCell = "(;![]#|~/+{})fi=OrderID|mac=" & pop
         .dbGridMagicCell = "(;![]#|~/+{})field=CompanyName|macro=#1#<br>#4#<br>#5#, #7#<p>#2#<br>#4#<br>#9#! field=ContactName|Macro=SQL:SELECT OrderID,OrderDate,RequiredDate,ShippedDate from orders where customerID= '#0#'"
         .dbNameMap = "field=CustomerID|alias=ID,field=CompanyName|alias  =Customer,field=ContactName|alias=Orders"
         .dbEmbeddedGridNameMap = "field=OrderID|alias=ID,field=OrderDate|alias=Orde  red,field=RequiredDate|alias=Required,field=Shippe  dDate|alias=Shipped"
         .dbSQL = "SELECT CustomerID,CompanyName,ContactName,ContactTitle,Ad  dress,City,Region,PostalCode, Country,Phone,Fax FROM Customers"
         .dbTextHolder = "Title=Tornado Demo - Advanced Master with embedded Charting Details|subtitle=Can you image you can do this with 30 lines of code without knowing too much about ASP or ASP.NET?"
         .ASPdbNET()
      End With
   Else
      Dim ID As String = Request.QueryString("Details")
      Dim E61 As New tornado.z()
      With E61
         .dbQP = "U=61| S=gold| M=GC| PS=-1| Ni=None| D=Nwind.mdb"
         .dbGridMagicCell = "field=0|tag=align=middle,field=2|tag=align=right,  field=4|tag=align=right,field=SubTotal|tag=align=r  ight|macro=#SubTotal:currency#,field=Quantity|tag=  align=right|macro=#quantity#"
         .dbCommonTables = "index=PID,PDesc|sql=Select ProductID,ProductName from Products"
         .dbLookUpFlds = "field=1|keyindex=PID|Lookupindex=PDesc"
         .dbSQL = "SELECT ProductId,ProductId as Description,UnitPrice,Quantity,Discount,UnitPrice*  Quantity*(1 -Discount) as SubTotal from [order details] where OrderID=" & ID
         .dbAggregate = "(~^ :Smilie: Fields=3^5|AggType=SUM|BottomTemp=<tr bgcolor=yellow align=right><td ColSPAN=3><b>Total </b></td><td>[[3:###,###]]<td><td Colspan=2 align=right>[[5:Currency]]</td>|CellFormat=Val"
         .dbTextHolder = "Title=Order Details for Order " & ID
         .dbChartData = "dataset0yValues=#(5)#| dataset0Labels=#1#"
         .dbChartParams = "Type=26| TitleString='Description vs SubTotal Spinning Plot'| titleFont='Arial,14,1'| Archieve=/tornado/Jars/|Width=350| Height=350| 3D=True| textLabelsOn=True| pieWidth=.40| pieHeight=.40| valueLabelsOn=True"
         .dbGridTemplate = "<center>[[Grid]]<P>[[Chart]]</center>"
         .ASPdbNET()
      End With
   End If
End Sub
</script>

----------


## Frank

The old example code has a typo in it ->

fi=OrderID!mac=" & pop

should be 

fi=OrderID|mac=" & pop

I updated the old code in the manual. Shoudl work now.

Use the ONE file method as it is cleaner in my opinion.

Frank

----------


## alarabie

I tried your new example, it still does not create a key you can select, try it with the dll you sent me, the order id is not a keyable field.

----------


## Frank

OK.. let sync the DLL first (I thought I just sent you one).


FK

----------


## alarabie

OK, version 4.5.27.10 confirmed and embedded grid works. I changed the format of all my embedded grid magic cells from using ! to |. 

There is still an issue with this version, the title defined with the .dbtextholder is now left justified instead of centered. It also does that on your example of the embedded grid.

----------


## Frank

not clear about this one.. attached is the screen dump of the code. Confirm the problem and show me where..

	Dim pop As String = "<A HREF='/site/tor/manual04/code/eg_MagicCellAdvancedDetail.aspx?Details=^0^' Target='Details' onClick=""window.open('','Details', 'width=500, height=500, left=400, top=400, directories=no, menubar=no, toolbar=no, status=no, resizable=yes')"">^0^</A>"
		Dim E63 As New tornado.z()
		With E63
			.dbUnit = 60
			.dbSkin = 2
			.dbMode = "Grid"
			.dbGridDisplayFlds = "CustomerID, CompanyName, ContactName"
			.dbDSN = "Nwind.mdb"
			.dbEmbeddedGridMagicCell = "(;![]^|~/+{})fi=OrderID|mac=" & pop
			.dbGridMagicCell = "(;![]^|~/+{})field=CompanyName|macro=^1^<br>^4^<br>^5^, ^7^<p>^2^<br>^4^<br>^9^!field=ContactName|Macro=SQ  L:SELECT OrderID,OrderDate,RequiredDate,ShippedDate from orders where customerID= '^0^'"
			.dbNameMap = " field=CustomerID|alias=ID,field=CompanyName|alias=  Customer,field=ContactName|alias=Orders"
			.dbEmbeddedGridNameMap = " field=OrderID|alias=ID,field=OrderDate|alias=Order  ed,field=RequiredDate|alias=Required,field=Shipped  Date|alias=Shipped"
			.dbSQL = "SELECT CustomerID,CompanyName,ContactName,ContactTitle,Ad  dress,City,Region,PostalCode, Country,Phone,Fax FROM Customers"
			.dbTextHolder = "Title=Tornado Demo - Advanced Master with embedded Charting Details|subtitle=Can you image you can do this with 30 lines of code om Notepad and without knowing too much about ASP or ASP.NET?"
			.ASPdbNET()
		End With

----------


## alarabie

Ran your code and attached the results.

----------


## Frank

that is strange..
1. send you the dll again and try again..
2. if stil the same then try refresh the CSS dir again (just pick one style)

FK

----------


## alarabie

That latest dll seems to work fine, I'll do some more testing.

----------


## alarabie

Found one more problem with version 4.5.27.10.

If you specify a size on an update field, and you set focus on that field, the cursor goes to the second character instead of the first, see the code I included.

Sub Page_Load(Source as Object, E as EventArgs)
Dim Add1 As New tornado.z()
With Add1
.dbQP = "U=7|Mode=ty=dh!sysind=t|D=nwind|Q=employees|p  s=-1|gdf=1|ni=b5,add,update|bm=employees;0| de=EditAction"
.dbEditAddFlds = "1,2,3,4"
.dbEditUpdateFlds = "fi=0|Ty=RONOUPDATE,1,2,3,fi=4|typ=tex|size=15  "

'.dbUserUpdateTemplate =server.mappath("/tornado/templates/versiontest.htm")

.ASPdbNET()
End With
end sub

If you remove the size attribute, the cursor goes to the first character.

----------

