hiding blank fields on database result table. (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

logoman -> hiding blank fields on database result table. (8/10/2002 11:37:40)

Hi everyone, maybe I didn' t explain myself clearly and I didn' t get the right answer but here is what I like to make it work like my last question on the NULL Statement. To make it simple, can we hide the blank fields on the database table by using DRW? Please HELP!!!



no_mac_jack -> RE: hiding blank fields on database result table. (8/10/2002 14:02:08)

So is this only displaying one record at a time and you want it to only display the column (field name & value) if the value is not null? I think you could do that with a some tweaking...if that' s what you' re after. [&:]

~no_mac_jack



logoman -> RE: hiding blank fields on database result table. (8/10/2002 14:35:08)

Hi no_mac_jack, I have a form which has several fields like: PageNumber, PartNumber, Comments, FirstName, LastName. All of these fields are on one row in a form and these fields are repeated again for up to 10 rows of same stuff as above. I then created a database table by using Database results wizard so that I can see the results online. Now is the problem. On the form as I said I have 10 rows of the same field names, repeated 10 times. when someone fills in the form and does not fill in all 10 rows, let say only the first 2 rows and then submits the form, I then go to the database table to see what' s in there and I see only the first 2 rows of records filled in and the rest of the fields are empty but also there. Now, is it possible to hide those empty fields on the database table so that it only shows the fields with information in them? I know this is long but I just wanna try to explain what I need and I have been strugling with this for a long time. Thanks in advance.
KN



KeithG -> RE: hiding blank fields on database result table. (8/10/2002 14:40:38)

logoman,

I think the problem lies more with building the table in such a way as to accept up to 10 records. The way I tend to approach this now that I have done it a few times is to limit it to one instance, have them submit and ask if they want to add another. This can be presented in a variety of ways that make it just as easy as being able to submit 10 at a time. Maybe consider a simple redesign on the table?

Keith



no_mac_jack -> RE: hiding blank fields on database result table. (8/10/2002 15:16:40)

Ahhhh...it makes sense now. [:D]

I think, like keith said, you may want to redesign your table(s). A simple book on database normalization might help. Basically, you want to ' normalize' the database so that there is little or no repeated data.

For example, rather than writing out the peoples' names for each record (like you have it now), you would make a separate table for customers (they may not be customers in your case, but it' s just an example). The table could be as simple as this...

Table Name: customers
Fields:
- cust_ID autoNumber Primary Key
- firstName
- lastName

Then, let' s say you have a parts order form. Just for this example, we' ll say that they can only order one part per order. So, you make an orders table...

Table Name: part_orders
Fields:
- order_ID autoNumber Primary Key
- cust_ID
- partNumber

So now you' ve got cust_ID representing all of the customer' s data. This saves you fom the problem of them changing their name or info. after they' ve already made some orders in the databse (which would wreak havoc on everything). I can' t explain this nearly as well as a good book, so maybe just check your local library and take a quick look at some of the books. It will really help.

With this kind of setup, you can pull all of the info together for display with some sort of SQL join. Here' s one way of combining the customer data with order data...

SELECT cust.firstName, cust.lastName, part_orders.partNumber FROM customsers AS cust, part_orders WHERE cust.cust_ID = part_orders.part_ID

Sorry to ramble, but I think a database re-design would really help avoid predicaments like this. In an ideal database, all of those 10 fields that people add in your case would be records in a seperate table.

Well, I' m not sure what that does for you. I need to go rest my weary fingers now. [:p] Good luck with whatever you decide on!

Again, sorry to go a little overboard. [:(]

~no_mac_jack



logoman -> RE: hiding blank fields on database result table. (8/10/2002 15:53:07)

HI, You know my supervisor thought it would be possible to show all records on one database results table as is but eliminate the empty fields bacuse there is no need to create more tables... there is only one person who enters into the form everyday. we use intranet and the database table will be viewed by my supervisor and he wants to see the entered records not the empty fields included. It also makes sense because if he submits 20 times and each time he fills in only 2 rows of info. then the database table will be REALLY long bacause it also contains those empty fields from each submission. that means there are 40 entered records plus 160 empty records!aren' t there codes that I can insert into the database results table to make it not show?
Thanks,
KN



no_mac_jack -> RE: hiding blank fields on database result table. (8/10/2002 16:29:24)

Doh! I think I misunderstood again.

So each of the 10 rows in your form is inserted as a new row in the database even if all of the fields are empty and you don' t want to display those rows? That makes more sense.

Assuming the above is accurate, couldn' t you have only the filled in rows inserted by filtering the form data when it is submitted or are you using the FP component to store the data?

Anyway, here' s a way to only display a record from the database if it is filled out (you need to put the page on the spooky diet first)... 

<!--#include file=" ../_fpclass/fpdbrgn1.inc" -->
  [color=red]<% if (FP_FieldVal(fp_rs," PageNumber" ) <> " "  AND FP_FieldVal(fp_rs," PartNumber" ) <> " " ) then %>[/color]
    <tr>
      <td><%=FP_FieldVal(fp_rs," PageNumber" )%>
      </td>
      <td><%=FP_FieldVal(fp_rs," PartNumber" )%>
      </td>
      <td><%=FP_FieldVal(fp_rs," Comments" )%>
      </td>
      <td><%=FP_FieldVal(fp_rs," FirstName" )%>
      </td>
      <td><%=FP_FieldVal(fp_rs," LastName" )%>
      </td>
    </tr>
  [color=red]<% end if %>[/color]
<!--#include file=" ../_fpclass/fpdbrgn2.inc" -->


Not knowing how your data works, I only checked two fields with the if statement, but you can add more fields using the same format. That' s the messy way to do it, but I hope that' s what you' re looking for.

Good luck!

~no_mac_jack



logoman -> RE: hiding blank fields on database result table. (8/10/2002 16:53:35)

No mac jack, this is between you and me only:)
What do you think if I send you the html code of my form and the database results table by DRW and see how I did and need to be changed? I don' t mind paying you to help me with this problem. I guess I just wanna make my life easier! Do you have email address? I can also email you the screen shots of what I have and what needs to be changed. It sounds too much but actually it' s not.
Thanks, logoman



no_mac_jack -> RE: hiding blank fields on database result table. (8/10/2002 17:03:42)

That' s fine with me becuase I feel like I' m only making things worse trying to slowly guess what exactly it is you' re working with since I can' t see it. E-mail is in my pofile (click the little paper and head icon under my user name).

Thx!

~no_mac_jack



Spooky -> RE: hiding blank fields on database result table. (8/10/2002 17:41:14)

Do you mean this? 

       
<td>
<%
If FP_FieldVal(fp_rs," PageNumber" ) <> " "  then
response.write FP_FieldVal(fp_rs," PageNumber" )
end if%>
</td>
<td><%
If FP_FieldVal(fp_rs," PartNumber" ) <> " "  then
response.write FP_FieldVal(fp_rs," PartNumber" )
end if%>
</td> 

etc.....



no_mac_jack -> RE: hiding blank fields on database result table. (8/10/2002 17:58:11)

*sigh*

I thought that' s what he said he didn' t want when you posted it in that other thread. [&:] I think I' m gonna have to see these screenshots. [:j]

~no_mac_jack



Spooky -> RE: hiding blank fields on database result table. (8/10/2002 18:00:03)

I remain slightly confused [&:]



no_mac_jack -> RE: hiding blank fields on database result table. (8/10/2002 18:28:26)

Moi aussi.

~no_mac_jack



logoman -> RE: hiding blank fields on database result table. (8/10/2002 18:48:20)

Yes I just sent a couple of screenshots + html codes to no mac jack. Hope this is the end of the problem.



no_mac_jack -> RE: hiding blank fields on database result table. (8/10/2002 20:25:03)

Got it! [:D]

Oh, my...It' s not as easy as I though it would be. For the others, let me explain what we' ve got...

The form as 20 rows of fields...each row has identical fields except for a number appended to the name of each field (increments with each row).

Now, each time the form is submitted, it only inserts one record. Sooo...each record has 181 fields. I thought that each of the 20 rows on the form was getting inserted as a separate record so the code I posted last time would work...but it won' t in this scenario. I have a hectic night ahead, but if it' s ok w/logoman, I' ll post these screenshots to my server so you can take a look.

IMHO, it would be best to redesign this beast so that each row in the form is inserted as its own record and that only form rows with data are inserted in the database.

Just my $0.02.

~no_mac_jack




KeithG -> RE: hiding blank fields on database result table. (8/11/2002 0:00:32)

That is what I suspected when I suggested the table redesign. Only because I had a project when I started out that led me to believe that was the way to do it.......it is not. As I said in an earlier post, I think if you make the table accept the details of one transaction vs 10 per record, then all you have to do is make the entry interface comfortable for the user and you will do everyone a favor. If you would like some suggestions on how that is possible, I would be glad to assist. Good Luck.

Keith



Spooky -> RE: hiding blank fields on database result table. (8/11/2002 1:17:01)

Normalisation. Ouch :-)



logoman -> RE: hiding blank fields on database result table. (8/13/2002 11:18:10)

Hi everyone, I really appreciated that NMJ looked at my codes. Reading over the messages from the others I still don' t know What KeithG' s table redesign means? You know I tried to make the update form really simple by keeping only 1 row of field and eliminating the other 19 rows of fields and make the form redirected itself after the submit button is clicked, this way I can have one record per row without having to worry about having blank fields shown on my database table. But they don' t like this beause someone will have to click the submit button each time the update form is filled. They' d rather fill in the form as many as they can up to 20 rows and click submit only once. I hope this explained my problem.
Thanks



KeithG -> RE: hiding blank fields on database result table. (8/13/2002 11:38:53)

Logoman,

I understand what you are trying to do after viewing the screen shots, pretty much what I thought you were doing. My concern is that you may run into a situation after you have built your project this way that will make it difficult to update or delete these records if you need to. You will have to update all 20 rows each time. Also, how will you find the record you want to edit or delete? One last question, I assume the field names are PageItemNumber1, PageItemNumber2, etc then PartNumber1, PartNumber2, etc and so on down the line for each field. How are you getting them to display in rows like that if they each have the same id number and are seemingly in one long row in your database? Curious about that.

Keith



logoman -> RE: hiding blank fields on database result table. (8/13/2002 12:02:44)

you know I think I don' t have to worry about update or delete records for now. I only have to add a search form to the database table so that they can check by date of what was entered on a certain date, for example. This datbase table will be deleted every 2 weeks. You are right that in order to avoid blank fields they have to enter all 20 rows of field names. As I said we don' t worry about updating or deleting, Do you think we can make it work?



KeithG -> RE: hiding blank fields on database result table. (8/13/2002 12:27:17)

I am still unsure how you are displaying the data in rows, are you building a seperate data region for each of the 20 parts of that record?



logoman -> RE: hiding blank fields on database result table. (8/13/2002 12:50:25)

Keith,
basically I have an update form on one page. I created another page that holds the results of that update form so I use DRW from within Frontpage. On my screenshots you see that there are 2 different pages, one to the update form and one to the page to see the results. The bottom line is that I like to see the databse results table to look like the update form except that if any of the field names on the update form are not filled in when submitted will not be included on the database results table. Did I answer your question?



no_mac_jack -> RE: hiding blank fields on database result table. (8/13/2002 14:55:31)

Well, I e-mailed you an updated form page that used a loop to display the rows to save you some coding (and sanity). Now I found a scalable form that I made a while back. You can choose how many rows you want. The code is posted here.

Also, a while back I helped someone with a similar setup where they were adding a variable number of records at a time, so I took the code at the bottom of this (using arrays with AddNew)ASPFAQ and modified it to work with the scalable form. I started modifying it to fit your DB, but I got a really strange error and I don' t have time to work it out right now. So, here' s the code for the processing page. I' m hoping either you can debug it or somone else can take a look for you. It is coded to make sure that a form row has both a PageItemNumber and a PartNumber before it inserts it. This means no blank rows in the database! [:D]

Also, here' s the screenshot I was sent of the database results page. To see the form, just run the scalable form that I made because it looks exactly the same.

Hope that gets you closer to a solution!

~no_mac_jack



logoman -> RE: hiding blank fields on database result table. (8/13/2002 16:08:43)

NMJ, thanks for the codes I will copy and work on that one to see how that goes and let you and all my helpers know later.
Thanks.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
9.423828E-02