Datagrid visible ? Argh

Guys,

Why, when I add a datagrid control to my screen, can I not get it to show at runtime ???

In a VB.Net windows app, I would set the datasource at runtime to a datatable held in memory, and it would all be cool.

Now in an ASP app I can't even get this datagrid to show ! I just need to show 2 columns, a name and a status. Is the datagrid the right way to go ?

Thanks
Bob

Not sure if the DataGrid is overkill - it depends what you are going to do to the data. For example for just displaying records in a database a DataGrid isn't the most efficient control to use. Give us a few more details.

Also post some code for how you are creating the DataTable and DataBinding the DataGrid.

DJ

Thanks for the feedback,

Agreed, I think the datatable is overkill, but this is the only method I have used before in my forms app.

I just need to display the 2 columns selected, readonly, but I would also like the ability to change the header text and also replace the status returned with a string. Not sure if I should do this at runtime, or in my SQL view ? I don't know how to do either !

Status could be an int, 0 - 9 inclusive. I want to replace this with . .

0 = "Not started"
1 = 13%
2 = 25%
3 = 38%
...
8 = 100%
9 = "Submitted"

This is my code to bind and display my datatable, which I have now managed to get showing at runtime, and my db class that builds the datatable.

TIA

Bob

VB Code:

Dim usersdt As New DataTable
        db.QbyID.QIDFill(usersdt, CInt(Questionnaires.SelectedItem.Value))
 
        DataGrid1.DataSource = New DataView(usersdt)
        DataGrid1.DataBind()
        DataGrid1.Visible = True
 
        usersdt.Dispose()
        usersdt = Nothing

VB Code:

Class QbyID
        Private Shared mda As SqlDataAdapter
        Friend Shared Function QIDFill(ByVal dt As DataTable, ByVal QID As Integer) As Integer
            QIDDataAdapter.SelectCommand.Parameters(0).Value = QID
            Return QIDDataAdapter.Fill(dt)
        End Function
        Private Shared Function QIDDataAdapter() As SqlDataAdapter
            If Not mda Is Nothing Then Return mda
            mda = New SqlDataAdapter
            With mda
                .SelectCommand = New SqlCommand
                With .SelectCommand
                    .Connection = cnn
                    .CommandText = "SELECT Staffname, QLStatus FROM vwQuestionnaireLine WHERE Questionnaire = @QID"
                    .Parameters.Add("@QID", SqlDbType.Int)
                End With
                AddHandler .FillError, AddressOf Fill_Error
                AddHandler .RowUpdated, New SqlRowUpdatedEventHandler(AddressOf DataAdapter_OnRowUpdated)
            End With
            Return mda
        End Function

Right so this is a read only table containing two columns one for name and the other for status.

Personally I would just use a DataReader and a Repeater to do what you want.

Code:

---

<asp:Repeater id="rptItems" runat="server">
        <HeaderTemplate>
                <table>
                        <tr>
                                <th>Name</th>
                                <th>Status</th>
                        </tr>
        <HeaderTemplate>
        <ItemTemplate>
                <tr>
                        <td><%# DataBinder.Eval(Container.DataItem, "Staffname") %></td>
                        <td><%# displayStatus(Convert.ToInt32(DataBinder.Eval(Container.DataItem, "QLStatus"))) %></td>
                </tr>
        </ItemTemplate>
        <FooterTemplate>
                </table>
        </FooterTemplate>
</asp:Repeater>

---

Then in the code behind you would populate the key/value pairs for the status and then use this in the displayStatus function used above to display the status string.

Code:

---

string displayStatus(int statusID) {
        Hashtable _status = new HashTable();
        _status.Add(0, "Not started");
        _status.Add(1, "13%");
        ...
        _status.Add(8, "100%");
        _status.Add(9, "Submitted");
       
        return _status[statusID];
}

---

I'd then create a method to grab the data and DataBind to the repeater - this could be called anywhere but generally I'd do it in Page_Load but it looks like to me this is called when the Questionnaires control (?Dropdownlist?) changes. Hence you pass the selectedvalue into the DataBind method.

Code:

---

void rptItems_DataBind(int QID) {
        SqlConnection dbConn = new SqlConnection("Enter connection string here");
        SqlCommand cmdSelect = new SqlCommand("SELECT Staffname, QLStatus FROM vwQuestionnaireLine WHERE Questionnaire = @QID", dbConn);
        cmdSelect.Parameters.Add("@QID", QID);
       
        dbConn.Open();
        SqlDataReader dtrItems = cmdSelect.ExecuteReader();
       
        rptItems.DataSource = dtrItems;
        rptItems.DataBind();

        dtrItems.Close();
        dbConn.Close();
}

---

Sorry it's in C# but I can read VB.NET fine but writing it is not so easy!

Let me know if I've misunderstood or you have any other questions.

DJ

Thanks DJ,

I now have these 3 lots of code, I made some changes to the html to make it build, but it doesn't display well, I just get . .

Name Status
BLUESTONE\matt.adam BLUESTONE\neil.bailey BLUESTONE\susan.ball BLUESTONE\david.barker BLUESTONE\peter.blackburn BLUESTONE\mark.brown BLUESTONE\jason.jones

. . .. rather than a new row for each user.

I have posted all code, my html, function and method.

Thanks again . .
Bob

Code:

---

<asp:Repeater id="rptitems" runat="server">
                                        <ItemTemplate>
                                                <tr>
                                                        <td><%# DataBinder.Eval(Container.DataItem, "Staffname") %></td>
                                                        <td><%# displayStatus(Convert.ToInt32(DataBinder.Eval(Container.DataItem, "QLStatus"))) %></td>
                                                </tr>
                                        </ItemTemplate>
                                        <HeaderTemplate>
                                                <table>
                                                        <tr>
                                                                <th>
                                                                        Name</th>
                                                                <th>
                                                                        Status</th>
                                                        </tr>
                                                </table>
                                        </HeaderTemplate>
                                        <FooterTemplate>
                                        </FooterTemplate>
                                </asp:Repeater>

---

VB Code:

Public Function displayStatus(ByVal statusID As Integer)
        Dim _status As New Hashtable
        _status.Add(0, "Not Started")
        _status.Add(1, "13% Complete")
        _status.Add(2, "25% Complete")
        _status.Add(3, "38% Complete")
        _status.Add(4, "50% Complete")
        _status.Add(5, "63% Complete")
        _status.Add(6, "75% Complete")
        _status.Add(7, "88% Complete")
        _status.Add(8, "100% Complete")
        _status.Add(9, "Submitted")
    End Function
 
    Public Sub rptItems_Databing(ByVal QID As Integer)
        Dim cnn As New SqlConnection("Data Source=BLS5TEST2;Initial Catalog=HPT;User Id=HPT;Password=kibby1;")
        Dim cmd As New SqlCommand("SELECT Staffname, QLStatus FROM vwQuestionnaireLine WHERE Questionnaire = '" & QID & "'", cnn)
 
        cnn.Open()
 
        Dim dr As SqlDataReader = cmd.ExecuteReader()
        rptitems.DataSource = dr
        rptitems.DataBind()
 
        cnn.Close()
 
    End Sub

The </table> should be in the FooterTemplate not the HeaderTemplate.

Try that.

DJ

Thanks mate, got that one.

The status doesn't show tho ?

Cheers
Bob

Because you aren't returning a value from the displayStatus function - it should return the value for the statusID entered.

DJ