How to populate a listview in ASP.NET 3.5 through a dataset?

Question

Is it possible to populate a listview with a dataset? I have a function that returns a dataset. Why im asking this is because my SQL is quite complicated and i can't convert it to a SQLDataSource...

Public Function getMessages() As DataSet
    Dim dSet As DataSet = New DataSet
    Dim da As SqlDataAdapter
    Dim cmd As SqlCommand
    Dim SQL As StringBuilder
    Dim connStr As StringBuilder = New StringBuilder("")
    connStr.AppendFormat("server={0};", ConfigurationSettings.AppSettings("USERserver").ToString())
    connStr.AppendFormat("database={0};", ConfigurationSettings.AppSettings("USERdb").ToString())
    connStr.AppendFormat("uid={0};", ConfigurationSettings.AppSettings("USERuid").ToString())
    connStr.AppendFormat("pwd={0};", ConfigurationSettings.AppSettings("USERpwd").ToString())
    Dim conn As SqlConnection = New SqlConnection(connStr.ToString())
    Try
        SQL = New StringBuilder
        cmd = New SqlCommand
        SQL.Append("SELECT m.MESSAGE_ID, m.SYSTEM_ID, m.DATE_CREATED, m.EXPIRE_DATE, ISNULL(s.SYSTEM_DESC,'ALL SYSTEMS') AS SYSTEM_DESC, m.MESSAGE ")
        SQL.Append("FROM MESSAGE m ")
        SQL.Append("LEFT OUTER JOIN [SYSTEM] s ")
        SQL.Append("ON m.SYSTEM_ID = s.SYSTEM_ID ")
        SQL.AppendFormat("WHERE m.SYSTEM_ID IN ({0}) ", sSystems)
        SQL.Append("OR m.SYSTEM_ID is NULL ")
        SQL.Append("ORDER BY m.DATE_CREATED DESC; ")

        SQL.Append("SELECT mm.MESSAGE_ID, mm.MODEL_ID, m.MODEL_DESC ")
        SQL.Append("FROM MESSAGE_MODEL mm ")
        SQL.Append("JOIN MODEL m ")
        SQL.Append("    ON m.MODEL_ID = mm.MODEL_ID ")
        cmd.CommandText = SQL.ToString
        cmd.Connection = conn
        da = New SqlDataAdapter(cmd)
        da.Fill(dSet)
        dSet.Tables(0).TableName = "BASE"
        dSet.Tables(1).TableName = "MODEL"
        Return dSet
    Catch ev As Exception
        cLog.EventLog.logError(ev, cmd)
    Finally
        'conn.Close()
    End Try
End Function

I've got a tip for you. Look up LINQ and SubSonic. A stored procedure (SQL Server) would even make this a lot more simple. — WVDominick, Apr 08 '10 at 14:01
Another tip for you: Your sampe code must be as simple as possible. Instead of investigation on your problem, people have to 'compile' your strings in mind, believing that join is right and all tables DO exist. — Ilya Smagin, Apr 08 '10 at 15:47

Germ · Answer 1 · 2010-04-08T15:43:30.263

2

Where to start with this one...

listview.DataSource = getMessages().Tables(0); 
listview.DataBind;

edited Apr 08 '10 at 15:43

answered Apr 08 '10 at 15:32

Germ

6,360
4
26
26

score 1 · Accepted Answer · answered Apr 08 '10 at 15:33

Sure. In the code-behind just set the datasource to the DataTable you want to bind the list to, then call DataBind() on the list:

Protected Sub Page_Load(ByVal sender As object, ByVal e As EventArgs)
    If Not Page.IsPostBack Then
       Me.LoadData()    
    End If
End Sub

Protected Sub LoadData()
    Dim ds As DataSet = Me.GetData()
    Me.lstSample.DataSource = ds("Model")
    Me.lstSample.DataBind()
End Sub

(hopefully that's valid VB.NET code, I normally code in C#).

How to populate a listview in ASP.NET 3.5 through a dataset?

2 Answers2