' ASCX_Northwind_Dataset_and_Status_WebUserControl_wucDataSet.ascx
'
' Mostly writen by Dan Garlen dan.g@kgsystems.com 2/22/04
' Use at your own risk - but, have fun doing it.
' Portions of this code have been shamlessly copied from other unnamed sources - thank you to all.
'
' Place a copy of "Northwind.mdb" in the project virtual directory ie C:\InetPub\wwwRoot\DataGridExample
' Create this WebUserControl named as "wucDataSet.ascx"
' Create a Client WebForm with a DataGrid (leave default name of "DataGrid1")
' Drag "wucDataSet.ascx" onto WebForm
' Leave name as default "WucDataSet1"
' Add this line of code: "Protected WithEvents wucDataSet1 As wucDataSet" under DataGrid1 declaration
'
' Modify Page_Load as shown
'
'    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'        Dim ds As DataSet = wucDataSet1.dsNorthwind
'        If wucDataSet1.Status Then
'            DataGrid1.DataSource = ds
'            DataGrid1.DataMember = "tblCustomers"
'            DataGrid1.DataBind()
'        End If
'    End Sub

Imports db = System.Data.OleDb

Public Class wucDataSet
    Inherits System.Web.UI.UserControl

#Region " Web Form Designer Generated Code "
' ...
#End Region

    Private m_StatusString As String = "No data available"
    Private m_Status As Boolean = False

    Public ReadOnly Property StatusString() As String
        Get
            Return m_Status
        End Get
    End Property

    Public ReadOnly Property Status() As Boolean
        Get
            Return m_Status
        End Get
    End Property


    Public ReadOnly Property dsNorthwind() As DataSet
        Get
            dsNorthwind = NorthwindDataSet(m_StatusString)
        End Get
    End Property

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        lblStatus.Text = m_StatusString
    End Sub


    Private Function NorthwindDataSet(ByRef Results As String) As DataSet
        Dim strSQL As String = "SELECT * FROM [Customers]"
        Dim strMDB As String = Server.MapPath("Northwind.mdb")
        Dim strDB As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data source ='" + strMDB + "';"

        Dim cn As New db.OleDbConnection
        cn.ConnectionString = strDB

        Try
            ' Setup Command
            Dim cmd As New db.OleDbCommand
            cmd.CommandText = strSQL
            cmd.CommandType = CommandType.Text  ' This is the default type
            cmd.Connection = cn

            'Setup DataAdapter
            Dim da As New db.OleDbDataAdapter
            da.SelectCommand = cmd

            ' Setup and populate DataSEt
            Dim ds As New DataSet
            da.Fill(ds, "tblCustomers")

            ' Cleanup after ourselves
            cmd = Nothing
            da = Nothing
            cn.Close()
            cn = Nothing
            m_Status = True
            Results = ds.Tables("tblCustomers").Rows.Count.ToString + " rows returned"
            Return ds
        Catch ex As Exception
            m_Status = False
            Results = "Houston, we have a problem: [" + ex.Message + "]"
            Return Nothing
        End Try
    End Function

End Class