OleDbDataAdapter & Inserting Records

[Melagan]

Greetings,

I am looking at the following code snippit in "SAM's Learn ASP.NET in 21 Days"

Dim Conn as New OleDbConnection( _
  "Provider=Microsoft.Jet.Oledb.4.0;" & _
  "Data Source="X:\mydb.mdb")

Dim ds as New DataSet("myDataSet")
Dim objCmd as New OleDbDataAdapter("SELECT * FROM " & _
  "tblUsers WHERE UserID < 10", Conn)

  objCmd.Fill(ds, "tblUsers")

Dim dr as DataRow = ds.Tables("tblUsers").NewRow()
  dr(0) = "Greg"
  dr(1) = "Smith"
  dr(2) = "434 Maple Apt B"
  dr(3) = "Minneapolis"
  dr(4) = "MN"
  dr(5) = "12588"
  dr(6) = "5189876259"
  ds.Tables("tblUsers").Rows.Add(dr)

  objCmd.InsertCommand = new OleDbCommand
  objCmd.InsertCommand.Text = "Insert INTO " & _
    "tblUsers (FirstName, LastName, Address, City, " & _
    "State, Zip, Phone) VALUES ('Greg', 'Smith', " & _ 
    "'434 Maple Apt B', 'Minneapolis', 'MN', '12588', " & _
    "'5189876259'"
  objCmd.InsertCommand.Connection = Conn

The book then goes on to say:

Note that these commands don't really alter any data. They merely provide commands to instruct ADO.NET how you want to push the data that you've already changed. For example, if you hadn't made any changes on lines [data row above], the command methods would do nothing, regardless of the SQL statements you specified.

Unless there is something that I'm missing or not understanding, this seems like double duty to me. What is the point of adding a new row to a DataSet, only to have it do nothing for you when you call the Update method to update your underlying database without having to manually write the SQL with the values you've already added to the dataset to insert the data?

I know you can use the OleDbCommandBuilder() object to generate the SQL automatically - but this still seems like double duty.




~Melagan
______
"It's never too late to become what you might have been.

 

[jmeckley]

This is acutally a seperation of duties, not double duty. The DataSet's purpose is to hold relational data in memory. They are most commonly used with DB CRUD operations, but they truely have nothing to do with the database. Therefore another object is required to allow the DataSet to communicate with the persistant storage.

The OleDbCommandBuilder object is the communication layer between a DataSet and a database.

For more information research "seperation of dutities", "agile programming", and "n-tier application design"

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[Melagan]

I think I understand what you're saying. Another answer to a more straight foward scenario might help my understanding as well.

Lets say that I create a DataSet from a table in a persistent database.

Dim myConn as New OleDbConnection (_
  ConnectionManager.AppSettings("ConnectionString"))
Dim strSQL as String = "SELECT * FROM tblMyTable"
Dim ds as DataSet = New DataSet _
  ("myDataSet")
Dim objCmd as New OleDbDataAdapter(strSQL, myConn)
  objCmd.Fill(ds, "tblMyTable")
[COLOR=green]'I note that the Fill method automatically opens and closes connection to database.[/color]

I understand now that I am working with my own DataSet, independant of the persistant database. Lets now say that I make some changes to the DataSet:

Dim strSQL as String = _ 
"[COLOR=blue]INSERT INTO tblMyTable (First, Last, eMail) VALUES ('John', 'Doe', 'jdoe@email.com')[/color]"

Dim objCmd as New OleDbCommand(strSQL, myConn)
  objCmd.ExecuteNonQuery()

Now that the DataSet has some new data, how can I effectivly and, hopefully, simply update the underlying database with the new data in the DataSet? Where would objCmd.Update() come into play?

~Melagan
______
"It's never too late to become what you might have been.

 

[jmeckley]

It's been awhile since I have worked directly with ADO.Net so I can say for sure how to hook up CRUD operations to the DataSet. however if you google ADO.Net or OleDbDataAdapter you will get plenty of information on the subject.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[Melagan]

I have looked all over the place, both in my books and online, and have yet to find a straight-forward example for something I'm sure is a pretty standard situation. Once I know how to do this, I can use this technique as a foundation to build on. Here goes:

mypage.aspx
interface:
a server control that displays data from a database, perhaps a repeater.

For that, I'll need to retrieve data from the database. Since I'll eventually be writing to the database, I might as well use a DataSet right upfront, correct?

So here is what I have so far:

<script runat="server">
Dim myConn as New OleDbConnection(ConfigurationManager.ConnectionStrings("Conn").ConnectionString
Dim strSELECT as String = "SELECT * From tblRegistery"
Dim objCmd as OleDbDataAdapter(strSELECT, myConn)
Dim ds as DataSet

Sub Page_Load(Sender as Object, e as EventArgs)
   If Not Page.IsPostBack Then
     FillRepeater()
   End If
End Sub

Sub FillRepeater()
   Try
     objCmd.Fill(ds, "registery")
   Catch ex as Exception
     Response.Write(ex.ToString)
   Finally
     rptRegistry.DataSource = ds.Tables("registery")
     DataBind()
   End Try
End Sub
</script>

<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] >
<head runat="server">
    <title>mypage.aspx</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:Repeater ID="rptRegister" runat="server">
        <HeaderTemplate>
            <table>
                <tr>
                    <th>ID</th>
                    <th>First</th>
                    <th>Last</th>
                    <th>Email</th>                        
                </tr>
        </HeaderTemplate>
        <ItemTemplate>
                <tr>
                    <td>
                    <%# Container.DataItem("UserID") %>
                    </td>
                    <td>
                    <%# Container.DataItem("First") %>
                    </td>
                    <td>
                    <%# Container.DataItem("Last") %>
                    </td>
                    <td>
                    <%# Container.DataItem("Email") %>
                    </td>
                </tr>
        </ItemTemplate>
        <FooterTemplate>
            </table>
        </FooterTemplate>
    </asp:Repeater>
    <table>
        <tr>
            <th>First</th>
            <th>Last</th>
            <th>Email</th>
        </tr>
        <tr>
            <td><asp:TextBox ID="txtFirst" runat="server" /></td>
            <td><asp:TextBox ID="txtLast" runat="server" /></td>
            <td><asp:TextBox ID="txtEmail" runat="server" /></td>                        
        </tr>
    </table>
</div>
</form>
</body>
</html>

The above gives me a simple interface with which to display all of the data from tblRegistery in my underlying database in the Repeater control, while also loading it into a DataSet. On the same page, below the repeater, there are three text boxes for me to enter additional data - First, Last, and Email.
My question: How do I insert that data into the DataSet, have it display in the repeater control once the page posts back to itself, then ultimately take all of the new data in the DataSet and update the underlying database?1
I'm sure I'll need two command buttons to do this - one to "submit" data and have it show in the repeater (and add to the DataSet), and one to "accept" the data and update the Database; I'm just not sure how to code those buttons to do what I want.

Thanks again in advance!

~Melagan
______
"It's never too late to become what you might have been.

 

[jmeckley]

1st seperate your markup from your code using code behind files. it's much easier to maintain.

Sub FillRepeater()
   Try
     objCmd.Fill(ds, "registery")
     rptRegistry.DataSource = ds.Tables("registery")
     [b]rptRegistry.DataBind()[/b]
   Catch ex as Exception
     Response.Write(ex.ToString)
   End Try
End Sub

Repeater controls are good for 1 way (read only) databinding. for read/write controls I would recommend the GridView (DataGrid 1.1).

Jason Meckley
Programmer
Specialty Bakers, Inc.