i'm in dataview hell - please someone help!

[sean]

Hi There,

I have tried just about everything to get the dataview working, all I want
to do is loop through rows generated from the stored procedure and then
suppress some of them depending on the amount of columns returned, with a
table like structure.

can someone help me out with a little code please.


Public Function ShowData(ByVal strDec as string, ByVal strImageID as
Integer)
Dim myCommand As SqlCommand


Dim ds as DataSet=New DataSet()
Dim da as New SQLDataAdapter()

Dim myConnection As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
myCommand = New SqlCommand("GetListingDetail",myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim pListingID As New SqlParameter("@listingID", SqlDbType.int)
pListingID.Value = strImageID
myCommand.Parameters.Add(pListingID)
myConnection.Open
da.SelectCommand=myCommand
da.Fill(ds, "tblListings")
Dim dv as DataView = ds.Tables("tblListings").DefaultView


End Function

 

[Jon Skeet [C# MVP]]

I have tried just about everything to get the dataview working, all I want
to do is loop through rows generated from the stored procedure and then
suppress some of them depending on the amount of columns returned, with a
table like structure.

And what problem are you having? You've given the code for filling the
table, but not what you've tried with the DataView, and how it didn't
work.

Could you post a short but complete program which demonstrates the
problem?

See http://www.pobox.com/~skeet/csharp/complete.html for details of
what I mean by that.

 

[William Ryan eMVP]

The number of columns returned will be the same for each row in a given
table so I don't think a dataview is going to do it for you. If you are
trying to 'suppress' something, does that mean not have it in a view or not
have it show in the UI? YOu may be able to solve your problem by using the
visible property of whatever control(s) you are dealing with.

Anyway, like John mentions, all this code shows is the filling of a
datatalbe and a dataview set to the default view. Using the RowFilter will
filter out rows based on a condition, but as far as filtering columns,
perhaps it'd be better to do it on the SQL side, but in the context of your
comments, I'm not sure what the goal is so it's hard to help.



--

W.G. Ryan, eMVP

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups

 

[sean]

Hi There,

I am trying to acheive the same sort of affect as using the if then else in
classic asp, I have a stored procedure that returns 28 columns, 8 of these
columna are the same for all results the other 20 are different depending on
the value of the @listingID parameter. The problem that I have is that I
need to have complete control over the display (see below 1), I have tried
using a datagrid but I can't seem to get it to format the display properly,
I can't have empty table cells on the page it looks ugly. I managed to get a
table control to display, but I want the columns that I decide to display in
the same format as the datagrid it self.(see the <asp:datagrid id="dgHyper"
runat="server" below).

Could someone help please, this is driving me nuts

Sean


1.

<template column>
if param <> "null" then
<table>
<tr>
<td>
Elseif formname = "Control Valves"
co9, col10,col11
some value some value
</td>
<td>
else

some value
</td>
</tr>
</table>
end if
<template column>




!----- full code snippet



Dim myConnection As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
myCommand = New SqlCommand("GetListingDetail",myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim pListingID As New SqlParameter("@listingID", SqlDbType.int)
pListingID.Value = strImageID
myCommand.Parameters.Add(pListingID)
myConnection.Open
da.SelectCommand=myCommand
ds = New DataSet()

da.Fill(ds, "tblListings")
'Dim dv as DataView = ds.Tables(0).DefaultView

Dim dc As DataColumn

For Each dc In ds.Tables(0).Columns
If Not dc.ColumnName = "ListingID" And Not dc.ColumnName =
"model" And Not dc.ColumnName = "listprice" And Not dc.ColumnName = "descri"
And Not dc.ColumnName = "manufacturer" And Not dc.ColumnName = "comments"
And Not dc.ColumnName = "formname" And Not dc.ColumnName = "username" And
Not dc.ColumnName = "lblquantity" And Not dc.ColumnName = "lbllistprice" And
Not dc.ColumnName = "lblcomments" And Not dc.ColumnName = "lbllocation"
And Not dc.ColumnName = "Location" Then
bindcolumn(dc.ColumnName, "")
End If
Next


Dim dr As DataRow
For Each dc In ds.Tables(0).Columns
Dim trow As New TableRow()
Dim tcellcolname As New TableCell()

'To Display the Column Names
For Each dr In ds.Tables(0).Rows
tcellcolname.Controls.Add(New LiteralControl(dc.ColumnName.ToString))
Next
If Not dc.ColumnName = "ListingID" And Not dc.ColumnName = "model" And
Not dc.ColumnName = "listprice" And Not dc.ColumnName = "descri" And Not
dc.ColumnName = "manufacturer" And Not dc.ColumnName = "comments" And Not
dc.ColumnName = "formname" And Not dc.ColumnName = "username" And Not
dc.ColumnName = "lblquantity" And Not dc.ColumnName = "lbllistprice" And Not
dc.ColumnName = "lblcomments" And Not dc.ColumnName = "lbllocation" And
Not dc.ColumnName = "Location" Then
trow.Cells.Add(tcellcolname)
End if

'To Display the Column Data
For Each dr In ds.Tables(0).Rows
Dim tcellcoldata As New TableCell()
If Not dc.ColumnName = "ListingID" And Not dc.ColumnName = "model" And
Not dc.ColumnName = "listprice" And Not dc.ColumnName = "descri" And Not
dc.ColumnName = "manufacturer" And Not dc.ColumnName = "comments" And Not
dc.ColumnName = "formname" And Not dc.ColumnName = "username" And Not
dc.ColumnName = "lblquantity" And Not dc.ColumnName = "lbllistprice" And Not
dc.ColumnName = "lblcomments" And Not dc.ColumnName = "lbllocation" And
Not dc.ColumnName = "Location" Then
tcellcoldata.Controls.Add(New
LiteralControl(dr(dc.ColumnName).ToString))
End if
trow.Cells.Add(tcellcoldata)
Next
If Not dc.ColumnName = "ListingID" And Not dc.ColumnName = "model" And
Not dc.ColumnName = "listprice" And Not dc.ColumnName = "descri" And Not
dc.ColumnName = "manufacturer" And Not dc.ColumnName = "comments" And Not
dc.ColumnName = "formname" And Not dc.ColumnName = "username" And Not
dc.ColumnName = "lblquantity" And Not dc.ColumnName = "lbllistprice" And Not
dc.ColumnName = "lblcomments" And Not dc.ColumnName = "lbllocation" And
Not dc.ColumnName = "Location" Then
Table1.Rows.Add(trow)
End if
Next

End Function


Sub bindcolumn(ByVal fldname As String, ByVal formatpattern As String)
Dim objbc As BoundColumn
objbc = New BoundColumn()
objbc.DataField = fldname
objbc.HeaderText = fldname
objbc.DataFormatString = formatpattern
dgHyper.Columns.Add(objbc)
dgHyper.DataSource = ds.Tables(0)
dgHyper.DataBind()
End Sub


</script>

</head>
<table cellspacing=1 cellpadding=1 width=500 border=1>
<tr>
<td valign=top>
<aspataGrid
ID="dg_details"
AutoGenerateColumns=False
Width="250"
HeaderStyle-BackColor="purple"
HeaderStyle-ForeColor="White"
HeaderStyle-HorizontalAlign="Center"
HeaderStyle-Font-Bold="True"

Runat=server>
<Columns>


<asp:TemplateColumn HeaderText="Image">
<ItemTemplate>
<asp:Image Width="200" Height="200" alt="Listing Image" ImageUrl='<%#
FormatURL(DataBinder.Eval(Container.DataItem, "ListingID")) %>' Runat=server
/>

</ItemTemplate>
</asp:TemplateColumn>


</Columns>

</aspataGrid>

</td>
<td>
<table cellspacing=1 cellpadding=1 width=250 border=1>
<tr><td valign=top>
<asp:datagrid id="dgHyper" runat="server"
HeaderStyle-BackColor="purple"
HeaderStyle-ForeColor="White"
HeaderStyle-HorizontalAlign="Center"
HeaderStyle-Font-Bold="True"
AutoGenerateColumns="False">
<COLUMNS>
<asp:TemplateColumn HeaderText="Listing Info">
<ItemTemplate>
<table border="0" width=250>

<tr>
<td align="right"><b>Manufacturer:</b></td>
<td><%# DataBinder.Eval(Container.DataItem, "manufacturer")
%></td>
</tr>
<tr>
<td align="right"><b>Model:</b></td>
<td><%# DataBinder.Eval(Container.DataItem, "model") %></td>
</tr>
<tr>
<td align="right"><b>Description:</b></td>
<td><%# DataBinder.Eval(Container.DataItem, "descri") %></td>
</tr>

</ItemTemplate>
</asp:TemplateColumn>

!--------------------------------------------------------------------------
Appear here


<asp:TemplateColumn>
<ItemTemplate>

<tr>
<td align="right"><b>Quantity:</b></td>
<td><%# DataBinder.Eval(Container.DataItem, "quantity") %></td>
</tr>
<tr>
<td align="right"><b>Price:</b></td>
<td><%# DataBinder.Eval(Container.DataItem, "listprice") %></td>
</tr>
<tr>
<td align="right"><b>Comments:</b></td>
<td><%# DataBinder.Eval(Container.DataItem, "comments") %></td>
</tr>
<tr>
<td align="right"><b>Location:</b></td>
<td><%# DataBinder.Eval(Container.DataItem, "Location") %></td>
</tr>
<tr>
<td align="right"><b>Type:</b></td>
<td><%# DataBinder.Eval(Container.DataItem, "formname") %></td>
</tr>
</table>
</ItemTemplate>
</asp:TemplateColumn>


</Columns>
</asp:datagrid>

</td></tr>
</table>
<asp:Table id="Table1" runat="server"
CellPadding=10
HorizontalAlign="left">

</asp:Table>
</td></tr>
</table>