ASP.NET & Databases Part 3
page 3 of 5
by . .
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 19660/ 81

Changing data and putting it back

Making some changes

We're going to do a simple example of where we take our data, change it and then update the data source.

Here is the table setup (designed for Microsoft Access)

Field Name Type
FName Text
LName Text
ID AutoNumber

 

 

FName LName ID
Philip Quinn 1
Joesph Payne 2
Douglas Adams 3
Michael Okuda 4

This is a pretty simple database, but we will be using it for all of our needs.

Example 1

Ok, I'll give you the code hunks, bit by bit and step you through it, then you can combine them to test it out.

<%@ Page Language="VB" %>

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Oledb" %>
<script language="VB" runat="server">
Sub Page_Load(sender as object, e as eventargs)

This is just your standard opening the language is VB and we need those two namespaces to do the data stuff.
 

Dim objConn as New OleDBConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=e:\sff\site\db\test.mdb")
objConn.Open()

Dim ds as Dataset = New DataSet()
Dim objAdapter as New OleDbDataAdapter("SELECT * FROM users", objConn)
Dim objCmdBld as New OleDbCommandBuilder(objAdapter)

objAdapter.Fill(ds, "users")
objConn.Close()

Ok, now we open the connection to the database (remember to specify your own database path when you do it).
We then open it (using the Open() method)
We declare a DataSet, a DataAdapter and a CommandBuilder. Then fill the DataSet and close the connection as we no longer need it.

For those of you who don't pay attention -

  • A DataAdapter is an interface between the database and the DataSet, allowing data to be transferred between both (2-way communication).
  • The CommandBuilder builds the SQL commands that you use to execute the SQL statements (we'll talk about this more soon).

The data is now in the DataSet table called "users"

Dim drow as DataRow

This declares a DataRow, we're going to use this one row to add two rows to the dataset.

drow = ds.Tables("users").NewRow()

drow(0) = "Gene"
drow(1) = "Rodenberry"
ds.Tables("users").Rows.Add(drow)

drow = ds.Tables("users").NewRow()

drow(0) = "Maxwell"
drow(1) = "Stewart"
ds.Tables("users").Rows.Add(drow)

From Part 2 you should be able to gather what this means. We make the DataRow a NewRow() from the DataTable "users", this means that it has the syntax and format of all the columns -

  • drow(0) is FName
  • drow(1) is LName
  • We don't need to specify drow(2) because it is an AutoNumber

We then add the row to the dataset and then go about creating another one.

objConn.Open()
objAdapter.Update(ds, "users")
objConn.Close()

This calls the Update() method of the DataAdapter which then uses the ds dataset's table "users" to update the database using the CommandBuilder's SQL Statements.

End Sub
</script>

Of course.

If you check the database you should have two new rows.


View Entire Article

User Comments

Title: ASP.NET & Databases : Part 3   
Name: ttt
Date: 2005-04-01 1:27:18 PM
Comment:
good

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-07-22 3:49:42 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search