[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How To Add Literal Values To A GridView Control?

Posted on 2006-06-01
11
Medium Priority
?
1,238 Views
Last Modified: 2008-01-16
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


0
Comment
Question by:DeniseGoodheart
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 9

Assisted Solution

by:bele04
bele04 earned 80 total points
ID: 16813865
Hi,

try adding rows manually to the datatable in your dataset like:

        If ds.Tables(0).Rows.Count = 0 Then
            Datarow newRow = ds.Tables(0).NewRow()
            newRow[0] = "California"
            newRow[1] = "0"
            ds.Tables(0).AddRow(newRow) //or was it ds.Tables(0).Rows.AddRow(newRow)
            With Me.GridView1
                .DataSource = ds.Tables(0)
                .DataBind()
            End With
        Else
            With Me.GridView1
                .DataSource = ds.Tables(0)
                .DataBind()
            End With
        End If

hope it helps
-bele04-
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 16815089
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
0
 
LVL 12

Expert Comment

by:deanvanrooyen
ID: 16815319
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:DeniseGoodheart
ID: 16820590
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




0
 

Author Comment

by:DeniseGoodheart
ID: 16820654
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
0
 
LVL 12

Accepted Solution

by:
deanvanrooyen earned 1920 total points
ID: 16826692
hi,

this might be a way of doing it, because you know what city names are required in the grid even before the records are fetched...

Manually add DataRows to a datatable so that you are in a way manualy creating the grid with rows of what data you expect to see but you are only going to set the first column with the city names and initialy set the other values to 0. then get the data from loop through each row and if state is in the fetched data then set the values of the other fields to the values in the fetched data

not using your sql to visualize the columns but using your example of
State                   Number
California             0
Arizona                0
Utah                    0

            //create datatable and columns -  do this for every column required , I only do it twice in your example, your sql looks like a few columns
            DataTable dt = new DataTable();
            DataRow r;
            DataColumn Column;
            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);

// do something to get the fetched data like read it from a database etc
// check each row in the fetched data, check if the state field in the fetched data exist in our manaul table if it does change the number column to match
psuedo code
foreach (datarow a in fetcheddatatable)
{

}
0
 
LVL 12

Assisted Solution

by:deanvanrooyen
deanvanrooyen earned 1920 total points
ID: 16826701
psuedo code
foreach (datarow a in fetcheddatatable)
{
          foreach (datarow b in dt)
          {
               if(a[0].tostring == b[0].tostring)
              {
                    b[1] = a[1];
              }
          }
}
bind table dt to the grid...

if the fetched data had
Arizona                10

State                   Number
California             0
Arizona                10
Utah                    0

then the grid would have

0
 
LVL 9

Expert Comment

by:bele04
ID: 16829643
What line of the code threw that error message?
0
 

Author Comment

by:DeniseGoodheart
ID: 16879775
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()
0
 
LVL 12

Assisted Solution

by:deanvanrooyen
deanvanrooyen earned 1920 total points
ID: 16882127
try add something like

dt.AcceptChanges
Me.GridView1.DataSource = dt.DefaultView

does the grid have autogenerate columns set to true or have you add columns -  make sure the columns are set to the right datafields state and number
0
 

Author Comment

by:DeniseGoodheart
ID: 16883066
....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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question