Strange Problem: SQL Insert Statement Does Not Insert All The Fields Into Table From Asp.net C# Webpage
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..
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
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?
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 ----------------------------------------------------
Hello I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake... Here's the sql management studio diagram :
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Question_SurveyTemplate". The conflict occurred in database "ankietyzacja", table "dbo.SurveyTemplate", column 'id'. The statement has been terminated. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ? Thanks a lot.
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them 1) Create the table 2) Create the trigger 3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value 4) Drop the trigger 5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
GO /************************************************ 2) - Create the trigger ************************************************/ CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table] INSTEAD OF INSERT AS BEGIN
INSERT INTO my_table ( forename, surname) SELECT forename, surname FROM inserted
END
/************************************************ 3) - Do the insert ************************************************/
INSERT INTO my_table ( forename , surname ) OUTPUT inserted.my_table_id INTO @my_insert VALUES( @forename , @surname )
select @@identity -- expect this value in @my_insert table select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************ 4) - Drop the trigger ************************************************/
drop trigger [dbo].[trig_my_table__instead_insert] go
/************************************************ 5) - Re-run insert from 3) ************************************************/ -- @my_insert now contains row expected with identity of inserted row -- i.e. OK
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.)
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.
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.
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
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...
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?
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.
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.
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:
/* 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])
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...
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).
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.
I'm quite stuck with this:I have an import table called ReferenceMatchingImport which containsdata that has been sucked from a data submission. The contents ofthis table have to be imported into another table ExternalReferencewhich has various foreign keys.This is simple but one of these keys says that the value inExternalReference.CompanyRef must be in the CompanyReference table.Of course if this is an initial import then it will not be so as partof my script I must insert a new row into CompanyReference andpopulate ExternalReference.CompanyRef with the identity column of thistable.I thought a good idea would be to use an SP which inserts a new rowand returns @@Identity as the value to insert. However this doesn'twork as far as I can tell. Is there a approved way to perform thissort of opperation? My code is below.Thanks.ALTER PROCEDURE SP00ReferenceMatchingImportAS/*Just some integrity checking going on here*/INSERT ExternalReference(ExternalSourceRef,AssetGroupRef,CompanyUnitRef,EntityTypeCode,CompanyRef, --this is the unknown ref which is returned by the spExternalReferenceTypeCode,ExternalReferenceCompanyReferenceMapTypeCode,StartDate,EndDate,LastUpdateBy,LastUpdateDate)SELECT rmi.ExternalDataSourcePropertyRef,rmi.AssetGroup,rmi.CompanyUnit,rmi.EntityType,SP01InsertIPDReference rmi.EntityType, --here I'm trying to run thesp so that I can use the return value as the insert value1,1,GETDATE(),GETDATE(),'RefMatch',GETDATE()FROM ReferenceMatchingImport rmiWHERE rmi.ExternalDataSourcePropertyRef NOT IN (SELECT ExternalSourceRefFROM ExternalReference)
SET DATEFORMAT dmy; INSERT INTO tblAddress (fkCityId, strAddressFull) SELECT pkCityId,'street_209' FROM tblCity WHERE strCityName = 'Test-City';
INSERT INTO tblCustomer (fkCustomerLanguageId, fkCustomerGenderId, strCustomerName, strCustomerFirstname, dtCustomerBirthDate) SELECT pkLanguageId, pkCustomerGenderId,'test','user','8/03/2007 9:25:17' FROM tblCustomerGender, tblCustomerLanguage WHERE tblCustomerGender.strCustomerGenderName ='Male' and tblCustomerLanguage.strLanguageAbbrev ='Dutch';
INSERT INTO tblCustomerAddress(fkCustomerId, fkAddressId) SELECT c.pkCustomerId, a.pkAddressId FROM tblCustomer AS c, tblAddress AS a WHERE c.strCustomerName = 'test' and c.strCustomerFirstname = 'user' and c.dtCustomerBirthDate = '8/03/2007 9:25:17' and a.strAddressFull ='street_209';
INSERT INTO tblAddressCategorie(fkCategorieAddressId, fkAddressId) SELECT c.pkCategorieAddressId, a.pkAddressId FROM tblAddress AS a, tblCategorieAddress AS c WHERE c.strCategorieName = 'Invoice' and a.strAddressFull = 'street_209';
INSERT INTO tblAddressCategorie(fkCategorieAddressId, fkAddressId) SELECT c.pkCategorieAddressId, a.pkAddressId FROM tblAddress AS a, tblCategorieAddress AS c WHERE c.strCategorieName = 'Privat' and a.strAddressFull = 'street_209';
MESSAGE:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(0 row(s) affected)
QUESTION: last insert query is not done... why, because he is the same as the query before (only the data is different)???
if i only insert 1 thing in tblAddressCategorie, then he insert it correctly if i insert 2 things he only insert the first if i insert 3 things he only insert the first 2 if i insert 4 things he only insert the first 3 if i ...
DATABASE DIAGRAM: database diagram that is involved with this problem: see picture
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
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset. I have teh following code but it works fien when column names are given ahead of time. If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match. Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition. here is my code... SET @sql1='select * from table1'SET @sql2='select * from table2' IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn -- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
On my site users can register using ASP Membership Create user Wizard control. I am also using the wizard control to design a simple question and answer form that logged in users have access to. it has 2 questions including a text box for Q1 and dropdown list for Q2. I have a table in my database called "Players" which has 3 Columns UserId Primary Key of type Unique Identifyer PlayerName Type String PlayerGenre Type Sting
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table. I am having problems getting this to work and keep getting exceptions. Be very helpful if somebody could check the code and advise where the problem is??
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e) { SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1"); MembershipUser myUser = Membership.GetUser(this.User.Identity.Name); Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue; DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString()); DataSource.Insert();
"INSERT INTO tblEquip(buydate,Country,feature,pc,Provider,Serial,Status,Warranty,Year,Typeid ) VALUES ('12/12/2008','Viet Nam','Supper Power ',0,'IBM','ABCDEF','Out of warranty','12/12/2008',1965,5);"
I use Visual 2005 IDE and code in C# , with an MS Access 2003 Databse.
Above SQL command is gotten from Debug.
It works well when i paste into a query in MS Access 2003.
But in my project, it return an error : Syntax error in INSERT INTO statement when I use a try catch statement.
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.
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:
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:
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]
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process: 1. get data from an existing view and insert in temptable 2. truncate/delete contents of table1 3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted) 4. insert data in table2 which are not yet present (comparing ID in t2 and temptable) 5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
