Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ASP > Show record in column in new rows

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 12-30-04, 02:02
mila04792 mila04792 is offline
Registered User
  
Join Date: Dec 2004
Location: Malaysia
Posts: 4
Show record in column in new rows
Hi everyone,


Can any one help me with this? I've developed a system and nearly complete it. In the report part, I found that I need to change the existing report.

-------------------------------------------------------------------

a.The table_CustomerInfo (which is used to create report)

1.SystemJobNumber
2.ReportedCode
3.ReportedSymptom
4.FailureCode
5.FailureDescription
6.RepairCode
7.RepairDescription
8.SerialNumber
and other fields (total 40 fields)

--------------------------------------------------------------------


b. The record in table_CustomerInfo after inserting data is like below.--------------------------------------------------------------------SystemJobNumber |ReportedCode |ReportedSymptom |FailureCode |.....
--------------------------------------------------------------------
10000001 |Code1;Code2; |Symptom1; |FCode1; |
|Code3; |Symptom2: |FCode2;FCode3;|
| |Symptom3: | |
----------------------------------------------------------------------


***** Notice that there are many data in 1 single column **********
***** The User can input until 10 data in one column **********


----------------------------------------------------------------------

My problem is the user wants the report to show 1 data in one new row.
Like the below example.


--------------------------------------------------------------------SystemJobNumber |ReportedCode |ReportedSymptom |FailureCode |.....
--------------------------------------------------------------------
10000001 |Code1; |Symptom1; |FCode1; |
---------------------------------------------------------------------
10000001 |Code2; |Symptom2; |FCode2; |
----------------------------------------------------------------------
10000001 |Code3; |Symptom3; |FCode3; |
-----------------------------------------------------------------------


OK.

I don't want to change the database table design but prefer to show the record in asp pages .This is because in the insert new record page, the user submit the form only once and 1 column can insert many data.

--------------------------------------------------------------------
A part of the coding


Dim rsWeeklyRR
Dim rsWeeklyRR_numRows

Set rsWeeklyRR = Server.CreateObject("ADODB.Recordset")
rsWeeklyRR.ActiveConnection = MM_conDallabTrack_STRING
rsWeeklyRR.Source = "SELECT tbCustomerInfo.* FROM tbCustomerInfo WHERE (Model = '" + Replace(rsWeeklyRR__strModel, "'", "''") + "') And (ShipDateTime Between '" + Replace(rsWeeklyRR__strStartD, "'", "''") + "' And '" + Replace(rsWeeklyRR__strEndD, "'", "''") + "') And (ServiceCentre='" + Replace(rsWeeklyRR__strSC, "'", "''") + "') ORDER BY SystemJobNum DESC"
rsWeeklyRR.CursorType = 0
rsWeeklyRR.CursorLocation = 2
rsWeeklyRR.LockType = 1
rsWeeklyRR.Open()
--------------------------------------------------------------------------



Can anyone help me ????

Thanks in advance.
Reply With Quote
  #2 (permalink)  
Old 12-30-04, 02:07
mila04792 mila04792 is offline
Registered User
  
Join Date: Dec 2004
Location: Malaysia
Posts: 4
Hi again, (the table is not so clear)

Can any one help me with this? I've developed a system and nearly complete it. In the report part, I found that I need to change the existing report.

-------------------------------------------------------------------

a.The table_CustomerInfo (which is used to create report)

1.SystemJobNumber
2.ReportedCode
3.ReportedSymptom
4.FailureCode
5.FailureDescription
6.RepairCode
7.RepairDescription
8.SerialNumber
and other fields (total 40 fields)

--------------------------------------------------------------------


b. The record in table_CustomerInfo after inserting data is like below.
--------------------------------------------------------------------SystemJobNumber |ReportedCode |ReportedSymptom |FailureCode |.....
--------------------------------------------------------------------
10000001 |Code1;Code2; |Symptom1; |FCode1; |
|Code3; |Symptom2: |FCode2;FCode3;|
| |Symptom3: | |
----------------------------------------------------------------------


***** Notice that there are many data in 1 single column **********
***** The User can input until 10 data in one column **********


----------------------------------------------------------------------

My problem is the user wants the report to show 1 data in one new row.
Like the below example.


--------------------------------------------------------------------SystemJobNumber |ReportedCode |ReportedSymptom |FailureCode |.....
--------------------------------------------------------------------
10000001 |Code1; |Symptom1; |FCode1; |
---------------------------------------------------------------------
10000001 |Code2; |Symptom2; |FCode2; |
----------------------------------------------------------------------
10000001 |Code3; |Symptom3; |FCode3; |
-----------------------------------------------------------------------


OK.

I don't want to change the database table design but prefer to show the record in asp pages .This is because in the insert new record page, the user submit the form only once and 1 column can insert many data.

--------------------------------------------------------------------
A part of the coding


Dim rsWeeklyRR
Dim rsWeeklyRR_numRows

Set rsWeeklyRR = Server.CreateObject("ADODB.Recordset")
rsWeeklyRR.ActiveConnection = MM_conDallabTrack_STRING
rsWeeklyRR.Source = "SELECT tbCustomerInfo.* FROM tbCustomerInfo WHERE (Model = '" + Replace(rsWeeklyRR__strModel, "'", "''") + "') And (ShipDateTime Between '" + Replace(rsWeeklyRR__strStartD, "'", "''") + "' And '" + Replace(rsWeeklyRR__strEndD, "'", "''") + "') And (ServiceCentre='" + Replace(rsWeeklyRR__strSC, "'", "''") + "') ORDER BY SystemJobNum DESC"
rsWeeklyRR.CursorType = 0
rsWeeklyRR.CursorLocation = 2
rsWeeklyRR.LockType = 1
rsWeeklyRR.Open()
--------------------------------------------------------------------------



Can anyone help me ????

Thanks in advance.
Reply With Quote
  #3 (permalink)  
Old 01-03-05, 20:58
DMWCincy DMWCincy is offline
Registered User
  
Join Date: May 2004
Posts: 125
To be honest, I would really think hard about changing the db design. I understand the wanting to not change it but in the long run you would probably thank youself. Being lazy on a db design could cost you days of pain down the road.

But to get back to your question, I would probably use arrays and two loops and build your table. Some like this

Code:
do until eof
  arrCol1 = split(column1, delimiter)
  arrCol2 = split(column2, delimiter)
  arrCol3 = split(column3, delimiter)
  'etc for each of the multi value columns
  for x = o to ubound(arrCol1)
     'start building table here to show user
  next 'x
loop

Hope this gives some ideas on where I'm going on this. There will probably be a lot of checks and stuff but this may work. If I get more time I'll post back with a better example.

Good luck
Last edited by DMWCincy; 01-03-05 at 21:02.
Reply With Quote
  #4 (permalink)  
Old 01-05-05, 20:46
mila04792 mila04792 is offline
Registered User
  
Join Date: Dec 2004
Location: Malaysia
Posts: 4
Wink Thanks DMWCincy
Hi DMWCincy,

This example is good already. With a bit adjustment I manage to do the report.

(If I get more time I'll post back with a better example. ---- )

And for your advice, I will correct the db design. I'll do a better db design next time.

Thanks for your advice too.
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.