ASP.NET Basics (Part 9): Different Strokes - Winds of Change
(Page 4 of 5 )
Winds of Change
Now, how about updating the database? Well, it's a little more involved. First, I'll need some code to list all the available records and allow the user to edit any of them. And before all you ASP.NET purists start screaming about the blatant use of in-line coding (contrary to the best practices described for this platform), I want to emphasize that this has been done only for simplicity in explanation.
<%@ Import Namespace="System.Data"%><%@ Import Namespace="System.Data.SqlClient"%>
<SCRIPT language=C# runat="server">
void Page_Load()
{
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
// create an instance of the Command object
SqlCommand objCommand = new SqlCommand("SELECT * FROM starwars;", objConn);
// open the connection
objConn.Open();
// populate an SqlDataReader object
SqlDataReader objReader = objCommand.ExecuteReader();
// output some HTML code
Response.Write("<basefont face="Arial">");
Response.Write("<div align="center">");
Response.Write("<p><b>Star Wars - Registration Desk</b></p>");
Response.Write("<table border="1" cellspacing="2"
cellpadding="2">");
Response.Write("<tr>");
// display the fields in the
for(int count = 0; count < objReader.FieldCount; count++)
{
Response.Write("<td><b>" + objReader.GetName(count).ToUpper() +
"</b></td>");
}
Response.Write("<td><b>EDIT</b></td></tr>");
// read each record from the result set and display in the table
while(objReader.Read())
{
Response.Write("<tr>");
Response.Write("<td>" + objReader.GetValue(0) + "</td>");
Response.Write("<td>" + objReader.GetValue(1) + "</td>");
Response.Write("<td>" + objReader.GetValue(2) + "</td>");
Response.Write("<td>" + objReader.GetValue(3) + "</td>");
Response.Write("<td>" + objReader.GetValue(4) + "</td>");
Response.Write("<td>" + objReader.GetValue(5) + "</td>");
Response.Write("<td><a href="edit.aspx?cid=" + objReader.GetValue(0) +
"">Edit</a></td>");
Response.Write("</tr>");
}
Response.Write("</table>");
Response.Write("</div>");
// clear up memory by closing all objects
objReader.Close();
objConn.Close();
}
</SCRIPT>
This is identical to the example I used in the previous segment of this tutorial. One minor addition - an "Edit" button appears next to each row, linking to "edit.aspx" and passing it the record ID.
Here's what it looks like:
The script "edit.aspx" displays the values for the selected record in a form, and allows the user to make changes to it. Here it is:
<%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %>
<SCRIPT language=C# runat="server">
string strName;
string strHomeworld;
string strSpecies;
string strGender;
string strAffiliation;
string strCid;
void Page_Load()
{
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
// the SQL query
string strSQL = "SELECT * FROM starwars";
// create an SqlDataAdapter object
SqlDataAdapter objAdapter =new SqlDataAdapter(strSQL,objConn);
// create a new DataSet object
DataSet objDataSet = new DataSet();
// populate the DataSet object
objAdapter.Fill(objDataSet, "starwars");
// create an instance of the DataTable
// in order to add a new record
DataTable objTab = objDataSet.Tables["starwars"];
DataRow[] objRowToChange = objTab.Select("id = " +
Request.QueryString["cid"]);
// populate the form fields
strName = objRowToChange[0]["name"].ToString();
strHomeworld = objRowToChange[0]["homeworld"].ToString();
strSpecies = objRowToChange[0]["species"].ToString();
strGender = objRowToChange[0]["gender"].ToString();
strAffiliation = objRowToChange[0]["affiliation"].ToString();
strCid = Request.QueryString["cid"].ToString();
// clear up memory by closing all objects
objDataSet.Clear();
objConn.Close();
}
</SCRIPT>
<BASEFONT face=Arial>
<FORM action=update.aspx method=post>
<DIV align=center>
<TABLE cellSpacing=5 cellPadding=10 border=2>
<TBODY>
<TR>
<TD align=middle colSpan=2><B>Registration Desk</B></TD></TR>
<TR>
<TD>Name<B>*</B></TD>
<TD><INPUT style="BACKGROUND-COLOR: #ffffa0"
value="<% Response.Write(strName); %>" name=name></TD></TR>
<TR>
<TD>Home World</TD>
<TD><INPUT value="<%Response.Write(strHomeworld); %>" name=homeworld></TD></TR>
<TR>
<TD>Species</TD>
<TD><INPUT value="<%Response.Write(strSpecies); %>" name=species></TD></TR>
<TR>
<TD>Gender</TD>
<TD><INPUT value="<% Response.Write(strGender);%>" name=gender></TD></TR>
<TR>
<TD>Affiliation</TD>
<TD><INPUT value="<%Response.Write(strAffiliation); %>"
name=affiliation></TD></TR>
<TR><INPUT type=hidden value="<% Response.Write(strCid); %>" name=cid>
<TD align=middle
colSpan=2><INPUT id=submit type=submit value=Submit></TD></TR></TBODY></TABLE></DIV></FORM>
The code starts by declaring the variables that will be used to store the values from the database and populate the HTML form fields later.
<%
string strName;
string strHomeworld;
string strSpecies;
string strGender;
string strAffiliation;
string strCid;
%>
Next, the SqlConnection, SqlDataAdapter, DataSet and DataTable objects are created as before, and the fields of the selected record are extracted from the database. I've not done this in the traditional way - instead, I've used the Select() method of the DataTable object. This allows me to specify a simple condition to filter out all but the required row(s). The result set generated by the query is stored in an array called "objRowToChange"; this is an array of DataRow objects, and can contain more than one row (if the query returns multiple records).
<%
// create datatable object
DataTable objTab = objDataSet.Tables["starwars"];
DataRow[] objRowToChange = objTab.Select("id = " +
Request.QueryString["cid"]);
// populate the form fields
strName = objRowToChange[0]["name"].ToString();
strHomeworld = objRowToChange[0]["homeworld"].ToString();
strSpecies = objRowToChange[0]["species"].ToString();
strGender = objRowToChange[0]["gender"].ToString();
strAffiliation = objRowToChange[0]["affiliation"].ToString();
strCid = Request.QueryString["cid"].ToString();
%>
Now, all that's left is to access the appropriate fields of the record, convert them into strings via the ToString() method, and then use Response.Write() to write these values to the relevant HTML form elements. Here's the result:
Note that this HTML form submits to another ASPX page, aptly called "update.aspx", which does the actual UPDATE. Let's take a quick peek at that next, shall we?
Command and Control
The "update.aspx" script takes the data entered into the form and uses it to update the corresponding database record. Here's how:
<%@ Page Language="C#" Debug="true" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %>
<SCRIPT language=C# runat="server">
void Page_Load()
{
if(Request.Form["name"] == "" )
{
output.Style["color"] = "Red";
output.Text = "<p>Please enter your name.</p>";
url.Text = "<p><a href="javascript:history.go(-1)">Click here to go
back.</a></p>";
return;
}
else
{
// we have all the data,
// let's update
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
// build the SQL query first
String strSQL = "UPDATE starwars SET name = @name, homeworld =
@homeworld, species = @species, gender = @gender, affiliation =
@affiliation WHERE id = @id";
// create an instance of the Command object
SqlCommand objCommand = new SqlCommand(strSQL, objConn);
// populate the variables in our UPDATE query above
// the "name" field
objCommand.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar, 50));
objCommand.Parameters["@name"].Value = Request.Form["name"];
// the "homeworld" field
objCommand.Parameters.Add(new SqlParameter("@homeworld",
SqlDbType.VarChar, 50));
objCommand.Parameters["@homeworld"].Value = Request.Form["homeworld"];
// the "species" field
objCommand.Parameters.Add(new SqlParameter("@species", SqlDbType.VarChar,
50));
objCommand.Parameters["@species"].Value = Request.Form["species"];
// the "gender" field
objCommand.Parameters.Add(new SqlParameter("@gender", SqlDbType.VarChar,
50));
objCommand.Parameters["@gender"].Value = Request.Form["gender"];
// the "affiliation" field
objCommand.Parameters.Add(new SqlParameter("@affiliation",
SqlDbType.VarChar, 50));
objCommand.Parameters["@affiliation"].Value = Request.Form["affiliation"];
// the "id" field
objCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 4));
objCommand.Parameters["@id"].Value = Request.Form["cid"];
// uncomment this for debugging
// Response.Write(objCommand.CommandText);
// open the connection
objConn.Open();
// execute the query
objCommand.ExecuteNonQuery();
// display message to user
output.Style["color"] = "Green";
output.Text = "<p>Record updated successfully.</p>";
url.Text = "<p><a href="./listing.aspx">Click here for list</a></p>";
// close all
objConn.Close();
}
}
</SCRIPT>
<BASEFONT face=Arial>
<DIV align=center><B>Registration Desk</B> <asp:label id=output runat="server"
name="output"></asp:label><asp:label id=url runat="server"
name="url"></asp:label></DIV>
The first part of this script is identical to the one I wrote earlier, so I'll jump straight to the parts that are different. Here, the primary difference lies in the way the UPDATE query is constructed - since the query uses form variables, it has been created using placeholder variables that will eventually be replaced with the actual data entered by the user.
<%
// build the SQL query first
String strSQL = "UPDATE starwars SET name = @name, homeworld = @homeworld,
species = @species, gender = @gender, affiliation = @affiliation WHERE id
= @id";
// create an instance of the Command object
SqlCommand objCommand = new SqlCommand(strSQL, objConn);
%>
And here's the code that actually performs the variable interpolation:
<%
// populate the variables in our UPDATE query above
// the "name" field
objCommand.Parameters.Add(new SqlParameter("@name",SqlDbType.VarChar,50));
objCommand.Parameters["@name"].Value = Request.Form["name"];
// the "homeworld" field
objCommand.Parameters.Add(new
SqlParameter("@homeworld",SqlDbType.VarChar,50));
objCommand.Parameters["@homeworld"].Value = Request.Form["homeworld"];
// the "species" field
objCommand.Parameters.Add(new SqlParameter("@species",SqlDbType.VarChar,50));
objCommand.Parameters["@species"].Value = Request.Form["species"];
// the "gender" field
objCommand.Parameters.Add(new SqlParameter("@gender",SqlDbType.VarChar,50));
objCommand.Parameters["@gender"].Value = Request.Form["gender"];
// the "affiliation" field
objCommand.Parameters.Add(new
SqlParameter("@affiliation",SqlDbType.VarChar,50));
objCommand.Parameters["@affiliation"].Value = Request.Form["affiliation"];
// the "id" field
objCommand.Parameters.Add(new SqlParameter("@id",SqlDbType.Int,4));
objCommand.Parameters["@id"].Value = Request.Form["cid"];
%>
The SqlCommand object's "Parameters" property, which is actually a collection of SQL parameters, stores the values for the various placeholders in the UPDATE query. Once they've all been set, the data can be committed to the database by calling the SqlCommand object's ExecuteNonQuery() method. This method is used to execute SQL commands such as INSERT, DELETE, and UPDATE, that do not return any values.
You can use the previous example to delete selected record(s) from the table as well. I'll leave that to you - just change the UPDATE query to a DELETE query in "update.aspx" and you should be on your way!
Next: Endgame >>
More ASP.NET Articles
More By Harish Kamath (c) Melonfire
/ 18 
