Binding to a Datagrid at Runtime

[rokitsalad]

I have a sql server stored procedure from which I'm creating a dataset containing rows which I want to display in a datagrid on my web page. The dataset is populated depending on the value in a text box on my page (all pretty basic stuff so far).

My problem is that I can't work out how to control which columns appear in my datagrid. I can't use the vs gui because the dataset is not created until the onclick event of my search button. Here's som of my code:

Dim con As New SqlConnection(ConfigurationSettings.AppSettings("SiteAccidentsCon"))
Dim adapter As New SqlDataAdapter("exec siteaccidents_getmonthend '" & tbContractNumber.Text & "'", con)
Dim ds As New DataSet
Dim dv As New DataView
con.Open()
Try
adapter.Fill(ds)
con.Close()
con.Dispose()
dgAccidents.DataSource = ds
dgAccidents.DataKeyField = "ref"
dgAccidents.DataBind()
dgAccidents.Visible = True
ds.Clear()
ds.Dispose()
Catch ex As Exception

What I want to do is prevent the keyfield from appearing in the datagrid and add a template column with a text box in it which will accept integer input. I can pretty much work things out once I know how to control which columns appear. At the moment, all columns from the dataset are listed.

Any help is greatly appreciated.

Pete.

 

[ca8msm]

You have lots of options really. Firstly, you could create the DataGrid in the HTML at design time and only include the relevant columns (it doesn't matter that the DataSource isn't set until runtime). Secondly, you could only return the relevant columns in the stored procedure that you actually want to display. Thirdly, you could hide certain columns on the DataGrid by setting their width to zero or removing the text.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[rokitsalad]

The method I would prefer would be to build the relevant columns in the HTML, but when I set the datasource as my dataset (which isn't declared when the page renders) my page falls over telling me that my datasource does not exist.

If I don't set the datasource as my dataset, the form loads but when I try to populate it with data, the datafields arean't found.

Here's my HTML source for the columns:
<Columns>
<asp:BoundColumn DataField="Date" ReadOnly="True" HeaderText="Date"></asp:BoundColumn>
<asp:BoundColumn DataField="Time" HeaderText="Time"></asp:BoundColumn>
<asp:BoundColumn DataField="SatusofInjuredPerson" HeaderText="Satus of Injured Person"></asp:BoundColumn>
<asp:BoundColumn DataField="NameofInjuredPerson" HeaderText="Name of Injured Person"></asp:BoundColumn>
</Columns>

Am I doing this right?

 

[ca8msm]

but when I set the datasource as my dataset (which isn't declared when the page renders) my page falls over telling me that my datasource does not exist.

Where and how are you doing this?

when I try to populate it with data, the datafields arean't found.

Same question as above really...


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[rokitsalad]

OK, here's my full datagrid declaration:

<asp:datagrid id="dgAccidents" style="Z-INDEX: 123; LEFT: 8px; POSITION: absolute; TOP: 368px"
runat="server" CssClass="objects" Width="648px" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="4" GridLines="Vertical"
ForeColor="Black" AutoGenerateColumns="False">
<SelectedItemStyle Font-Bold="True" ForeColor="White" BackColor="#CE5D5A"></SelectedItemStyle>
<AlternatingItemStyle BackColor="White"></AlternatingItemStyle>
<ItemStyle BackColor="#F7F7DE"></ItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#6B696B"></HeaderStyle>
<FooterStyle BackColor="#CCCC99"></FooterStyle>
<Columns>
<asp:BoundColumn DataField="Date" ReadOnly="True" HeaderText="Date"></asp:BoundColumn>
<asp:BoundColumn DataField="Time" HeaderText="Time"></asp:BoundColumn>
<asp:BoundColumn DataField="SatusofInjuredPerson" HeaderText="Satus of Injured Person"></asp:BoundColumn>
<asp:BoundColumn DataField="NameofInjuredPerson" HeaderText="Name of Injured Person"></asp:BoundColumn>
</Columns>
<PagerStyle HorizontalAlign="Right" ForeColor="Black" BackColor="#F7F7DE" Mode="NumericPages"></PagerStyle>
</asp:datagrid>

When a value is entered into a text field on my page and a button is clicked, this sub is called:

Private Sub butFindContract_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butFindContract.Click
If tbContractNumber.Text <> "" Then
Dim con As New SqlConnection(ConfigurationSettings.AppSettings("SiteAccidentsCon"))
Dim adapter As New SqlDataAdapter("exec siteaccidents_getmonthend '" & tbContractNumber.Text & "'", con)
Dim ds As New DataSet
Dim dv As New DataView
con.Open()
Try
adapter.Fill(ds)
con.Close()
con.Dispose()
dgAccidents.DataSource = ds
dgAccidents.DataKeyField = "ref"
dgAccidents.DataBind()
dgAccidents.Visible = True
ds.Clear()
ds.Dispose()
Catch ex As Exception
If Not con.State.Closed Then
con.Close()
End If
con.Dispose()
ds.Dispose()
Throw New Exception(ex.Message)
End Try
End If
End Sub

If I don't add any columns in the html and switch the value for AutoGenerateColumns to true then all fields are listed. I want to control that so that the keyfield isn't displayed and a further template column is added to the end with a text box in it. I've used datagrids a couple of times before so I can manage to do the rest if I can get to that point. I just can't find any instructions anywhere on how to do this without declaring my dataset globally, which I really don't want to do.

I hope you can help me out.