By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,560 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

strange update problem .net

P: 1
Hi guys -

I have written a form, and a stored procedure to update the said form.
It really is as simple as that. A user can go into the form, update some fields and hit the update button to update the information which is stored in a SQL database.

In testing we noticed that the form was updating correctly but the update mechanism was also updating the first record of the table in the sql database every time. No error messages are on screen - it looks like everything has happened correctly until you go into the table manually and realise it has updated the first record of the table with exacly the same info as the correct record.

We have tried a number of things here to stop it updating the first record, but can't manage it. It's like the update completely ignores the unique identifier and decides it's going to update the first record in the table also.

Has anyone had trouble like this before?
This is a fairly important application, so if anyone could shed some light on it that would be great.

I have included as much code as I can below - there is an awful lot of it.
If there are any questions you have you need clarified I'd be happy to do that.

Many thanks in advance.
DS


Code Behind


Expand|Select|Wrap|Line Numbers
  1. Partial Class css_ESA55pbc
  2.     Inherits System.Web.UI.Page
  3.     ' Procedure to insert the form
  4.     Protected Sub InsertButton_Click1(ByVal sender As Object, ByVal e As System.EventArgs)
  5.         Try
  6.             'Create a New Connection for the Stored Procedure
  7.             Dim myConnection As New Data.SqlClient.SqlConnection(Me.SqlDataSource1.ConnectionString)
  8.             myConnection.Open()
  9.             Dim myCommand As New Data.SqlClient.SqlCommand("INSERT_FORM", myConnection)
  10.             myCommand.CommandType = Data.CommandType.StoredProcedure
  11.  
  12.             'Add the INSERT parameters
  13.             myCommand.Parameters.AddWithValue("Case_Id", CType(Me.FormView1.FindControl("Case_IDLabel"), Label).Text)
  14.  
  15.             myCommand.Parameters.AddWithValue("Form_Id", CType(Me.FormView1.FindControl("Form_IDLabel"), Label).Text)
  16.  
  17.             myCommand.Parameters.AddWithValue("Date_Issued", Convert.ToDateTime(CType(Me.FormView1.FindControl("Date_Issued"), TextBox).Text))
  18.  
  19.             myCommand.Parameters.AddWithValue("BF_Date", Convert.ToDateTime(CType(Me.FormView1.FindControl("BF_DateTextBox"), TextBox).Text))
  20.  
  21.             ' Test to see if Returned Date Text Box is a null value
  22.             ' if yes insert null
  23.             If ((CType(Me.FormView1.FindControl("Returned_DateTextBox"), TextBox).Text <> "")) Then
  24.                 myCommand.Parameters.AddWithValue("Returned_Date", Convert.ToDateTime(CType(Me.FormView1.FindControl("Returned_DateTextBox"), TextBox).Text))
  25.  
  26.             Else : myCommand.Parameters.AddWithValue("Returned_Date", DBNull.Value)
  27.  
  28.             End If
  29.  
  30.             myCommand.Parameters.AddWithValue("Na", CType(Me.FormView1.FindControl("NA"), CheckBox).Checked)
  31.  
  32.             myCommand.Parameters.AddWithValue("Notes", CType(Me.FormView1.FindControl("NotesTextBox"), TextBox).Text)
  33.  
  34.             'Execute the command
  35.             myCommand.ExecuteReader()
  36.             'Response.Redirect("~/GenericErrorPage.aspx", False)
  37.  
  38.             myConnection.Close()
  39.             'Declare variables and assign values
  40.             Dim CaseRef As String = CType(Me.FormView1.FindControl("Case_IDLabel"), Label).Text.ToString
  41.             Dim FormRef As String = CType(Me.FormView1.FindControl("Form_IDLabel"), Label).Text.ToString
  42.             Response.Redirect("~/logged_in/success.aspx?Form_ID=" & FormRef & "&Case_ID=" & CaseRef, False)
  43.  
  44.         Catch ex As Exception
  45.             Response.Redirect("~/GenericErrorPage.aspx", False)
  46.  
  47.         End Try
  48.  
  49.     End Sub
  50.  
  51.     Protected Sub Calendar1_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs)
  52.         'Assign value to textbox from Calendar
  53.         CType(Me.FormView1.FindControl("BF_DateTextBox"), TextBox).Text = CType(Me.FormView1.FindControl("Calendar1"), Calendar).SelectedDate.AddDays(7).ToString("dd/MM/yyyy")
  54.         CType(Me.FormView1.FindControl("Date_Issued"), TextBox).Text = CType(Me.FormView1.FindControl("Calendar1"), Calendar).SelectedDate.ToString("dd/MM/yyyy")
  55.     End Sub
  56.  
  57.     Protected Sub Calendar2_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs)
  58.         'Assign value to textbox from Calendar
  59.         CType(Me.FormView1.FindControl("Returned_DateTextBox"), TextBox).Text = CType(Me.FormView1.FindControl("Calendar2"), Calendar).SelectedDate.ToString("dd/MM/yyyy")
  60.     End Sub
  61.  
  62.     Protected Sub Calendar3_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs)
  63.         'Assign value to textbox from Calendar
  64.         CType(Me.FormView1.FindControl("BF_DateTextBox"), TextBox).Text = CType(Me.FormView1.FindControl("Calendar3"), Calendar).SelectedDate.ToString("dd/MM/yyyy")
  65.     End Sub
  66.  
  67.     Protected Sub ImageButton1_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
  68.         ' Switches between hiding and showing the calendar
  69.         If Me.FormView1.FindControl("Calendar1").Visible = "false" Then
  70.             Me.FormView1.FindControl("Calendar1").Visible = "true"
  71.         Else
  72.             Me.FormView1.FindControl("Calendar1").Visible = "false"
  73.         End If
  74.     End Sub
  75.     Protected Sub ImageButton2_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
  76.         ' Switches between hiding and showing the calendar
  77.         If Me.FormView1.FindControl("Calendar2").Visible = "false" Then
  78.             Me.FormView1.FindControl("Calendar2").Visible = "true"
  79.         Else
  80.             Me.FormView1.FindControl("Calendar2").Visible = "false"
  81.         End If
  82.     End Sub
  83.  
  84.     Protected Sub ImageButton3_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
  85.         ' Switches between hiding and showing the calendar
  86.         If Me.FormView1.FindControl("Calendar3").Visible = "false" Then
  87.             Me.FormView1.FindControl("Calendar3").Visible = "true"
  88.         Else
  89.             Me.FormView1.FindControl("Calendar3").Visible = "false"
  90.         End If
  91.     End Sub
  92.  
  93.     Public Sub Page_LoadComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  94.         'Try
  95.  
  96.         If Not Page.IsPostBack Then
  97.             If Request.QueryString("update") = "yes" Then
  98.                 'Test if update requested
  99.                 Me.FormView1.DefaultMode = FormViewMode.Edit
  100.                 Dim sqlConnection1 As New Data.SqlClient.SqlConnection(Me.SqlDataSource1.ConnectionString)
  101.                 Dim cmd As New Data.SqlClient.SqlCommand
  102.                 Dim case_id As String
  103.                 Dim form_id As String
  104.                 case_id = Request.QueryString("case_id")
  105.                 form_id = Request.QueryString("form_id")
  106.  
  107.                 'this dim used in selecting case_link_id
  108.                 ' Dim singleReturn As New Data.SqlClient.SqlParameter("@SingleReturn", Data.SqlDbType.Int)
  109.                 Dim singleReturn As New Data.SqlClient.SqlParameter("@RETURN VALUE", Data.SqlDbType.Int)
  110.  
  111.  
  112.                 'these dims used in select for entire record
  113.                 Dim thisCaseID As New Data.SqlClient.SqlParameter("@thisCaseID", Data.SqlDbType.Int)
  114.                 Dim thisFormID As New Data.SqlClient.SqlParameter("@thisFormID", Data.SqlDbType.Int)
  115.                 Dim thisDateIssued As New Data.SqlClient.SqlParameter("@thisDateIssued", Data.SqlDbType.DateTime)
  116.                 Dim thisBFDate As New Data.SqlClient.SqlParameter("@thisBFDate", Data.SqlDbType.DateTime)
  117.                 Dim thisReturnedDate As New Data.SqlClient.SqlParameter("@thisReturnedDate", Data.SqlDbType.DateTime)
  118.                 Dim thisNA As New Data.SqlClient.SqlParameter("@thisNA", Data.SqlDbType.Bit)
  119.                 Dim thisNotes As New Data.SqlClient.SqlParameter("@thisNotes", Data.SqlDbType.VarChar)
  120.                 Dim thisValue As Integer
  121.  
  122.                 sqlConnection1.Open()
  123.                 singleReturn.Direction = Data.ParameterDirection.ReturnValue
  124.                 With cmd
  125.                     ' gets case_link_id
  126.                     .CommandText = "getSingleValue"
  127.                     .CommandType = Data.CommandType.StoredProcedure
  128.                     .Parameters.AddWithValue("@case_id", case_id)
  129.                     .Parameters.AddWithValue("@form_id", form_id)
  130.                     ' .Parameters.AddWithValue("@SingleReturn", 0)
  131.                     '.Parameters.Add(singleReturn).Direction = Data.ParameterDirection.Output
  132.                     '.Parameters.Add(singleReturn).Direction = Data.ParameterDirection.ReturnValue
  133.                     .Parameters.Add(singleReturn)
  134.                     .Connection = sqlConnection1
  135.                     .ExecuteNonQuery()
  136.  
  137.                 End With
  138.                 thisValue = singleReturn.Value
  139.                 CType(Me.FormView1.FindControl("lblCaseLinkID"), Label).Text = thisValue.ToString
  140.  
  141.                 sqlConnection1.Close()
  142.  
  143.                 'Create a New Connection for the Stored Procedure
  144.                 Dim myConnection As New Data.SqlClient.SqlConnection(Me.SqlDataSource1.ConnectionString)
  145.                 myConnection.Open()
  146.  
  147.                 Dim strSQL As String
  148.                 strSQL = "SELECT * FROM tbl_Forms_Case_Link WHERE Form_Case_Link_ID = " & thisValue
  149.                 Dim myCommand As New Data.SqlClient.SqlCommand(strSQL, myConnection)
  150.                 myCommand.CommandType = Data.CommandType.Text
  151.                 Dim dr As Data.SqlClient.SqlDataReader = myCommand.ExecuteReader()
  152.                 Dim arrayForms As New ArrayList()
  153.                 Dim i As Integer
  154.                 i = 0
  155.                 i = dr.FieldCount()
  156.                 'fill array with database fields
  157.                 If dr.HasRows Then
  158.                     While dr.Read()
  159.                         For i = 0 To dr.FieldCount - 1
  160.                             arrayForms.Add(dr.Item(i))
  161.                         Next
  162.                     End While
  163.                 End If
  164.  
  165.                 dr.Close()
  166.  
  167.                 myConnection.Close()
  168.                 'populate form fields from array of fields
  169.                 CType(Me.FormView1.FindControl("Date_Issued"), TextBox).Text = arrayForms.Item(3).ToShortDateString
  170.                 CType(Me.FormView1.FindControl("Calendar1"), Calendar).SelectedDate = arrayForms.Item(3)
  171.                 CType(Me.FormView1.FindControl("BF_DateTextBox"), TextBox).Text = arrayForms.Item(4).ToShortDateString
  172.  
  173.                 If arrayForms.Item(5) IsNot DBNull.Value Then
  174.                     CType(Me.FormView1.FindControl("Returned_DateTextBox"), TextBox).Text = arrayForms.Item(5).ToShortDateString
  175.  
  176.  
  177.                 End If
  178.  
  179.                 CType(Me.FormView1.FindControl("NA"), CheckBox).Checked = arrayForms.Item(6)
  180.  
  181.                 CType(Me.FormView1.FindControl("NotesTextBox"), TextBox).Text = arrayForms.Item(7).ToString
  182.  
  183.  
  184.             Else
  185.                 If Not Page.IsPostBack Then
  186.                     'pre-populate date fields and calendar
  187.                     CType(Me.FormView1.FindControl("Date_Issued"), TextBox).Text = Date.Now.ToString("dd/MM/yyyy")
  188.                     CType(Me.FormView1.FindControl("BF_DateTextBox"), TextBox).Text = Date.Now.AddDays(7).ToString("dd/MM/yyyy")
  189.                     CType(Me.FormView1.FindControl("Calendar1"), Calendar).SelectedDate = CType(Me.FormView1.FindControl("Date_Issued"), TextBox).Text
  190.  
  191.                 End If
  192.  
  193.                 If Page.IsPostBack Then
  194.                     'fill BF Date information automatically
  195.                     CType(Me.FormView1.FindControl("BF_DateTextBox"), TextBox).Text = Convert.ToDateTime(CType(Me.FormView1.FindControl("Date_Issued"), TextBox).Text).Date.AddDays(7).ToShortTimeString("dd/MM/yyyy")
  196.                     CType(Me.FormView1.FindControl("Calendar1"), Calendar).SelectedDate = Convert.ToDateTime(CType(Me.FormView1.FindControl("Date_Issued"), TextBox).Text).Date.AddDays(7).ToString("dd/MM/yyyy")
  197.  
  198.                 End If
  199.             End If
  200.         End If
  201.         ' Catch ex As Exception
  202.         'Response.Redirect("~/GenericErrorPage.aspx", False)
  203.  
  204.         'End Try
  205.     End Sub
  206.  
  207.     Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs)
  208.         Try
  209.             'Create a New Connection for the Stored Procedure
  210.             Dim myConnection As New Data.SqlClient.SqlConnection(Me.SqlDataSource1.ConnectionString)
  211.             Data.SqlClient.SqlConnection.ClearAllPools()
  212.             myConnection.Open()
  213.             'Setup SQLCommand for UPDATE statement from SQLDataSource1
  214.             Dim myCommand As New Data.SqlClient.SqlCommand("UPDATE_FORM", myConnection)
  215.             myCommand.CommandType = Data.CommandType.StoredProcedure
  216.             Dim param2 As Date
  217.  
  218.             'Try Catch statement for testing Returned_Date value
  219.             Try
  220.                 param2 = Convert.ToDateTime(CType(Me.FormView1.FindControl("Returned_DateTextBox"), TextBox).Text.ToString)
  221.                 myCommand.Parameters.AddWithValue("@Returned_Date", param2)
  222.             Catch ex As Exception
  223.                 myCommand.Parameters.AddWithValue("@Returned_Date", DBNull.Value)
  224.  
  225.             End Try
  226.  
  227.             'Add the rest of the insert parameters
  228.             myCommand.Parameters.AddWithValue("@form_case_link_id", CType(Me.FormView1.FindControl("lblCaseLinkID"), Label).Text)
  229.  
  230.             myCommand.Parameters.AddWithValue("@date_issued", Convert.ToDateTime(CType(Me.FormView1.FindControl("date_issued"), TextBox).Text))
  231.  
  232.             myCommand.Parameters.AddWithValue("@bf_date", Convert.ToDateTime(CType(Me.FormView1.FindControl("bf_dateTextBox"), TextBox).Text))
  233.  
  234.             myCommand.Parameters.AddWithValue("@NA", CType(Me.FormView1.FindControl("NA"), CheckBox).Checked)
  235.  
  236.             myCommand.Parameters.AddWithValue("@Notes", CType(Me.FormView1.FindControl("NotesTextBox"), TextBox).Text)
  237.  
  238.             'Execute the command
  239.             'Dim reader As Data.SqlClient.SqlDataReader = myCommand.ExecuteReader
  240.             myCommand.ExecuteReader()
  241.             myConnection.Close()
  242.  
  243.             'build redirect url
  244.             Dim CaseRef As String = CType(Me.FormView1.FindControl("Case_IDLabel"), Label).Text.ToString
  245.             Dim FormRef As String = CType(Me.FormView1.FindControl("Form_IDLabel"), Label).Text.ToString
  246.             Response.Redirect("~/logged_in/update_success.aspx?Form_ID=" & FormRef & "&Case_ID=" & CaseRef, False)
  247.  
  248.         Catch ex As Exception
  249.             Response.Redirect("~/GenericErrorPage.aspx", False)
  250.  
  251.         End Try
  252.     End Sub
  253.  
  254.     Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
  255.         'clears returned date checkbox
  256.         CType(Me.FormView1.FindControl("Returned_DateTextBox"), TextBox).Text = ""
  257.         Me.FormView1.FindControl("Calendar2").Visible = "false"
  258.     End Sub
  259. End Class
  260.  
  261.  
Form

Expand|Select|Wrap|Line Numbers
  1.  
  2. <%@ Page Language="VB" MasterPageFile="~/logged_in/MasterPage.master" AutoEventWireup="false" CodeFile="ESA55pbc.aspx.vb" Inherits="css_ESA55pbc" title="MAID - ESA55pbc" %>
  3. <asp:Content ID="Content1" ContentPlaceHolderID="pageTitle" Runat="Server">
  4.     ESA55pbc
  5. </asp:Content>
  6. <asp:Content ID="Content2" ContentPlaceHolderID="mainContent" Runat="Server">
  7.     &nbsp;<asp:FormView DefaultMode="Insert" ID="FormView1" runat="server" DataKeyNames="Form_Case_Link_ID"
  8.         DataSourceID="SqlDataSource1" Width="666px">
  9.         <EditItemTemplate>
  10.             <h3>
  11.             Update Form - <%Response.write(Session("nino").ToString) %></h3>
  12.             <br />
  13.  
  14.             <asp:Label ID="lblCaseLinkID" runat="server" Text="Label"></asp:Label><br />
  15.             <span class="form_header">Case ID:</span>
  16.             <asp:Label ID="Case_IDLabel" runat="server" Text='<%# Request.QueryString("Case_ID") %>'></asp:Label><br />
  17.             <span class="form_header">Form ID:</span>
  18.             <asp:Label ID="Form_IDLabel" runat="server" Text='<%# Request.QueryString("form_ID") %>'></asp:Label><br />
  19.             <br><span class="form_header">Date Issued:</span>
  20.             <asp:ImageButton ID="ImageButton1" runat="server" ImageUrl="~/images/calendar.gif"
  21.                 OnClick="ImageButton1_Click" AlternateText="Calendar Button - Click to show date picker" ToolTip="Click to show date picker" />
  22.             <asp:Calendar ID="Calendar1" Visible="false" runat="server" SelectedDate='<%# Bind("Date_Issued") %>' OnSelectionChanged="Calendar1_SelectionChanged" CssClass="table-align">
  23.             </asp:Calendar>
  24.             <br/>
  25.             <br/><span class="form_header">Date Selected:</span><asp:TextBox ID="Date_Issued" runat="server" AutoPostBack="True" ReadOnly="True" ></asp:TextBox>
  26.             <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="Date_Issued"
  27.                 Display="Dynamic" ErrorMessage="RequiredFieldValidator">You must have a date issued</asp:RequiredFieldValidator>
  28.             <asp:RegularExpressionValidator ID="RegularExpressionValidator2" runat="server" ControlToValidate="Date_Issued"
  29.                 Display="Dynamic" ErrorMessage="Date must be in the format dd/mm/yyyy" ValidationExpression="(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)[0-9]{2}"></asp:RegularExpressionValidator><br />
  30.             <br />
  31.  
  32.             <br><span class="form_header">BF Date:</span>
  33.             <asp:ImageButton ID="ImageButton3" runat="server" ImageUrl="~/images/calendar.gif"
  34.                 OnClick="ImageButton3_Click" AlternateText="Calendar Button - Click to show date picker" ToolTip="Click to show date picker" />
  35.             <asp:Calendar ID="Calendar3" Visible="false" runat="server" SelectedDate='<%# Bind("Date_Issued") %>' OnSelectionChanged="Calendar3_SelectionChanged" CssClass="table-align" CellPadding="0" CellSpacing="1">
  36.             <TitleStyle BackColor="#326171" ForeColor="#FFF2B3" Font-Bold="True" HorizontalAlign="Center" CssClass="angles" />
  37.                 <SelectedDayStyle BackColor="#EEEEEE" BorderColor="Black" BorderStyle="Solid" BorderWidth="1px"
  38.                     ForeColor="Black" />
  39.                 <TodayDayStyle BackColor="#FFFFC0" BorderColor="Gray" BorderStyle="Solid" BorderWidth="1px"
  40.                     ForeColor="Black" />
  41.                 <WeekendDayStyle BackColor="#EEEEEE" />
  42.                 <OtherMonthDayStyle BackColor="White" />
  43.                 <NextPrevStyle ForeColor="#FFF2B3" HorizontalAlign="Center" />
  44.             </asp:Calendar>
  45.             <br/>
  46.             <br><span class="form_header">Date Selected:</span>
  47.             <asp:TextBox ID="BF_DateTextBox" runat="server" ReadOnly="True" ></asp:TextBox><br/>
  48.             <br/><span class="form_header">Returned Date:</span>
  49.             <asp:ImageButton ID="ImageButton2" runat="server" ImageUrl="~/images/calendar.gif"
  50.                 OnClick="ImageButton2_Click" AlternateText="Calendar Button - Click to show date picker" ToolTip="Click to show date picker" />
  51.             <asp:Calendar ID="Calendar2" CssClass="table-align" Visible="false" runat="server" OnSelectionChanged="Calendar2_SelectionChanged">
  52.             <TitleStyle BackColor="#326171" ForeColor="#FFF2B3" Font-Bold="True" HorizontalAlign="Center" CssClass="angles" />
  53.                 <SelectedDayStyle BackColor="#EEEEEE" BorderColor="Black" BorderStyle="Solid" BorderWidth="1px"
  54.                     ForeColor="Black" />
  55.                 <TodayDayStyle BackColor="#FFFFC0" BorderColor="Gray" BorderStyle="Solid" BorderWidth="1px"
  56.                     ForeColor="Black" />
  57.                 <WeekendDayStyle BackColor="#EEEEEE" />
  58.                 <OtherMonthDayStyle BackColor="White" />
  59.                 <NextPrevStyle ForeColor="#FFF2B3" HorizontalAlign="Center" />
  60.             </asp:Calendar>
  61.              <br><span class="form_header">Date Selected:</span>
  62.            <asp:TextBox ID="Returned_DateTextBox" runat="server"></asp:TextBox>
  63.             <asp:LinkButton ID="LinkButton1" runat="server" OnClick="LinkButton1_Click">Clear Date Selected</asp:LinkButton>
  64.             <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ControlToValidate="Returned_DateTextBox"
  65.                 Display="Dynamic" ErrorMessage="Date must be in the format dd/mm/yyyy" ValidationExpression="(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)[0-9]{2}"></asp:RegularExpressionValidator><br />
  66.             <br><span class="form_header">NA:</span>
  67.             <asp:CheckBox ID="NA" runat="server" TextAlign="Left" /><br />
  68.             <span class="form_header">Notes:</span>
  69.             <asp:TextBox ID="NotesTextBox" runat="server" Height="146px" MaxLength="250" Text='<%# Bind("Notes") %>'
  70.                 TextMode="MultiLine" Width="292px"></asp:TextBox><br />
  71.             <br><asp:Button ID="Submit" runat="server" CommandName="Update" OnClick="Submit_Click"
  72.                 Text="Update" />
  73.          <br />
  74.          <br />
  75.             <a href="case_Details.aspx?case_id=<%Response.write(Request.QueryString("Case_ID").ToString) %>&NINO=<%Response.write(Session("nino").ToString) %>">Back to Case Details for NINO: <%Response.Write(Session("nino").ToString)%> </a>
  76.  
  77.         </EditItemTemplate>
  78.         <InsertItemTemplate>
  79.         <h3>
  80.             Insert Form - <%Response.write(Session("nino").ToString) %></h3>
  81.             <br />
  82.             <asp:Label ID="lblCaseLinkID" runat="server" Text="Label"></asp:Label><br />
  83.             <span class="form_header">Case ID:</span>
  84.             <asp:Label ID="Case_IDLabel" runat="server" Text='<%# Request.QueryString("Case_ID") %>'></asp:Label><br />
  85.             <span class="form_header">Form ID:</span>
  86.             <asp:Label ID="Form_IDLabel" runat="server" Text='<%# Request.QueryString("form_ID") %>'></asp:Label><br />
  87.             <br/>
  88.             <span class="form_header">Date Issued: </span>
  89.             <asp:ImageButton ID="ImageButton1" runat="server" ImageUrl="~/images/calendar.gif"
  90.                 OnClick="ImageButton1_Click" AlternateText="Calendar Button - Click to show date picker" ToolTip="Click to show date picker" />
  91.             <asp:Calendar ID="Calendar1" Visible="false" runat="server" SelectedDate='<%# Bind("Date_Issued") %>' OnSelectionChanged="Calendar1_SelectionChanged" CssClass="table-align" CellPadding="0" CellSpacing="1">
  92.             <TitleStyle BackColor="#326171" ForeColor="#FFF2B3" Font-Bold="True" HorizontalAlign="Center" CssClass="angles" />
  93.                 <SelectedDayStyle BackColor="#EEEEEE" BorderColor="Black" BorderStyle="Solid" BorderWidth="1px"
  94.                     ForeColor="Black" />
  95.                 <TodayDayStyle BackColor="#FFFFC0" BorderColor="Gray" BorderStyle="Solid" BorderWidth="1px"
  96.                     ForeColor="Black" />
  97.                 <WeekendDayStyle BackColor="#EEEEEE" />
  98.                 <OtherMonthDayStyle BackColor="White" />
  99.                 <NextPrevStyle ForeColor="#FFF2B3" HorizontalAlign="Center" />
  100.             </asp:Calendar>
  101.             <br/>
  102.  
  103.             <span class="form_header">Date Selected:</span>
  104.             <asp:TextBox ID="Date_Issued" runat="server" AutoPostBack="True" ReadOnly="True" ></asp:TextBox>
  105.             <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="Date_Issued"
  106.                 ErrorMessage="RequiredFieldValidator" Display="Dynamic">You must have a date issued</asp:RequiredFieldValidator>
  107.             <asp:RegularExpressionValidator ID="RegularExpressionValidator2" runat="server" ControlToValidate="Date_Issued"
  108.                 Display="Dynamic" ErrorMessage="Date must be in the format dd/mm/yyyy" ValidationExpression="(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)[0-9]{2}"></asp:RegularExpressionValidator><br />
  109.             <br />
  110.             <span class="form_header">BF Date:</span>
  111.             <asp:ImageButton ID="ImageButton3" runat="server" ImageUrl="~/images/calendar.gif"
  112.                 OnClick="ImageButton3_Click" AlternateText="Calendar Button - Click to show date picker" ToolTip="Click to show date picker" />
  113.             <asp:Calendar ID="Calendar3" Visible="false" runat="server" SelectedDate='<%# Bind("Date_Issued") %>' OnSelectionChanged="Calendar3_SelectionChanged" CssClass="table-align" CellPadding="0" CellSpacing="1">
  114.             <TitleStyle BackColor="#326171" ForeColor="#FFF2B3" Font-Bold="True" HorizontalAlign="Center" CssClass="angles" />
  115.                 <SelectedDayStyle BackColor="#EEEEEE" BorderColor="Black" BorderStyle="Solid" BorderWidth="1px"
  116.                     ForeColor="Black" />
  117.                 <TodayDayStyle BackColor="#FFFFC0" BorderColor="Gray" BorderStyle="Solid" BorderWidth="1px"
  118.                     ForeColor="Black" />
  119.                 <WeekendDayStyle BackColor="#EEEEEE" />
  120.                 <OtherMonthDayStyle BackColor="White" />
  121.                 <NextPrevStyle ForeColor="#FFF2B3" HorizontalAlign="Center" />
  122.             </asp:Calendar>
  123.             <br/>
  124.             <br><span class="form_header">Date Selected:</span>
  125.             <asp:TextBox ID="BF_DateTextBox" runat="server" ReadOnly="True" ></asp:TextBox><br/>
  126.             <br/><span class="form_header">Returned Date:</span>
  127.             <asp:ImageButton ID="ImageButton2" runat="server" ImageUrl="~/images/calendar.gif"
  128.                 OnClick="ImageButton2_Click" AlternateText="Calendar Button - Click to show date picker" ToolTip="Click to show date picker" />
  129.             <asp:Calendar ID="Calendar2" Visible="false" runat="server" SelectedDate='<%# Bind("Returned_Date") %>' OnSelectionChanged="Calendar2_SelectionChanged" CssClass="table-align" CellPadding="0" CellSpacing="1">
  130.             <TitleStyle BackColor="#326171" ForeColor="#FFF2B3" Font-Bold="True" HorizontalAlign="Center" CssClass="angles" />
  131.                 <SelectedDayStyle BackColor="#EEEEEE" BorderColor="Black" BorderStyle="Solid" BorderWidth="1px"
  132.                     ForeColor="Black" />
  133.                 <TodayDayStyle BackColor="#FFFFC0" BorderColor="Gray" BorderStyle="Solid" BorderWidth="1px"
  134.                     ForeColor="Black" />
  135.                 <WeekendDayStyle BackColor="#EEEEEE" />
  136.                 <OtherMonthDayStyle BackColor="White" />
  137.                 <NextPrevStyle ForeColor="#FFF2B3" HorizontalAlign="Center" />
  138.             </asp:Calendar>
  139.             <br/>
  140.             <span class="form_header">Date Selected:</span>
  141.             <asp:TextBox ID="Returned_DateTextBox" runat="server" Text='<%# Bind("Returned_Date") %>' ReadOnly="True" ></asp:TextBox>
  142.             <asp:LinkButton ID="LinkButton1" runat="server" OnClick="LinkButton1_Click">Clear Date Selected</asp:LinkButton>
  143.             <asp:RegularExpressionValidator ID="RegularExpressionValidator3" runat="server" ControlToValidate="Returned_DateTextBox"
  144.                 Display="Dynamic" ErrorMessage="Date must be in the format dd/mm/yyyy" ValidationExpression="(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)[0-9]{2}"></asp:RegularExpressionValidator><br />
  145.             <br/>
  146.             <span class="form_header">NA:</span><asp:CheckBox ID="NA" runat="server" Checked='<%# Bind("NA") %>' TextAlign="Left" /><br />
  147.             <span class="form_header">Notes:</span>
  148.             <asp:TextBox ID="NotesTextBox" runat="server" Text='<%# Bind("Notes") %>' Height="146px" MaxLength="250" TextMode="MultiLine" Width="292px"></asp:TextBox><br />
  149.             <br/>
  150.             <asp:Button ID="InsertButton" runat="server" CausesValidation="True"
  151.                 Text="Insert" OnClick="InsertButton_Click1"></asp:Button>
  152.            <br />
  153.            <br />
  154.             <a href="case_Details.aspx?case_id=<%Response.write(Request.QueryString("Case_ID").ToString) %>&NINO=<%Response.write(Session("nino").ToString) %>">Back to Case Details for NINO: <%Response.Write(Session("nino").ToString)%> </a>
  155.  
  156.         </InsertItemTemplate>
  157.         <ItemTemplate>
  158.             Form_Case_Link_ID:
  159.             <asp:Label ID="Form_Case_Link_IDLabel" runat="server" Text='<%# Eval("Form_Case_Link_ID") %>'></asp:Label><br />
  160.             Case_ID:
  161.             <asp:Label ID="Case_IDLabel" runat="server" Text='<%# Bind("Case_ID") %>'></asp:Label><br />
  162.             Form_ID:
  163.             <asp:Label ID="Form_IDLabel" runat="server" Text='<%# Bind("Form_ID") %>'></asp:Label><br />
  164.             Date_Issued:
  165.             <asp:Label ID="Date_IssuedLabel" runat="server" Text='<%# Bind("Date_Issued") %>'></asp:Label><br />
  166.             BF_Date:
  167.             <asp:Label ID="BF_DateLabel" runat="server" Text='<%# Bind("BF_Date") %>'></asp:Label><br />
  168.             Returned_Date:
  169.             <asp:Label ID="Returned_DateLabel" runat="server" Text='<%# Bind("Returned_Date") %>'></asp:Label><br />
  170.             NA:
  171.             <asp:Label ID="NALabel" runat="server" Text='<%# Bind("NA") %>'></asp:Label><br />
  172.             Notes:
  173.             <asp:Label ID="NotesLabel" runat="server" Text='<%# Bind("Notes") %>'></asp:Label><br />
  174.         </ItemTemplate>
  175.     </asp:FormView>
  176.     <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ESA_TrackerConnectionString %>"
  177.         SelectCommand="SELECT * FROM [tbl_Forms_Case_Link]" UpdateCommand="UPDATE_FORM" DeleteCommand="DELETE FROM [tbl_Forms_Case_Link] WHERE [Form_Case_Link_ID] = @Form_Case_Link_ID" InsertCommand="INSERT INTO [tbl_Forms_Case_Link] ([Case_ID], [Form_ID], [Date_Issued], [BF_Date], [Returned_Date], [NA], [Notes]) VALUES (@Case_ID, @Form_ID, @Date_Issued, @BF_Date, @Returned_Date, @NA, @Notes)" UpdateCommandType="StoredProcedure">
  178.         <UpdateParameters>
  179.             <asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />
  180.             <asp:Parameter Name="form_case_link_id" Type="Int32" />
  181.             <asp:Parameter Name="date_issued" Type="DateTime" />
  182.             <asp:Parameter Name="bf_date" Type="DateTime" />
  183.             <asp:Parameter Name="Returned_Date" Type="DateTime" />
  184.             <asp:Parameter Name="NA" Type="Boolean" />
  185.             <asp:Parameter Name="Notes" Type="String" />
  186.         </UpdateParameters>
  187.         <DeleteParameters>
  188.             <asp:Parameter Name="Form_Case_Link_ID" Type="Int32" />
  189.         </DeleteParameters>
  190.         <InsertParameters>
  191.             <asp:Parameter Name="Case_ID" Type="Int32" />
  192.             <asp:Parameter Name="Form_ID" Type="Int32" />
  193.             <asp:Parameter Name="Date_Issued" Type="DateTime" />
  194.             <asp:Parameter Name="BF_Date" Type="DateTime" />
  195.             <asp:Parameter Name="Returned_Date" Type="DateTime" />
  196.             <asp:Parameter Name="NA" Type="Boolean" />
  197.             <asp:Parameter Name="Notes" Type="String" />
  198.         </InsertParameters>
  199.     </asp:SqlDataSource>
  200.     <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ESA_TrackerConnectionString %>"
  201.         SelectCommand="getSingleValue" SelectCommandType="StoredProcedure" >
  202.         <SelectParameters>
  203.             <asp:QueryStringParameter Name="Case_ID" QueryStringField="case_id" Type="Int32" />
  204.             <asp:QueryStringParameter Name="Form_ID" QueryStringField="form_id" Type="Int32" />
  205.             <asp:Parameter Direction="InputOutput" Name="SingleReturn" Type="Int32" />
  206.         </SelectParameters>
  207.     </asp:SqlDataSource>
  208.  
  209. </asp:Content>
  210.  
  211.  

Expand|Select|Wrap|Line Numbers
  1.  
  2. SET ANSI_NULLS ON
  3. SET QUOTED_IDENTIFIER ON
  4. go
  5.  
  6.  
  7.  
  8.  
  9.  
  10.  
  11. ALTER PROCEDURE [dbo].[GetSingleValue]
  12. @Case_ID int,
  13. @Form_ID int
  14. --@SingleReturn int OUTPUT,
  15.  
  16. AS
  17. DECLARE @rc int
  18.  
  19. --SELECT @SingleReturn = Form_Case_Link_ID
  20. --SELECT Form_Case_Link_ID
  21. SELECT @rc = Form_Case_Link_ID
  22. FROM tbl_Forms_Case_Link
  23. WHERE (Case_ID = @Case_ID) AND (Form_ID = @Form_ID)
  24.  
  25. --SET @SingleReturn = Form_Case_Link_ID
  26.  
  27. RETURN @rc
  28.  
  29.  
Nov 3 '08 #1
Share this Question
Share on Google+
2 Replies


Frinavale
Expert Mod 5K+
P: 9,731
In the future it might be a good idea just to post the code that you think is causing the problem instead of everything. It's hard to read through a lot of code to just to find the function(s) that you are having problems....It makes it especially hard to understand when you have buttons named like "Submit" and "LinkButton1"...I'm not sure what these buttons are supposed to actually be doing.

Anyways, your Submit button seems to have code for handling your table update...however your page load event also seems to be doing something with your table. One of these methods could be updating the wrong thing.

-Frinny
Nov 3 '08 #2

Plater
Expert 5K+
P: 7,872
Avoiding for now that your page_load() looks like it is doing something fishy and is likely the culprit...

I don't see your actual update procedure, just an sql function that could select multiple results, but you only return one.


In my StoredProcedures that do updates, I frequently will include a count() to determine the number of records effected.
For isntance a snippit might be:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.    @RowsAffected=count(*)
  3. FROM
  4.    [Assemblies]
  5. WHERE 
  6.    [AssemblyID] = @AssemblyID; 
  7.  
  8. --do update
  9. UPDATE 
  10.    [Assemblies]
  11. SET 
  12.    [LaborHours] = @LaborHours
  13.    ,[Notes] = @Notes
  14.    ,[ModifiedDate] = @ModifiedDate
  15. WHERE 
  16.    [AssemblyID] = @AssemblyID;
  17.  
  18. RETURN @RowsAffected;
  19.  
Nov 3 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.