insert or replace: Inserted records missing in sql table yet tables primary key field has been incremented.

I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails.

I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table. Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record.

And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.

Hi Nick,

Your problem seems interesting. Would you please put some related code here. So that we can analyze what exactly going on there.

|||

The code below indicates when the automated email is send and after that is the markup of my page.

ProtectedSub DetailsView1_ItemInserted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.DetailsViewInsertedEventArgs)

'I have code to send automated emails here.

EndIf

Catch exAs Exception

'iam not catching nor doing any thing here. (possibly i should have done some thing here)

Finally

Response.Redirect("AfterInserting.aspx")

EndTry

And the markup is below

<asp:ContentID="Content1"ContentPlaceHolderID="ContentPlaceHolder1"Runat="Server">

<table>

<tr>

<tdstyle="width: 100px; height: 21px; text-align: left;"valign="top">

<asp:LabelID="Label8"runat="server"Width="126px"></asp:Label>

<asp:LabelID="Label20"runat="server"Width="128px"ForeColor="#0000FF"></asp:Label></td>

<tdstyle="width: 100px; height: 21px; text-align: left;"valign="top">

<asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"DataKeyNames="Incident_id"

DataSourceID="SqlDataSource1"DefaultMode="Insert"Height="50px"Width="497px"Font-Size="Smaller"OnItemInserted="DetailsView1_ItemInserted"BackColor="LightGoldenrodYellow"BorderColor="Tan"BorderWidth="1px"CellPadding="2"ForeColor="Black"OnItemInserting="DetailsView1_ItemInserting">

<asp:TemplateFieldHeaderText="Incident_id"InsertVisible="False"SortExpression="Incident_id">

<asp:LabelID="Label1"runat="server"Text='<%# Eval("Incident_id") %>'></asp:Label>

</EditItemTemplate>

<asp:LabelID="Label20"runat="server"Text='<%# Bind("Incident_id") %>'ToolTip="This is the Incident Number"></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Person Raising Report"SortExpression="Incident_Reported_By">

<asp:TextBoxID="TextBox7"runat="server"Text='<%# Bind("Incident_Reported_By") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox2"runat="server"Text='<%# Bind("Incident_Reported_By") %>'ToolTip="Type the name of the person raising the report here (Your Name)"></asp:TextBox>

<asp:RequiredFieldValidatorID="RequiredFieldValidator1"runat="server"ControlToValidate="TextBox2"

ErrorMessage='You have not provided your name ......You must enter your name in the Person raising report field in order to report this incident .'

SetFocusOnError="True"ValidationGroup="email"EnableTheming="False">*</asp:RequiredFieldValidator>

</InsertItemTemplate>

<asp:LabelID="Label7"runat="server"Text='<%# Bind("Incident_Reported_By") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Person Raising Report's Employee#"SortExpression="Emp_No">

<asp:TextBoxID="TextBox2"runat="server"Text='<%# Bind("Emp_No") %>'></asp:TextBox>

</EditItemTemplate>

<asp:DropDownListID="DropDownList1"runat="server"DataSourceID="SqlDataSource20"

DataTextField="EmpNumber"DataValueField="EmpNumber"SelectedValue='<%# Bind("Emp_No") %>'

Width="156px"ToolTip="Select Your Employee Number here. If you have no number check other options at bottom of the list and select one that suits you">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource20"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [EmpNumber] FROM [EmpNumbers] ORDER BY [EmpNumber]"></asp:SqlDataSource>

<asp:RequiredFieldValidatorID="RequiredFieldValidator2"runat="server"ControlToValidate="DropDownList1"

ErrorMessage="You must select your Employee Number. Other options are : Trainee, Contractor, Casual, Canteen staff and Security personnel. "

InitialValue=".."ValidationGroup="email">.</asp:RequiredFieldValidator>

<asp:TextBoxID="TextBox24"runat="server"Text='<%# Eval("Emp_No") %>'Visible="False"></asp:TextBox>

</InsertItemTemplate>

<asp:LabelID="Label2"runat="server"Text='<%# Bind("Emp_No") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Personnel Directly Involved"SortExpression="Personnel_Directly_Involved">

<asp:TextBoxID="TextBox10"runat="server"Text='<%# Bind("Personnel_Directly_Involved") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox4"runat="server"Text='<%# Bind("Personnel_Directly_Involved") %>'ToolTip="Type the name of the person directly involved in the Incident here"></asp:TextBox>

<asp:RequiredFieldValidatorID="RequiredFieldValidator4"runat="server"ControlToValidate="TextBox4"

ErrorMessage="Error in Personnel directly Involved Field....This field can not left blank"SetFocusOnError="True"

ValidationGroup="email">*</asp:RequiredFieldValidator>

</InsertItemTemplate>

<asp:LabelID="Label10"runat="server"Text='<%# Bind("Personnel_Directly_Involved") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Witness 1">

<asp:TextBoxID="TextBox21"runat="server"ToolTip="Type the name of the witness here. You can not leave this field blank"></asp:TextBox>

<asp:RequiredFieldValidatorID="RequiredFieldValidator8"runat="server"ControlToValidate="TextBox21"

EnableTheming="True"ErrorMessage="You must atleast specify one witness to the Incident. Please type the witness name."

SetFocusOnError="True"ValidationGroup="email">.</asp:RequiredFieldValidator>

</InsertItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Witness 2">

<asp:TextBoxID="TextBox22"runat="server"Text='<%# Bind("Witness_2") %>'ToolTip="Type the name of the second witness here if any. (Optional)"></asp:TextBox>

</InsertItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Witness 3">

<asp:TextBoxID="TextBox23"runat="server"Text='<%# Bind("witness_3") %>'ToolTip="Type the name of the third witness here if any (Optional)"></asp:TextBox>

</InsertItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Date Incident Occured "SortExpression="Incident_Date">

<asp:TextBoxID="TextBox1"runat="server"Text='<%# Bind("Incident_Date") %>'></asp:TextBox>

</EditItemTemplate>

<cc1:GMDatePickerID="GMDatePicker1"runat="server"AutoPosition="True"CalendarOffsetX="-200px"CalendarOffsetY="25px"CalendarTheme="Green"CalendarWidth="250px"CallbackEventReference=""Culture="English (United States)"DateString='<%# bind("Incident_Date") %>'EnableDropShadow="True"MaxDate="2020-12-31"MinDate=""NextMonthText=">"NoneButtonText="None"ShowNoneButton="False"ShowTodayButton="True"TextBoxWidth="150"ZIndex="1"InitialText="select date"ToolTip="Click the icon on the right to select the date on which the incident occurred">

<CalendarTodayDayStyleBackColor="#C0FFC0"/>

</cc1:GMDatePicker>

<asp:RequiredFieldValidatorID="RequiredFieldValidator6"runat="server"ControlToValidate="GMDatePicker1"

ErrorMessage="You must select the date on which this Incident Occurred. Click the icon next to the incident occurred date field to show a calendar and then click the desired date from the calendar."

SetFocusOnError="True"ValidationGroup="email"InitialValue="select date">.</asp:RequiredFieldValidator>

</InsertItemTemplate>

<asp:LabelID="Label1"runat="server"Text='<%# Bind("Incident_Date") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Date Incident Is Reported "SortExpression="Date_Reported">

<asp:TextBoxID="TextBox9"runat="server"></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="Textbox3"runat="server"Text='<%# Bind("Date_Reported") %>'ReadOnly="True"Font-Size="9pt"ForeColor="#6666FF"ToolTip="Do not type anything here. This field is automated to always display and save the current date"></asp:TextBox>

<asp:RequiredFieldValidatorID="RequiredFieldValidator3"runat="server"ControlToValidate="TextBox3"

ErrorMessage="Error in Incident Reported Date....This field can not be left blank. "

SetFocusOnError="True"ValidationGroup="email">*</asp:RequiredFieldValidator>

<asp:CompareValidatorID="CompareValidator2"runat="server"ControlToValidate="TextBox3"

ErrorMessage='Error in Incident Reported Date Field. Re-enter date in month/day/year format '

Operator="DataTypeCheck"SetFocusOnError="True"Type="Date"ValidationGroup="email">*</asp:CompareValidator>

</InsertItemTemplate>

<asp:LabelID="Label9"runat="server"Text='<%# Bind("Date_Reported") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Time Incident Occurred"SortExpression="TimeCoomencedshift">

<asp:TextBoxID="TextBox16"runat="server"Text='<%# Bind("TimeCoomencedshift") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox9"runat="server"Text='<%# Bind("time_incident_occurred") %>'Width="67px"Height="21px"ToolTip="Type the time at which the incident occurred here in 24 hour format."></asp:TextBox>

<asp:ListBoxID="ListBox1"runat="server"Height="24px"Width="55px"ToolTip="Use the up and down arrows to specify AM or PM">

<asp:ListItem>PM</asp:ListItem>

<asp:ListItem>AM</asp:ListItem>

</asp:ListBox>

<asp:RequiredFieldValidatorID="RequiredFieldValidator7"runat="server"ControlToValidate="TextBox9"

ErrorMessage="You must enter the time at which the Incident occurred"SetFocusOnError="True"

ValidationGroup="email"Height="10px">.</asp:RequiredFieldValidator>

</InsertItemTemplate>

<asp:LabelID="Label16"runat="server"Text='<%# Bind("TimeCoomencedshift") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="ReminderDate"SortExpression="ReminderDate">

<asp:TextBoxID="TextBox8"runat="server"Text='<%# Bind("ReminderDate") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox6"runat="server"Text='<%# Bind("ReminderDate") %>'Font-Size="9pt"ForeColor="#6666FF"ReadOnly="True"ToolTip="Do not type any thing here. This field is automated to always add 3 days to the current date "></asp:TextBox>

</InsertItemTemplate>

<asp:LabelID="Label8"runat="server"Text='<%# Bind("ReminderDate") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Department">

<asp:DropDownListID="DropDownList5"runat="server"DataSourceID="DEPTDataSource1"

DataTextField="name"DataValueField="name"SelectedValue='<%# Bind("Dept") %>'

Width="155px"ToolTip="Click the arrow ponting down to select a department of the person involved from this list ">

</asp:DropDownList><asp:SqlDataSourceID="DEPTDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [name] FROM [Deptments] ORDER BY [name]"></asp:SqlDataSource>

</InsertItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Incident Location"SortExpression="Incident_Location">

<asp:TextBoxID="TextBox3"runat="server"Text='<%# Bind("Incident_Location") %>'></asp:TextBox>

</EditItemTemplate>

<asp:DropDownListID="DropDownList2"runat="server"DataSourceID="SqlDataSource3"

DataTextField="Area_Name"DataValueField="Area_Name"SelectedValue='<%# Bind("Incident_Location") %>'

Width="155px"ToolTip="Select the location where the incident occurred from this list">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource3"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [Area_Name] FROM [Incident_Areas] ORDER BY [Area_Name]"></asp:SqlDataSource>

</InsertItemTemplate>

<asp:LabelID="Label3"runat="server"Text='<%# Bind("Incident_Location") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Incident Category"SortExpression="Incident_Category">

<asp:TextBoxID="TextBox4"runat="server"Text='<%# Bind("Incident_Category") %>'></asp:TextBox>

</EditItemTemplate>

<asp:DropDownListID="DropDownList3"runat="server"DataSourceID="SqlDataSource5"

DataTextField="Category_Name"DataValueField="Category_Name"SelectedValue='<%# Bind("Incident_Category") %>'

Width="155px"ToolTip="Select the category of the incident from this list. Please take special note of injuries ">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource5"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [Category_Name] FROM [Incident_Category] ORDER BY [Category_Name]">

</asp:SqlDataSource>

</InsertItemTemplate>

<asp:LabelID="Label4"runat="server"Text='<%# Bind("Incident_Category") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Incident Severity"SortExpression="Incident_Severity">

<asp:TextBoxID="TextBox5"runat="server"Text='<%# Bind("Incident_Severity") %>'></asp:TextBox>

</EditItemTemplate>

<asp:DropDownListID="DropDownList4"runat="server"DataSourceID="SqlDataSource7"

DataTextField="Incident_Severity"DataValueField="Incident_Severity"SelectedValue='<%# Bind("Incident_Severity") %>'

Width="157px"ToolTip="Select the incident severity from this list">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource7"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [Incident_Severity] FROM [Incident_Severity] ORDER BY [Incident_Severity]">

</asp:SqlDataSource>

</InsertItemTemplate>

<asp:LabelID="Label5"runat="server"Text='<%# Bind("Incident_Severity") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Incident classification"SortExpression="Incident_classification"Visible="False">

<asp:TextBoxID="TextBox12"runat="server"Text='<%# Bind("Incident_classification") %>'></asp:TextBox>

</EditItemTemplate>

<asp:DropDownListID="DropDownList7"runat="server"DataSourceID="SqlDataSource16"

DataTextField="classification"DataValueField="classification"SelectedValue='<%# Bind("Incident_classification") %>'

Width="157px">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource16"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [classification] FROM [Incident_Classification]"></asp:SqlDataSource>

</InsertItemTemplate>

<asp:LabelID="Label12"runat="server"Text='<%# Bind("Incident_classification") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText=" Incident timing"SortExpression="TimingOfIncident"Visible="False">

<asp:TextBoxID="TextBox19"runat="server"Text='<%# Bind("TimingOfIncident") %>'></asp:TextBox>

</EditItemTemplate>

<asp:DropDownListID="DropDownList8"runat="server"DataSourceID="SqlDataSource26"

DataTextField="timing"DataValueField="timing"SelectedValue='<%# Bind("TimingOfIncident") %>'

Width="155px">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource26"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [timing] FROM [roster_timing_OfIncident]"></asp:SqlDataSource>

</InsertItemTemplate>

<asp:LabelID="Label19"runat="server"Text='<%# Bind("TimingOfIncident") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Shift Details"SortExpression="ShiftDetails"Visible="False">

<asp:TextBoxID="TextBox18"runat="server"Text='<%# Bind("ShiftDetails") %>'></asp:TextBox>

</EditItemTemplate>

<asp:DropDownListID="DropDownList9"runat="server"DataSourceID="SqlDataSource27"

DataTextField="shiftdetails"DataValueField="shiftdetails"SelectedValue='<%# Bind("ShiftDetails") %>'

Width="158px">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource27"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [shiftdetails] FROM [ShiftDetails]"></asp:SqlDataSource>

</InsertItemTemplate>

<asp:LabelID="Label18"runat="server"Text='<%# Bind("ShiftDetails") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Equipment Involved"SortExpression="EquipmentInvolved">

<asp:TextBoxID="TextBox13"runat="server"Text='<%# Bind("EquipmentInvolved") %>'></asp:TextBox>

</EditItemTemplate>

<asp:DropDownListID="DropDownList6"runat="server"DataSourceID="SqlDataSource28"

DataTextField="Cause"DataValueField="Cause"SelectedValue='<%# Bind("EqiupmentInvolved") %>'

Width="156px"ToolTip="Select the equipment involved in incident. If the equipment involved does not exist in the list, please notify safety to have the equipment added to the list.">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource28"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [Cause] FROM [WhatCausedInjury] ORDER BY [Cause]"></asp:SqlDataSource>

</InsertItemTemplate>

<asp:LabelID="Label13"runat="server"Text='<%# Bind("EquipmentInvolved") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="N0. of days into Roster Cycle"SortExpression="Time Incident Occurred"Visible="False">

<asp:TextBoxID="TextBox17"runat="server"Text='<%# Bind("NumberOfDaysintoRosterCycle") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox10"runat="server"Text='<%# Bind("NumberOfDaysintoRosterCycle") %>'></asp:TextBox>

</InsertItemTemplate>

<asp:LabelID="Label17"runat="server"Text='<%# Bind("NumberOfDaysintoRosterCycle") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Hours into shift"SortExpression="Hoursintoshift"Visible="False">

<asp:TextBoxID="TextBox15"runat="server"Text='<%# Bind("Hoursintoshift") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox8"runat="server"Text='<%# Bind("Hoursintoshift") %>'></asp:TextBox>

</InsertItemTemplate>

<asp:LabelID="Label15"runat="server"Text='<%# Bind("Hoursintoshift") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Time To Finish shift"SortExpression="TimeToFinishshift"Visible="False">

<asp:TextBoxID="TextBox14"runat="server"Text='<%# Bind("TimeToFinishshift") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox7"runat="server"Text='<%# Bind("TimeToFinishshift") %>'></asp:TextBox>

</InsertItemTemplate>

<asp:LabelID="Label14"runat="server"Text='<%# Bind("TimeToFinishshift") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Incident Brief Description"SortExpression="Incident_Description">

<asp:TextBoxID="TextBox11"runat="server"Text='<%# Bind("Incident_Description") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox5"runat="server"Height="45px"Text='<%# Bind("Incident_Description") %>'

TextMode="MultiLine"Width="199px"ToolTip="Briefly describe the incident here. You can type upto a maximum of 4000 characters"></asp:TextBox>

<asp:RequiredFieldValidatorID="RequiredFieldValidator5"runat="server"ControlToValidate="TextBox5"

ErrorMessage="Error in Incident Description Field....You must briefly describe the nature of the Incident"SetFocusOnError="True"

ValidationGroup="email">*</asp:RequiredFieldValidator>

</InsertItemTemplate>

<asp:LabelID="Label11"runat="server"Text='<%# Bind("Incident_Description") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Immediate Action"SortExpression="Immediate_Action">

<asp:TextBoxID="TextBox6"runat="server"Text='<%# Bind("Immediate_Action") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox20"runat="server"Text='<%# Bind("Immediate_Action") %>'

TextMode="MultiLine"Height="41px"Width="201px"ToolTip="Type the immediate action taken when the incident occurred here"></asp:TextBox>

<asp:RequiredFieldValidatorID="RequiredFieldValidator9"runat="server"ControlToValidate="TextBox20"

ErrorMessage="No Immediate Action Entered: Please first enter the Immediate Action taken when the Incident Occurred"

SetFocusOnError="True"ValidationGroup="email">.</asp:RequiredFieldValidator>

</InsertItemTemplate>

<asp:LabelID="Label6"runat="server"Text='<%# Bind("Immediate_Action") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Foward To Your Head Of Department">

<asp:DropDownListID="DropDownList10"runat="server"DataSourceID="SqlDataSource50"

DataTextField="Names"DataValueField="Names"SelectedValue='<%# Bind("Foward_to") %>'

Width="154px"ToolTip="Select the head of department you want to foward the incident to from here">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource50"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [Names] FROM [H.O.D's] ORDER BY [Names]"></asp:SqlDataSource>

<asp:RequiredFieldValidatorID="RequiredFieldValidator10"runat="server"ControlToValidate="DropDownList10"

ErrorMessage="You have not selected the Head Of Department. Please select your head of department and then report agian."SetFocusOnError="True"ValidationGroup="email">.</asp:RequiredFieldValidator>

</InsertItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldShowHeader="False">

<asp:ButtonID="Button1"runat="server"CausesValidation="True"CommandName="Insert"

Text="Report Incident/Hazard"ValidationGroup="email"/>

<asp:ButtonID="Button2"runat="server"PostBackUrl="~/StartPage.aspx"Text="<< Exit"/>

</InsertItemTemplate>

<ItemStyleHorizontalAlign="Center"/>

<asp:ButtonID="Button1"runat="server"CausesValidation="False"CommandName="New"

Text="New"/>

</ItemTemplate>

</asp:TemplateField>

</Fields>

<FieldHeaderStyleHorizontalAlign="Right"/>

<InsertRowStyleHorizontalAlign="Left"/>

<FooterStyleBackColor="Tan"/>

<EditRowStyleBackColor="DarkSlateBlue"ForeColor="GhostWhite"/>

<PagerStyleBackColor="PaleGoldenrod"ForeColor="DarkSlateBlue"HorizontalAlign="Center"/>

<HeaderStyleBackColor="Tan"Font-Bold="True"/>

<AlternatingRowStyleBackColor="PaleGoldenrod"/>

</asp:DetailsView>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"DeleteCommand="DELETE FROM [Report_Incident] WHERE [Incident_id] = @.original_Incident_id"

InsertCommand="INSERT INTO Report_Incident(Incident_Reported_By, Incident_Date, Date_Reported, ReminderDate, Personnel_Directly_Involved, Incident_Location, Incident_Category, Incident_Severity, Immediate_Action, Incident_Description, Incident_Assigned_To, EqiupmentInvolved, Emp_No, Foward_to, Dept, witness_1, witness_2, witness_3, time_incident_occurred) VALUES (@.Incident_Reported_By,@.Incident_Date,@.Date_Reported,@.ReminderDate, @.Personnel_Directly_Involved,@.Incident_Location,@.Incident_Category,@.Incident_Severity, @.Immediate_Action,@.Incident_Description,@.Incident_Assigned_To,@.EqiupmentInvolved, @.Emp_No,@.Foward_to,@.Dept,@.witness_1,@.witness_2,@.witness_3,@.time_incident_occurred) "

OldValuesParameterFormatString="original_{0}"SelectCommand="SELECT Incident_id, Incident_Reported_By, Incident_Date, Date_Reported, Personnel_Directly_Involved, Incident_Location, Incident_Category, Incident_Severity, Immediate_Action, Incident_Description, Incident_Assigned_To, Incident_classification, EqiupmentInvolved,Emp_No, ReminderDate,Foward_to, Dept, witness_1, witness_2, witness_3, time_incident_occurred FROM Report_Incident"EnableCaching="True">

<asp:ParameterName="original_Incident_id"Type="Int32"/>

</DeleteParameters>

<asp:ParameterName="Incident_Reported_By"Type="String"/>

<asp:ParameterName="Emp_No"/>

<asp:ParameterName="Incident_Date"Type="DateTime"/>

<asp:ParameterName="Date_Reported"Type="DateTime"/>

<asp:ParameterName="ReminderDate"/>

<asp:ParameterName="Personnel_Directly_Involved"Type="String"/>

<asp:ParameterName="Incident_Location"Type="String"/>

<asp:ParameterName="Incident_Category"Type="String"/>

<asp:ParameterName="Incident_Severity"Type="String"/>

<asp:ParameterName="Immediate_Action"Type="String"/>

<asp:ParameterName="Incident_Description"Type="String"/>

<asp:ParameterName="Incident_Assigned_To"Type="String"/>

<asp:ParameterName="EqiupmentInvolved"/>

<asp:ParameterName="Foward_to"/>

<asp:ParameterName="Dept"/>

<asp:ParameterName="witness_1"/>

<asp:ParameterName="witness_2"/>

<asp:ParameterName="witness_3"/>

<asp:ParameterName="time_incident_occurred"/>

</InsertParameters>

</asp:SqlDataSource>

<asp:ValidationSummaryID="ValidationSummary1"runat="server"ShowMessageBox="True"

ShowSummary="False"ValidationGroup="email"Font-Strikeout="True"Height="1px"Width="179px"/>

</td>

<tdstyle="height: 21px; width: 3px;"valign="top">

<br/>

<asp:ButtonID="Button3"runat="server"Text="Get Help ?"Width="101px"Font-Bold="False"OnClientClick='window.open("Help/onreporting.aspx")'/><br/>

<br/>

<asp:ButtonID="Button1"runat="server"PostBackUrl="~/StartPage.aspx"Text="<< Back"

Width="97px"/><br/>

</td>

</tr>

</table>

</asp:Content>

|||

Identity column data is not guaranteed to be consecutive.

If the inserts were done in the context of a transaction, and the transaction is rolled back, then that is exactly what you will see. They were there at one point, but since the transaction was rolled back they are no longer there, and the identity seed is incremented.

|||

Motley:
Identity column data is not guaranteed to be consecutive.
If the inserts were done in the context of a transaction, and the transaction is rolled back, then that is exactly what you will see. They were there at one point, but since the transaction was rolled back they are no longer there, and the identity seed is incremented.

Thanks.|||

I got the problem. There was a field in the table with varchar(7) datatype and if some one tried to insert a record and typed more than 7 characters in the textbox that insertes into this table column, the identity field would be incremented but nothing would actually be saved in the database. In my own opinion, i would say microsoft should have designed it in a way that if nothing is inserted due to such a problem, then let nothing be done on database as well. Incrementing the identity field even when no record has been inserted makes it harder to troubleshoot.

Friday, March 30, 2012

Inserted records missing in sql table yet tables primary key field has been incremented.

No comments:

Post a Comment

insert or replace

Blog Archive

About Me