insert or replace: key

Showing posts with label key. Show all posts

Friday, March 30, 2012

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.

Monday, March 26, 2012

INSERT, SCOPE_IDENTITY, and parameters...

I have a simple table Person (PersonID, PersonName, and PersonAge). PersonID is the primary key and it's also an identity field. Let me paste a sample code and I'll explain at the bottom what's happening.

 SqlConnection conn =new SqlConnection(@."Server=.\SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=True"); conn.Open();try { SqlCommand cmd =new SqlCommand(); cmd.Connection = conn;// delete all rows cmd.CommandText ="DELETE FROM Person"; cmd.ExecuteNonQuery(); Response.Write("start... <br><br>");// ad-hoc insert cmd.CommandText ="SET IDENTITY_INSERT Person ON"; cmd.ExecuteNonQuery(); cmd.CommandText ="INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (5, 'John Smith', 20)"; cmd.ExecuteNonQuery(); cmd.CommandText ="SELECT SCOPE_IDENTITY()"; Response.Write("ID = "); Response.Write(cmd.ExecuteScalar()); Response.Write("<br>"); cmd.CommandText ="SET IDENTITY_INSERT Person OFF"; cmd.ExecuteNonQuery();// parameter insert cmd.CommandText ="SET IDENTITY_INSERT Person ON"; cmd.ExecuteNonQuery(); cmd.CommandText ="INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (@.PersonID, @.PersonName, @.PersonAge)"; p =new SqlParameter("@.PersonID", 11); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); p =new SqlParameter("@.PersonName","Jon Doe2"); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); p =new SqlParameter("@.PersonAge", 21); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.CommandText ="SELECT SCOPE_IDENTITY()"; Response.Write("ID = "); Response.Write(cmd.ExecuteScalar()); Response.Write("<br>"); cmd.CommandText ="SET IDENTITY_INSERT Person OFF"; cmd.ExecuteNonQuery(); Response.Write("<br>end."); }finally { conn.Close(); }

I'm basically trying to insert rows in the table in two ways: one is ad-hoc (hardcoded sql statement) and another using parameters. Using the ad-hoc method everything is OK. Whenever I use the "parameter insert" method I can not get back the ID using SCOPE_IDENTITY (I always get back a DbNull value, the data gets into the table just fine). I'm rather new to using parameters, so it's gotta be something very easy that I'm missing...

Thank you.

If I add "; SELECT SCOPE_IDENTITY()" to the INSERT statement and use ExecuteScalar instead of ExecuteNonQuery I get the identity back. The question is, why do I have to do this? If I use the SQL Server Profiler I don't see any difference between the two methods. Any thoughts?

Thank you.

|||Your SCOPE_IDENTITY() should be in the same batch as your INSERT. You are making 3 separate calls each with a different T-SQL statement and each one is treated independent of other. Better yet, use a stored proc and do it all in one place.|||

What exactly defines a batch? And if CommandText = "INSET INTO Table (fields..) VALUES (values); SELECT SCOPE_IDENTITY()" is a single batch when using parameters, why does SCOPE_IDENTITY work in two separate "batches" when not using parameters? I guess, what I'm trying to figure out is why are how are they different? Thanks.

|||

Hi,

A batch is guaranteed to be executed together. If you execute the command in 2 batches, there is possibility that a concurrent user may be also inserting into that table. Then your SELECT SCOPE_IDNETITY() query might return the incorrect number.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

sql

INSERT violates primary key. Which way out?

Hi,
newbie question. I have an plain INSERT INTO clause:

BEGIN TRAN
INSERT INTO BILLS
SELECT
BillCode,
MyUNID
FROM DPA_BILLS
WHERE ErrorCode IS NULL
COMMIT TRAN

The original DPA_BILLS table can hold (and actually holds) rows with
non-unique values of BillCode, which is primary key in the destination
BILLS table. An acceptable behaviour would be to update the existing
row. Given that these constraints have to be kept, which is the best
way to act? Shall I process in advance my source table, resolving
foreing key conflicts, or shall I rely on some error handling in the
INSERT clause?

Thanx> Shall I process in advance my source table, resolving
> foreing key conflicts

Depends on what logic you want to apply. If it's something you can put
into code then do it wherever it is easiest. Error handling isn't the
answer because the whole of the insert fails on error, not just the
duplicate row(s).

--
David Portas
SQL Server MVP
--|||BTW you don't need BEGIN TRAN ... COMMIT TRAN. A transaction is
implicit for a single DML statement.

--
David Portas
SQL Server MVP
--

Insert values in Primary key field

Hi,

Does anyone know how should I create a table in order that I can insert values(numbers) in the primary key field, using insert statements. I also would like to know if there are any differences between SQL 2k and SQL 2k5.

Thanks in advance for any reply.

I've found the answer myself if anyone interested!

SET IDENTITY_INSERT table_name ON \g

Friday, March 23, 2012

Insert Trigger Question

Can someone help me with this scenario:
I have 2 tables one detail and one summary. The key fields for betwen the the detail and summary tables are the date field na the part # field.
I need to create a trigger that will insert records when they do not exist in the summary table and also only update the records that are needing to be modified. Any Ideas?

ThanksReal time warehousing?

Can't you run a scheduled process instead?|||Originally posted by Brett Kaiser
Real time warehousing?

Can't you run a scheduled process instead?

I need to have this summary infomration available at any time the daily reports need to be run. A scheduled process would be easier but I need to keep this table up to date as transcations are processed in the detail table.|||Use this as an example:

create table item(id int identity,item# varchar(10))
create table itemsummary(id int identity,item# varchar(10),quantity int)
go
create trigger iu_item on item
for insert,update
as
insert itemsummary(item#)
select item# from inserted i
where not exists(select 1 from itemsummary where item#=i.item#)
update itemsummary set quantity=(select count(*) from item i where i.item#=itemsummary.item#)
go
insert item(item#) values('#1')
insert item(item#) values('#1')
insert item(item#) values('#2')

select * from item
select * from itemsummary|||He'll need to update..the count? for the part#

So you need 2 sections...

Check

-- An Update
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)

--An INSERT
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)

Then do your apporpriate action...

What's the transaction level?sql

Wednesday, March 21, 2012

Insert to a table

Can I do an insert from table A to table B only columns that are in A but not
in B and changing them before insert? I want the primary key of rows in A
that don't exist in B to be inserted in B, but I want to flag them in a
couple of other columns. I see that I can either use an Insert query with
select statement where all the columns are inserted as is or Insert...Values
where all are manual values. Is there a statement that can be a combination
of these two?
Thanks
You should be able to formulate an INSERT SELECT statement to do what you
want:
INSERT INTO TableB (col1, col2, col3, col4, ...)
SELECT colA, 'some value', 1234, colB, ...
FROM TableA
WHERE ...
David Portas
SQL Server MVP
|||On Mon, 13 Dec 2004 15:01:01 -0800, Niles wrote:

>Can I do an insert from table A to table B only columns that are in A but not
>in B and changing them before insert? I want the primary key of rows in A
>that don't exist in B to be inserted in B, but I want to flag them in a
>couple of other columns. I see that I can either use an Insert query with
>select statement where all the columns are inserted as is or Insert...Values
>where all are manual values. Is there a statement that can be a combination
>of these two?
>Thanks
Hi Niles,
You mean something like this?
INSERT INTO TableB (KeyColumn, OtherColumn, ThirdColumn)
SELECT KeyColumn, OtherColumn, 'Literal value'
FROM TableA AS a
WHERE NOT EXISTS
(SELECT *
FROM TableB AS b
WHERE b.KeyColumn = a.KeyColumn)
or alternatively:
INSERT INTO TableB (KeyColumn, OtherColumn, ThirdColumn)
SELECT a.KeyColumn, a.OtherColumn, 'Literal value'
FROM TableA AS a
LEFT OUTER JOIN TableB AS b
ON b.KeyColumn = a.KeyColumn
WHERE b.KeyColumn IS NULL
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Insert to a table

Can I do an insert from table A to table B only columns that are in A but not
in B and changing them before insert? I want the primary key of rows in A
that don't exist in B to be inserted in B, but I want to flag them in a
couple of other columns. I see that I can either use an Insert query with
select statement where all the columns are inserted as is or Insert...Values
where all are manual values. Is there a statement that can be a combination
of these two?
ThanksYou should be able to formulate an INSERT SELECT statement to do what you
want:
INSERT INTO TableB (col1, col2, col3, col4, ...)
SELECT colA, 'some value', 1234, colB, ...
FROM TableA
WHERE ...
--
David Portas
SQL Server MVP
--|||On Mon, 13 Dec 2004 15:01:01 -0800, Niles wrote:
>Can I do an insert from table A to table B only columns that are in A but not
>in B and changing them before insert? I want the primary key of rows in A
>that don't exist in B to be inserted in B, but I want to flag them in a
>couple of other columns. I see that I can either use an Insert query with
>select statement where all the columns are inserted as is or Insert...Values
>where all are manual values. Is there a statement that can be a combination
>of these two?
>Thanks
Hi Niles,
You mean something like this?
INSERT INTO TableB (KeyColumn, OtherColumn, ThirdColumn)
SELECT KeyColumn, OtherColumn, 'Literal value'
FROM TableA AS a
WHERE NOT EXISTS
(SELECT *
FROM TableB AS b
WHERE b.KeyColumn = a.KeyColumn)
or alternatively:
INSERT INTO TableB (KeyColumn, OtherColumn, ThirdColumn)
SELECT a.KeyColumn, a.OtherColumn, 'Literal value'
FROM TableA AS a
LEFT OUTER JOIN TableB AS b
ON b.KeyColumn = a.KeyColumn
WHERE b.KeyColumn IS NULL
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Monday, March 19, 2012

Insert stored procedure with output parameter

Hello everyone.

I need a stored procedure that excecutes a INSERT sentence.
That's easy. Now, what I need is to return a the key value of the just inserted record.

Someone does know how to do this?

In you SP use:

return SCOPE_IDENTITY()

Then in C# code:

comm = new SqlCommand("InsertANewRequest", conn);

comm.CommandType = CommandType.StoredProcedure;

SqlParameter newReqNumber = new SqlParameter("@.RETURN_VALUE", SqlDbType.Int);

comm.Parameters.Add(newReqNumber);

newReqNumber.Direction = ParameterDirection.ReturnValue;

try

{

// Open the connection

conn.Open();

// Execute the command

comm.ExecuteNonQuery();

int newReq = Convert.ToInt32(newReqNumber.Value);

}

|||

Thanks a lot!

While you posted this I solved it out using SELECT @.@.Identity

Is there any diference with the solution you gave me?

|||

Quote from BOL:

SCOPE_IDENTITY, IDENT_CURRENT, and @.@.IDENTITY are similar functions because they return values that are inserted into identity columns.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).
SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @.@.IDENTITY is not limited to a specific scope.

|||

Konstantin Kosinsky wrote:
In you SP use:
return SCOPE_IDENTITY()
Then in C# code:
comm = new SqlCommand("InsertANewRequest", conn);
comm.CommandType = CommandType.StoredProcedure;
SqlParameter newReqNumber = new SqlParameter("@.RETURN_VALUE", SqlDbType.Int);
comm.Parameters.Add(newReqNumber);
newReqNumber.Direction = ParameterDirection.ReturnValue;
try
{
// Open the connection
conn.Open();
// Execute the command
comm.ExecuteNonQuery();
int newReq = Convert.ToInt32(newReqNumber.Value);
}

Insert statements, primary keys and relationships

I have a number of tables (30+) all collecting info about societies. I have a primary key (soc_ID) in all my tables and a number of one-to-one relationships. All the tables are joined.

Q1 : If I add into the first row in one table, do I HAVE to insert the PK in EVERY other table its related to? Q2:is it wise to have foreign keys nullable?
My MS SQL SERVER statement::

insert into society(soc_id) values ('1');

ERROR: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Society_EDU_materials_targeted". The conflict occurred in database "mem_soc", table "dbo.EDU_materials_targeted", column 'soc_id'.
The statement has been terminated.

I understand the error. Is there a short way to insert the PK in all related tables automatically otherwise my insert statement will be HUGE.
This method seems REALLY long winded...I'm 99% sure that you've got the primary and foreign keys reversed between your EDU_materials_targeted and society tables. At least as I understand it, the soc_id should be the primary key in the society table, and it should be the foreign key in the dbo.EDU_materials_targeted table.

A foreign key should allow NULL values if the relationship has a cardinality of "1 to zero or more". In other words, if the child table might not have a matching row in the parent table, then the FK should allow NULL values.

Normally I'd move this discussion to the Microsoft SQL Server (http://www.dbforums.com/forumdisplay.php?f=7) forum, but since it still is a relatively pure SQL issue I'm Ok with leaving it here in the SQL forum.

-PatP

-PatP|||Thanks a lot! You're 100% correct!

Monday, March 12, 2012

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

For some reason, I'm getting this error, even without the DBCC Check:

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'mydb', table 'Category', column 'CategoryID'.
The statement has been terminated.

The very first insert fails...it was working fine before:

DELETE Category;

-- Now, insert the initial 'All' Root Record

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 2, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'

/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/

WHILE (@.@.ROWCOUNT <> 0)
BEGIN
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
AND s.CategoryName <> 'All'

Here's the schema:

CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [mydatabase]
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])

The error message is pretty clear. You have specified key values that doesn't exist in the parent table. The message indicates the constraint and table so you can find out which tables are involved. I think your problem is the following:

You are using IDENTITY column as primary key. And you have another column that references the identity column in the same table. When you do a DELETE it doesn't reset the seed for identity column. So if you had performed data manipulations before you are going to get new identity value based on the previous generated values and it will not start at 1 or whatever your seed is. TRUNCATE TABLE on the other hand will reset the identity seed to the original value. Or you can also use DBCC CHECKIDENT. Typically when you store this sort of parent child relationship you should have the root's parent as NULL to simplify your operations rather than the root id itself.

|||

A few questions then to your response:

1) I cannot use truncate on that table, because it has a FK relationship

2) I've tried DBCC CHECKIDENT ('Category', RESEED, 0). That seems to work only half the time, then I get that error again here and there still

What I want is, every time we need to import new categories

a) Delete all data in the Category Table

b) Ensure that the new inserts start at 1 again for the CategoryID identity

how can this be accomplished if 1 and 2 have been tried..based on my SQL I've coded?

|||DBCC CHECKIDENT is the only way if you have references. Could you post some sample code that demonstrates the problem and also mention the version of SQL Server? I haven't had any issues with DBCC CHECKIDENT to reseed identity values. And I don't know of any bug in SQL Server related to the DBCC CHECKIDENT command.|||I need to drop the constraint, do my inserts, then recreate the constraint at the end. I am working on the syntax for that drop and re-create..|||I've had this error msg and simply deleted the relationship, and in Enterprise Manager redefined the relationship by clicking FIRST on the primary table key and then linking it to the other table's key.|||

I get this problem as well and have the following table and with constraints:

CREATE TABLE [BudgetDetail] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,

[BudgetHeaderID] [int] NULL ,
[SupersededDetailID] [int] NULL

CONSTRAINT [PK_BudgetDetail] PRIMARY KEY NONCLUSTERED
(
[RecordID]
) ON [PRIMARY] ,
CONSTRAINT [FK_BudgetDetail_BudgetDetail_SupersededDetailID] FOREIGN KEY
(
[SupersededDetailID]
) REFERENCES [BudgetDetail] (
[RecordID]
)
) ON [PRIMARY]
END

The delete fails with the conbstraint error even though the [SupersededDetailID] is set to NULL.

DELETE FROM BudgetDetail WHERE BudgetHeaderID = 1885 AND RecordID = 269673

DELETE statement conflicted with COLUMN SAME TABLE REFERENCE constraint 'FK_BudgetDetail_BudgetDetail_SupersededDetailID'. The conflict occurred in database 'SitestreamTest', table 'BudgetDetail', column 'SupersededDetailID'.
The statement has been terminated.

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

For some reason, I'm getting this error, even without the DBCC Check:

The very first insert fails...it was working fine before:

DELETE Category;

-- Now, insert the initial 'All' Root Record

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 2, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'

/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/

Here's the schema:

CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [mydatabase]
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])

|||

A few questions then to your response:

1) I cannot use truncate on that table, because it has a FK relationship

2) I've tried DBCC CHECKIDENT ('Category', RESEED, 0). That seems to work only half the time, then I get that error again here and there still

What I want is, every time we need to import new categories

a) Delete all data in the Category Table

b) Ensure that the new inserts start at 1 again for the CategoryID identity

how can this be accomplished if 1 and 2 have been tried..based on my SQL I've coded?

I've had this error msg and simply deleted the relationship, and in Enterprise Manager redefined the relationship by clicking FIRST on the primary table key and then linking it to the other table's key.|||

I get this problem as well and have the following table and with constraints:

CREATE TABLE [BudgetDetail] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,

[BudgetHeaderID] [int] NULL ,
[SupersededDetailID] [int] NULL

The delete fails with the conbstraint error even though the [SupersededDetailID] is set to NULL.

DELETE FROM BudgetDetail WHERE BudgetHeaderID = 1885 AND RecordID = 269673

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

For some reason, I'm getting this error, even without the DBCC Check:

The very first insert fails...it was working fine before:

DELETE Category;

-- Now, insert the initial 'All' Root Record

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 2, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'

/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/

Here's the schema:

CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [mydatabase]
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])

|||

A few questions then to your response:

1) I cannot use truncate on that table, because it has a FK relationship

2) I've tried DBCC CHECKIDENT ('Category', RESEED, 0). That seems to work only half the time, then I get that error again here and there still

What I want is, every time we need to import new categories

a) Delete all data in the Category Table

b) Ensure that the new inserts start at 1 again for the CategoryID identity

how can this be accomplished if 1 and 2 have been tried..based on my SQL I've coded?

INSERT statement conflicted with COLUMN FOREIGN KEY constraint...

Hi there,

I have a stored procedure which i pass a number of parameters into. One of these parameters is staffNo (only passed this in because i couldn't execute the query without it). The thing is this field can be Null, but when trying to pass null into it it comes up with an Foreign Key conflict. staffNo is a foreign key within the table i'm inserting the data into.

This is the error i get:

"INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'PropStaffFK'. The conflict occurred in database 'DewMountain', table 'TblStaff', column 'staffNo'. The statement has been terminated. The 'PropertyAdvert' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. "

Does anyone know of away around this? how to pass a null value to the stored procedure without it causing this error.

Thank you

Melanie

There is a conflict, you say that staffNo can be null, however, PropStaffFK says that it can not, since you have no staff member with an id of null.|||

Thanks for your reply Motley.

So does that mean if you have a foreign key referencing a primary key from another table that it can't be null?

That makes sence that it would be true, however, in one of my tables TblProperty i have the column staffNo and in this column i want to allow null values to indicate that the property hasn't been approved by a memeber of staff (if it has been approved then the member of staff that approved the property, thier staffNo will be inserted in there) . Do you know of any way to do this?

Thanks

Melanie

|||

Here's the problem. In order to set a FK contraint, you need to reference a primary key table (That has a primary key set). You can't set a primary key index on a column that is nullable. Since the Primary key table doesn't have a NULL-value as one of it's values, then the FK table can not have null as one of it's values either. (Actually, it wouldn't really make all that much sense anyhow, because even if there was a null vaue in the PK table, since null represents UNKNOWN, and UNKNOWN=UNKNOWN is always false, it still shouldn't work even if null was allowed in the PK table).

A workaround is to create an entry into the PK table for unassigned values, like "0" or "-1". Then make the column in the FK table, not-nullable, with a default constraint of "0" or "-1".

|||Another option is to remove the primary key on the primary key table, enter a null value in the old PK column, then apply a unique constraint on that column, then reestablish your FK relationship. Of course, if the old PK table column as of type identity, you'll have to remove that since identity columns can not contain null either.|||

Yeah thats the thing, i have the primary key column set as type Identity.

I like the first idea you mentioned, by putting a 0 or -1 in there replacing the null entry.

Could use the identity type below ( seed 0 and increment 1)

IDENTITY (0, 1)

then the null entry will be replaced by '0' by inserting a fake entry at the start.

Thanks for your help Motley i'll go and try this out.

Melanie

|||You can just remove the indentity from the column, insert your record, and add the identity back on the column too, that works too.|||

How would i carry that out?

I mean to add the identity back to the column after inserting a row of data, wouldn't this require me dropping the table which in turn removes the data entered previouly entered?

Could you tell me the way of doing this without dropping the table?

Thank you

Melanie

|||

I use management studio, so while technically you are correct, it hides it all in the background for me (Copying all the data), and if the table isn't too big, it's quick. But as far as I know, you are correct, you must create a new table to reinstate the identity.

|||

Thanks Motley

What do you suggest for me then?

Guess i'll have to go with '0' to replace the null entry.

Melanie

|||

You might try doing the insert by doing a SET IDENTITY_INSERT {Your table} ON then insert the record, then SET IDENTITY_INSERT {Your table} OFF

INSERT statement - autonumbering

Hi All,

I am having trouble with a simplet INSERT statement. I want to insert a record. The field "dbid" is the primary key and should be autonumbering. What do I need to add to my code?

Dim myconnectionAs SqlConnection myconnection =New SqlConnection() myconnection.ConnectionString = _ ConfigurationManager.ConnectionStrings("infoNoticeDBConnectionString").ConnectionStringDim strSQLAs String ="INSERT INTO users " & _"(dbid, infoid) VALUES (@.dbid, @.infoid)"Dim dbCommAs New SqlCommand(strSQL, myconnection) dbComm.Parameters.Add("infoid", SqlDbType.NVarChar, 50,"@.infoid") dbComm.Parameters.Add("dbid", SqlDbType.UniqueIdentifier,"dbid") dbComm.Parameters("infoid").Value = gv2.SelectedValueTry myconnection.Open() dbComm.ExecuteNonQuery()Catch exAs Exception Response.Write(ex.Message) Response.End()Finally If myconnection.State = ConnectionState.OpenThen myconnection.Close()End If End Try Response.Write("A new record has been added") Response.End()End Sub

Thanks,

If that field has been configured in your database as an autonumber column, then you wouldnot include it in the insert statement.
The database will automatically populate it with the next value.

|||

Got it..Thanks. also had to make field Indentity "yes"

Thanks!

Friday, March 9, 2012

Insert Rows into four tables at one time

Hello,

I have 4 tables having Customer, Customer_personal_info, Customer_Financial_info, Customer_Other_info

In this Customer table had a primary key CustomerID , related with every other table with fkey.

I want to insert data into four tables using one form having TABs .

I created class and storedProcedures to insert row for each table.

How to execute all four classes using beginTrans-commitTrans-Rollback-EndTrans.

Thanking you,

I am not sure if you have created one stored procedure or four stored procedures (your post mentions "stored procedures")..

If you want to go the stored procedure way, It is ideal to create one stored procedure on which you can use a SQL level BEGIN TRAN and ROLLBACK. These are keywords written inside the SQL stored procedure.

Another way is to execute the queries or stored procedures usingSQLCommand's ExecuteNonQuery method, but before execution create aSQLTransaction object and associate that with the SQLconnection. If the transaction (Insert statements in SQL or SP) works fine, thencommit the transaction.

HTH

Wednesday, March 7, 2012

Insert Query on table with identity column

I cannot insert into my appointments table because the primary key and identity column, appt_id, cannot be added. What do I have to change in my SQL statement to add new records into this table? I'm using SQL Server 2000 BE with Access Data Project FE.

tbl_appointment
------
1. appt_id (pk) -- identity column, seed 25, increment 1
2. date_id
3. time_start
4. time_end
5. appt_details
6. lkp_emp_id

Private Sub btnAddAppts_Click()
On Error GoTo Err_btnAddAppts_Click
Dim strsql As String
DoCmd.SetWarnings False
strsql = "INSERT INTO [tbl_appointments] (lkp_emp_id, date_id, time_start, time_end, appt_details) values ('" & txtLkpEmpID & "', '" & txtDateID & "', '" & txtStartTime & "', '" & txtEndTime & "', '" & txtApptDetails & "')"
DoCmd.RunSQL strsql
DoCmd.SetWarnings True
DoCmd.Close

Exit_btnAddAppts_Click:
Exit Sub

Err_btnAddAppts_Click:
MsgBox Err.Description
Resume Exit_btnAddAppts_Click
End Sub

I did check through Access and through Enterprise Manager and it is setup correctly. So I returned all rows in enterprise manager to manually enter an appointment to the table. I get the same error when doing data-entry straight to the table.

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update identity column 'appt_id'.

It does not automatically populate the appt_id field the way it's supposed to. When I try to manually set a value in there, i get an error: "Cannot edit this cell."[posted and mailed, please reply in news]

Dave (dcermelixx@.ucwphilly.rr.com) writes:
> I cannot insert into my appointments table because the primary key and
> identity column, appt_id, cannot be added. What do I have to change in
> my SQL statement to add new records into this table? I'm using SQL
> Server 2000 BE with Access Data Project FE.
> tbl_appointment
> ------
> 1. appt_id (pk) -- identity column, seed 25, increment 1
> 2. date_id
> 3. time_start
> 4. time_end
> 5. appt_details
> 6. lkp_emp_id
>
> Private Sub btnAddAppts_Click()
> On Error GoTo Err_btnAddAppts_Click
> Dim strsql As String
> DoCmd.SetWarnings False
> strsql = "INSERT INTO [tbl_appointments] (lkp_emp_id, date_id, time_start,
> time_end, appt_details) values ('" & txtLkpEmpID & "', '" & txtDateID &
> "', '" & txtStartTime & "', '" & txtEndTime & "', '" & txtApptDetails &
> "')"
> DoCmd.RunSQL strsql
> DoCmd.SetWarnings True
> DoCmd.Close

This is not a good way of writing SQL statements. Try to enter
the value "It's good" in txtApptDetails to see what happens.

The above is open for an attack known as SQL injection, whereby an
attacker can change your SQL statement to do something you did not intend.

The remedy is to add parameterized statments:

INSERT [tbl_appointments)
(lkp_emp_id, date_id, time_start, time_end, appt_details)
VALUES (?, ?, ?, ?, ?)

The client library then takes care of necessary quoting, converting of
date formats etc. (The above presumes that SQL Server will interpret
the dates, which may not work well.)

I don't really know which client library you are using, so I can't tell
how you would do it. But should definitely investigate the possibilities.

> I did check through Access and through Enterprise Manager and it is
> setup correctly. So I returned all rows in enterprise manager to
> manually enter an appointment to the table. I get the same error when
> doing data-entry straight to the table.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update identity
> column 'appt_id'.

Since the INSERT statement looks OK, I would look into whether there is
a trigger on the table.

If you run the INSERT statement from Query Analyzer, do you get the
same error message? In such case, pay attention on whether the error
message includes a procedure name.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> INSERT [tbl_appointments)
> (lkp_emp_id, date_id, time_start, time_end, appt_details)
> VALUES (?, ?, ?, ?, ?)

I agree, and I used to think this was silly.

What you need to do is set this up as a command object, and then add
five parameter objects to it. Right before you fire the command, you
plug in their values. Example:

Dim cmd as new oledb.oledbCommand("Insert...", dbConn)
cmd.Parameters.Add( New Paremeter("@.lkp_emp_id", dbVarChar)

Then

cmd.Parameters(0).Value = 7
cmd.ExecuteNonQuery

> If you run the INSERT statement from Query Analyzer, do you get the
> same error message? In such case, pay attention on whether the error
> message includes a procedure name.

Another thing is if you're using Access as your client, even though
it's basically just sending the SQL through to the backend database,
sometimes it'll parse it first, so it's a good idea to surround all
your field-names with [square brackets] to make it clear you're
talking about a field.

Insert Query has Conflict with Foreign Key

there have a exception when I trying to run a insert query

the exception is occur on cmd.ExecuteReder(); and shows
Insert Query conflict with Foreign key..

why? and how to resolve it?

thank you

You need to look up foreign keys in books online, but basically it has to do with putting invalid data into a column that references the data in the primary (or unique) key of another table. As an example:

use tempdb
go
create table parent
(
parentId int primary key
)
go
create table child
(
childId int primary key,
parentId int foreign key references parent(parentId)
)
go
insert into child
values (1,1)
go
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__child__parentId__0CBAE877". The conflict occurred in database "tempdb", table "dbo.parent", column 'parentId'.
The statement has been terminated.
go
insert into parent
values (1)
go
insert into child
values (1,1)

Friday, February 24, 2012

Insert Query

Hi ,
What is the query to insert the table A to table b if the table b primary
key did not
exist in table A ?
--
Travis TanHello
if in table B primary key collumn is uniqueidentifier then i would try
insert into table b ( primary_key_collumns, columnA, columnB...)
(
select newid(), , columnA, columnB...
from table A
)
--
Wojciech Znaniecki
U¿ytkownik "Travis" <Travis@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:AF89E88B-F942-41CF-9D95-7BD08FECA853@.microsoft.com...
> Hi ,
> What is the query to insert the table A to table b if the table b
primary
> key did not
> exist in table A ?
> --
> Travis Tan|||Try this
INSERT INTO b SELECT * FROM a WHERE NOT EXISTS (SELECT * FROM b WHERE
a.PK=b.PK)
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:AF89E88B-F942-41CF-9D95-7BD08FECA853@.microsoft.com...
> Hi ,
> What is the query to insert the table A to table b if the table b
primary
> key did not
> exist in table A ?
> --
> Travis Tan|||Hi Travis
Just try this query
insert into tableB Select * from TableA A Where A.Key NOT IN (SELECT B.KEY
from TableB B)
thanks and regards
Chandra
"Travis" wrote:
> Hi ,
> What is the query to insert the table A to table b if the table b primary
> key did not
> exist in table A ?
> --
> Travis Tan|||Hi
I may get a wrong output if b.key is NULL
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:93AF9ED8-273F-4180-842B-EA33A2F52CA3@.microsoft.com...
> Hi Travis
> Just try this query
> insert into tableB Select * from TableA A Where A.Key NOT IN (SELECT B.KEY
> from TableB B)
> thanks and regards
> Chandra
>
> "Travis" wrote:
> > Hi ,
> >
> > What is the query to insert the table A to table b if the table b
primary
> > key did not
> > exist in table A ?
> >
> > --
> > Travis Tan

Insert procedure in two tables with Foreign Key relation ship

I was wondering how I do to insert values in two tables that are related each other by a FK?

That is the procedure that illustrate what I meant to be.

ALTERProcedure [dbo].[new_user]

@.masternchar(10),

@.nicknchar(10),

@.fishnchar(10),

@.e_mailnchar(30)

Begin

INSERTINTO users

(nick, fish, e_mail)

VALUES (@.nick,@.fish,@.e_mail)

INSERTINTO friends

(user_id, e_mail)

VALUES (Selectuser_idfrom userswhere nick=@.master,@.e_mail)

End

Thank you very much.

ALTER Procedure [dbo].[new_user]@.masternchar(10),@.nicknchar(10),@.fishnchar(10),@.e_mailnchar(30)AsBeginSET NOCOUNT ONDeclare @.useridintINSERT INTO users (nick, fish, e_mail)VALUES (@.nick,@.fish,@.e_mail)SELECT @.userid = SCOPE_IDENTITY()INSERT INTO friends ([user_id], e_mail)VALUES (@.userid, @.e_mail)SET NOCOUNT OFFEnd

|||

Thanks to reply ndinakar.

The value inserted "@.userid" in friends table, it is not the same just inserted in the users table. This value is determined by a consult in users table, where nick = "@.master", the return of this consult will say which user_id I will insert in friends table.

|||So do you still need to do an INSERT into the users table with the values you receive in the stored proc or are the values just to be used for lookup?|||

The value @.master is for lookup the table users and gets the user_id. The values @.nick, @.fish, @.e_mail, are for create a new user. And is inserted a new record in friends table with user_id equals to the value consulted.

ALTERProcedure [dbo].[new_user]

@.masternchar(10),

@.nicknchar(10),

@.fishnchar(10),

@.e_mailnchar(30)

Begin

Declare@.returned_valuenchar(10)

Selectuser_idfrom userswhere nick=@.master

--I garante it will return a unique value. Supose to be called @.returned_value.

INSERTINTO users

(nick, fish, e_mail)

VALUES (@.nick,@.fish,@.e_mail)

INSERTINTO friends

(user_id, e_mail)

VALUES (@.returned_value,@.e_mail)

End

That will result a data base. Which the new user created will be related with the person who added.

|||

ALTER Procedure [dbo].[new_user]@.masternchar(10),@.nicknchar(10),@.fishnchar(10),@.e_mailnchar(30)AsBeginDeclare @.returned_valueint-- assuming userid is numeric if not I would recommend nvarchar instead of nchar.Select @.returned_value = [user_id]from userswhere nick=@.master--I garante it will return a unique value. Supose to be called @.returned_value.INSERT INTO users (nick, fish, e_mail)VALUES (@.nick,@.fish,@.e_mail)INSERT INTO friends ([user_id], e_mail)VALUES (@.returned_value,@.e_mail)End

|||Thank you very much indeed. But at last why you would recommend nvarchar instead of nchar?|||

When you use char, the length becomes a fixed size. So even if you send in a value less than the specified length, SQL pads the value with blank spaces to make it the fixed length.

So 'abc' <> 'abc '. Besides it is also a waste of space/memory. If you use varchar (Variable char) it allocates only as much is required, up to the specified length.

|||

On the select statement I mentioned that it will return a unique value, but if it return nothing or more fields?

If don't want waste your time explaining to me, could just give me the word that I look for on the internet? I tried some but I would never find about this problem specifically. And just wondering, do you know if Microsoft pays some one to stay here in this forum answering questions?

Thank you very much indeed.

Insert Primary Keys to all tables in a database

I have a database of 300 tables that needed to insert Primary Key:
1.Set ENO as PRIMARY KEY in all tables that contains the field ENO.
2. Set RPT_NAME as PRIMARY KEY in all tables that contains the field
RPT_NAME.
Is there a fast way to insert it once and for all rather than inserting
it one by one?
Another issue is to change the width of field named COST_CENTR of
VARCHAR 10 to 12 in all tables that has it. ( maintain the contents )
Currently using MsSQL 2K. Help is greatly appreciated. ThanksYou can speed this up by scripting.
You can derive every table and column name from various system calls, e.g
use informationschema for tables and
exec sp_columns 'myTable' will list the column names.
You could add some logic to check the coloumn names and then perform an
action , such as add PK
A similar process as above for your second problem - check for column
COST_CENTR and do an ALTER TABLE to widen the col width
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"ymcj" <june.ymc@.gmail.com> wrote in message
news:1145520225.026547.254790@.u72g2000cwu.googlegroups.com...
> I have a database of 300 tables that needed to insert Primary Key:
> 1.Set ENO as PRIMARY KEY in all tables that contains the field ENO.
> 2. Set RPT_NAME as PRIMARY KEY in all tables that contains the field
> RPT_NAME.
> Is there a fast way to insert it once and for all rather than inserting
> it one by one?
> Another issue is to change the width of field named COST_CENTR of
> VARCHAR 10 to 12 in all tables that has it. ( maintain the contents )
> Currently using MsSQL 2K. Help is greatly appreciated. Thanks
>|||On 20 Apr 2006 01:03:45 -0700, ymcj wrote:

>I have a database of 300 tables that needed to insert Primary Key:
>1.Set ENO as PRIMARY KEY in all tables that contains the field ENO.
>2. Set RPT_NAME as PRIMARY KEY in all tables that contains the field
>RPT_NAME.
Hi ymcj,
This is a very strange request. If alll tables share the same primary
key, then why is your data spread out over 300 tables? The idea of
normalization is to use different tables for data that needs DIFFERENT
keys.
Can you elaborate a bit on what you're trying to achieve and why?
(snip)
>Another issue is to change the width of field named COST_CENTR of
>VARCHAR 10 to 12 in all tables that has it. ( maintain the contents )
Execute the following SQL in Query Analyzer (using the results to text
option instead of the results to grid option!!)
SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + '
' + 'ALTER COLUMN ' + COLUMN_NAME + ' varchar(12)'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'COST_CENTR'
Check the result. If it looks okay, select it, copy and paste it to the
query window and execute it.
Test this on a test database first. After that, make sure that you
perform this task during scheduled down time, and make sure that you
have a good backup.
Hugo Kornelis, SQL Server MVP

Friday, March 30, 2012

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

Monday, March 26, 2012

INSERT, SCOPE_IDENTITY, and parameters...

INSERT violates primary key. Which way out?

Insert values in Primary key field

Friday, March 23, 2012

Insert Trigger Question

Wednesday, March 21, 2012

Insert to a table

Insert to a table

Monday, March 19, 2012

Insert stored procedure with output parameter

Insert statements, primary keys and relationships

Monday, March 12, 2012

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

INSERT statement conflicted with COLUMN FOREIGN KEY constraint...

Hi there,

This is the error i get:

INSERT statement - autonumbering

Friday, March 9, 2012

Insert Rows into four tables at one time

Wednesday, March 7, 2012

Insert Query on table with identity column

Insert Query has Conflict with Foreign Key

Friday, February 24, 2012

Insert Query

Insert procedure in two tables with Foreign Key relation ship

Insert Primary Keys to all tables in a database

insert or replace

Blog Archive

About Me