Need Help With Insert Statement On Table With Several Non-null Fields
Apr 30, 2007
On my aspx page I have a basic sqldatasource and gridview - both were set up using the wizards. The SQL Server 2000 database table that the sqldatasource is querying has some fields in it that are set to not allow nulls, however, the gridview control is not displaying all of the fields in the table - including some of the non-null fields.
My problem is, when I run an insert on the table from my aspx page, I get an error that says: "Cannot insert the value NULL into column 'ColumnName', table 'TableName'; column does not allow nulls. INSERT fails. The statement has been terminated."
The 'ColumnName', as you may have guessed, is one of the aforementioned columns that doesn't allow nulls, but isn't in the GridView.
How can I do an insert on this table without messing with the non-null fields. Those fields are relevant to the purposes of the gridview, so I don't want to display them, let alone allow editing of them.
Any suggestions on my options would be greatly appreciated!
Thanks
Capella07
View 2 Replies
ADVERTISEMENT
Apr 21, 2008
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
thanks
View 7 Replies
View Related
Jul 21, 2004
Can someone help me with this :
Case WHEN ISNULL (dbo.BLH.n1name,'NOTHING')= 'NOTHING' then dbo.BLH.coname else dbo.BLH.n1name End as CustName
as the strored proc is not returning value when the fileds is blank
cheers
View 2 Replies
View Related
Oct 23, 2014
I am attempting to update a table that drives a report. The report is at the order level. An order can have several types of demand (revenue $): Ship and/or Backordered. When I update one of these demand fields on the report table, I would expect those orders that have that type of demand to update, and those orders that don't have that type of demand to remain at their current value ($0 in this example which is the table default). But what is happening is that orders that don't have that type of demand are changing to NULL. What am i doing wrong with my update statement that is causing this?
Code:
CREATE TABLE #sales(
OrdNo int,
StatusGrp varchar(10),
Demand decimal(10,2)
)
INSERT INTO #sales
VALUES (1,'Ship',100.00)
[Code] .....
View 6 Replies
View Related
Jan 15, 2008
Hi all,
I have a projects table with 2 foreign key fields that both link to the same employees table because a project has a Package Engineer (PkgEngineerID) and a Contract Administrator (PkgContrAdmin). When I try to insert a record with null values for either or both of these foreign keys I get an error:
The data in row xxx was not commited. The record can't be added or changed. Referential integrity rules require a related record in table 'tblEmployees'. The transaction ended in the trigger. The batch has been aborted.
An insert statement for the above would look something like the following:
INSERT INTO tblPackages (PkgNo, PkgName, PkgEngineerID, PkgContrAdmin, PkgRemark)VALUES (1234, 'My Package', NULL, NULL, 'My Package remark')
And the create table statements are:
USE [PASSQL]GO/****** Object: Table [dbo].[tblPackages] Script Date: 01/15/2008 23:25:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tblPackages]( [ID] [int] IDENTITY(1,1) NOT NULL, [PkgNo] [nvarchar](20) NULL, [PkgName] [nvarchar](255) NULL, [PkgEngineerID] [int] NULL, [PkgContrAdmin] [int] NULL, [PkgRemark] [nvarchar](255) NULL, [upsize_ts] [timestamp] NULL, CONSTRAINT [aaaaatblPackages_PK] PRIMARY KEY NONCLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[tblPackages] WITH NOCHECK ADD CONSTRAINT [FK_tblPackages_tblEmployees] FOREIGN KEY([PkgEngineerID])REFERENCES [dbo].[tblEmployees] ([ID])GOALTER TABLE [dbo].[tblPackages] CHECK CONSTRAINT [FK_tblPackages_tblEmployees]GOALTER TABLE [dbo].[tblPackages] WITH NOCHECK ADD CONSTRAINT [FK_tblPackages_tblEmployees1] FOREIGN KEY([PkgContrAdmin])REFERENCES [dbo].[tblEmployees] ([ID])GOALTER TABLE [dbo].[tblPackages] CHECK CONSTRAINT [FK_tblPackages_tblEmployees1]
And:
USE [PASSQL]GO/****** Object: Table [dbo].[tblEmployees] Script Date: 01/15/2008 23:28:01 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tblEmployees]( [ID] [int] IDENTITY(1,1) NOT NULL, [EmpName] [nvarchar](255) NULL, [EmpShort] [nvarchar](255) NULL, [upsize_ts] [timestamp] NULL, CONSTRAINT [aaaaatblEmployees_PK] PRIMARY KEY NONCLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
Any ideas on how to accomplish this would be great!
Thanks in advance!
View 4 Replies
View Related
Aug 3, 2007
Can someone help with this? Let me know if what I'm trying to do is possible...
Here's code example:
@ClientID int,
@QuoteID int,
@Base real,
@One real,
@DwellingLimit real
AS
BEGIN
Insert Into tblOne(ClientID,QuoteID,GuideID,GuideRate,GuideMult,Premium)
Select @ClientID, @QuoteID, GuideID,GuideRate, @+"GuideMult"+,GuideRate*GuideMult
From tblTwo
Where Choose = 'True';
END
I need the value stored in tblTwo.GuideMult (ie. One, BaseRate) to be translated
into the numerical value shown on a webform (ie. @One, @BaseRate) and then
insert the numerical values into tblOne.GuideMult
Clear as mud? Does somebody have a better way to do this?
View 1 Replies
View Related
Feb 26, 2008
I need to create a table that only allows fields to be updated if the field value is NULL. Is there anyway to configure a table in this manner?
In the example below the record will be created with a serial number and verification ready flag set to true. Other processes down stream will update the remaining fields as needed but should not update fields that have already been initialized.
Table Definition
USE [DB_AUTOMATED_PACKAGING_SYSTEM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBL_LABEL_VERIFICATION](
[SerialNumber] [varchar](50) NOT NULL,
[VerificationReady] [int] NOT NULL,
[VoidLabelImage] [image] NULL,
[VerifiedBy] [varchar](50) NULL,
[VerifiedDate] [datetime] NULL,
[Verified] [int] NULL,
CONSTRAINT [PK_TBL_LABEL_VERIFICATION] PRIMARY KEY CLUSTERED
(
[SerialNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
View 1 Replies
View Related
Jul 21, 2007
I am copying a simple table from a Sql Server 2005 database to an *.sdf mobile database.
I am brand new to SSIS and I am probably doing something wrong. But after executing the SSIS package all the rows and all the fields are NULL in the destination database. I put a datagrid viewer between the OLE DB Source and the Sql Server compact edition destination and I can see the real data which is obviously not ALL NULL.
Does anyone have a clue as to why it would be doing this?
Any help would be much appreciated.
Thanks...
View 1 Replies
View Related
Jun 13, 2008
All- Supposing I have table_a and table_b. Table_a has fields FIRST_A and LAST_A for people's first and last names. Similarly, table_b has FIRST_B and LAST_B for people's first and last names.
How would I create a record in table_b for each record in table_a, such that for each added record, the values of FIRST_A is copied to FIRST_B and LAST_A is copied to LAST_B. (Table_b will have other fields that are left unfilled.)
Thanks!
-Kurt
View 1 Replies
View Related
Apr 20, 2008
need help on update from one table to another like this
this is my first table
tb_all_holiday
id
fname
Start_Date
End_Date
val_holiday
111
aaaa
15/03/2008
21/03/2008
1
222
bbbb
02/05/2008
09/05/2008
3
333
cccc
03/04/2008
15/05/2008
4
333
cccc
29/04/2008
07/07/2008
1
444
dddd
01/05/2008
02/05/2008
1
444
dddd
09/05/2008
19/08/2008
1
555
EEE
09/07/2008
09/08/2008
4
666
fff
10/09/2008
12/09/2008
1
this is my second table to insert into !
i need to insert to another table like this
single row for each day from start_date TO END_DATE
check each employee add row for each day
insert all employee one after one
ID fname new_date val_holiday
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1
222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3
333 ccc 03/04/2008 4
333 ccc 04/04/2008 4
......................................................add row for each day
...............................
333 ccc 15/05/2008 4
TNX for help
View 6 Replies
View Related
May 31, 2006
Hi friends,
I am trying to add records from one table into another.
SQL
use riqdb2
INSERT INTO tblItemGrades
(chrvItemGradeStaticComment,decItemGrade)
SELECT chrvItem2StaticComment,decItem2Grade
FROM tblItems2
GO
Error msg
/*------------------------
use riqdb2
INSERT tblItemGrades
(chrvItemGradeStaticComment,decItemGrade)
SELECT chrvItem2StaticComment,decItem2Grade
FROM tblItems2
GO
------------------------*/
Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'ItemGradesID', table 'RIQDB2.dbo.tblItemGrades'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Please advise how to insert new records - Is it because I havent set up Identity (1,1)????
Thanks
Cm
View 9 Replies
View Related
Apr 26, 2007
Hi all!
I'm runing an access application that has an sql server 2000 as its back end. Up to few weeks ago everything was fine, but now when a user tries to add a record from an access form into the database I get an error that it cannot insert a null value into the field.
The two fields that cause the problem do not allow nulls as they keep the user name and the time that the record was inserted into the table.
I trace the access program but I cannot pinpoint where the valuees are inserted into the table. All the other fields are bound on the form. When the problem first appeard I found out that at that same day the active directory server that keeps the logins to the network was restarded. The problem appears on the live database that the users use and as well as on my test sql server that is not connected to the main database server but is instaled on my pc together with the access application.
But I still have to login onto the same network to use my pc that is part of the same domain
The authentication on the sql server 2000 is windows only.
Any solutions or directions for this proplems?
Thanks for any help
George
View 3 Replies
View Related
Apr 10, 2008
We are just upgrading a server from 2000 to 2005 and we are getting the message below when we execute a sql statement against a table with a date field with null values:
"Error converting data type DBTYPE_DBTIMESTAMP to datetime."
View 3 Replies
View Related
May 16, 2006
Hi ,
I have 2 tables (Dept and Emp)
The columns in table Dept are Deptno and Deptname. Deptno is bigint and it is primary key. In Emp table, columns are Empno(PK) ,EmpName and Deptno(foreign key referring to Dept)
To Insert or Update record in Emp through application, value of Deptno is coming as 0(Zero). I want the value of Deptno to be inserted or updated as null if the value is Zero (0). How to do this in sql server 2005 by using trigger on table Emp
Thanks in advance
regards,
Srinivas Govada
View 6 Replies
View Related
Jul 20, 2007
Here is the scenario that I cannot resolve
CREATE TABLE [dbo].[tEvents](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[EventName] [varchar](1000) NOT NULL,
CONSTRAINT [PK_tEvent] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[tEventSelections](
[EventSelectionID] [int] IDENTITY(1,1) NOT NULL,
[EventID] [int] NOT NULL,
[StatusPID] [int] NOT NULL,
CONSTRAINT [PK_tEventSelections] PRIMARY KEY CLUSTERED
(
[EventSelectionID] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
then try this
SELECT e.eventName, es.statuspid
INTO #tmpTable
FROM tEventSelections ES
INNER JOIN tEvents E
ON E.EVentID = ES.EventID
INSERT INTO #tmpTable (eventName) values ('Another One')
DROP TABLE #tmpTable
this causes a null insert issue
(0 row(s) affected)
Msg 515, Level 16, State 2, Line 7
Cannot insert the value NULL into column 'statuspid', table 'tempdb.dbo.#tmpTable___________________________________________________________________________________________________________000000000130'; column does not allow nulls. INSERT fails.
The statement has been terminated.
So how do I allow the null, as the not null is coming from the ES table. But I want to allow the insert to happen without having to create the table first, this code works in SQL 2000 but fails in 2005, inserting all fileds into the insert also has it's own issues as some of the fields are delibertly left blank so in some circumstances the data returned to a grid displays correctly.
This method has been used in quite a lot of stored procedures and will be a nightmare to correct if each has to be edited.
One example of the use of is to return a dataset and then add a row at the bottom which is a sum of all the rows.
Regards
View 20 Replies
View Related
Aug 8, 2007
I hope I can explain this!!
I am trying to insert data from one table into another:
from table "sheet1" into table "actoremployments". sheet1 is a temp table that had data imported into it from an Excel doc.
Here is what I used
INSERT ACTOREMPLOYMENTS
(EmployerCity, EmployerZip, EmployerFax, EmployerPhone, EmployerEmail, EmployerBlockNbr, StateID, EmployerStreet)
SELECT EmployerCity, EmployerZip, EmployerFax, EmployerPhone, EmployerEmail, EmployerBlockNbr, StateID, EmployerStreet
FROM Sheet1
WHERE (BarNum = ACTORS.BarNum)
ACTOREMPLOYMENTS has a column named ActorID (FK) and key links to a table.column called ACTORS.ActorID. I need to insert the data from sheet1 into actoremployments where the BarNum is linked to ActorID. Sheet1 table also has a column called BarNum.
Does this make sense? How can I make this work?
View 5 Replies
View Related
Mar 31, 2004
Is there a way to insert data into two tables with one statement in my SPROC? Something like: Insert into ThisTable,ThatTable (my columns) values (my values). I don't want to have to write two statements if I can do it with one.
View 2 Replies
View Related
Nov 1, 2015
I've 2 tables as follow, --> Full script and data as attachment, Scripts.zip
CREATE TABLE [dbo].[myMenuCollection](
[menuCollection_idx] [int] NOT NULL,
[parentID] [int] NULL,
[code]....
You can see - User select 3 Menu, which is the Menu Id is 1, 4, 10.If the Parent Id for Menu is 0, there is 1 record only to insert. If the Parent Id for Menu != 0, we've to make sure the Insert statement will insert the Parent Menu automatically
Based on Photo Above, there's 3 Menu is selected. But, in back-end - Insert statement will insert 4 record. Please see Menu Id = 10. The Parent Id = 9. So, we need to insert Menu Id = 9 automatically into myInsertedMenu table
View 4 Replies
View Related
Jul 15, 2015
I have a flat file with the following columns
SampleID Rep_Number Product Protein Fat Solids
In the flat file SampleID and Product are populated in the first row only, rest of the rows only have values for Rep_Number, Protein, Fat, Solids.
SampleID and Product are blank for the rest of the rows. So my task is to fill those blank rows with the first row that has the sampleID and Product and load into the table.
View 7 Replies
View Related
Jan 11, 2008
Hi,A conflict error occured when I insert data using Formview.The INSERT statement conflicted with the FOREIGN KEY constraint "FK_personal_personal_category". The conflict occurred in database "E:INETPUBWWWROOTWEBSITEAPP_DATADATABASE.MDF", table "dbo.personal_category", column 'cat_id'.The statement has been terminated. ----------------------------------------------------Personal TablepID (primary)pNamecat_id (foreign key from personal_category, which has a relationship with it's primary key cat_id)pID | pName | cat_id1 | Scott | 112 | John | 122 | Mick | 13-----------------------------------------------------Personal_Category Tablecat_id (primary)cat_Descriptioncat_id | cat_description11 | Dean12 | Student13 | Professor-----------------------------------------------------I'm inserting a new data on Personal Table while choosing a category usinga DropdownList which is binded to another DataSource (LinqDataSource2).... <asp:FormView ID="FormView1" runat="server" DataKeyNames="tut_id" DataSourceID="LinqDataSource1" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" DefaultMode="Insert" ForeColor="Black" GridLines="Vertical"> <FooterStyle BackColor="#CCCC99" /> <RowStyle BackColor="#F7F7DE" /> <InsertItemTemplate> <asp:TextBox ID="pName" runat="server" Text='<%# Bind("pName") %>' /> <br /> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="LinqDataSource2" DataTextField="cat_desc" DataValueField="cat_id" DataSource='<%# Bind("tut_cat") %>' > </asp:DropDownList> </InsertItemTemplate> <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" /> <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" /> </asp:FormView> <asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="PersonalsDataContext" TableName="Personal" EnableInsert = "true"> </asp:LinqDataSource> <asp:LinqDataSource ID="LinqDataSource2" runat="server" ContextTypeName="PersonalCategoriesDataContext" TableName="Personal_categories" EnableInsert="false" > </asp:LinqDataSource>... Hope you could help.cheers,imperialx
View 2 Replies
View Related
Nov 3, 2006
hi.
i cant get this quite right.
i have a table and i need to insert one column with data from another table. it goes something like this (although i know this is wrong, just here for a visual explaination) :
Code:
INSERT INTO List
(list_date, email_address, list_status, list_email)
values
(
GetDate()
, 'name@rice.edu'
, 0
, SELECT emailAddress FROM Users WHERE id = '72'
)
so, list_email needs the email address from the Users
table. i tried messing around with inner joins but, well,
here i am...
thanks in advace.
View 5 Replies
View Related
Jan 17, 2006
Hi guys,
anyone can help me?
i using sp to select a select statement from a join table. due to the requirement, i need to group the data into monthly/weekly basic.
so i already collect the data for the month and use the case to make a new compute column in the selete statement call weekGroup. this is just a string showing "week 1", "week 2" .... "week 5".
so now i want to group the weekgroup and disply the average mark. so i need to insert all the record from the select statement into the temporary table and then use 2nd select statement to collect the new data in 5 record only. may i know how to make this posible?
regards
terence chua
View 4 Replies
View Related
Apr 20, 2015
I am having 2 tables one is staging temp and another is main import table.
In my staging table there are 3 column Col001,Id,Loaddate
in Col001 column data are present with '¯' delemeter.
I am having function which is used to load data from staging to import table using one function.
this function create a insert statement.
My Existing function
-- Description: To Split a Delimited field by a Delimiter
ALTER FUNCTION [dbo].[ufn_SplitFieldByDelimiter]
(
@fieldname varchar(max)
,@delimiter varchar(max)
,@delimiter_count int
[Code] ....
I am unable to get correct statement with above function.
View 1 Replies
View Related
Sep 18, 2015
I have two tables for insertion in one transaction scope. Table one have 10 rows. After first table insert statement (not yet committed) if I run select on first table from other session, it holds table until my insert is committed or rolled back and from (SSMS), it display 10 rows and then wait for transaction scope till finished. My question is do I need to use no lock hint in this situation. Or there is something wrong with isolation level. One saying that in this situation table should not hols select while insert is in transaction scope.
View 5 Replies
View Related
Feb 2, 2015
I have to add a step into a SQL job agent which needs to be a insert statement to a table from another table. The insert statement is not the issue as you can see below. The purpose of this insert statement is to update table dbo.INV_Items with any new item numbers added to dbo.PartInfo table. I guess my question is is it just a insert statement or do i require a special function.
insert into [dbo].[INV_Items](ITEMNMBR)
select [Item] as ITEMNMBR
FROM [dbo].[PartInfo]
View 3 Replies
View Related
Aug 1, 2006
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])
View 8 Replies
View Related
Oct 10, 2007
Hello,
This seems like such a simple problem but I am new developer even through I have been on the administration end of things for some time. I will go into more detail about my tables and there relationships below. Anyway, I am trying to create a many-to-many relationship within ms sql server 2005. I have created both of my primary tables and also a junction table per the directions on microsoft's website all per ms's instructions as stated here...
http://msdn2.microsoft.com/en-us/library/ms178043.aspx
At then end of these instruction it states as a NOTE: The creation of a junction table in a database diagram does not insert data from the related tables into the junction table. For information about inserting data into a table, see How to: Create Insert Results Queries (Visual Database Tools).
http://msdn2.microsoft.com/en-us/library/ms189098.aspx
and these directions do not go into detail on how to do an insert on a junction table. And I cant find out how to do this anywhere on the internet... I did create a T-SQL INSERT statement in a trigger as listed below but I end up getting an error AS LISTED BELOW....
Here is how I set everything up...
PetitionSet table consists of:
PetitionSetID int auto-increment primary key
PetitionSetName varchar(50) no nulls
PetitionSetScope varchar(50) no nulls
the Petition table consists of:
PetitionID int auto-increment primary key
PetitionSetID int no nulls
PetitionName varchar(50) no nulls
the SetToPetitionJunction table consists of:
PetitionSetID int
PetitionID int
And, there is a composite key made up of both the PetitionSetID and PetitionID fields.
I have created the foreign key relationships with DEFAULT VALUES from the SetToPetitionJunction table to each column's respective corresponding column in each of the tables: PetitionSet and Petition.
The trigger is on the Petition table and it has the following code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
ALTER TRIGGER .[SetToPetitionJunctionTrigger]
ON .[dbo].[Petition]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO SetToPetitionJunction
(PetitionID, PetitionSetID)
SELECT Petition.PetitionID, PetitionSet.PetitionSetID
FROM Petition INNER JOIN
PetitionSet ON Petition.PetitionSetID = PetitionSet.PetitionSetID
END
I have created an asp.net 2.0 front end to insert values into the PetitionSet table and the Petition Table. And in the detailsview for the Petition table I manually insert the PetitionSetID field to the number that corresponds to an auto-generated number on the primary key of the PetitionSet table. So I am maintaining referential integrity...
The first time it works and inserts one record in the Junction table containing the PetitionSetID from the PetitionSet table and the PetitionID from the petition table.
Then when I try to add in another petition for the same petition set number just like I did the first time and then I get this error...
Violation of PRIMARY KEY constraint 'PK_SetToPetitionJunction'. Cannot insert duplicate key in object 'dbo.SetToPetitionJunction'.
The statement has been terminated.
David
All Rights Reserved in All Media
View 3 Replies
View Related
Jul 9, 2014
I understand that minimal logging can occur on a non clustered indexed heap as long as [URL] ...
*not replicated
*tablock is used
*table is empty
The following test seems to contradict this
In the test I create a non indexed heap, insert some record and check the log, then repeat the test on an indexed heap.
The results suggest that even though the conditions for minimal logging into a indexed heap are met, minimal logging is not happening although it does happen on an non indexed heap. What am I doing wrong?
CREATE DATABASE logtest
GO
USE logtest
GO
CREATE TABLE test (field varchar(100))
GO
CHECKPOINT
[Code] ....
View 2 Replies
View Related
Apr 9, 2015
Sometimes I want to quickly to edit a record in a table instead of using an insert statement.
Sometimes there are auditing columns like DateCreated, and CreatedBy,
I know it can be made as default. for DateCreated to be sysdatetime, and createdby to be system user.
But I just curious to know if there is a way to manually enter today's date and the user in the cell?
View 9 Replies
View Related
Apr 2, 2007
I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." -- But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.
Server Error in '/' Application.
Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.Source Error:
Line 89: sContact.Phone = sPhone.Text.Trim
Line 90: sContact.Email = sEmail.Text.Trim
Line 91: sContact.Save()
Line 92:
Line 93: Dim bContact As Contact = New Contact()Source File: F:InetpubwwwrootOutman KnifeCheckout.aspx.vb Line: 91 Stack Trace:
[SqlException (0x80131904): Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +214
System.Data.SqlClient.SqlDataReader.Read() +9
System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) +39
System.Data.SqlClient.SqlCommand.ExecuteScalar() +148
SubSonic.SqlDataProvider.ExecuteScalar(QueryCommand qry) +209
SubSonic.DataService.ExecuteScalar(QueryCommand cmd) +37
SubSonic.ActiveRecord`1.Save(String userName) +120
SubSonic.ActiveRecord`1.Save() +31
Checkout.btnCheckout_Click(Object sender, EventArgs e) in F:InetpubwwwrootOutman KnifeCheckout.aspx.vb:91
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
View 8 Replies
View Related
Feb 12, 2014
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'
How can I do this with tSQL using a single select statement?
View 6 Replies
View Related
Sep 30, 2015
I would like to INSERT an array of integer into a table in MSSQL Server, then count the number of rows in the table with c++ using ODBC. Here you find my code to do this task:
#include <windows.h>
#include <stdlib.h>
#include <stdio.h>
#include<tchar.h>
#include <sql.h>
#include <sqlext.h>
#include<sqltypes.h>
[Code] ....
In my code, I would like to Insert the array and then count the number of rows:
SQLTCHAR Statement[] = _T("INSERT INTO information1(Wert1,Wert2) VALUES(?,?) select count(*) as a from information1 ") ;
Problem : My expectation is, that first 9 rows are inserted into table then comes 9 as result to user (if the table is empty) but this code returns me 1 if first the table is empty. If the table is not empty, it returns 1+number of existing rows in the table. If I take a look inside the table, the 9 rows are successfully inserted in it. Only the number of rows in the table is wrong.
Hint : If I monitor the database using SQL Profiler. It looks like this:
Why this statement doesn't work correctly?
View 7 Replies
View Related
Feb 20, 2008
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
View 8 Replies
View Related
