asked on

How To Add Literal Values To A GridView Control?

Hi,

I’m using cross joins to populate many GridView controls. My problem occurs when there is no data in the cross joins. The GridView is empty. I still need to report the data as follows:

State Number
California 0
Arizona 0
Utah 0

How do I write code to add the data above to the GridView control?

The following code checks to see if the dataset is empty. If it is not empty then it populates the GridView Control. If it is empty, then I need to add some literal values to the GridView for reporting purposes.

Protected Sub btnLoadGrid_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLoadGrid.Click

Dim sCon As String
Dim sLoginID As String
Dim sPassword As String
Dim strSQL As String

sLoginID = "sa"
sPassword = "beatles"
strSQL = "Select * from vwStates"

sCon = "Data Source=beatles;Initial Catalog=Recruit;User Id=" & sLoginID & ";Password=" & sPassword & ";"
Dim da As New SqlDataAdapter(strSQL, sCon)

Dim ds As New DataSet
da.Fill(ds, "vwStates")

' Test to see if there is an empty dataset
If ds.Tables(0).Rows.Count = 0 Then
‘Add code to provide literal values to the grid
Else
With Me.GridView1
.DataSource = ds.Tables(0)
.DataBind()
End With
End If

Thanks,
Denise

SOLUTION

bele04

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rejojohny

i assume vwStates is a view .. cld u pls post the code for the view .. becuase i think u might have a join between table to get the count .. it needs to do a left join with ur master table for state to get all the states and their count .. so that if for a state the count is 0 it will still appear ...

Rejo

deanvanrooyen

your question mentions joins but the select statement is a simple select, so I am trying to understand where the issue is - is it with the sql or are you saying that if no records actually exist you would like to show some states with a 0 next to them? ofr manualy use bele04

DeniseGoodheart

ASKER

Hi Rejojohny

Thanks for asking. This is the situation, unfortunately the master table does not contain the missing data, and this is where my problem comes in. I simplified my view for my question. My views are actually 3 levels (derived) and they are for cities. If the cities are not in the master table, then I will not be able to get the city names, but I still need to report the cities, and display it on my GridView. My GridView is not based on relational data, but more like a cross tab query(spreadsheet type).

My views are as follows:

vwRegionUnicorpCentral_1:
SELECT TOP 100 PERCENT CITY, COUNT(*) AS UnicorpCentralCount
FROM dbo.ACTIVE_REF_LAST_MONTH
GROUP BY CITY
HAVING (CITY = N'Cowan Heights') OR
(CITY = N'El Modena') OR
(CITY = N'North Tustin')
ORDER BY CITY

===========
vwRegionUnicorpCentral_2:
SELECT UnicorpCentralCount AS UnincorpCentralSum
FROM dbo.vwRegionUnicorpCentral_1
GROUP BY UnicorpCentralCount

============
vwRegionUnicorpCentral_3:
SELECT dbo.vwRegionUnicorpCentral.City AS UnCentralRegionCities,
COALESCE (ROUND(CAST(dbo.vwRegionUnicorpCentral_2.UnincorpCentralSum AS Decimal) / CAST(dbo.vwRecordCount.RecordCount AS Decimal)
* 100, 2), 0) AS UnCentralTotalPercent, COALESCE ('dbo.vwRegionUnicorpCentral_1.UnincorpCentralCount', '0') AS UnCentralNumber,
dbo.vwRegionUnicorpCentral_2.UnincorpCentralSum AS UnCentralTotalNumber
FROM dbo.vwRegionUnicorpCentral_1 RIGHT OUTER JOIN
dbo.vwRegionUnicorpCentral ON dbo.vwRegionUnicorpCentral_1.CITY = dbo.vwRegionUnicorpCentral.City CROSS JOIN
dbo.vwRecordCount CROSS JOIN
dbo.vwRegionUnicorpCentral_2

Thanks,
Denise

DeniseGoodheart

ASKER

Hi bele04,

Thanks for the code. I'm getting the following error message:

DataRow is a type and cannot be used as an expression

Thanks,
Denise

ASKER CERTIFIED SOLUTION

deanvanrooyen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

deanvanrooyen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bele04

What line of the code threw that error message?

DeniseGoodheart

ASKER

Hi deanvanrooyen:

Thanks for your code. I converted your code to VB.NET and added the following:

Me.GridView1.DataSource = dt
Me.GridView1.DataBind()

The static data does not appear on the grid. Any suggestions as to why it does not display on the grid?

Many Thanks,
Denise

My entire code is as follows:

Dim dt As DataTable = New DataTable
Dim r As DataRow
Dim Column As DataColumn
Column = New DataColumn
Column.DataType = System.Type.GetType("System.String")
Column.AllowDBNull = False
Column.Caption = "State"
Column.ColumnName = "State"
Column.DefaultValue = ""
dt.Columns.Add(Column)
Column = New DataColumn
Column.DataType = System.Type.GetType("System.String")
Column.AllowDBNull = False
Column.Caption = "Number"
Column.ColumnName = "Number"
Column.DefaultValue = ""
dt.Columns.Add(Column)
'add the 3 states/numbers to the datatable, do this for every state required
r = dt.NewRow
r("State") = "California"
r("Number") = 0
dt.Rows.Add(r)
r = dt.NewRow
r("State") = "Arizona"
r("Number") = 0
dt.Rows.Add(r)
r = dt.NewRow
r("State") = "Utah"
r("Number") = 0
dt.Rows.Add(r)

Me.GridView1.DataSource = dt
Me.GridView1.DataBind()

SOLUTION

deanvanrooyen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DeniseGoodheart

ASKER

....Many Thanks for everyones help!

Hi deanvanrooyen:

I opened the grid smart tag and added the right columns to match the code, and it works great!

Many Thanks,
Denise