[thelist] .NET newb - populating form from database, then updating - huge long question

Wed Aug 1 17:39:24 CDT 2007

I'm creating a tool to grab a single record from a database table,
populate a form field, then allow the fields to be modified so the table
can be updated.

<background blather)
While this whole function could be done by an experienced and careful
SQL person using Query Anaylyzer or whatever, this tool needs to be
usable by junior network admins and include some basic data validation
</bb>

Here, in all its pain, is the page, followed by the vb codebehind file.
At present, I'm back to an error regarding calling Populate_Form() in
the Page_Load sub, but there are other errors I'm aware of (but
certainly don't know how to fix) like the value of txtEmpID should
become the value passed from another form, allowing that value to be
used in the SQL query in Populate_Form() but it's blank. However, even
if I explicitly set it to a valid value, the query doesn't appear to
return any records so I'm apparently trying to write nothing to all the
form fields.

Any and all coding suggestions are welcome. I'd rather start out
somewhere in the neighborhood of best practices, rather than what I did
with classic ASP :(

The page:

==================================================
<%@ Page Inherits="Single_Table_Update" Src="singletableupdate.vb"
Debug="True" %>
<Script Runat="Server">
Sub Page_Load
	If Not IsPostBack Then
		txtEmpID.Text = Request.Form("EmpID2").ToString()
		Populate_Form()
	End If
End Sub
</Script>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<title>Helm's Deep</title>
  <meta name="description" content="Helm's Deep" />
  <meta name="keywords" content="Helm's Deep" />
	<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1" />
	<meta http-equiv="imagetoolbar" content="no" />
	<link rel="stylesheet" type="text/css"
href="http://minerva:8080/main.css" />
</head>
<body>
<div id="header">
	<h1><a href="http://minerva:8080/default.asp">The Helm</a></h1>
	<h2>Network Tools</h2>
</div>
<div id="content">
<script src="http://minerva:8080/dateselector.js"></script>

<div align="center">
	<form name="theForm"  Runat="Server">
	<asp:TextBox id="txtEmpID" Columns="4" Runat="Server"
Visible="True" />

		<table cellpadding="3" cellspacing="0" border="1">
			<tr>
				<td>DIDid</td>
				<td>OfficeID</td>
				<td rowspan="4">DIDid and OfficeID are
<strong>not</strong> the same as<br />
				phone number and office number.<br />
				<strong>Do not </strong> update these
fields<br />
				unless you know exactly what you are
doing.</td>
			</tr>
			<tr>
				<td>
				<asp:TextBox id="txtDIDid" Columns="20"
Runat="Server" /></td>
				<td>
				<asp:TextBox id="txtOfficeID"
Columns="20" Runat="Server" /></td>
			</tr>
			<tr>
				<td>Last Name</td>
				<td>First Name</td>
			</tr>
			<tr>
				<td>
				<asp:TextBox id="txtLastName"
Columns="20" Runat="Server" /></td>
				<td>
				<asp:TextBox id="txtFirstName"
Columns="20" Runat="Server" /></td>
			</tr>
			<tr>
				<td>Department</td>
				<td>Title</td>
				<td>Notes</td>
			</tr>
			<tr>
				<td>
				<asp:TextBox id="txtDepartment"
Columns="20" Runat="Server" /></td>
				<td>
				<asp:TextBox id="txtTitle" Columns="20"
Runat="Server" /></td>
				<td rowspan="3">
				<asp:TextBox id="txtNotes"
TextMode="multiline" Columns="48" Rows="4" Runat="Server" /></td>
			</tr>
			<tr>
				<td>Hire Date</td>
				<td>Term Date</td>
			</tr>
			<tr>
				<td>
				<asp:TextBox id="txtHire" Columns="12"
Runat="Server" /><a href="javascript:show_calendar('theForm.txtHire');"
onclick="document.theForm.txtHire.style.backgroundColor='white';"><img
src="http://minerva:8080/calendar.gif" width="24" height="22" border="1"
align="absmiddle" id="txtHireImg" /></a></td>
				<td>
				<asp:TextBox id="txtTermination"
Columns="12" Runat="Server" /><a
href="javascript:show_calendar('theForm.txtTermination');"
onclick="document.theForm.txtTermination.style.backgroundColor='white';"
><img src="http://minerva:8080/calendar.gif" width="24" height="22"
border="1" align="absmiddle" id="txtTerminationImg" /></a></td>
			</tr>
			<tr>
				<td colspan="3" align="center">
				<asp:button id="btnUpdate" Text="Update
Table" OnClick="Update_Table" Runat="Server" /></td>
			</tr>
		</table>
	</form>
</div>
<!--#include virtual="/footer.inc"-->

==================================================
The codebehind vb:
==================================================

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls

Public Class Single_Table_Update
	Inherits Page

	Sub Populate_Form()
		Dim cnxHelm as SqlConnection
		Dim strQuery as String
		Dim cmdGetEmpInfo as SqlCommand
		Dim dtrEmpInfo as SqlDataReader
		Dim txtEmpID, txtDIDid, txtOfficeID, txtLastName,
txtFirstName, txtNotes, txtDepartment, txtTitle, txtHire, txtTermination
as TextBox

		cnxHelm = New
SqlConnection("server=Minerva;UID=ntuser;PWD=password;Database=Helm")
		strQuery = "select DIDid, OfficeID, LastName, FirstName,
Notes, Department, Title, Hire, Termination from Employees where id =
@EmpID"
		cmdGetEmpInfo = New SqlCommand(strQuery, cnxHelm)
			cmdGetEmpInfo.Parameters.Add("@EmpID", "57")
		cnxHelm.Open()
			dtrEmpInfo = cmdGetEmpInfo.ExecuteReader()
			While dtrEmpInfo.Read()
				txtDIDid.Text = dtrEmpInfo("DIDid")
				txtOfficeID.Text =
dtrEmpInfo("OfficeID")
				txtLastName.Text =
dtrEmpInfo("LastName")
				txtFirstName.Text =
dtrEmpInfo("FirstName")
				txtNotes.Text = dtrEmpInfo("Notes")
				txtDepartment.Text =
dtrEmpInfo("Department")
				txtTitle.Text = dtrEmpInfo("Title")
				txtHire.Text = dtrEmpInfo("Hire")
				txtTermination.Text =
dtrEmpInfo("Termination")
			End While
			dtrEmpInfo.Close()
		cnxHelm.Close()
	End Sub

	Sub Update_Table(s As Object, e As EventArgs)
		Dim cnxHelm as SqlConnection
		Dim strUpdate as String
		Dim cmdUpdate as SqlCommand
		Dim txtEmpID, txtDIDid, txtOfficeID, txtLastName,
txtFirstName, txtNotes, txtDepartment, txtTitle, txtHire, txtTermination
as TextBox

		cnxHelm = New
SqlConnection("server=Minerva;UID=ntuser;PWD=password;Database=Helm")
		strUpdate = "update Employees set DIDid = @DIDid,
OfficeID = @OfficeID, LastName= @LastName, FirstName = @FirstName, Notes
= @Notes, Department = @Department, Title = @Title, Hire = @Hire,
Termination = @Termination where id = @EmpID"
		cmdUpdate = New SqlCommand(strUpdate, cnxHelm)
			cmdUpdate.Parameters.Add("@EmpID",
txtEmpID.Text)
			cmdUpdate.Parameters.Add("@DIDid",
txtDIDid.Text)
			cmdUpdate.Parameters.Add("@OfficeID",
txtOfficeID.Text)
			cmdUpdate.Parameters.Add("@LastName",
txtLastName.Text)
			cmdUpdate.Parameters.Add("@FirstName",
txtFirstName.Text)
			cmdUpdate.Parameters.Add("@Notes",
txtNotes.Text)
			cmdUpdate.Parameters.Add("@Department",
txtDepartment.Text)
			cmdUpdate.Parameters.Add("@Title",
txtTitle.Text)
			cmdUpdate.Parameters.Add("@Hire", txtHire.Text)
			cmdUpdate.Parameters.Add("@Termination",
txtTermination.Text)
		cnxHelm.Open()
			cmdUpdate.ExecuteNonQuery()
		cnxHelm.Close()
	End Sub
End Class

==================================================
If you've made it this far, ask me what you've won . . . 

thanks

joel