Beginner: How do datalists work in asp.net/vb.net?

Question

I'm very new to asp.net and I have a general question about datalists. I was able to get this to work, but I don't understand how really. I think I need someone to give me the "theory" on this.

So first I connect to the database:

Dim MyConnection As SqlConnection
MyConnection = New SqlConnection("myconnectionstring=workingfine")
Dim DS As New DataSet
Dim MyCommand As New SqlDataAdapter("select * from thetable", MyConnection)
MyCommand.Fill(DS, "field1")
MyDataList.DataSource = DS.Tables("field1").DefaultView
MyDataList.DataBind()

Then I output it like this:

<form runat="server" id="form1">
<asp:datalist ID="MyDataList" runat="server">
<ItemTemplate>
<%#DataBinder.Eval(Container.DataItem,"field1") %>
<%#DataBinder.Eval(Container.DataItem,"field2") %>
</ItemTemplate>
</asp:datalist>
</form>

Here are my questions:
- Why is the datalist pulling field2 successfully?
- Are the MyCommand.Fill(DS, "field1") and DS.Tables("field1").DefaultView meaningless?
- I guess my bottom-line question is, why do I have to specify fields in the vb if I can output any field?

I think I need some general guidance...

Answer 1

Hi

I develop asp.net applications in c#, I had to use VB for a short while so I can shed some light on this.

Firstly, I'm pretty sure that in your SqlDataAdapter Fill method, it is simply placing all the data into a table called "field1" which sits inside your DataSet called 'DS':

MyCommand.Fill(DS, "field1") 
MyDataList.DataSource = DS.Tables("field1").DefaultView

Then you are instructing your DataList to use the table called 'field1' inside the DataSet 'DS'...

In your markup, your simply outputting the columns, it just so happens that you labeled your table as 'field1' in the code-behind causing the confusion.

Try changing the table name from "field1" to something like "TestTable" ... it should still work.

eg.

MyCommand.Fill(DS, "TestTable")     
MyDataList.DataSource = DS.Tables("TestTable").DefaultView

Remember, in the code behind you are defining the name of the table holding your results, this can be called anything, it is not the column name - the columns you wish to output are decided in your markup... as you have done correctly.

Finally, a DataSet can hold multiple results of table data:

MyCommand As New SqlDataAdapter("select * from employee", MyConnection)
MyCommand.Fill(DS, "EmployeeTable")

MyCommand As New SqlDataAdapter("select * from product", MyConnection)
MyCommand.Fill(DS, "ProductTable")

MyCommand As New SqlDataAdapter("select * from order", MyConnection)
MyCommand.Fill(DS, "OrderTable")

Your DataSet will now have 3 table-sets of results labelled as "EmployeeTable", "ProductTable" and "OrderTable".

To assign any of these tables to a control you would use the following:

MyDataList.DataSource = DS.Tables("ProductTable").DefaultView

Answer 2

Maybe some clarity on your code might help explain:

...
MyCommand.Fill(DS, "TABLENAME")
MyDataList.DataSource = DS.Tables("TABLENAME").DefaultView
...

To answer Q#1:

Why is the datalist pulling field2 successfully?

In the your code block you had "field1" where a table name would normally go so maybe that was causing some confusion when viewing the code. The datalist is pulling the field "field1" correctly because it is a field and your SQL query. Your SQL is "select *" which I assume is selecting "field1" and "field2" as part of the query.

To answer Q2:

Are the MyCommand.Fill(DS, "field1") and DS.Tables("field1").DefaultView meaningless?

The "Fill" method is extracting the data from your database and populating the dataset. In your code you specify "field1" in the parameter which is actually the Table name.

The setting of the datalist's datasource to the DefaultView is actually binding the datalist to the datatable within the dataset not to a field:

MyDataList.DataSource = DS.Tables("TABLENAME").DefaultView

To answer Q3:

I guess my bottom-line question is, why do I have to specify fields in the vb if I can output any field?

You still need to specify the fields to be output to your application. The datalist is iterating over the rows in your datatable and requires that you specify the columns from each row that you want to output to the screen.