How to display a varying number of rows 1

[rjn2001]

Hi, I am using Access, to retieve data from the Db, I have a verying number of records in the table relating to each user, one user may have 4 rows, one may have 99, another may have none.

How can i reflect this on my site?



Richard Noon

http://www.davidmasonconsultancy.co.uk

 

[FesterSXS]

How do you want to display the data - we need more info really - could you give us an example:

eg)

User1
record 1 info
record 2 info
...
record n info

User2
record 1 info

User3
record 1 info
record 2 info
...
record n info


Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[rjn2001]

sorry, of course:

it is user specific,

So it is:

UserName

Rec1 column 1 column 2 column 3 column 4
info info info info

Rec2 info info info info

Rec3 info info info info

but as my previous post says, the user may have many or none records, so it needs to be flexible

Richard Noon

http://www.davidmasonconsultancy.co.uk

 

[FesterSXS]

OK - this is fairly straightforward to do. Something like this perhaps...

<table cols="2" width="500" cellspacing="0" cellpadding="0" border="0">
<tr>
  <td width="250"></td>
  <td width="250"></td>
</tr>
<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnectionString

strSQL = "SELECT Username, col1, col2 FROM myTable ORDER BY Username ASC;"
Set objRS = objConn.Execute(strSQL)

If NOT objRS.EOF Then
  While NOT objRS.EOF
    strUserName = objRS("Username")    

    If strUserName <> strOldUserName Then
      Response.Write "<tr><td colspan='2'><h2>" & strUserName & "</h2></td></tr>" & vbcrlf
    End If

    Response.Write "<tr><td><p>" & objRS("col1") & "</p></td><td><p>" & objRS("col2") & "</p></td></tr>"

    strOldUserName = strUserName
    objRS.MoveNext
  Wend
Else
  Response.Write "<tr><td colspan='2'><p>No records found</p></td></tr>" & vbcrlf
End If

Set objRS = Nothing

objConn.Close
Set objConn = Nothing
%>
</table>

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[rjn2001]

Thanks Tony, just a little problem...

Microsoft VBScript runtime error '800a01a8' 
Object required: '' 

/JobProfile3.asp, line 88

from this line of code:

If NOT objRS.EOF Then       *****This line******
  While NOT objRS.EOF
    strUserName = objRS("UserID")

Richard Noon

http://www.davidmasonconsultancy.co.uk

 

[FesterSXS]

did you put your own connection string in place or strConnectionString?

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[SecondToNone]

Richard,

Tony was just giving you a sample code...you cannot use it as it is...

You need to have your own connection string to your database...

something like this...

objConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;" & _
"Uid=admin;" & _
"Pwd=XXX"

-SecondToNone

 

[rjn2001]

yeah, I know, here is the code I am using...

<table cols="2" width="500" cellspacing="0" cellpadding="0" border="0">
<tr>
  <td width="250"></td>
  <td width="250"></td>
</tr>
<%
set conn = Server.CreateObject("ADODB.Connection")
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open "D:/Websites/richard/admin/db/users.mdb"

strSQL = "SELECT [UserID], JobStart, JobFinish, JobRole, JobDetail, EmployerName FROM tblJob ORDER BY UserID ASC;"
Set rsCheckUser = Server.CreateObject("ADODB.Recordset")

If NOT objRS.EOF Then
  While NOT objRS.EOF
    strUserName = objRS("UserID")    

    If strUserName <> strOldUserName Then
      Response.Write "<tr><td colspan='2'><h2>" & strUserName & "</h2></td></tr>" & vbcrlf
    End If

    Response.Write "<tr><td><p>" & objRS("JobStart") & "</p></td><td><p>" & objRS("JobFinish") & "</p></td></tr>"

    strOldUserName = strUserName
    objRS.MoveNext
  Wend
Else
  Response.Write "<tr><td colspan='2'><p>No records found</p></td></tr>" & vbcrlf
End If

Set objRS = Nothing

objConn.Close
Set objConn = Nothing
%>
</table>

Richard Noon

http://www.richardnoon.co.uk

 

[FesterSXS]

replace this...

Set rsCheckUser = Server.CreateObject("ADODB.Recordset")

with this...

Set rsCheckUser = conn.Execute(strSQL)

Then go through the code and replace all objRS with rsCheckUser and replace all objConn with conn

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[SecondToNone]

it should be

Set objRS = conn.Execute(strSQL)

since yor are using objRS in the latter part of the code...

-SecondToNone

 

[rjn2001]

Right!, it works, your earning your stars!!

One thing, the code is bringing up the entire recordset of the table, rather than the user of which the data belongs to.

Any hints?

Richard Noon

http://www.richardnoon.co.uk

 

[rjn2001]

its ok, changed the SQL statement.

Thanks everyone!

Richard Noon

http://www.richardnoon.co.uk

 

[FesterSXS]

just filter in the SQL string...

strSQL = "SELECT [UserID], JobStart, JobFinish, JobRole, JobDetail, EmployerName FROM tblJob [red]WHERE UserID=whatever[/red] ORDER BY UserID ASC;"

If you are only showing results for 1 user at a time then you can remove the strOldUserName bits and move the UserID title out of the While Loop - leaving you with...

If NOT rsCheckUser.EOF Then
  Response.Write "<tr><td colspan='2'><h2>" & rsCheckUser("UserID") & "</h2></td></tr>" & vbcrlf

  While NOT objRS.EOF
    Response.Write "<tr><td><p>" & rsCheckUser("JobStart") & "</p></td><td><p>" & rsCheckUser("JobFinish") & "</p></td></tr>" & vbcrlf

    rsCheckUser.MoveNext
  Wend
End If

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[FesterSXS]

in my above post - please change...

While NOT objRS.EOF

to...

While NOT rsCheckUser.EOF

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/