Getting a count of records and populating them 1

[chinedu]

I posted this same code earlier but I posted for a different reason which I later worked out.

This time, it is same code but for different reason.

I have a database that has 2 ids.
One Id is called a system ID and the other is called Geo_ID.

The BlockID is unique but the system_ID is not.

The way the database is structured is:

System_ID has a field called fullname.

It is almost like a category.

Within this fullname is another field called blocks.

The fullname name can have one or more blocks and each block has a unique blockID.

The layout is usually like this:

System_ID Fullname Blocks BlockID
1 James Str Martin Dr 1
1 James Str Michelle Rd 2
1 James Str Karen Circle 3
2 MLK Dr Grayhound Way 1
2 MLK Dr Anderson park Dr 2

etc, I hope you get the picture now.

There are 3 things I am trying to do now.

One, If we query the database say get fullname, and the name is called James Str, since there are 3 blocks under James Str, we want to able to: Number of Blocks ____

The next thing we would like to do is say:
If we click on one of the blocks, we would like to say:

Block 1 of 3 (since in this case there are 3 blocks)

Lastly, we would like to populate 5 additional input textboxes with the value of each block.

So far, the only thing I have been able to do is populate 1 input textbox with system_Id.

Any help on any of these 3 issues would be greatly, greatly appreciated.

You guys have really been great here on this forum.

Here is code I am working with so far.

     <td align="left"><font size="2" color="Black" face="Arial"><b>Project Name:</b></font></td>
        <td><select name="TxtprojectName" size="1" onChange="document.sidewalk.TxtsysID.value = this.value;">
         <option selected>&lt;Choose One&gt;</option>
       <%
       dim Conn,SQLstr
       Function OpenDBFConn(Path)
       Dim Conn: Set Conn = CreateObject("ADODB.Connection")
        Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & Path & ";" & _
                   "Extended Properties=""DBASE IV;"";"
       Set OpenDBFConn = Conn
       End Function

      Dim DBConn
      Set DBConn = OpenDBFConn("c:\arcgis")
      'Open recordset from inventory table

      Dim rs
      Set rs = DBConn.Execute("SELECT * FROM cline.dbf order by system_id")

          While not rs.EOF

       %>

         <OPTION value="<% =rs("system_ID") %>"><% =rs("full_name") %></OPTION>

       <%

         rs.MoveNext

        wend

        rs.close

        set rs=nothing
       %>
      </select></td>
     <td align="right" nowrap><font size="2" color="Black" face="Arial"><i>System ID:</i></font></td>
     <td><input type="text" name="TxtsysID" id="TxtsysID" size="5" value=""></td>

 

[Tarwn]

If all you going to display is the one row with the fullname and number of blocks, thats going to be a pretty empty page. I mean if your searching from the select box you will only ever have one match...?

I started writing some example code but then I realized tat your description of your database doesn't make much sense. Is all that in one table or are there two seperate tables?

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
Help, the rampaging, spear-waving, rabid network gnomes are after me!

 

[chinedu]

All that is on one table, unfortunately.

The table is actually the "main" table that contains information from various other tables.

This table doesn't belong to our group and so the "owners" of the table can only allow us a pull access to their table.

 

[Tarwn]

Let me reword this and see if I have it right:
You want to be able to select a full_name from the dropdown and automatically make the number of blocks appear, populate 5 inputs, etc?

You could probably build a set of javascript arrays to hold this data and supply it as needed. My inclination would be to do this on two pages (one for selection of full_name and block, one for editing) using a refresh on the first page to populate a second dropdown (blocks).

First Page: So the first dropdown would be filled as usual. onChange it would change the form action field to the first file name and then submit the form. The second dropdown would be built in an if statement, if a SystemID is passed then select blocks from the DB, otherwise leave it empty. Maybe also build a hidden field to hold the number of blocks available for the selected System_ID. You could even add a span below the block dropdown and use javascript to change it's innerHTML to "[javascript: value of selected item from blocks] of [ASP: number of blocks that were used for options]".

Second Page: Use the passed System_ID and BlockID to create your editable form, easy peasy one two threesy.

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
Help, the rampaging, spear-waving, rabid network gnomes are after me!

 

[chinedu]

It sounds too easy because you have a way of making them look easy.
But, I will give it a run and see if I can handle it.

 

[Tarwn]

Let me know if your run into any issues they I glossed over to lightly, I think I wrote that before hitting my morning coffee quota

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
Help, the rampaging, spear-waving, rabid network gnomes are after me!

 

[chinedu]

Given the code I posted earlier, can you please tell me how to proceed with it?

I think the biggest challenge will be getting the number of BlockIDs associated with each systemID,
For instance, I am tempted to say:
for each systemID, give me the blockIDs associated with it and give me a count of the blocks.

If you can assist with getting to that point from the code I already poste, it will be greatly appreciated.

 

[Tarwn]

I'm still not entirely sure what you were aiming for with the previous code. It seems like your trying to have System selection, block selection, and editing inputs all in one page and populated dynamically without the browser submitting back to the server. If tats te case then your going to have to store all the recordset informaiton in javascript arrays to be able to access it. Again, I would not suggest doing it in this manner because it not only makes it more complicated then it needs to be but it also will make things slower because your basically passing all the table data to the client instead of just the data that is necessary (ie, list of systems, list of blocks for one system, list of other data for selected block vs list of all posible data for all possible blocks for all possible systems).

You would need to work out a method to store the data in javascript arrays in a way that you could access it. Once that is done, the method to get counts of blocks would be simply to do counts of array values. Your best bet is probably to have a three tier array where the outer dimension is the index for the select (systems) and then inside each system you have an array of blocks, which are each an array of the data for that block. Then you would just use the onChange event of the System select to populate inputs and such based on that array. The block count or a specific system would be system_array(frmName.txtProjectname.SelectedIndex).length or in english, the size of the 2nd-tier array for the selected system.

I still think that breaking this up into two pages (or even three) would be a better way to go. Your first pag would be something like

<%
Option Explicit

Dim objConn, sql_system, sql_blocks, rs_sytstem, rs_blocks
Dim hasSelSystem, selSystemId, block_count

'if a system was sleected, set our flag and grab the number
If Request.Form("selSystem") <> "" Then
   hasSelSystem = True
   selSystemId = Request.Form("selSystem")
Else
   hasSelSystem = False
   selSystemId = -1
End If

'setup connection
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Your Connection String"

'get System info
sql_system = "SELECT DISTINCT System_ID, Fullname FROM YourTable ORDER BY Fullname"
Set rs_system = objCOnn.Execute(sql_system)

'get block info if a system was selected
If hasSelSystem Then
   sql_blocks = "SELECT BlockID, Blocks FROM YourTable WHERE System_ID = " & selSystemId & " ORDER BY BlockID"
   Set rs_blocks = objConn.Execute(sql_blocks)
End If
%>
<html>
<body>
<form method="POST" action="Editblock.asp" name="frmSelection">
   <!-- When a system is selected, submit back to this page for block selection -->
   Select System: <select name="selSystemId" onChange="frmSelection.action='SelectBlock.asp';frmSelection.submit();">
      <%
      'queue up system rs
      If Not rs_system.EOF Then rs_system.MoveFirst

      'output a generic selection if there wasn't one yet
      If Not hasSelSystem Then
         Response.Write "<option value=""""> [ Please Select System ] </option>"
      End If

      'output system options based on whether or not one was selected yet
      Do Until rs_system.EOF
         If hasSelSystem And rs_system("System_ID") = selSystemId Then
            Response.Write "<option value=""" & rs_system("System_ID") & "" selected>" & rs_system("Fullname") & "</option>"
         Else
            Response.Write "<option value=""" & rs_system("System_ID") * "">" & rs_system("Fullname") & "</option>"
         End If
         rs_system.MoveNext
      Loop
      %>
   </select><br/>

Select Block: <select name="selBlockId">
   <%
   'if a System hasn't been selected yet show empty option only
   If Not hasSelSystem Then
      Response.Write "<option value=""""> [ No System Selected ] </option>"
   Else
      'queue up the blocks rs
      If Not rs_blocks.EOF Then rs_block.MoveFirst

      'output generic option
      Response.Write "<option value=""""> [ Please Select Block ]"

      'loop through blocks rs
      block_count = 0
      Do Until rs_blocks.EOF
         block_count = block_count + 1
         Response.Write "<option value=""" & rs_blocks("BlockID") & """>" & rs_blocks("Blocks") & "</option>"
         rs_blocks.MoveNext
      Loop
   %>
   </select><br/>
   <input type="hidden" name="blockCount" value="<%=block_count%>">
   <input type="submit" value="Edit Selected Block">
</form>
</body>
</html>

In this example I assumed that the current page was called SelectBlock.asp. The first time you load it you get a list of options in the System selection and no options in the block selection. After pick an option in the System selection it automatically submits the page back to itself so it can fill the blocks selection correctly. If you then change the system again it will continue to submit back to re-fill the blocks selection. Selection of a block and hitting the submit button will send the selected System_ID, BlockID, and block count to the 2nd page (I called it EditBlock.asp). On that second page you would select from your database for those selected system and block ids and display the one returned record in text inputs for editing. You could also display at the top that this is block x of y by:

Response.Write "Block " & Request.Form("selBlockID") & " of " & Request.Form("blockCount") & "<br>"

I wrote this code on the fly so it may contain some minor syntac errors here and there andit also doesn't have clean up code (closing connection, recordsets, etc), but it could provide a base for writing the first page then you would just need to write the second page (would should be a rather simple selection and display in text inputs).

You will still need a third page, as well, for processing the changes they made, but you would have needed that for your original effort as well.

-T

 

[chinedu]

This is very, very kind of you!
Thanks very, very much, Tarwn.
This is really appreciated.

 

[Tarwn]

Not a problem, I realize sometimes reading an explanation is harder to follow then reading example code, especially when it is me writing the explanation
Glad you found this assistance helpful,

-T

 

[chinedu]

I wasn't sure if I should post this separately but didn't risk annoying the moderators.

This is page2 of the code Tarwn helped out with.

Tarwn helped with page1 of it.

This code works fine as far as doing what I wanted it to do.

However, the only issue that I have is the layout.

I have been struggling to re-arrange it but to avail.

I would like the layout to look like this:

Fullname:______________ System ID:______ Project No:____
From/At:______________ To:________ No. of Segments:____

Then the other formats can stay as they are:
So far it isn't working.

Any help would be greatly appreciated.

Sorry about the long code:

<%
' from the prior page, we get the requested SystemID:
systemID = Request("TxtsysID")
full_Name = Request("TxtprojectName")

       dim Conn,SQLstr
       Function OpenDBFConn(Path)
       Dim Conn: Set Conn = CreateObject("ADODB.Connection")
        Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & Path & ";" & _
                   "Extended Properties=""DBASE IV;"";"
       Set OpenDBFConn = Conn
       End Function

      Dim DBConn
      Set DBConn = OpenDBFConn("c:\arcgis")
      'Open recordset from basins table
      Dim rs
      Set rs = DBConn.Execute("SELECT Full_Name, geo_oid FROM cline.dbf where system_ID =" & SystemID)


' How many rows are there?
BlockCount = UBound(rows,2) + 1

' okay, ready to generate form...
%>

<FORM Name="MainForm" Method=Post Action="sam2.asp" onFilt="return validateForm(this);">
<INPUT Type=HIDDEN Name="blockCount" Value="<%=blockCount%>" >
<INPUT Type=HIDDEN Name="systemID" Value="<%=systemID%>" >

<table CellPadding=5>
<tr>
    <td align="right">System ID:</td>
    <td><%=systemID%></td>
    <td align="right">Project No:</td>
    <td><input type="text" size=5 name="projID" value=""></td>
</tr>
  <tr>
    <td width="101"><font size="2" color="Black" face="Arial"><i>From/At:</i></font></td>
    <td><input type="text" name="TxtfromAt" size="20" maxlength="20" value=""></td>
    <td align="right"><font size="2" color="Black" face="Arial"><i>To:</i></font></td>
    <td><input type="text" name="Txt_To" size="20" maxlength="20" value=""></td>
    <td align="right"><font size="2" color="Black" face="Arial"><i>No. of Segments:</i></font></td>
    <td><%=blockCount%></td>
  </tr>
</table>
<hr>
<table CellPadding=5>
<tr>
    <td colSpan=10>
    Processing block <span ID="ID_curBlock">1</span>
    of <%=blockCount%> blocks.
    </td>
</tr>
<%
' now generate hidden divs for each row of data:
For row = 0 To UBound(rows,2)
    full_Name  = rows(COL_NAME,row)
    geo_oid   = rows(COL_BLKID,row)
%>
    <INPUT Type=Hidden Name="Row_<%=row%>" Value="<%=blockID%>">
    <INPUT Type=Hidden Name="Fullname_<%=row%>" Value="<%=fullName%>">
    <tr ID="ROWA_<%=row%>" style="display: none">
        <td>Block ID:</td>
        <td><%=geo_oid%></td>
        <td>Fullname:</td>
        <td><%=full_Name%></td>
    </tr>
    <tr ID="ROWB_<%=row%>" style="display: none">
        <td>From:</td>
        <td><input type="text" size=10 name="from_<%=row%>" value=""></td>
        <td>To:</td>
        <td><input type="text" size=10 name="to_<%=row%>" value=""></td>
    </tr>
    <tr ID="ROWC_<%=row%>" style="display: none">
        <td>Direction:</td>
        <td><input type="text" size=5 name="direction_<%=row%>" value=""></td>
        <td>To:</td>
        <td><input type="text" size=5 name="directionTo_<%=row%>" value=""></td>
    </tr>
<%
Next ' next row
%>
</TABLE>

<INPUT Type=Button Name="prior" Value="<<prior block" onFilt="goBlock(-1);">
&nbsp; &nbsp; &nbsp; &nbsp;
<INPUT Type=Submit Value="Process Everything!">
&nbsp; &nbsp; &nbsp; &nbsp;
<INPUT Type=Button Name="next" Value="next block>>" onFilt="goBlock(1);">
</FORM>

<SCRIPT Language="JavaScript">
// check one set of block info for validity
// This simple minded check just looks for blank values
// You get to fix it to do the right thing
//
function checkOne(which)
{
    var frm = document.MainForm;

    var fldFrom = frm.elements["from_" + which];
    var fldTo   = frm.elements["to_" + which];
    var fldDir  = frm.elements["direction_" + which];
    var fldDirTo = frm.elements["directionTo_" + which];
    if (fldFrom.value == "" || fldTo.value == ""
          || fldDir.value == "" || fldDirTo.value == "")
    {
        alert("Must fill in all info for block " + (1+which));
        return false;
    }
    return true;
}

// and ditto this simple minded form validation
//
function validateForm(frm)
{
   for (var block = 0; block < <%=blockCount%>; ++block)
   {
       if (!checkOne(block)) return false;
   }
   if (frm.projID.value == "")
   {
       alert("You must give a project id!");
       return false;
   }
   return true;
}

var curBlock = 0;

// moves from blockID to blockID, hiding one and displaying next
function goBlock(whichWay)
{
    var frm = document.MainForm;

    var newBlock = curBlock + whichWay;
    if (newBlock < 0 || newBlock >= <%=blockCount%>)
        return; // can't go past ends

    // warn if all info is not filled in
    if ( whichWay != 0 ) // except when starting up
    {
        if (!checkOne(curBlock)) return;
    }

    var rowa, rowb, rowc;
    // hide current row...
    rowa = document.getElementById("ROWA_" + curBlock);
    rowb = document.getElementById("ROWB_" + curBlock);
    rowc = document.getElementById("ROWC_" + curBlock);
    rowa.style.display = "none";
    rowb.style.display = "none";
    rowc.style.display = "none";
    // move to requested row
    curBlock = newBlock;
    // and show it
    rowa = document.getElementById("ROWA_" + curBlock);
    rowb = document.getElementById("ROWB_" + curBlock);
    rowc = document.getElementById("ROWC_" + curBlock);
    rowa.style.display = "inline";
    rowb.style.display = "inline";
    rowc.style.display = "inline";

    // and update which blockid is being worked on:
    document.getElementById("ID_curBlock").innerText = (curBlock+1);

    // disable prior/next if can't move more in that direction
    frm.prior.disabled = (curBlock == 0);
    frm.next.disabled = (curBlock >= <%=blockCount-1%>);

}

// and to get things started, call goBlock but don't move counter:
goBlock( 0 ); // with 0, nothing moves

 

[Tarwn]

There shouldn't be a problem posting this seperately since it is a very differant question despite being associated to the page we were working on in this thread. It should help you get more responses as well so I'll hold off until you repost so you don't have to follow two threads on the same subject

-T