|
 |
aspx_beginners thread: ASP.NET 1.0 Example HELP
Message #1 by jchristiana@k... on Tue, 19 Nov 2002 15:54:39
|
|
In chapter 7 page 363. I am trying to finish this example. Can anyone help
finish this. I am new to asp.net and am trying to make this example update
a row. Everything works except for the updating part. I wish WROX would
have finished this example. This is my results when I show the SQL Query
when I press update:
The SQL statement that would be executed is:
UPDATE LabData SET catName='Name', catDate='06/22/1956', catNotify='Hi
MOM', catTime='2:22:00 AM', catReport='Report', catCharges='Charges',
catFormCompleted='From Compleated', catLocation='Location',
catMedicalRecords='Medical Records', catErroneous='Erroneous',
catFormComDate='06/22/1955', catBy='By', catMicroPath='Micro Path',
catNewANumber='NewANumber', catResultsEdited='Results Edited',
catResultsEntered='Results Enterd', catEditReason='Edit Reason' WHERE
IdUni='613200211332017'
Plese help. Thanks
Message #2 by "Ben Densmore" <ben_densmore@e...> on Tue, 19 Nov 2002 10:44:44 -0500
|
|
That looks like a complete Update statement. I'm not familiar with the
example you are talking about but what makes you think it's not
complete?
Ben
-----Original Message-----
From: jchristiana@k... [mailto:jchristiana@k...]
Sent: Tuesday, November 19, 2002 3:55 PM
To: aspx_beginners
Subject: [aspx_beginners] ASP.NET 1.0 Example HELP
In chapter 7 page 363. I am trying to finish this example. Can anyone
help
finish this. I am new to asp.net and am trying to make this example
update
a row. Everything works except for the updating part. I wish WROX would
have finished this example. This is my results when I show the SQL Query
when I press update:
The SQL statement that would be executed is:
UPDATE LabData SET catName='Name', catDate='06/22/1956', catNotify='Hi
MOM', catTime='2:22:00 AM', catReport='Report', catCharges='Charges',
catFormCompleted='From Compleated', catLocation='Location',
catMedicalRecords='Medical Records', catErroneous='Erroneous',
catFormComDate='06/22/1955', catBy='By', catMicroPath='Micro Path',
catNewANumber='NewANumber', catResultsEdited='Results Edited',
catResultsEntered='Results Enterd', catEditReason='Edit Reason' WHERE
IdUni='613200211332017'
Plese help. Thanks
Message #3 by jchristiana@k... on Tue, 19 Nov 2002 16:48:42
|
|
Yes it looks very complete. I don?t know why but since I have started
working with asp.net I cant seem to figure out how to submit this data too
my SQL database.
This is the code that I am attempting to write, so it will send my data to
a 2000 SQL database
void ExecuteSQLStatement(string strSQL)
{
SqlConnection thisConnection = new SqlConnection(
@"Data Source=KRMC-VPN;user
id=HelpDesk;password=????????;" +
"Initial Catalog=PPR");
thisConnection.Open();
SqlDataAdapter thisAdapter = new SqlDataAdapter(
"SELECT * FROM LabData", thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder
(thisAdapter);
//DataSet thisDataSet = new DataSet();
//thisAdapter.Fill(thisDataSet, "LabData");
//DataRow thisRow = thisDataSet.Tables["LabData"].NewRow();
lblSQL.Text = "<b>The SQL statement that would be executed
is:</b><br />" + strSQL; //This line just displays the data.
//thisDataSet.Tables["LabData"].Rows.Update(thisRow);
//thisAdapter.Update (thisDataSet, "LabData");
// thisConnection.ExecuteNonQuery();
thisConnection.Close();
}
All I want is when the update button is pressed the data displayed gets
sent to my SQL database. The SQL query looks like it should work. But the
example only shows how to display the data. It doesn?t submit it to the
database. If they finishd the example in the book I would be able to
complete this. Let me know if you want to look at all the code. I can send
it to you. I would really like to solve this. I am probably missing
something real easy here.
<<<<Selecting and Editing Data in a DataList Control>>>>
Here is the entire page of code:
<%@ Page Language="C#" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Register TagPrefix="wrox" TagName="connect" Src="\global\connect-
strings.ascx" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><head>
<title>Selecting and Editing Data in a DataList Control</title>
<style type="text/css">
body, td {font-family:Tahoma,Arial,sans-serif; font-size:10pt}
input {font-family:Tahoma,Arial,sans-serif; font-size:8pt}
.heading {font-family:Tahoma,Arial,sans-serif; font-size:14pt; font-
weight:bold}
.subhead {font-family:Tahoma,Arial,sans-serif; font-size:12pt; font-
weight:bold; padding-bottom:5px}
.cite {font-family:Tahoma,Arial,sans-serif; font-size:8pt}
.rHead {font-family:Lucida Handwriting,Comic Sans MS,Tahoma,Arial;
font-size:14pt; font-weight:bold; padding:8px; color:green}
.rItem {font-family:Lucida Handwriting,Comic Sans MS,Tahoma,Arial,sans-
serif;
: font-size:10pt}
.rFoot {font-family:Tahoma,Arial; font-size:8pt; padding:8px;
color:darkgray}
</style></head>
<body bgcolor="#ffffff">
<span class="heading">Problem Patients</span><hr /><a
href="http://10.6.3.2:53/ProblemPatient.aspx">Back<<<</a><BR>
<!-------------------------------------------------------------------------
-->
<%// -- insert connection string script --%>
<wrox:connect id="ctlConnectStrings" runat="server" />
<div id="outError" runat="server" />
<ASP:Label id="lblSQL" runat="server" /><p />
<form runat="server">
<ASP:DataList id="MyDataList" runat="server"
CellSpacing = "2"
SelectedItemStyle-BackColor="LightGreen"
SelectedItemStyle-ForeColor="Black"
EditItemStyle-BackColor="yellow"
EditItemStyle-ForeColor="black"
DataKeyField="IdUni"
OnItemCommand="DoItemSelect"
OnEditCommand="DoItemEdit"
OnUpdateCommand="DoItemUpdate"
OnDeleteCommand="DoItemDelete"
OnCancelCommand="DoItemCancel">
<ItemTemplate>
<ASP:Button CommandName="Select" Text="View" runat="server" />
<%# DataBinder.Eval(Container.DataItem, "PPatientName") %>
</ItemTemplate>
<SelectedItemTemplate>
<ASP:Button CommandName="Edit" Text="Click To Enter Corrective
Actions For This Patient" runat="server" /><br /><CENTER>Form Number: <b><%
# DataBinder.Eval(Container.DataItem, "IdUni") %></b></CENTER><br />
<b>Problem Patient</b><br />
Name: <b><%# DataBinder.Eval(Container.DataItem, "PPatientName") %
> </b>Birth Date: <b><%# DataBinder.Eval
(Container.DataItem, "PPatientBD", "{0:D}") %><br /></b>
SS#: <b><%# DataBinder.Eval
(Container.DataItem, "PPatientSS") %
></b> Unit#: <b><%# DataBinder.Eval
(Container.DataItem, "PPatientUnit") %
></b> Account#: <b><%# DataBinder.Eval
(Container.DataItem, "PPatientAccount") %></b><br />
<br /><b>Correct Patient</b><br />
Name: <b><%# DataBinder.Eval
(Container.DataItem, "CPatientName") %
> </b>Birth Date: <b><%# DataBinder.Eval
(Container.DataItem, "CPatientBD", "{0:D}") %><br /></b>
SS#: <b><%# DataBinder.Eval
(Container.DataItem, "CPatientSS") %
></b> Unit#: <b><%# DataBinder.Eval
(Container.DataItem, "CPatientUnit") %
></b> Account#: <b><%# DataBinder.Eval
(Container.DataItem, "CPatientAccount") %></b><br />
<br />
<b>Error discovered by:</b><br />
Name: <b><%# DataBinder.Eval(Container.DataItem, "Errordisbyname") %
></b> Date: <b><%# DataBinder.Eval
(Container.DataItem, "Errordisbydate") %
></b> Time: <b><%# DataBinder.Eval
(Container.DataItem, "Errordisbytime") %></b><br />
Comments: <b><%# ((String)DataBinder.Eval
(Container.DataItem, "Comments")).Replace("\r\n","<br>") %></b>
</SelectedItemTemplate>
<EditItemTemplate>
<ASP:Button CommandName="Update" Text="Save Corrections"
runat="server" />
<ASP:Button CommandName="Delete" Text="File To History"
runat="server" />
<ASP:Button CommandName="Cancel" Text="Cancel"
runat="server" /><br />
<b>Patient Selected: <%# DataBinder.Eval
(Container.DataItem, "PPatientName") %></b><BR />
<B>CIS notified:</B><BR />
Name:
<ASP:TextBox id="txtcatname" Text='<%# DataBinder.Eval
(Container.DataItem, "catName") %>'
size="10" runat="server" />
<asp:RequiredFieldValidator id="RequiredFieldValidator16" runat="server"
ControlToValidate="txtcatname"
ErrorMessage="*"
ForeColor="Red"
Display="dynamic">
</asp:RequiredFieldValidator>
Date:
<asp:textbox id="txtcatDate" Text='<%# DataBinder.Eval
(Container.DataItem, "catDate") %>'
size="10" runat="server" />
<asp:CompareValidator id="valCompareDate1" runat="server"
ControlToValidate="txtcatDate"
ValueToCompare="3/3/1800"
Operator="GreaterThan"
Type="Date"
ErrorMessage="mm/dd/yyyy"
ForeColor="Red"
Display="dynamic">
</asp:CompareValidator>
Time:
<ASP:TextBox id="txtcatTime" Text='<%# DataBinder.Eval
(Container.DataItem, "catTime") %>'
size="10" runat="server" />
<asp:RequiredFieldValidator id="RequiredFieldValidator1" runat="server"
ControlToValidate="txtcatTime"
ErrorMessage="*"
ForeColor="Red"
Display="dynamic">
</asp:RequiredFieldValidator>
By:
<ASP:TextBox id="txtcatby" Text='<%# DataBinder.Eval
(Container.DataItem, "catBy") %>'
size="10" runat="server" /><asp:RequiredFieldValidator
id="RequiredFieldValidator2" runat="server"
ControlToValidate="txtcatby"
ErrorMessage="*"
ForeColor="Red"
Display="dynamic">
</asp:RequiredFieldValidator><br />
New Account Number given to correct patient:
<ASP:TextBox id="txtcatNewANumber" Text='<%# DataBinder.Eval
(Container.DataItem, "catNewANumber") %>'
size="32" runat="server" /><asp:RequiredFieldValidator
id="RequiredFieldValidator3" runat="server"
ControlToValidate="txtcatNewANumber"
ErrorMessage="*"
ForeColor="Red"
Display="dynamic">
</asp:RequiredFieldValidator><br />
Results edited out of wrong patient in Meditech:
<ASP:TextBox id="txtcatResultsEdited" Text='<%# DataBinder.Eval
(Container.DataItem, "catResultsEdited") %>'
size="30" runat="server" /><asp:RequiredFieldValidator
id="RequiredFieldValidator4" runat="server"
ControlToValidate="txtcatResultsEdited"
ErrorMessage="*"
ForeColor="Red"
Display="dynamic">
</asp:RequiredFieldValidator><br />
Erroneous reports on wrong patient retrieved Doctor:
<ASP:TextBox id="txtcatErroneous" Text='<%# DataBinder.Eval
(Container.DataItem, "catErroneous") %>'
size="24" runat="server" /><asp:RequiredFieldValidator
id="RequiredFieldValidator5" runat="server"
ControlToValidate="txtcatErroneous"
ErrorMessage="*"
ForeColor="Red"
Display="dynamic">
</asp:RequiredFieldValidator><br />
&nb
sp; Location:
<ASP:TextBox id="txtcatLocation" Text='<%# DataBinder.Eval
(Container.DataItem, "catLocation") %>'
size="34" runat="server" /><asp:RequiredFieldValidator
id="RequiredFieldValidator6" runat="server"
ControlToValidate="txtcatLocation"
ErrorMessage="*"
ForeColor="Red"
Display="dynamic">
</asp:RequiredFieldValidator><br />
&nb
sp; Medical Records:
<ASP:TextBox id="txtcatMedicalRecords" Text='<%# DataBinder.Eval
(Container.DataItem, "catMedicalRecords") %>'
size="25" runat="server" /><asp:RequiredFieldValidator
id="RequiredFieldValidator7" runat="server"
ControlToValidate="txtcatMedicalRecords"
ErrorMessage="*"
ForeColor="Red"
Display="dynamic">
</asp:RequiredFieldValidator><br />
Results entered on correct patient in Meditech:
<ASP:TextBox id="txtcatResultsEntered" Text='<%# DataBinder.Eval
(Container.DataItem, "catResultsEntered") %>'
size="31" runat="server" /><asp:RequiredFieldValidator
id="RequiredFieldValidator8" runat="server"
ControlToValidate="txtcatResultsEntered"
ErrorMessage="*"
ForeColor="Red"
Display="dynamic">
</asp:RequiredFieldValidator><br />
Micro/Path reports resent to EMR to update:
<ASP:TextBox id="txtcatMicroPath" Text='<%# DataBinder.Eval
(Container.DataItem, "catMicroPath") %>'
size="34" runat="server" /><asp:RequiredFieldValidator
id="RequiredFieldValidator9" runat="server"
ControlToValidate="txtcatMicroPath"
ErrorMessage="*"
ForeColor="Red"
Display="dynamic">
</asp:RequiredFieldValidator><br />
Charges removed from wrong patient:
<ASP:TextBox id="txtcatCharges" Text='<%# DataBinder.Eval
(Container.DataItem, "catCharges") %>'
size="41" runat="server" /><asp:RequiredFieldValidator
id="RequiredFieldValidator10" runat="server"
ControlToValidate="txtcatCharges"
ErrorMessage="*"
ForeColor="Red"
Display="dynamic">
</asp:RequiredFieldValidator><br />
Notify office/location of error:
<ASP:TextBox id="txtcatNotify" Text='<%# DataBinder.Eval
(Container.DataItem, "catNotify") %>'
size="30" runat="server" /><asp:RequiredFieldValidator
id="RequiredFieldValidator11" runat="server"
ControlToValidate="txtcatNotify"
ErrorMessage="*"
ForeColor="Red"
Display="dynamic">
</asp:RequiredFieldValidator><br />
Report printed on correct patient:
<ASP:TextBox id="txtcatReport" Text='<%# DataBinder.Eval
(Container.DataItem, "catReport") %>'
size="26" runat="server" /><asp:RequiredFieldValidator
id="RequiredFieldValidator12" runat="server"
ControlToValidate="txtcatReport"
ErrorMessage="*"
ForeColor="Red"
Display="dynamic">
</asp:RequiredFieldValidator><br />
Edit Reason for Visit on wrong Acc#:
<ASP:TextBox id="txtcatEditReason" Text='<%# DataBinder.Eval
(Container.DataItem, "catEditReason") %>'
size="22" runat="server" /><asp:RequiredFieldValidator
id="RequiredFieldValidator13" runat="server"
ControlToValidate="txtcatEditReason"
ErrorMessage="*"
ForeColor="Red"
Display="dynamic">
</asp:RequiredFieldValidator><br />
Form completed by:
<ASP:TextBox id="txtcatFormCompleted" Text='<%# DataBinder.Eval
(Container.DataItem, "catFormCompleted") %>'
size="30" runat="server" /><asp:RequiredFieldValidator
id="RequiredFieldValidator14" runat="server"
ControlToValidate="txtcatFormCompleted"
ErrorMessage="*"
ForeColor="Red"
Display="dynamic">
</asp:RequiredFieldValidator>
Date:
<asp:textbox id="txtcatFormComDate" Text='<%# DataBinder.Eval
(Container.DataItem, "catFormComDate") %>'
size="10" runat="server" />
<asp:CompareValidator id="valCompareDate" runat="server"
ControlToValidate="txtcatFormComDate"
ValueToCompare="3/3/1800"
Operator="GreaterThan"
Type="Date"
ErrorMessage="mm/dd/yyyy"
ForeColor="Red"
Display="dynamic">
</asp:CompareValidator>
<br />
</EditItemTemplate>
</ASP:DataList>
</form>
<!-------------------------------------------------------------------------
-->
<script language="C#" runat="server">
void Page_Load(Object sender, EventArgs e)
{
if (!Page.IsPostBack)
BindDataGrid(); // create data set and
bind to list control
}
void DoItemSelect(Object objSource, DataListCommandEventArgs
objArgs )
{
lblSQL.Text = ""; // clear any content from
SQL statement Label
// see if it was the Select button that was clicked
if (objArgs.CommandName == "Select")
{
// set the SelectedIndex property of the list to
this item// s index
MyDataList.SelectedIndex = objArgs.Item.ItemIndex;
BindDataGrid(); // bind the data and
display it
}
}
//<------------------------------------------------------------------------
--->
void DoItemEdit(Object objSource, DataListCommandEventArgs objArgs)
{
MyDataList.SelectedIndex = -1;
MyDataList.EditItemIndex = objArgs.Item.ItemIndex;
BindDataGrid();
}
//<------------------------------------------------------------------------
--->
void DoItemUpdate(Object objSource, DataListCommandEventArgs
objArgs)
{
TextBox objcatNameCtrl = (TextBox)
objArgs.Item.FindControl("txtcatName");
TextBox objcatDateCtrl = (TextBox)
objArgs.Item.FindControl("txtcatDate");
TextBox objcatTimeCtrl = (TextBox)
objArgs.Item.FindControl("txtcatTime");
TextBox objcatByCtrl = (TextBox)
objArgs.Item.FindControl("txtcatBy");
TextBox objcatNotifyCtrl = (TextBox)
objArgs.Item.FindControl("txtcatNotify");
TextBox objcatReportCtrl = (TextBox)
objArgs.Item.FindControl("txtcatReport");
TextBox objcatFormCompletedCtrl = (TextBox)
objArgs.Item.FindControl("txtcatFormCompleted");
TextBox objcatFormComDateCtrl = (TextBox)
objArgs.Item.FindControl("txtcatFormComDate");
TextBox objcatLocationCtrl = (TextBox)
objArgs.Item.FindControl("txtcatLocation");
TextBox objcatMedicalRecordsCtrl = (TextBox)
objArgs.Item.FindControl("txtcatMedicalRecords");
TextBox objcatChargesCtrl = (TextBox)
objArgs.Item.FindControl("txtcatCharges");
TextBox objcatMicroPathCtrl = (TextBox)objArgs.Item.FindControl
("txtcatMicroPath");
TextBox objcatErroneousCtrl = (TextBox)objArgs.Item.FindControl
("txtcatErroneous");
TextBox objcatEditReasonCtrl = (TextBox)objArgs.Item.FindControl
("txtcatEditReason");
TextBox objcatResultsEnteredCtrl = (TextBox)
objArgs.Item.FindControl("txtcatResultsEntered");
TextBox objcatResultsEditedCtrl = (TextBox)
objArgs.Item.FindControl("txtcatResultsEdited");
TextBox objcatNewANumberCtrl = (TextBox)objArgs.Item.FindControl
("txtcatNewANumber");
string strSQL = "UPDATE LabData SET catName='" +
objcatNameCtrl.Text + "', "
+ "catDate='" + objcatDateCtrl.Text + "', "
+ "catNotify='" + objcatNotifyCtrl.Text
+ "', "
+ "catTime='" + objcatTimeCtrl.Text + "', "
+ "catReport='" + objcatReportCtrl.Text
+ "', "
+ "catCharges='" + objcatChargesCtrl.Text
+ "', "
+ "catFormCompleted='" + objcatFormCompletedCtrl.Text
+ "', "
+ "catLocation='" + objcatLocationCtrl.Text + "', "
+ "catMedicalRecords='" + objcatMedicalRecordsCtrl.Text
+ "', "
+ "catErroneous='" + objcatErroneousCtrl.Text + "', "
+ "catFormComDate='" + objcatFormComDateCtrl.Text
+ "', "
+ "catBy='" + objcatByCtrl.Text + "', "
+ "catMicroPath='" + objcatMicroPathCtrl.Text + "', "
+ "catNewANumber='" + objcatNewANumberCtrl.Text + "', "
+ "catResultsEdited='" + objcatResultsEditedCtrl.Text
+ "', "
+ "catResultsEntered='" + objcatResultsEnteredCtrl.Text
+ "', "
+ "catEditReason='"
+ objcatEditReasonCtrl.Text + "' "
+ "WHERE IdUni='" + MyDataList.DataKeys
[objArgs.Item.ItemIndex] + "'";
ExecuteSQLStatement(strSQL);
// set EditItemIndex property of grid to -1 to switch out
of Edit mode
MyDataList.EditItemIndex = -1;
BindDataGrid(); // bind the data and display it
}
//<------------------------------------------------------------------------
--->
void DoItemDelete(Object objSource, DataListCommandEventArgs
objArgs)
{
// create a suitable SQL statement and execute it
string strSQL = "DELETE FROM LabData WHERE IdUni='"
+ MyDataList.DataKeys
[objArgs.Item.ItemIndex] + "'";
ExecuteSQLStatement(strSQL);
// set EditItemIndex property of grid to -1 to switch out
of Edit mode
MyDataList.EditItemIndex = -1;
BindDataGrid(); // bind the data and display it
}
//<------------------------------------------------------------------------
--->
void DoItemCancel(Object objSource, DataListCommandEventArgs
objArgs)
{
// set EditItemIndex property of grid to -1 to switch out
of Edit mode
MyDataList.EditItemIndex = -1;
BindDataGrid(); // bind the data and display it
}
//<------------------------------------------------------------------------
--->
void ExecuteSQLStatement(string strSQL)
{
SqlConnection thisConnection = new SqlConnection(
@"Data Source=KRMC-VPN;user
id=HelpDesk;password=????????;" +
"Initial Catalog=PPR");
thisConnection.Open();
SqlDataAdapter thisAdapter = new SqlDataAdapter(
"SELECT * FROM LabData", thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder
(thisAdapter);
//DataSet thisDataSet = new DataSet();
//thisAdapter.Fill(thisDataSet, "LabData");
//DataRow thisRow = thisDataSet.Tables["LabData"].NewRow();
lblSQL.Text = "<b>The SQL statement that would be executed
is:</b><br />" + strSQL;
//thisDataSet.Tables["LabData"].Rows.Update(thisRow);
//thisAdapter.Update (thisDataSet, "LabData");
// thisConnection.ExecuteNonQuery();
thisConnection.Close();
}
void BindDataGrid()
{
// get connection string from ..\global\connect-
strings.ascx user control
string strConnect =
ctlConnectStrings.OLEDBConnectionString;
// create a SQL statement to select some rows from the
database
string strSelect = "SELECT * FROM LabData";
// create a variable to hold an instance of a DataReader
object
OleDbDataReader objDataReader;
try
{
// create a new Connection object using the
connection string
OleDbConnection objConnect = new OleDbConnection
(strConnect);
// open the connection to the database
objConnect.Open();
// create a new Command using the connection
object and select statement
OleDbCommand objCommand = new OleDbCommand
(strSelect, objConnect);
// execute the SQL statement against the command
to get the DataReader
objDataReader = objCommand.ExecuteReader();
}
catch (Exception objError)
{
// display error details
outError.InnerHtml = "<b>* Error while accessing
data</b>.<br />"
+
objError.Message + "<br />" + objError.Source + "<p />";
return; // and stop execution
}
// set the DataSource property and bind the list
MyDataList.DataSource = objDataReader;
MyDataList.DataBind();
}
</script>
</body>
</html>
|
|
|
View Cart
