Datagrid visible ? Argh

[staticbob]

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

[dj4uk]

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

[staticbob]

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

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

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

[dj4uk]

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.

<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.

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.

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

[staticbob]

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

<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>

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

[dj4uk]

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

Try that.

DJ

[staticbob]

Thanks mate, got that one.

The status doesn't show tho ?

Cheers
Bob

[dj4uk]

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

DJ