Transact SQL :: Delete A Default Constraint On A Column?
May 5, 2015
 I have a table named [New Item] and created a default constraint on a column, and i wanted to change the data type of the column using the query
alter table [new item]
alter column pcs_qty decimal(15,2) not null
but the name of the default constraint is 'DF__New Item__Pcs_Qt__2D12A970' and i am not able to delete the constraint because it contains a space in between.Is there any work around for this.
I tried to delete the constraint by using the query
alter table [new item]
drop constraint 'DF__New Item__Pcs_Qt__2D12A970'
but I am getting the exception,
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'DF__New Item__Pcs_Qt__2D12A970'.
View 2 Replies
ADVERTISEMENT
Apr 24, 2013
IF NOT EXISTS (SELECT TOP 1 1 FROM dbo.syscolumns WHERE id = OBJECT_ID(N'dbo.Employee) and name = 'DoNotCall')
BEGIN
ALTER TABLE [dbo].[Employee] ADD [DoNotCall] bit not null Constraint DoNot_Call_Default DEFAULT 0
IF ( @@ERROR <> 0 )
GOTO QuitWithRollback
END
It just takes a LOT of time in SQL Server Management studio. I have to cancel the query and cancelling takes a whole lot time. I am using SQL Server 2008.
View 4 Replies
View Related
Dec 7, 2001
I can't seem to get the syntax correct for ALTERing an existing column with a default constraint. I've been to Help and BOL. There are examples that show how to use the ALTER command to add a column with a default constraint but not how to do it on an existing column.
Any help would be appreciated.
Sidney Ives
View 1 Replies
View Related
Mar 9, 2000
I cannot figure out how to add a default constraint to an existing column. The syntax I'm using is :
ALTER TABLE table_name WITH NOCHECK
ADD CONSTRAINT column_name DEFAULT (0)
This gives me a syntax error.
The column was originally added with a default constraint of 1 to a 2.6 million row table.
I dropped the existing constraint and need to add the new default constraint of 0 for that column.
Anyone have any ideas? Thanks in advance.
View 2 Replies
View Related
Mar 16, 2004
I need to alter the datatype of a column from smallint to decimal (14,2) but the column was originally created with the following:
alter my_table
add col_1 smallintNot Null
constraint df_my_table__col_1 default 0
go
I want to keep the default constraint, but i get errors when I try to do the following to alter the datatype:
alter table my_table
alter column col_1 decimal(14,2)Not Null
go
Do I need to drop the constraint before I alter the column and then rebuild the constraint? An example would be helpful.
Thx
View 1 Replies
View Related
Oct 26, 2004
Hi,
On my aspx Web page, I want to delete a member from database table 'tblMember', but if this MemberID is used as FK in another table, I want to display a user friendlier message like "You cannot delete this member, ....." I am using Try, Catch blocks in my Web Page.
Currently it display this message:
"DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_..._....' The conflict occurred in database '...', table 'tblMembers', column 'MemberID'. The statement has been terminated. "
So how should I precisely trap this error? Does anybody know what Exception is it? or what error number in SQL server?
Thanks
View 2 Replies
View Related
May 8, 2015
I have a table with ~30M records. I'm trying to add a column to the existing table with default value and have noticed following ... When using alter with default value- (Executes more than 45 min and killed forcefully)
ex: Â
ALTER TABLE dbo.Table_X Add is_Active BIT CONSTRAINT DF_Table_X_is_Active DEFAULT 'FALSE' NOT NULL
GO
When using update command after adding column with alter (without default value) it completes is 5 min.
ex: Â
ALTER TABLE dbo.Table_X Add is_Active BIT NULL
GO
UPDATE Table_X SET is_Active = 0 WHERE is_Active IS NULL
GO
Why there is so much of difference in execution times ? I was just trying to understand internal behavior of the SQL in these two scenarios.Â
View 4 Replies
View Related
Aug 8, 2006
Hi,I see the following in Books Online: CONSTRAINT--Is an optional keywordindicating the beginning of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGNKEY, or CHECK constraint definition...But I have a table column defined as follows:[MONTH] [decimal] (2, 0) NOT NULL CONSTRAINT[DF__TBLNAME__MONTH__216361A7] DEFAULT (0)My question: Is "DEFAULT" a constraint, or is it called something else?Thanks,Eric
View 2 Replies
View Related
Jul 26, 2004
Hi,
I need list out the defaulat and constraint value in SQL2K. Where I get those values.
Thanks,
Ravi
View 3 Replies
View Related
Jan 2, 2007
A few weeks ago a client asked me to add a column to a table so Icreated this script:ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULLCONSTRAINT fldRenewalStatus_Default DEFAULT 0Now they want to change it from a BIT to an INT, to store an enum.Fair enough. However, no matter how much I wrangle with a script, Ican't find a reliable way to alter the column. I've mixed and matchedthe following and nothing seems to work:EXEC sp_unbindefault 'tblIndividual.fldRenewalStatus'DROP DEFAULT DF_tblIndividual_fldRenewalStatusALTER TABLE tblIndividualDROP CONSTRAINT fldRenewalStatus_DefaultALTER TABLE tblIndividualDROP COLUMN fldRenewalStatusGOALTER TABLE tblIndividualADD fldRenewalStatus int NOT NULLCONSTRAINT fldRenewalStatus_Default DEFAULT 0Thoughts?ThanksEdward
View 4 Replies
View Related
Apr 16, 2008
Hello,
I have a table set up called tbl_DailySummary_new. In that table I have a column called €˜RevType€™ char(2) NOT NULL Default €˜00€™. I'm trying to populate this table from another table called tbl_DailySummary. The RevType column in tbl_DailySummary allows null values (and has them). When I attempt to insert all records from tbl_dailysummary into tbl_DailySummary_new, I get the following error message:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'RevType', table 'TxnRptg.dbo.tbl_DailySummary_new'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Why dosen€™t the default constraint on the RevType column in the new table pick this up?
Thank you for your help!
cdun2
View 2 Replies
View Related
May 20, 2008
Hi ,
I want to define default constraint for a column to force it to UPPER.
USE [GLP]
GO
ALTER TABLE [dbo].[col1] ADD CONSTRAINT [DF_col1_TypeCode] DEFAULT (N'UPPER(col1)') FOR [col1]. Its defined..
But when I tried ti insert into records..It not convertedto uppecase at all...Isn't it possible this way ?
Thanks,
-V
View 3 Replies
View Related
Apr 28, 2008
Troops,
I'm a software developer trying to hack my way thru a SQL script to increase the size of a column. The column has a constraint on it that won't allow the alter unless its dropped.
Alter command:
ALTER TABLE dbo.QueueBack ALTER Column QBQueue varchar(4) NOT NULL
Message response:
Msg 5074, Level 16, State 1, Line 10
The object 'DF__QueueBack__QBQue__76818E95' is dependent on column 'QBQueue'.
Msg 4922, Level 16, State 9, Line 10
ALTER TABLE ALTER COLUMN QBQueue failed because one or more objects access this column.
So, my script drops the default constraint, alters the column size and then adds the constraint back. The problem is that the constraint does NOT appear to be the same as it was prior. I can run the Alter command on the column and it will allow me to change the size of the column... where it prohibited me from doing such before. How do I restore the default constraint so it is exactly the way it was before I dropped it?
Thx,
Stretch
---------------------------------
Script for drop, alter, add... modified slightly for simplicity...
declare @Error int
BEGIN TRAN
SELECT @Error = 0
DECLARE @defname VARCHAR(100), @cmd VARCHAR(1000)
-- this is retrieved at run time from the sysconstraints table; hard-coded here...
set @defname = 'DF__QueueBack__QBCategory'
IF @defname <> ''
BEGIN
SET @cmd = 'ALTER TABLE dbo.QueueBack DROP CONSTRAINT '+ @defname
EXEC(@cmd)
if @@ERROR = -1
BEGIN
SET @Error = -1
END
END
if @Error = 0
BEGIN
-- modify the column
ALTER TABLE dbo.QueueBack ALTER Column QBCategory varchar(7) NOT NULL
if @@ERROR = -1
BEGIN
SET @Error = -1
END
END
if @Error = 0
BEGIN
-- Add the default constraint back
SET @cmd = 'ALTER TABLE dbo.QueueBack ADD CONSTRAINT '+ @defname + ' DEFAULT ('''') FOR QBCategory'
EXEC(@cmd)
if @@ERROR = -1
BEGIN
SET @Error = -1
END
END
if @Error = 0
BEGIN
-- Commit if no error...
COMMIT TRAN
END
ELSE
BEGIN
-- Rollback if error...
ROLLBACK TRAN
END
GO
View 1 Replies
View Related
Jun 29, 2015
I need to delete records from a table (Table1) which has a foreign key column in a related table (Table2).
Table1 columns are: table1Id; Name. Table2 columns include Table2.table1Id which is the foreign key to Table1.
What is the syntax to delete records from Table1 using Table1.Name='some name' and remove any records in Table2 that have Table2.table1Id equal to Table1.table1Id?
View 11 Replies
View Related
Nov 20, 2007
Okay, maybe I'm getting ahead of myself.
Using SQL Server Express, VWD and .net 2.0 I've figured out how to drop a Table Column Constraint or Default Value/Binding and then Create it again using a stored procedure. What I can't figure out is how to retrieve that column's constraint value and write it to, say a label, in an aspx page, simply for reference. Is it possible? In this case the Data Type of the column is money.
I'm using it to perform a calculation to a column with a value that the user inserts into another column. (Column1(user input) minus Column2(with Default Value) = Column3(Difference). I just want to read Column2's Default Value for reference so I know whether to change it or not.
View 6 Replies
View Related
Oct 26, 2005
Hi guys,
I have this problem. I want to add a new primary key to a table but i want the name of the contstraint to be generated by the system. I have this TSQL code.
ALTER TABLE TableTest
ADD CONSTRAINT PRIMARY KEY (C1)
Reading the BOL, it says that if you don't supply a name for the constraint it generates one. But I get this error "Incorrect syntax near the keyword 'PRIMARY'".
If I add a name to the constraint, it works fine.
I'm using SQL Server 2000
Thanks
Darkneon
View 7 Replies
View Related
Aug 23, 2006
I have two fields CourseID and Erpid in table.
CourseID has identity property with integer datatype.
I need to add a default value for Erpid column which will show a value like 'A' + CourseID column. Erpid is Varchar column.
How can I use Convert function in default constraint?
Thanks!
View 4 Replies
View Related
Jun 12, 2007
I have a default constratint on DateColumn getdate()-1
I have used enterprise manager to update it to yesterday's date everyday.
I would like to have a SQL which can check for the date in the system
or even a trigger which checks when the date changes the constraint is updated itself. If this is not possible I would like to have a stored procedure which I will schedule to run as a job everyday once.
So if today 6/12/2006, the default value in the Datecolumn should be
6/11/2006.
This gives me a error, i tried but could not fix the bug.
Alter Table TABLE_NAME
Alter Constraint DF_DATECOLUMN
Default getdate()-1
Ashley Rhodes
View 4 Replies
View Related
Jan 3, 2005
how can i implement delete constraint? i mean i don't want the rows of the primary key table to be deleted if they are used as foreign key in some other table. so i want to check if that PK is used as foreign key in other tables before deleting.
View 10 Replies
View Related
Dec 7, 2003
Can somebody tell me about on delete constraint and where should it be used - table having foriegn key?
coz want i want is- the moment i delete the data from the table whose primary key is been referenced as foreign key , The Data in all the tables where its primary key is beeen used as forein key should be deleted.
:confused:
View 2 Replies
View Related
Sep 9, 2014
What are the tables are having the Default Constraint those table's Script the User can't generate but remaining tables He/She can generate,If i want DENY he/she to do not generate the script those are not having the DF Constraints what should i do.....
View 1 Replies
View Related
Oct 26, 2007
Are there any vices to using default constraints on all columns in your table.
For example an Int that defaults to 0
or a char or varchar that defaults to ''
I know that 0 and Null are not the same thing. But if your programs don't have the concept of NULL then you have to convert the NULL to zero.
So, DEFAULT CONSTRAINTS on every column. Is it good or Bad?
Thanks
Darin Clark
View 10 Replies
View Related
Oct 4, 2007
In SQL Server 2005, I hava a client where I do not have access to their SQL Server. I update the database structure by giving them scripts which they run. As I update the structures I occasionally need to delete a constraint, then typically recreate it later. Usually I use this type of snippet:
IF EXISTS (SELECT * FROM sys.default_constraints
WHERE object_id = OBJECT_ID(N'[dbo].[ConstraintName]')
AND parent_object_id = OBJECT_ID(N'[dbo].[tablename]'))
ALTER TABLE [dbo].[tablename] DROP CONSTRAINT [ConstraintName]
This assumes I know the constraint name. A lot of the older constraints have random type names which I assume are different on their server than mine; they were not explicitly given names when created, so they would heve been given names by SQL Server.
Is there a way to delete a constraint without knowing its name? For example, delete all constraints associated with a field, or all constraints ssociated with a table?
Management Studio is an option, but for my client that involves a higher degree of paperwork and permission seeking than just running a script.
Many thanks,
Mike Thomas
View 3 Replies
View Related
Oct 14, 2005
I have several default constraints defined on a table. When I use theObject Browser and expand the constraints for this table andright-click and then select "Script Object to New Window As Create", acreate constraint statement for a different default constraint isdisplayed than the one I just right-clicked on. For example, I clickon constraint "DF_C" and it shows me "DF_B".The last time I encountered this, the solution was to dump contents ofthe table into another, drop, recreate it, and restore the contents.That's not a good option this time.Is there another way to fised this or at least navigate the catalog tofind out what is "off" about this?Thanks
View 1 Replies
View Related
Nov 22, 2007
Hello, I try to delete a user from the Membership table but I receive this error. "The DELETE statement conflicted with the REFERENCE constraint "FK__aspnet_Me__UserI__15502E78". The conflict occurred in database "E:INETPUBWEBSITE4APP_DATAASPNETDB.MDF", table "dbo.aspnet_Membership", column 'UserId'.The statement has been terminated."...<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" DataKeyNames="UserId" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display."> <Columns> <asp:CommandField ShowDeleteButton="True" /> <asp:BoundField DataField="UserId" HeaderText="UserId" SortExpression="UserId" /> <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" /> <asp:BoundField DataField="LastActivityDate" HeaderText="LastActivityDate" SortExpression="LastActivityDate" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString1 %>" DeleteCommand="DELETE FROM [aspnet_Users] WHERE [UserId] = @UserId" InsertCommand="INSERT INTO [aspnet_Users] ([ApplicationId], [UserId], [UserName], [LoweredUserName], [MobileAlias], [IsAnonymous], [LastActivityDate]) VALUES (@ApplicationId, @UserId, @UserName, @LoweredUserName, @MobileAlias, @IsAnonymous, @LastActivityDate)" ProviderName="<%$ ConnectionStrings:ASPNETDBConnectionString2.ProviderName %>" SelectCommand="SELECT [ApplicationId], [UserId], [UserName], [LoweredUserName], [MobileAlias], [IsAnonymous], [LastActivityDate] FROM [aspnet_Users]" UpdateCommand="UPDATE [aspnet_Users] SET [ApplicationId] = @ApplicationId, [UserName] = @UserName, [LoweredUserName] = @LoweredUserName, [MobileAlias] = @MobileAlias, [IsAnonymous] = @IsAnonymous, [LastActivityDate] = @LastActivityDate WHERE [UserId] = @UserId"> <InsertParameters> <asp:Parameter Name="ApplicationId" Type="Object" /> <asp:Parameter Name="UserId" Type="Object" /> <asp:Parameter Name="UserName" Type="String" /> <asp:Parameter Name="LoweredUserName" Type="String" /> <asp:Parameter Name="MobileAlias" Type="String" /> <asp:Parameter Name="IsAnonymous" Type="Boolean" /> <asp:Parameter Name="LastActivityDate" Type="DateTime" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="ApplicationId" Type="Object" /> <asp:Parameter Name="UserName" Type="String" /> <asp:Parameter Name="LoweredUserName" Type="String" /> <asp:Parameter Name="MobileAlias" Type="String" /> <asp:Parameter Name="IsAnonymous" Type="Boolean" /> <asp:Parameter Name="LastActivityDate" Type="DateTime" /> <asp:Parameter Name="UserId" Type="Object" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="UserId" Type="Object" /> </DeleteParameters> </asp:SqlDataSource></Content> ... cheers,imperialx
View 1 Replies
View Related
Sep 30, 2014
We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Code] ....
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change
DECLARE@size varchar(5)--The new size of the column
DECLARE @TSQL varchar(255)--Contains the code to be executed
DECLARE @Object varchar(50)--Holds the name of the table
DECLARE @dropc varchar(255)-- Drop constraint script
[Code] ....
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
None of the fields I try to create the key on are nullable.
View 2 Replies
View Related
May 14, 2008
Hello All, i have 2 files in excel which i am uploading them to a folder which is located in the root directory. then i am importing these two files into a sqldatabase. i needed some help in importing them and i got it from my previous post at http://forums.asp.net/t/1261155.aspx but now i have a problem. the import works perfectly first time but when i do it the second time i am getting an error on of the file (header). to explain clearly, what i am doing is , every time i upload a new file , i am deleting the data from the tables, so the new data can be inserted ( basically trying to achieve overwirting the existing data). now this technique works fine with the Detail table but not with the Header table. i think the reason is the header table has a primary key on OrderID and a relationship does exists between the Header and Detail. now how would i overcome this error. can some one please guide me. I really appreciate it.here is my code: // connection for header file
protected OleDbCommand headExcelConnection() { // Connect to the Excel Spreadsheet
string headConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/imports/headerorder.xls") + ";" + "Extended Properties=Excel 8.0;"; // create your excel connection object using the connection string
OleDbConnection headXConn = new OleDbConnection(headConnStr); headXConn.Open(); // use a SQL Select command to retrieve the data from the Excel Spreadsheet // the "table name" is the name of the worksheet within the spreadsheet // in this case, the worksheet name is "Sheet1" and is expressed as: [Sheet1$]
OleDbCommand headCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", headXConn); return headCommand; } // importing header information
protected void BtnImpHeader_Click(object sender, EventArgs e) { PanelUpload.Visible = false; PanelView.Visible = false; PanelImport.Visible = true; LabelImport.Text = ""; // reset to blank // Create a new Adapter
OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(); // retrieve the Select command for the Spreadsheet
objDataAdapter.SelectCommand = headExcelConnection();
// Create a DataSet
DataSet objDataSet = new DataSet(); // Populate the DataSet with the spreadsheet worksheet data
objDataAdapter.Fill(objDataSet);
// deleting the exisitng table before copy
SqlConnection mycon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); SqlCommand SqlCmd = null; mycon.Open(); SqlCmd = mycon.CreateCommand(); SqlCmd.CommandText = "DELETE FROM Header"; ---- showing error over on second time SqlCmd.ExecuteNonQuery(); mycon.Close(); // entering the newer header information
SqlConnection mysqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); mysqlcon.Open(); foreach (DataRow dr in objDataSet.Tables[0].Rows) { String sqlinsert = "insert into Header values(@param1,@param2,@param3,@param4,@param5,@param6,@param7,@param8,@param9,@param10,@param11,@param12,@param13,@param14,@param15,@param16,@param17,@param18,@param19,@param20,@param21,@param22,@param23,@param24,@param25,@param26,@param27,@param28,@param29,@param30,@param31,@param32)"; SqlCommand cmd = new SqlCommand(sqlinsert, mysqlcon); cmd.Parameters.AddWithValue("@param1", dr[0].ToString()); cmd.Parameters.AddWithValue("@param2", dr[1].ToString()); cmd.Parameters.AddWithValue("@param3", dr[2].ToString()); cmd.Parameters.AddWithValue("@param4", dr[3].ToString()); cmd.Parameters.AddWithValue("@param5", dr[4].ToString()); cmd.Parameters.AddWithValue("@param6", dr[5].ToString()); cmd.Parameters.AddWithValue("@param7", dr[6].ToString()); cmd.Parameters.AddWithValue("@param8", dr[7].ToString()); cmd.Parameters.AddWithValue("@param9", dr[8].ToString()); cmd.Parameters.AddWithValue("@param10", dr[9].ToString()); cmd.Parameters.AddWithValue("@param11", dr[10].ToString()); cmd.Parameters.AddWithValue("@param12", dr[11].ToString()); cmd.Parameters.AddWithValue("@param13", dr[12].ToString()); cmd.Parameters.AddWithValue("@param14", dr[13].ToString()); cmd.Parameters.AddWithValue("@param15", dr[14].ToString()); cmd.Parameters.AddWithValue("@param16", dr[15].ToString()); cmd.Parameters.AddWithValue("@param17", dr[16].ToString()); cmd.Parameters.AddWithValue("@param18", dr[17].ToString()); cmd.Parameters.AddWithValue("@param19", dr[18].ToString()); cmd.Parameters.AddWithValue("@param20", dr[19].ToString()); cmd.Parameters.AddWithValue("@param21", dr[20].ToString()); cmd.Parameters.AddWithValue("@param22", dr[21].ToString()); cmd.Parameters.AddWithValue("@param23", dr[22].ToString()); cmd.Parameters.AddWithValue("@param24", dr[23].ToString()); cmd.Parameters.AddWithValue("@param25", dr[24].ToString()); cmd.Parameters.AddWithValue("@param26", dr[25].ToString()); cmd.Parameters.AddWithValue("@param27", Convert.ToDecimal(dr[26].ToString())); cmd.Parameters.AddWithValue("@param28", Convert.ToDecimal(dr[27].ToString())); cmd.Parameters.AddWithValue("@param29", Convert.ToDecimal(dr[28].ToString())); cmd.Parameters.AddWithValue("@param30", Convert.ToDecimal(dr[29].ToString())); cmd.Parameters.AddWithValue("@param31", Convert.ToDecimal(dr[30].ToString())); cmd.Parameters.AddWithValue("@param32", dr[31].ToString()); cmd.ExecuteNonQuery(); // new SqlCommand (stmt, mysqlcon).ExecuteNonQuery();
LabelImport.Text = "Rows Inserted"; } mysqlcon.Close(); }// connection for detail file
protected OleDbCommand detExcelConnection() { // Connect to the Excel Spreadsheet
string detConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/imports/detailorder.xls") + ";" + "Extended Properties=Excel 8.0;"; // create your excel connection object using the connection string
OleDbConnection detXConn = new OleDbConnection(detConnStr); detXConn.Open(); // create your excel connection object using the connection string // use a SQL Select command to retrieve the data from the Excel Spreadsheet // the "table name" is the name of the worksheet within the spreadsheet // in this case, the worksheet name is "Sheet1" and is expressed as: [Sheet1$]
OleDbCommand detCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", detXConn); return detCommand; }// importing detail information
protected void ButtonImport_Click(object sender, EventArgs e) { PanelUpload.Visible = false; PanelView.Visible = false; PanelImport.Visible = true; LabelImport.Text = ""; // reset to blank // Create a new Adapter
OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(); // retrieve the Select command for the Spreadsheet
objDataAdapter.SelectCommand = detExcelConnection();
// Create a DataSet
DataSet objDataSet = new DataSet(); // Populate the DataSet with the spreadsheet worksheet data
objDataAdapter.Fill(objDataSet);
// deleting the exisitng table before copy
SqlConnection mycon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); SqlCommand SqlCmd = null; mycon.Open(); SqlCmd = mycon.CreateCommand(); SqlCmd.CommandText = "DELETE FROM Detail"; SqlCmd.ExecuteNonQuery(); mycon.Close(); // entering newer detail information
SqlConnection mysqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); mysqlcon.Open(); foreach (DataRow dr1 in objDataSet.Tables[0].Rows) { String sqlinsert = "insert into Detail values(@param1,@param2,@param3,@param4,@param5,@param6,@param7,@param8,@param9,@param10,@param11,@param12,@param13)"; SqlCommand cmd = new SqlCommand(sqlinsert, mysqlcon); cmd.Parameters.AddWithValue("@param1", dr1[0].ToString()); cmd.Parameters.AddWithValue("@param2", dr1[1].ToString()); cmd.Parameters.AddWithValue("@param3", dr1[2].ToString()); cmd.Parameters.AddWithValue("@param4", dr1[3].ToString()); cmd.Parameters.AddWithValue("@param5", dr1[4].ToString()); cmd.Parameters.AddWithValue("@param6", dr1[5].ToString()); cmd.Parameters.AddWithValue("@param7", dr1[6].ToString()); cmd.Parameters.AddWithValue("@param8", Convert.ToDecimal(dr1[7].ToString())); cmd.Parameters.AddWithValue("@param9", Convert.ToDecimal(dr1[8].ToString())); cmd.Parameters.AddWithValue("@param10", dr1[9].ToString()); cmd.Parameters.AddWithValue("@param11", dr1[10].ToString()); cmd.Parameters.AddWithValue("@param12", dr1[11].ToString()); cmd.Parameters.AddWithValue("@param13", dr1[12].ToString()); cmd.ExecuteNonQuery(); LabelImport.Text = "Rows Inserted"; } mysqlcon.Close(); } the error is as follows:
Server Error in '/WebSite6' Application.
The DELETE statement conflicted with the REFERENCE constraint
"FK_Detail_Header". The conflict occurred in database "C:DOCUMENTS AND
SETTINGSMEMY DOCUMENTSVISUAL STUDIO
2005WEBSITESWEBSITE6APP_DATADATABASE.MDF", table "dbo.Detail", column
'OrderID'.The statement has been terminated. 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: The DELETE statement conflicted with the
REFERENCE constraint "FK_Detail_Header". The conflict occurred in database
"C:DOCUMENTS AND SETTINGSMEMY DOCUMENTSVISUAL STUDIO
2005WEBSITESWEBSITE6APP_DATADATABASE.MDF", table "dbo.Detail", column
'OrderID'.The statement has been terminated.Source Error:
Line 176: SqlCmd = mycon.CreateCommand();Line 177: SqlCmd.CommandText = "DELETE FROM Header";Line 178: SqlCmd.ExecuteNonQuery();Line 179: mycon.Close();Line 180: again i really appreciate.Thanks
View 1 Replies
View Related
Aug 12, 2015
I want to enforce a constraint on the table where , the same MenuId cannot have 2 different prices for the same date range (accidentally).I think CHECK constraint won't work.
View 4 Replies
View Related
Sep 19, 2015
There is a table exists in a database name tbDatabaseProviders.
I want to alter this table to add a column DatabaseProvider Also I want to add a default constraint so if nothing than 1 should be added. Â
Remember, the table already exists so when I write the script query then I want to first check the constraint if it exists then I dont want to add the constrait otherwise I will add it. Â
I was having problem to first add a constraint with the constraint name so I could find the constraint before finding it if it already exists.Â
What should I do the above.Â
View 3 Replies
View Related
May 27, 2015
ALTER TABLE [dbo].[bkrm_lendcoll] ADD CONSTRAINT
   ReqdCovgAmt_constraint33 CHECK   Â
( caseÂ
 when CovgAmt = 0 and  ReqdCovgAmt = 0 then 1
 when CovgAmt = 0 and  ReqdCovgAmt = 1 then 1
 when CovgAmt = 1 and  ReqdCovgAmt = 0 then 1
 when CovgAmt = 1 and  ReqdCovgAmt = 1 then 0
end =1
)
This is my first attempt to add a constraint for business logic. Â The desired behavior is that the two columns together have the same behavior as a radio button. Â (one can be true, the other true, both can be false, but both cannot be true.) I get this error when I attempt to update it.
The ALTER TABLE statement conflicted with the CHECK constraint "ReqdCovgAmt_constraint33". The conflict occurred in database "Std", table "dbo.lendcoll".
So, basically my question is, when you have two bit columns and want them to have the truth table such as described, how can I set a Check constraint to enforce this?
View 14 Replies
View Related
Feb 6, 2008
Hi
I want to delete the Default value for a specific column which is set to Null
I've used
ALTER TABLE SYSTEMS_PATIENT_LOG ALTER COLUMN SYSTEMS_LOGID DROP DEFAULT
It is giving error
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'DEFAULT'.
plz could any one tell me where I could be wrong
View 3 Replies
View Related
Jul 9, 2015
I'm trying to update rows in a simple table that has a UNIQUE KEY CONSTRAINT defined on one of its columns. Here is the DDL for the table:
CREATE TABLE [dbo].[SEC_USER](
 [SEC_USER_ID] [int] IDENTITY(1,1) NOT NULL,
 [USER_CODE] [varchar](100) NOT NULL,
 [USER_NAME] [varchar](128) NOT NULL,
 [EMP_CODE] [varchar](6) NOT NULL,
[Code] ....
When trying to execute the UPDATE statement the query fails with a constraint violation error:
Violation of UNIQUE KEY constraint 'UQ__SEC_USER__A039F1EE62FE8444'. Cannot insert duplicate key in object 'dbo.SEC_USER'. The duplicate key value is (34337).
What has me baffled is that I'm not doing any insert. Also, the value that it's referencing - 34337 - doesn't exist in the table at all. I'd rather not drop the constraint.
View 8 Replies
View Related
Oct 6, 2015
I am trying to create a check constraint that prevents entry of a date range that overlaps any other date range in the table. The below example script results in the error message: The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap". what I am doing wrong?
CREATE TABLE t1 (d1 date,d2 date)
go
create function dbo.dt_overlap (@d1 date, @d2 date) RETURNS BIT
AS
BEGIN
 IF EXISTS (SELECT 1 from t1 where d1 between @d1 and @d2 or d2 between @d1 and @d2)
  RETURN 1
  RETURN 0
END
go
alter table t1 add constraint ck_dt_overlap CHECK (dbo.dt_overlap(d1,d2)=0)
go
insert into t1 values ('2015-01-01','2015-02-01')
insert into t1 values ('2015-01-15','2015-02-15')
--BOTH inserts result in the following message:Â The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap".
drop table t1
drop function dbo.dt_overlap
View 14 Replies
View Related
