UPDATE Statement to SQL from .NET

[EDN Admin]

Im not entirely sure if I am actually in the right forum but I found similar topics in this forum so I am going to give it a shot. If it is, I ask for someone to politely lead me in the correct direction.
Right, my problem is with updating a SQL Server 2012 Database from text boxes. I have already used this method to add a record (getting the data from the text boxes and adding them to the database). However, when I try to update a selected record it
just does not seem to work. My code is as follows:
This page and code is a new window for a page that includes a repeater full of the data that is received from the SQL database. When you click view, the code below and page is what is shown with the data selected.
<pre class="prettyprint public void btnSave_Click(object sender, EventArgs e)
{
string connectionString = "my string connection here";
string cmdText = "UPDATE TestExpenseForm SET Expense_Date=@Expense_Date, Expense_Detail=@Expense_Detail, Expense_PostcodeFrom=@Expense_PostcodeFrom, Expense_PostcodeTo=@Expense_PostcodeTo," +
"Expense_Miles=@Expense_Miles, Expenses_Passengers=@Expenses_Passengers, Expense_TravelCost=@Expense_TravelCost, Expense_Hospitality=@Expense_Hospitality, Expense_NoInParty=@Expense_NoInParty," +
"Expense_NoWCInParty=@Expense_NoWCInParty, Expense_TotalEntert=@Expense_TotalEntert, Expense_3rdPartyEntert=@Expense_3rdPartyEntert, Expense_StaffEntert=@Expense_StaffEntert," +
"Expense_CommCharges=@Expense_CommCharges, Expense_MiscDetail=@Expense_MiscDetail, Expense_VATRecInc=@Expense_VATRecInc, Expense_Gross=@Expense_Gross, Expense_Net=@Expense_Net," +
"Expense_VAT=@Expense_VAT, Status_Id=@Status_Id, CostCode_Id=@CostCode_Id, Expense_SalesCode=@Expense_SalesCode WHERE Expense_Id=@Expense_Id";

using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(cmdText, con);

cmd.Parameters.AddWithValue("@Expense_Id", Convert.ToInt32(txtExpense_Id.Text));
string[] dates = txtExpense_Date.Text.Split(/);
cmd.Parameters.AddWithValue("@Expense_Date", Convert.ToDateTime(dates[2] + "-" + dates[1] + "-" + dates[0]));
cmd.Parameters.AddWithValue("@Expense_Detail", ddlstExpense_Detail.SelectedItem.Text);
cmd.Parameters.AddWithValue("@Expense_PostcodeFrom", txtExpense_PostcodeFrom.Text);
cmd.Parameters.AddWithValue("@Expense_PostcodeTo", txtExpense_PostcodeTo.Text);

decimal miles;
miles = (String.IsNullOrEmpty(txtExpense_Miles.Text)) ? Convert.ToDecimal(0.00) : Convert.ToDecimal(txtExpense_Miles.Text);
cmd.Parameters.AddWithValue("@Expense_Miles", miles);

int passengers;
passengers = (String.IsNullOrEmpty(txtExpense_Passengers.Text)) ? Convert.ToInt32(0) : Convert.ToInt32(txtExpense_Passengers.Text);
cmd.Parameters.AddWithValue("@Expenses_Passengers", passengers);

decimal travel;
travel = (String.IsNullOrEmpty(txtExpense_TravelCost.Text)) ? Convert.ToDecimal(0.00) : Convert.ToDecimal(txtExpense_TravelCost.Text);
cmd.Parameters.AddWithValue("@Expense_TravelCost", travel);

decimal hospitality;
hospitality = (String.IsNullOrEmpty(txtExpense_Hospitality.Text)) ? Convert.ToDecimal(0.00) : Convert.ToDecimal(txtExpense_Hospitality.Text);
cmd.Parameters.AddWithValue("@Expense_Hospitality", hospitality);

int party;
party = (String.IsNullOrEmpty(txtExpense_NoInParty.Text)) ? Convert.ToInt32(0) : Convert.ToInt32(txtExpense_NoInParty.Text);
cmd.Parameters.AddWithValue("@Expense_NoInParty", Convert.ToInt32(txtExpense_NoInParty.Text));

int wc;
wc = (String.IsNullOrEmpty(txtExpense_NoWCInParty.Text)) ? Convert.ToInt32(0) : Convert.ToInt32(txtExpense_NoWCInParty.Text);
cmd.Parameters.AddWithValue("@Expense_NoWCInParty", Convert.ToInt32(txtExpense_NoWCInParty.Text));

decimal totalEntert;
totalEntert = (String.IsNullOrEmpty(txtExpense_TotalEntert.Text)) ? Convert.ToDecimal(0.00) : Convert.ToDecimal(txtExpense_TotalEntert.Text);
cmd.Parameters.AddWithValue("@Expense_TotalEntert", totalEntert);

cmd.Parameters.AddWithValue("@Expense_3rdPartyEntert", (totalEntert / Convert.ToInt32(txtExpense_NoInParty.Text)) * (Convert.ToInt32(txtExpense_NoInParty.Text) - Convert.ToInt32(txtExpense_NoWCInParty.Text)));
cmd.Parameters.AddWithValue("@Expense_StaffEntert", (totalEntert / Convert.ToInt32(txtExpense_NoInParty.Text)) * Convert.ToInt32(txtExpense_NoWCInParty.Text));

decimal comm;
comm = (String.IsNullOrEmpty(txtExpense_CommCharges.Text)) ? Convert.ToDecimal(0.00) : Convert.ToDecimal(txtExpense_CommCharges.Text);
cmd.Parameters.AddWithValue("@Expense_CommCharges", comm);

cmd.Parameters.AddWithValue("@Expense_MiscDetail", txtExpense_MiscDetail.Text);

cmd.Parameters.Add("@Expense_Gross", (travel + hospitality) + totalEntert + comm * Convert.ToDecimal(0.8));
cmd.Parameters.Add("@Expense_Net", travel + hospitality + totalEntert + comm);
cmd.Parameters.Add("@Expense_VAT", (travel + hospitality + totalEntert + comm) / 100 * 20);
cmd.Parameters.AddWithValue("@Status_Id", ddlstExpense_Status.SelectedValue);
cmd.Parameters.AddWithValue("@Costcode_Id", ddlstExpense_Detail.SelectedValue);
cmd.Parameters.AddWithValue("@Expense_SalesCode", user.SalesCode);
// get managers salescode needs to go here

if (ddlstExpense_VATRecInc.SelectedItem.Text == "Yes")
{
cmd.Parameters.AddWithValue("@Expense_VATRecInc", Convert.ToBoolean(1));
}

else
{
cmd.Parameters.AddWithValue("@Expense_VATRecInc", Convert.ToBoolean(0));
}

try
{
con.Open();
Int32 rowsAffected = cmd.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", rowsAffected);
string msg = "Your record has been submitted";
string js = "<script language=javascript>alert(" + msg + "); window.location = http://hc-sps-dev2/_layouts/Expense_Report/Expenses_Form.aspx;</script>";
ClientScript.RegisterClientScriptBlock(GetType(), "Register", js);
}

catch (Exception ex)
{
Console.WriteLine(ex.Message);
}

con.Close();

HttpContext context = HttpContext.Current;

if (HttpContext.Current.Request.QueryString["IsDlg"] != null)
{
context.Response.Write("<script type=text/javascript>window.frameElement.commitPopup()</script>");
context.Response.Flush();
context.Response.End();
}
}
}[/code]

When a user changes any of the contents of the text boxes, its happy. But when the code executes through, it changes all of the text boxes back to the original entry, apart from Status_Id. That changes and saves to the database. The other problem is the
drop down list in the code for the Expense_Detail. It will always default to value 2, occasionally 1 but more often 2. I have no idea why. These are the only things that change. Below is the code for the ASP.NET page associated with the code:

<pre class="prettyprint <asp:Content ID="Main" ContentPlaceHolderID="PlaceHolderMain" runat="server
<asp:Label ID="lblEdit" CssClass="titleLBL" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_Date" Text="Date" CssClass="lbl" runat="server" />

<asp:TextBox ID="txtExpense_Id" Visible="false" runat="server" />

<asp:TextBox ID="txtExpense_Date" CssClass="textbox" AutoPostBack="false" runat="server" />
<asp:RegularExpressionValidator ID="regexExpense_Date" ControlToValidate="txtExpense_Date" ValidationExpression="^(((0[1-9]|[12]d|3[01])/(0[13578]|1[02])/((19|[2-9]d)d{2}))|((0[1-9]|[12]d|30)/(0[13456789]|1[012])/((19|[2-9]d)d{2}))|((0[1-9]|1d|2[0-8])/02/((19|[2-9]d)d{2}))|(29/02/((1[6-9]|[2-9]d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))))$" ErrorMessage="Please use the format DD/MM/YYYY" runat="server" />
<asp:RequiredFieldValidator ID ="reqExpense_Date" ControlToValidate="txtExpense_Date" ErrorMessage="Required field" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_Detail" Text="Expense Details" CssClass="lbl" runat="server" />
<aspropDownList ID="ddlstExpense_Detail" runat="server
<asp:ListItem Text="Mileage" Value="1" />
<asp:ListItem Text="Travel" Value="2" />
<asp:ListItem Text="Parking" Value="3" />
<asp:ListItem Text="Meal" Value="4" />
<asp:ListItem Text="Lodging" Value="5" />
<asp:ListItem Text="Telephone" Value="6" />
<asp:ListItem Text="3rd Party Entertainment" Value="7" />
<asp:ListItem Text="Staff Entertainment" Value="8" />
<asp:ListItem Text="Funded" Value="9" />
<asp:ListItem Text="Postage" Value="10" />
<asp:ListItem Text="Eye Test" Value="11" />
<asp:ListItem Text="WC Marketing" Value="12" />
<asp:ListItem Text="Internal Use" Value="13" />
<asp:ListItem Text="Training" Value="14" />
<asp:ListItem Text="Gifts" Value="15" />
<asp:ListItem Text="Stationary" Value="16" />
<asp:ListItem Text="R+M" Value="17" />
</aspropDownList>
<br />
<br />

<asp:Label ID="lblExpense_PostcodeFrom" Text="Postcode From" CssClass="lbl" runat="server" />
<asp:TextBox ID="txtExpense_PostcodeFrom" runat="server" CssClass="textbox" ontextchanged="txtExpense_PostcodeFrom_TextChanged" style="text-transform:uppercase" />
<asp:RegularExpressionValidator ID="regexExpense_PostcodeFrom" ControlToValidate="txtExpense_PostcodeFrom" ValidationExpression="^(GIR 0AA|[A-PR-UWYZ]([0-9]{1,2}|([A-HK-Y][0-9]|[A-HK-Y][0-9]([0-9]|[ABEHMNPRV-Y]))|[0-9][A-HJKPS-UW]) {0,1}[0-9][ABD-HJLNP-UW-Z]{2})$" ErrorMessage="Please enter a valid UK postcode using block capitals" ForeColor="red" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_PostcodeTo" Text="Postcode To" CssClass="lbl" runat="server" />
<asp:TextBox ID="txtExpense_PostcodeTo" CssClass="textbox" ontextchanged="txtExpense_PostcodeTo_TextChanged" style="text-transform:uppercase" runat="server" />
<asp:RegularExpressionValidator ID="regexExpense_PostcodeTo" ControlToValidate="txtExpense_PostcodeTo" ValidationExpression="^(GIR 0AA|[A-PR-UWYZ]([0-9]{1,2}|([A-HK-Y][0-9]|[A-HK-Y][0-9]([0-9]|[ABEHMNPRV-Y]))|[0-9][A-HJKPS-UW]) {0,1}[0-9][ABD-HJLNP-UW-Z]{2})$" ErrorMessage="Please enter a valid UK postcode using block capitals" ForeColor="red" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_Miles" Text="Miles" CssClass="lbl" runat="server" />
<asp:TextBox ID="txtExpense_Miles" runat="server" ontextchanged="txtExpense_Miles_TextChanged" CssClass="textbox" />
<asp:RegularExpressionValidator ID="regexExpense_Miles" ControlToValidate="txtExpense_Miles" ValidationExpression="d+.?d{0,2}$" ErrorMessage="Please only insert numbers" ForeColor="red" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_Passengers" Text="Number of Passengers" CssClass="lbl" runat="server" />
<asp:TextBox ID="txtExpense_Passengers" CssClass="textbox" runat="server" />
<asp:RegularExpressionValidator ID="regexExpense_Passengers" ControlToValidate="txtExpense_Passengers" ValidationExpression="[0-9]+" ErrorMessage="Please only insert whole numbers" ForeColor="red" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_TravelCost" Text="Travel Cost including tolls and parking" CssClass="lbl" runat="server" />
<asp:TextBox ID="txtExpense_TravelCost" CssClass="textbox" ontextchanged="txtExpense_TravelCost_TextChanged" runat="server" />
<asp:RegularExpressionValidator ID="regexExpense_TravelCost" ControlToValidate="txtExpense_TravelCost" ValidationExpression="d+.?d{0,2}$" ErrorMessage="Please only insert numbers" ForeColor="red" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_Hospitality" Text="Meals and Lodging Costs" CssClass="lbl" runat="server" />
<asp:TextBox ID="txtExpense_Hospitality" CssClass="textbox" ontextchanged="txtExpense_Hospitality_TextChanged" runat="server" />
<asp:RegularExpressionValidator ID="regexExpense_Hospitality" ControlToValidate="txtExpense_Hospitality" ValidationExpression="d+.?d{0,2}$" ErrorMessage="Please only insert numbers" ForeColor="red" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_NoInParty" Text="Number of People in Party" CssClass="lbl" runat="server" />
<asp:TextBox ID="txtExpense_NoInParty" CssClass="textbox" runat="server" />
<asp:RegularExpressionValidator ID="regexExpense_NoInParty" ControlToValidate="txtExpense_NoInParty" ValidationExpression="[0-9]+" ErrorMessage="Please only insert whole numbers" ForeColor="red" runat="server" />
<asp:RequiredFieldValidator ID="reqExpense_NoInParty" ControlToValidate="txtExpense_NoInParty" ErrorMessage="Required field" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_NoWCInParty" Text="Number of Westcoast staff in Party" CssClass="lbl" runat="server" />
<asp:TextBox ID="txtExpense_NoWCInParty" CssClass="textbox" runat="server" />
<asp:RegularExpressionValidator ID="regexExpense_NoWCInParty" ControlToValidate="txtExpense_NoWCInParty" ValidationExpression="[0-9]+" ErrorMessage="Please only insert whole numbers" ForeColor="red" runat="server" />
<asp:CompareValidator ID="comExpense_NoWCInParty" Operator="LessThanEqual" Type="Integer" ControlToValidate="txtExpense_NoWCInParty" ControlToCompare="txtExpense_NoInParty" ErrorMessage="You cannot have more Westcoast staff in your party than overall amount of people in your party" runat="server" />
<asp:RequiredFieldValidator ID="reqExpense_NoWCInParty" ControlToValidate="txtExpense_NoWCInParty" ErrorMessage="Required field" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_TotalEntert" Text="Total Cost of Entertainment" CssClass="lbl" runat="server" />
<asp:TextBox ID="txtExpense_TotalEntert" CssClass="textbox" ontextchanged="txtExpense_TotalEntert_TextChanged" runat="server" />
<asp:RegularExpressionValidator ID="regexExpense_TotalEntert" ControlToValidate="txtExpense_TotalEntert" ValidationExpression="d+.?d{0,2}$" ErrorMessage="Please only insert numbers" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_3rdPartyEntert" Text="Third Party Entertainment" CssClass="lbl" runat="server" />
<asp:TextBox ID="txtExpense_3rdPartyEntert" CssClass="textbox" ReadOnly="true" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_StaffEntert" Text="Staff Entertainment" CssClass="lbl" runat="server" />
<asp:TextBox ID="txtExpense_StaffEntert" CssClass="textbox" ReadOnly="true" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_CommCharges" Text="Telephone and Internet Costs" CssClass="lbl" runat="server" />
<asp:TextBox ID="txtExpense_CommCharges" CssClass="textbox" ontextchanged="txtExpense_CommCharges_TextChanged" runat="server" />
<asp:RegularExpressionValidator ID="regexExpense_CommCharges" ControlToValidate="txtExpense_CommCharges" ValidationExpression="d+.?d{0,2}$" ErrorMessage="Please only insert numbers" ForeColor="red" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_MiscDetail" Text="Miscellaneous - Details are required" CssClass="lbl" runat="server" />
<asp:TextBox ID="txtExpense_MiscDetail" CssClass="textbox" MaxLength="255" runat="server" />
<asp:RegularExpressionValidator ID="regexExpense_MiscDetail" ControlToValidate="txtExpense_MiscDetail" ValidationExpression="^.{1,255}$" ErrorMessage="You cannot exceed 255 characters" ForeColor="red" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_VATRecInc" Text="VAT Receipt Included?" CssClass="lbl" runat="server" />
<aspropDownList ID="ddlstExpense_VATRecInc" runat="server
<asp:ListItem Text="Yes" Value="1" />
<asp:ListItem Text="No" Value="0" />
</aspropDownList>
<br />
<br />

<asp:Label ID="lblExpense_Gross" Text="Gross" CssClass="lbl" runat="server" />
<asp:TextBox ID="txtExpense_Gross" CssClass="textbox" ReadOnly="true" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_Net" Text="Net" CssClass="lbl" runat="server" />
<asp:TextBox ID="txtExpense_Net" CssClass="textbox" ReadOnly="true" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_VAT" Text="VAT" CssClass="lbl" runat="server" />
<asp:TextBox ID="txtExpense_VAT" CssClass="textbox" ReadOnly="true" runat="server" />
<br />
<br />

<asp:Label ID="lblExpense_Status" Text="Status" CssClass="lbl" runat="server" />
<aspropDownList ID="ddlstExpense_Status" runat="server
<asp:ListItem Text="Pending" Value="1" />
<asp:ListItem Text="Accepted" Value="2" />
<asp:ListItem Text="Rejected" Value="3" />
<asp:ListItem Text="Paid" Value="4" />
<asp:ListItem Text="Unpaid" Value="5" />
</aspropDownList>
<br />
<br />

<asp:Button ID="Save" Text="Save" CssClass="btnOther" runat="server" OnClick="btnSave_Click" />

</asp:Content>[/code]
<br/>
I hope you take the time to read through this and help me, because it really has me stumped and I just want to progress in my project. Thank you in advance for your time and replies.
<hr class="sig http://sharepointythings.blogspot.co.uk/ http://sharepointythings.blogspot.co.uk

View the full article