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"
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.
Of course.
If you check the database you should have two
new rows.
