get-dataset-control.ascx

This is a book about vb.you could learn this from this book

<%@Control Language="VB"%>

<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.Common" %>
<%@Import Namespace="System.Data.OleDb" %>

<script language="vb" runat="server">

Public Function BooksDataSet(strConnect As String, strISBN As String) As DataSet

   'specify the SELECT statement to extract the BookList data
   Dim strSelectBooks As String
   strSelectBooks = "SELECT * FROM BookList WHERE ISBN LIKE'" & strISBN & "'"

   'specify the SELECT statement to extract the BookAuthor data
   Dim strSelectAuthors As String
   strSelectAuthors = "SELECT * FROM BookAuthors WHERE ISBN LIKE'" & strISBN & "'"

   'specify the SELECT statement to extract the BookPrices data
   Dim strSelectPrices As String
   strSelectPrices = "SELECT * FROM BookPrices WHERE ISBN LIKE'" & strISBN & "'"

   'declare a variable to hold a DataSet object
   'note that we have to create it outside the Try..Catch block
   'as this is a separate block and so is a different scope
   Dim objDataSet As New DataSet()

   Try

      'create a new Connection object using the connection string
      Dim objConnect As New OleDbConnection(strConnect)

      'create a new Command object
      Dim objCommand As New OleDbCommand()

      'set the properties
      objCommand.Connection = objConnect
      objCommand.CommandType = CommandType.Text
      objCommand.CommandText = strSelectBooks

      'create a new DataAdapter object
      Dim objDataAdapter As New OleDbDataAdapter()
      'and assign the Command object to it
      objDataAdapter.SelectCommand = objCommand

      'get the data from the "BookList" table in the database and
      'put it into a table named "Books" in the DataSet object
      objDataAdapter.Fill(objDataSet, "Books")

      'change the SELECT statement in the ADOCommand object
      objCommand.CommandText = strSelectAuthors
      'then get data from "BookAuthors" table into the DataSet
      objDataAdapter.Fill(objDataSet, "Authors")

      'and do the same again to get the "BookPrices" data
      objCommand.CommandText = strSelectPrices
      objDataAdapter.Fill(objDataSet, "Prices")

      'declare a variable to hold a DataRelation object
      Dim objRelation As DataRelation

      'create a Relation object to link Books and Authors
      objRelation = New DataRelation("BookAuthors", _
                        objDataSet.Tables("Books").Columns("ISBN"), _
                        objDataSet.Tables("Authors").Columns("ISBN"))
      'and add it to the DataSet object's Relations collection
      objDataSet.Relations.Add(objRelation)

      'now do the same to link Books and Prices
      objRelation = New DataRelation("BookPrices", _
                        objDataSet.Tables("Books").Columns("ISBN"), _
                        objDataSet.Tables("Prices").Columns("ISBN"))
      objDataSet.Relations.Add(objRelation)

   Catch objError As Exception

      'display error details
      'return Nothing on error
      BooksDataSet = Nothing
      Exit Function  ' and stop execution

   End Try

   'return our new disconnected DataSet object
   Return objDataSet

End Function

</script>