SQL Server 2014 :: Table Locking While Insert Statement
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
ADVERTISEMENT
Jun 11, 1999
Does anyone know how to stop SQL server from applying exclusive table locks on mass Insert statements. I need to allow concurrent inserts into the same table using INSERT ... SELECT * FROM ...
SQL Server applies exclusive table locks that prevent this. I've tried setting LE as high as it goes but with no success. Microsoft suggest using single row inserts but this will be far too slow!
Any help would be much appreciated.
Jim Plant
View 1 Replies
View Related
Jun 11, 1999
Does anyone know how to stop SQL server from applying exclusive table locks on mass Insert statements. I need to allow concurrent inserts into the same table using INSERT ... SELECT * FROM ...
SQL Server applies exclusive table locks that prevent this. I've tried setting LE as high as it goes but with no success. Microsoft suggest using single row inserts but this will be far too slow!
Any help would be much appreciated.
Jim Plant
View 1 Replies
View Related
Sep 10, 2014
There are 2 tables which need to have data inserted into them for auditing purposes. The number of inserts per minute seems be at least 50-100. How to reduce locks during inserts.
There are 2 tables
Table1
ID - Surrogate Key/identity Column
SomeColumn1
SomeColumn2
SomeColumn3
SomeColumn4_timestamp
clustered index on ID column
Table2
ID Column ..... there's a call to get id from SCOPE_IDENTITY()
SomeColumn1
SomeColumn2
clustered index on ID column
NC idx on SomeColum1
NC idx on SomeColum2
A Sproc has the following code:
I changed the names to protect the innocent
CREATE PROCEDURE [dbo].[logging_sp]
@Audit BIGINT,
@varT1_1 NVARCHAR (50),
@varT1_2 NVARCHAR (64),
@varT1_3 INT,
@VarTime DATETIME,
@varT2_1 NVARCHAR (50),
@varT2_2 NVARCHAR (1024),
[code]....
View 5 Replies
View Related
Jun 10, 2015
Here is my table:
My question is: How can I insert a row for each unique TemplateId. So let's say I have templateIds like, 2,5,6,7... For each unique templateId, how can I insert one more row?
View 0 Replies
View Related
Jul 29, 2014
I need to load the following data into a SQL table. This is how the vendor is able to provide it to us.
CRCorp Daily Report,,,,,,
,,,,,,
Facility,Location,Purchase Order #,Vendor,Inventory #,Date Ordered,Extended Cost
09-Mowtown 495 CRST,09-402A Women's Imaging,327937,"BARD PERIPHERAL VASCULAR, INC.",113989,7/25/2014,650
09-Mowtown 495 CRST,09-402A Women's Imaging,327936,"WB MASON CO., INC.",112664,7/25/2014,8.64
01-Mowtown 499 CRST,01-302B Oncology,327947,McKesson General Medical,n/a,7/25/2014,129.02
[Code] ....
I have attempted to bulk insert it into this table with no luck.
CREATE TABLE POMaster
(Facility VARCHAR(75),
Location VARCHAR(75),
PONum INT,
VendorNm INT,
INVENTORYNUM VARCHAR(25),
orderDte DATE,
ExtendedPrice NUMERIC(10,2)
)
GO
It does not like the double quotes. How to make this format work? Do I need a format file?
View 2 Replies
View Related
Jul 29, 2014
I am doing a performance testing for In-memory option is sql server 2014. As a part I want to insert 500 million rows of records into a in-memory enabled test table I have created.
I need a sample script to insert 500 million records into a table ....
View 9 Replies
View Related
Jul 31, 2014
I am trying to add multiple records to my table (insert/select).
INSERT INTO Users
( User_id ,
Name
)
SELECT ( SELECT MAX(User_id) + 1
FROM Users
) ,
Name
But I get the error:
Violation of PRIMARY KEY constraint 'PK_Users'. Cannot insert duplicate key in object 'dbo.Users'.
But I am using the max User_id + 1, so it can't be duplicate
This would insert about 20 records.
Why the error?
View 7 Replies
View Related
Feb 4, 2015
I have a master table with after insert trigger on it.. When record is inserted into master table, the trigger fires and is captured in the backoffice table. In case the trigger fails, my record is neither in the master table nor in the back office table..
Is there anyway to capture the record either in the master table or in a separate table.
View 6 Replies
View Related
Apr 4, 2007
Hi,
I want to insert a record in a table having an identity column as primary key. I want to lock the table while inserting. so that no one should be able to insert, select, update, delete from the table. and once my insert is over, then will release the lock.
Can I have the code for the same. M using SQL SERVER 2005.
Thanks,
Rahul Jha
View 8 Replies
View Related
Sep 24, 2007
Hi guys,
I have a question regarding a locking scheme in MSSQL I hope you guys can help. In Sybase, I am able to specify datarow locking in DDL (ex. create table, alter table). Can I do the same in MSSQL or is there an equivalent option in CREATE TABLE statement in MSSQL? I came across a few articles in MSDN about datarow locking and it seems to me that MSSQL only allows locking through DML... Is that true? Thanks.
View 2 Replies
View Related
May 4, 2015
I have a main table called Stock for example.
One process ( a service ) inserts data into this table , as well as updates certain fields of the table periodically.
Another process ( SQL Job ) updates the table with certain defaults and rules that are unknown to the service - to deal with some calculations and removal of null values where we can estimate the values etc.
These 2 processes have started to deadlock each other horribly.
The SQL Job calls one stored procedure that has around 10 statements in it. This stored proc runs every minute. Most of them are of the form below - the idea being that once this has corrected the data - the update will not affect these rows again. I guess there are read locks on the selecting part of this query - but usually it updates 0 rows - so I am wondering if there are still locks taken ?
UPDATE s
SET equivQty = Qty * ISNULL(p.Factor,4.5) / 4.5
FROM Stock s
LEFT OUTER JOIN Pack p
on s.Product = p.ProductId
AND s.Pack = p.PackId
WHERE ISNULL(equivQty,0) <> Qty * ISNULL(p.Factor,4.5) / 4.5
The deadlocks are always between these statements from the stored procedure - and the service updating rows. I can't really see how the deadlocks occur but they do.
Another suggestion has been to try and use an exists before the update as below
IF EXISTS( SELECT based on above criteria )
( UPDATE as before )
Does this reduce the locking at all ? I don't know how to test the theory - i added this code to some of the statements, and it didn't seem to make much difference ?
Is there a way to make a process ( in my case the stored procedure ) - give up if it can't aquire the locks rather than being deadlocked - which leads to job failures and emails etc ?
We are currently trying to filter down the data that is updated to be only the last few months - to reduce the amount of rows even analyzed - as the deadlocking does seem to be impacted by the number of rows in the tables.
View 9 Replies
View Related
Jun 22, 2015
I have a need to insert stored procedure output a table and in addition to that add a datetimestamp column.. For example, Below is the process to get sp_who output into Table_Test table. But I want to add one additional column in Table_test table with datetimestamp when the procedure was executed.
insert into Table_Test execute sp_who
View 2 Replies
View Related
Apr 2, 2015
I have a production DB that all of a sudden it seems that any and every insert causes massive locks/blocks.
If I kill the offending spid anther spids pops up with the block/lock.!
View 7 Replies
View Related
Apr 21, 2015
I have a situation where I have Table A, Table B.
View C is created by joining table A and table B.
I have written a instead of trigger D on view C.
I do not insert/update/delete on the view directly.
For every insert/update in table A /B the values should get insert/update in the view respectively. This insert/update on view should invoke the trigger.
And I am unable to see this trigger work on the view if any insert/update occurs on base table level.
Trigger is working only if any operation is done directly on the view.
View 2 Replies
View Related
Feb 8, 2008
Hi,
I have implemented Remus Resanu's implementation from the Recycling Conversations article and I am experiencing locking issue when I try to insert new conversation handles to the SessionConversations table. I have copied the code in the article exactly including the activation procedure. Any ideas why I may be locking. I am thinking it is related to the HOLDLOCK hint on the table.
The sepcific line where I see locking is directly from the article:
INSERT INTO [SessionConversations] (SPID, FromService, ToService, OnContract, Handle) VALUES (...etc)
Thanks
View 6 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
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
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
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
Apr 3, 2015
I am unable to load data from flat file to sql table using bulk insert sql statement
My code:-
DECLARE @filePath VARCHAR(200)
DECLARE @sql VARCHAR(8000)
Declare @filename varchar(100)
set @filename='CCNVZ_150401054418'
SET @filePath = 'I:IncomingFiles'+@FileName+'.txt'
[Code] .....
View 1 Replies
View Related
Sep 30, 2015
I have a table :
Person(Id bigint , FirstName nvarchar(50) , LastName nvarchar(50),Address nvarchar(100))
and I have a sp for search data in "Person" table:
CREATE PROCEDURE SelectPerson
@First nvarchar(50) = NULL,
@Last nvarchar(50) = NULL,
@Address nvarchar(100) = NULL
[Code] ....
And there exists 500 record in Person.
I execute SelectPerson by this code:
"
DECLARE@return_value int
EXEC@return_value = [dbo].[SelectPerson ]
@First = 'ام',
@Last = NULL,
@Address= NULL
SELECT'Return Value' = @return_value
GO
"
but result don't has any rows.why?
View 9 Replies
View Related
Jun 11, 2014
I want to pivot a table something like this .I pivoted it successfully but the results are not correct.
Here is the example :
install-Name Fiscal year Question Answer
Washington 2010 what is the reason for install? tttttt ggg yttt o
washington 2010 reason id 12345
washington 2010 install start date 10/10/2010
washington 2010 install end date 10/12/2010
washington 2010 install status successfull
[code]....
I want the above data to get pivoted like this
Install-name | Fiscal year |what is the reason for install? | reason id | install start date | install end date |
install status |Do you feel the install is incomplete? | Is the expiration of 90 days exceeded? |
washington | 2010 | tttttt ggg yttt o | 12345 | 10/10/2010 | 10/12/2010 |
successful | | |
washington | 2010 | trtbnbthwgt hrgthjrt | - | 12/3/2010 | 12/8/2010 |
| | |
washington | 2011 | sbjeh dhebwdh dbjw | 345 | 10/10/2010 | 10/12/2010 |
successful | No | Yes ,but b b b b |
View 2 Replies
View Related
Sep 9, 2014
I have TSQL query using UNION ALL. First subset returns 92 rows, second subset return 0 (ZERO) rows. Now if I execute them separately they execute subsecond. If I put UNION ALL between them then it hangs forever exactly on 88s row.
I understand I need to provide execution plans, actual queries etc but before going into all this details, why on earth even in theory UNION ALL with empty result will result in a hang of the first subset?
Based on estimated execution plan 2 subqueries are performed in parallel and then CONCATENTION is being used which shall result with just first subset immediately displayed in result.
Estimated execution plan attached, I can not get actual execution plan because query never completes.
View 9 Replies
View Related
Sep 15, 2015
The below code returns a NULL for all the Differential backups. I am trying to return the word Differential when there is a NULL because I can tell from when the NULLs occur that those are my differential backups. How can I get the result I want?
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE
WHEN msdb..backupset.type = 'D' THEN 'Database'
[Code] .....
View 9 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
Feb 13, 2014
We have a table with 2 columns 'OrderNo' and 'Amount' as below
ORDERNO | AMOUNT
1D1ZX000 | 9262.5
1D1ZX001 | 9000.0
1D1ZX001 | 9000.0
1D1ZX002 | 10000
1D1ZX003 | 1000
1D1ZX003 | 200.50
1D1ZX003 | 100.50
1D1ZX004 | 500.0
1D1ZX004 | 1000
1D1ZX004 | 2000
1D1ZX004 | 1000
as per my client requirement we need subtotal of 'Amount' group by 'OrderNo'. column so am writing a select statement with WHERE condition and I would like to have another column called SUBTOTAL in the result set (select statement result) with subtotals for that order Number as below
ORDERNO | AMOUNT | SubTotal
1D1ZX000 | 9262.5 | 9262.5
1D1ZX001 | 9000.0 | 18000
1D1ZX001 | 9000.0 | 18000
1D1ZX002 | 10000 | 10000
1D1ZX003 | 1000.0 | 3001
1D1ZX003 | 2000.5 | 3001
1D1ZX003 | 1000.5 | 3001
1D1ZX004 | 500.00 | 4500
1D1ZX004 | 1000.0 | 4500
1D1ZX004 | 2000.0 | 4500
1D1ZX004 | 1000.0 | 4500
View 7 Replies
View Related
Mar 23, 2014
We are facing a problem with loading data from .pdf files from vendor..pdf files have data in tabular format and we would like to insert those fields into a SQL table.We do not want to insert the physical location of the file but, we need to insert the data within the file.How can we read a pdf file?
View 6 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
Feb 24, 2015
My task is to convert jpeg's to binary and then insert them into a table called "images". I need to convert/insert all jpeg files in a directory. I'm able to accomplish the task if the files are numbered. The query below works by retrieving one file at a time based on the value of @i. However, I also have directories where the files are not numbered but have ordinary text names like "Red_Sofa.jpg". I need to iterate through these directories as well and convert/insert the jpeg's. I'm running SSMS 2014 Express on 4.0 and Windows 7.
DROP TABLE images
CREATE TABLE images
(
image_name varchar(500) null
,image_data varbinary(max) null
[Code] ....
View 2 Replies
View Related
Jul 22, 2015
I'm testing, with SQL 2014 on the same DB, a procedure that extracts data from a table into a file and Loads data from that file into a different table which has the same columns as the initial table (I use a function to create the create table statement from the source table and change the name of the destination table)
when doing my bcp -c the record with the special character "é" doesn't make it to the file.
when doing a bcp -w the record with the special character makes it to the file but the bulk insert omits the whole record.
The result file in the case that makes it to the file is unicode. I'm not using a format file (don't see the need for it)
The bulk insert into the destination table that contains identical columns as the source (a mixture on int, varchar, char) --> didn't work
I also tried by building the columns of the destination table with nvarchars -- Still doesn't work.
I tried the bulk insert with:
codepage = 'ACP, 'RAW' --> didn't change anything. still didn't work.
It's a complicated process that takes 1 XML record that contains information + the Create Table Statement (to eventually be able to this on a different server/DB) + the title Row for each column + the Data... Each of these are created with a BCP command (all with the same options). they are then appended to each other with a copy /B c:file1.txt + c:File2.txt + c:File3.txt + c:File4.csv c:ResultFile
Once the result file is created I bulk insert the 2 first rows in one table "TableA"
create the tmp table "TABLE B" with the create table statement that is in "TableA"
and do another bulk insert of the remainder of the file into the newly created table.
What else can I try? Should I be creating a format file? what are the benefits of a format file?
It's a very long procedure that does both Extract and Load (with 12 parameters) not sure what I should put here.
View 6 Replies
View Related
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
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
