Rowspan in html table according to the record from DB....

Tash18

Hi guys.. I am working on a web application on Vb platform. Now in my application i have a button on clicking it generates an html table based on the records retrieved from my database.. Now the issue is I wanna rowspan certain cells in my html table for repeated Records... Now heres the code:

Private Sub BtnApp\_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnApp.Click
    Response.Redirect("form\_frameset.aspx?sno=&page=UserApplication&keyid=&keyinfo=All")
End Sub

on the above button click it navigates to another page where the Html table gets populated.. the code on the next page is as follows:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim page As String = Request.QueryString("page")
Dim keyid As String = Request.QueryString("keyid")
Dim keyinfo As String = Request.QueryString("keyinfo")
MyConn = New OdbcConnection(Session("sybase_constr"))
Dim SelectCmd As OdbcCommand
If page = "UserApplication" Then
Dim sqlstring As String
If keyinfo = "All" Then
sqlstring = "Select ug.UserID,u.firstname, fg.GroupID, g.GroupDesc, f.FunctionID,f.FunctionDesc from EACS_Function f, EACS_UserGroup ug, EACS_FunctionGroup fg, EACS_Group g, EACS_user_1 u where f.ApplicationID='" & keyid & "' and fg.FunctionID = f.FunctionID and fg.GroupID = ug.GroupID and ug.userid = u.userid and fg.GroupID = g.GroupID order by ug.UserID"
End If
SelectCmd = New OdbcCommand(sqlstring, MyConn)
Dim DS As DataSet
Dim MyCommand As OdbcDataAdapter
MyCommand = New OdbcDataAdapter(SelectCmd)
MyConn.Open()
DS = New DataSet
MyCommand.Fill(DS)
Dim i As Integer
i = 0
If page = "UserApplication" Then
If DS.Tables(0).Rows.Count > 0 Then
InsertPageBreak()
InsertAppUserHeader()
For i = 0 To DS.Tables(0).Rows.Count - 1
PopulateAppUsrRow(DS.Tables(0).Rows(i)(0), DS.Tables(0).Rows(i)(1), DS.Tables(0).Rows(i)(2), DS.Tables(0).Rows(i)(3), DS.Tables(0).Rows(i)(4), DS.Tables(0).Rows(i)(5))
Next
Else
Response.Write("<script language=javascript > alert('Sorry no records found'); </script>")
End If

Private Sub PopulateAppUsrRow(ByVal Usrid As String,

Brij

As you are creating the HTML already at server side.You have full control over the HTML what you have to show.Have some check at the time of creating table according to the data coming from DB. Set the rowspan.Check after the table is created,whether it is right or not.If not it means there is some problem in the creating the table.

Cheers!! Brij

Tash18

I cant set my row span because it depends on the data retrieved from the database... Im not able to logically implement this requirement... Plz advice.. Thanx in advance, Tash

Brij

<> wrote:

I cant set my row span because it depends on the data retrieved from the database...

Why, you just get the data from db, check how many records are there and where and howmauch rowsapn you need , you can decide if you are having data.Set it accordingly.You'll have to take decision of the count of rowspan after getting the data from DB. You can not hard code the rowspan.

Cheers!! Brij

Member 4501940

The requirement is simply using 'control break logic'. Search for it. Note that the sort order of the recordset is important. Read the first record. Save the 'control break' (userid?, can be more than 1 value). Write_All_Cells. For every record after that... Read. If value same as saved 'control break' then Write_Some_Cells (one td has a column span = n) else replace control break value Write_All_Cells

Tash18

Any sample code or articles plzz???? Thanx & regards, Tash

Member 4501940

Sure. http://www.cerritos.edu/dmellas/cis103/chap07-mod.pdf[^]

Tash18

Sorry im not able to cope with the replies posted here... I mean im not able to implement the logic... Im totally hit with it... Any advice is appreciated... Thanx in advance, Tash

Member 4501940

Yes, that whole logic thing will get you everytime.

Dim connection As New SqlConnection("server=MyServer;Trusted_Connection=true;database=MyDatabase")
connection.Open()
Dim command As New SqlCommand("select MyKey,MyField1,MyField2 from MyTable order by MyKey;", connection)
Dim reader As SqlDataReader = command.ExecuteReader()

Response.Write("<table border='1'>")
'Read the first record
reader.Read()
'Set the control break
Dim ControlBreak As Integer = CInt(reader(0))
'Write all cells
Response.Write("<tr><td>" & reader(0) & "</td><td>" & reader(1) & "</td><td>" & reader(2) & "</td></tr>")

While reader.Read()
If reader(0).Equals(ControlBreak) Then
'Write some cells
Response.Write("<tr><td colspan='2'> </td><td>" & reader(2) & "</td></tr>")
Else
'Reset the control break
ControlBreak = CInt(reader(0))
'Write all cells
Response.Write("<tr><td>" & reader(0) & "</td><td>" & reader(1) & "</td><td>" & reader(2) & "</td></tr>")
End If
End While
Response.Write("</table>")
reader.Close()
connection.Close()

Tash18

Thanx for ur reply... but in my code i dont use reader i store it in a dataset.... so working on it.. Any advice is appreciated.. Thanx in advence.. Tash

Tash18

Thanx a lot i found another solution for my issue.... actually i used the following code to solve it... i used String collection to store the value and checked it and printed the table accordingly

ElseIf page = "UserApplication" Then
If DS.Tables(0).Rows.Count > 0 Then
InsertPageBreak()
InsertAppUserHeader()
Dim nfile As StringCollection = New StringCollection
Session("sc1") = nfile
For i = 0 To DS.Tables(0).Rows.Count - 1
PopulateAppUsrRow(DS.Tables(0).Rows(i)(0), DS.Tables(0).Rows(i)(1), DS.Tables(0).Rows(i)(2), DS.Tables(0).Rows(i)(3), DS.Tables(0).Rows(i)(4))
'DataSetToExcel.Convert(DS, Response)
Next

Now the Html table gets populated by the following code:

Public Sub PopulateAppUsrRow(ByVal Usrid As String, ByVal Usrname As String, ByVal Grpid As String, ByVal Grpdsc As String, ByVal FncDsc As String)
If Not Session("sc1").Contains(Usrid) Then
Session("sc1").Add(Usrid)
Response.Write("<tr><td align=left>" + Usrid + "</td><td align=left >" + Usrname + "</td><td align=left >" + Grpid + "</td><td align=left >" + Grpdsc + "</td><td align=left >" + FncDsc + "</td></tr>")
Else
Response.Write("<tr><td align=left> </td><td align=left >" + Usrname + "</td><td align=left >" + Grpid + "</td><td align=left >" + Grpdsc + "</td><td align=left >" + FncDsc + "</td></tr>")
End If
End Sub

Im happy with this kinda output.. any advice is appreciated... Thanx in advance... Tash