Month: July 2013

In our last article we designed a menu option that allowed us to see the content of a particular survey. The information was displayed to the user in an HTML table and in some cases if there are two many fields in the file then the table was large and not very usable. In this example we will take this code a stage further and allow the user to enter the select statement that they would like to use to present the data.

The first thing we need to do is to change the way the function that creates the table is called. It currently looks like this,

Read more

In this article we will look at how we can display some data in a list as a menu item action. Let us pretend that we need to see all the records in a particular survey. At this stage , we will just display it , but in future articles we will show how you could update them.

So, what do we need , well we need our menu system from previous examples and we will place the code under the menu option B.

Select Case Option1Menu
	Case = {mBack}
		Goto pMainMenu
	Case = {mOption1_OptionA}
		message.Label.Inserts["MESSAGE"] = MakeIFrame("http://www.smarterDataCollection.com")
	Case = {mOption1_OptionB}
		message.Label.Inserts["MESSAGE"] = MakeDataList(gFMROOT,gSQLServer,gUserDB,"*","")
End Select

As you can see we are using our “User” survey that we created in [This article], we are passing in the fmroot folder name and the name of the SQL server. The last two paramaters are the fields to show ( astrix means all ) and then a where clause, Blank means all records. So what does the function look like,

Function MakeDataList(gFMROOT,gSQLServer,sDB,sFields,sWhere)
	Dim oConnection , oRecordset,oField
	Dim sHTML
	
	Set oConnection = CreateObject("ADODB.Connection")
	oConnection.ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrRdbDsc2;Location='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" + sDB + ";Data Source=" + gSQLServer + "';Initial Catalog=\\" + gFMROOT + "\Master\" + sDB + "\" + sDB + ".mdd;MR Init Category Names=1;MR Init Project=" + sDB
	oConnection.Open()
	
	If ( oConnection.State = 1 ) Then
	
		Set oRecordset = CreateObject("ADODB.Recordset")
		
		oRecordset.Open("select " + sFields + " from vdata " + sWhere ,oConnection,3,1)
			
			sHTML = MakeHTMLTableFromRS(oRecordset)
				
		oRecordset.Close()

		Set oRecordset = Null
		
	End If

	oConnection.Close()
	
	Set oConnection = Null

	MakeDataList = sHTML
	
End Function

So what does MakeDataList actualy do , well the first thing , after the declarations is to connect to the Data Collection survey. This is done with a standard ADODB connection. Next if the conection is open we create a recordset based on the fields and where clause that we pass in. In out next article we will see how we can paramatarize this.

After we have opened the recordset we decide what we want to do with the records we have. In this case we are going to convert them to a HTML table.

Function MakeHTMLTableFromRS(oRecordSet)
Dim sHTML,oField

sHTML = "" + mr.cr
sHTML = sHTML + "" + mr.cr

For Each oField in oRecordset.Fields
	sHTML = sHTML + "" + mr.cr
Next

sHTML = sHTML + "" + mr.cr

Do while oRecordset.EOF = false and oRecordset.bof = false
		sHTML = sHTML + "" + mr.cr
			For Each oField in oRecordset.Fields
				sHTML = sHTML + "" + mr.cr
			Next
		sHTML = sHTML + "" + mr.cr
	oRecordset.MoveNext()
Loop

sHTML = sHTML + "
" + oField.Name + "
" + MakeSpaceIfBlank(oField) + "
" + mr.cr

MakeHTMLTableFromRS = sHTML

End Function

So what does this new funtion do. First off it loops the names of the fields in the recordset and places them on the first row of the table, next it loops through all the records in the table and displays the content of each field in its own cell. Finaly it closes the table and returns the created HTML.

The key thing to notice here / think about here is that we are taking something and converting it to an HTML item. Why do we need to think about this , well perhaps rather than produce a html table of the data you wanted to produce a dropdown of one of the fields. You might say why would you do this and in later articles you will see how , but you could rather than render an HTML dropdown you could render a Dojo spinner.

Anyway back to our table. You will see in the makeHtmlTableFromRS function we call a function MakeSpaceIfBlank the content of that function is as follows.

Function MakeSpaceIfBlank(oField)
Dim sValue
	
	sValue = CText(oField.Value)
	if ( sValue = "" ) Then sValue = "."
	
MakeSpaceIfBlank = sValue
End Function

This function is simple , all it does is to convert the field value passed in to a string and if the value is nothing convert it to a full stops. In this example, it just makes the table look a little neater and could eaily convereted to return any value you like. When you run the script and select option B this is what you should see.

And there you have it, code that can be used to show on a web page the content of a survey. In the next article we will see how we can possibly take this a stage further and allow the user enter the field names and where clause.

So 7 months into the year and this is the first post , how bad is that 🙂 … Anyway back on the posting again which is good. At the end of last year i started some posts titled “I am more than just a survey tool” and i covered everything you needed to know about how to design a Menu system. Now the next set of articles will show you the code i use to display content in menu system. This article assumes you have followed the previous examples and you have a menu system like this ,

with a sub menu of this

The code we add will be added to the sub menu “Option A”. We will make a function ( MakeIFrame ) that will return some HTML code so our menu code will look like this,

Select Case Option1Menu

	Case = {mBack}
		Goto pMainMenu
	Case = {mOption1_OptionA}
		message.Label.Inserts["MESSAGE"] = MakeIFrame("http://www.smarterDataCollection.com")
	Case = {mOption1_OptionB}

		message.Label.Inserts["MESSAGE"] = "Option B"
End Select

So what will the function do. Well it will point us to another web page and display it in our page with the use of an IFrame. The html Iframe is perfect for this and once you know how to create one you can do many things with them as you will see in later articles. So what does our code look like?

Function MakeIFrame(sURL)
Dim sHTML
sHTML = "<iframe src="" height="240" width="320" frameborder="">"
sHTML = replace(sHTML,"{URL}",sURL)
MakeIFrame = sHTML
End Function

This function is simple , but highlights a powerfull feature that we will use over and over again. Using the replace function we can take a pre-defined html string that we have perhaps designed in out web tool and then replace it with the code that we like , so in our case , we have take the iframe code that will create a windows 1024 pixels wide by 600 high. The window has no border so it will look as if it is part of our original page and it will show the URL that we pass into it. After the replace function has worked we will end up with a string like this,

""

And when we run the code , we will see our page connected to our menu system.

And that is it for this post , but whats next? Well , the use of an iframe is good, but how good , showing a static page is ok , but what about surveys , or forms to collect data , how can we do that. If we use a survey , how can we keep calling the same record? All these questions will be answered in my next post , so watch this space.