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
