Failed to script out Dropping Default Constraint script for table columns

I used the following code to script out Creating default constraints. It works fine. But if I change so.ScriptDrops = true to script Dropping default constraints, it always fails with

Exception: {"Script failed for DefaultConstraint 'DF_Employee_VacationHours'. "}

InnerException: {"Object reference not set to an instance of an object."}

Can anyone shred some light on it? Thanks in advance.

using System;

using System.Collections.Generic;

using System.Collections.Specialized;

using System.Text;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

namespace Test2

{

class Program

{

static void Main(string[] args)

{

Server s = new Server(".");

StringCollection sc;

ScriptingOptions so=new ScriptingOptions();

so.ScriptDrops = false;

s.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

foreach (Table t in s.Databases["Adventureworks"].Tables)

{

foreach (Column c in t.Columns)

{

if (c.DefaultConstraint!=null)

{

Console.WriteLine(string.Format("{0},{1}", t.Name, c.Name));

sc=c.DefaultConstraint.Script(so);

foreach (string st in sc)

{

Console.WriteLine(string.Format("{0}", st));

}

sc = null;

}

}

}

return;

}

}

}

Hi,

Thank you for reporting this issue. This is a defect in SMO, and we're filing a bug report. We will try to fix it for the next release of the product.

Failed to enable constraints. One or more rows contain values violating non-null, unique, or for

Hello all, I just want to make a quick post on the “Failed

to enable constraints. One or more rows contain values violating non-null,

unique, or foreign-key constraints.”

I am working on a small system at the moment and I have a

CODES table with columns CODE and DESC. When I first set up the database I set CODE

to varchar(MAX) and same with DESC, Then when I put all the codes and

descriptions in and tried to run fill I got the error.

Now weather this will help anyone I don’t know, I found out

that it was the Colum DESC in the DataSet that was causing the problem, if you

enter your dataset designer and select one of your columns, Right click, properties,

there is a field called MAXLENGTH, When I checked it was set to 50 even though I

had set varchar(MAX), so I knew that some of my descriptions where bigger than

50 so I upped it to 100 and everything was working fine. I don’t know if this

will be applicable to everyone or just me for being stupid but hey, it worked

;)

Moved to SQL server - seems off topic for the VS debugger.|||

I got the same error message because my dataset had columns with minoccur property as null but there were no records in the database. Some of these columns were nullable in the database but not marked as 0 in dataset minoccur property.

Solution - For every column thats marked as nullable in database, I set the minoccur property to 0. This makes dataser expect null values

Failed to enable constraints. One or more rows contain values violating non-null, unique, or for

Hello all, I just want to make a quick post on the “Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.”

I am working on a small system at the moment and I have a CODES table with columns CODE and DESC. When I first set up the database I set CODE to varchar(MAX) and same with DESC, Then when I put all the codes and descriptions in and tried to run fill I got the error.

Now weather this will help anyone I don’t know, I found out that it was the Colum DESC in the DataSet that was causing the problem, if you enter your dataset designer and select one of your columns, Right click, properties, there is a field called MAXLENGTH, When I checked it was set to 50 even though I had set varchar(MAX), so I knew that some of my descriptions where bigger than 50 so I upped it to 100 and everything was working fine. I don’t know if this will be applicable to everyone or just me for being stupid but hey, it worked ;)

Moved to SQL server - seems off topic for the VS debugger.|||

I got the same error message because my dataset had columns with minoccur property as null but there were no records in the database. Some of these columns were nullable in the database but not marked as 0 in dataset minoccur property.

Solution - For every column thats marked as nullable in database, I set the minoccur property to 0. This makes dataser expect null values

|||hello I'm new using c# I would like to know how to put the minoccur property to 0. Im having the same error
thanks

Failed to enable constraints. One or more rows contain values violating non-null, unique, or

Hello all, I just want to make a quick post on the “Failed

to enable constraints. One or more rows contain values violating non-null,

unique, or foreign-key constraints.”

I am working on a small system at the moment and I have a

CODES table with columns CODE and DESC. When I first set up the database I set CODE

to varchar(MAX) and same with DESC, Then when I put all the codes and

descriptions in and tried to run fill I got the error.

Now weather this will help anyone I don’t know, I found out

that it was the Colum DESC in the DataSet that was causing the problem, if you

enter your dataset designer and select one of your columns, Right click, properties,

there is a field called MAXLENGTH, When I checked it was set to 50 even though I

had set varchar(MAX), so I knew that some of my descriptions where bigger than

50 so I upped it to 100 and everything was working fine. I don’t know if this

will be applicable to everyone or just me for being stupid but hey, it worked

;)

Moved to SQL server - seems off topic for the VS debugger.|||

I got the same error message because my dataset had columns with minoccur property as null but there were no records in the database. Some of these columns were nullable in the database but not marked as 0 in dataset minoccur property.

Solution - For every column thats marked as nullable in database, I set the minoccur property to 0. This makes dataser expect null values

Failed to enable constraints

I have to admit I'm pretty new to ASP.net.

I'm trying to insert a row of data into my SQLExpress database table "Prets"

I'm getting the user_ID from the table "Membres"

Here's the code I have:

Imports System.Web.UI.Page
Imports System.Security.Principal
Imports WiseNetTableAdapters

PartialClass membre_sec_Pret_demande_pret
Inherits System.Web.UI.Page

Protected Sub submit_btn_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles submit_btn.Click
Dim UserAs String = My.User.Name
Dim pretAdapterAs New PretsTableAdapter
Dim membreAdapterAs New MembresTableAdapter
pretAdapter.AjouterPret(Convert.ToInt32(membreAdapter.GetUserIDbyName(User)), Convert.ToDecimal(montant_txt.Text), Convert.ToString(raison_txt.Text))
End Sub
End Class

And here's the error I get at runtime:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

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.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Source Error:

Line 1610: }Line 1611: WiseNet.MembresDataTable dataTable = new WiseNet.MembresDataTable();Line 1612: this.Adapter.Fill(dataTable);Line 1613: return dataTable;Line 1614: }

Source File:c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\wisenetindustries\afc388c7\16067f12\App_Code.tyietv41.0.cs Line:1612

Stack Trace:

[ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.] System.Data.DataTable.EnableConstraints() +1820725 System.Data.DataTable.set_EnforceConstraints(Boolean value) +39 System.Data.DataTable.EndLoadData() +138 System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +218 System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +318 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +221 System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +162 System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +107 WiseNetTableAdapters.MembresTableAdapter.GetUserIDbyName(String nom) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\wisenetindustries\afc388c7\16067f12\App_Code.tyietv41.0.cs:1612 membre_sec_Pret_demande_pret.submit_btn_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\WiseNetIndustries\membre_sec\Pret\demande_pret.aspx.vb:12 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

Make sure you have not insert duplicate values on the primary key column, you may take a look at this post:

http://forums.asp.net/thread/1213814.aspx

sql

Failed to enable constraints

Hello there,

I have a table under SQL2005 express with 8 fields with a primaryKey defined.

In my BLL in the table adapter there is one "SELECT DISTINCT prodCAT from prods"

The prodCAT is a nvarchar(50) and it is not part of an index or primaryKey.

When I preview the data from my BLL I get the correct data.

I defined a new web form (aspx) and put a ddl (dropDownList) with an objectdataSource that relates to the above sql function/command

and I get a

"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

BTW when I defined the above SQL in the BLL I got a "The new command text makes the function return data with schema different from the schema of the main query"

Any suggestions?

Does your BLL return a dataset? If so you shoudl not get the behaviour described.|||

yes, it should return a dataset.

|||

sorry,

it return a data table

|||Try changing it to return a dataset.|||How do I do that??|||Look athttp://www.dotnetjunkies.com/QuickStartv20/howto/doc/adoplus/GetDataFromDB.aspx ADO.NET: Populate a DataSet from a Database|||

First thanx a lot

If I populate the dataset using this technique by-pass the BLL. Is it the correct way to do things??

best regards

|||There are times when a Business Logic Layer is useful - this does not look like one! You could of course put an entry into the BLL that calls the layer below to get a dataset and pass it up to the GUI.

Failed to enable constraints

does anyone had any idea on the following codes that i'm facing?i have no ideas what is constraints and i hardly can view my page. In addition to that, i also faced the same problems when i'm trying to change my user password. it eventually managed to change the password but then the changes leads to the failed to enable constraints too. however, the following codes is not related to the change password but the purchase item. Hope someone can help me up! thanks!

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
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.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Source Error:

Line 223: SqlDataAdapter2.SelectCommand.ExecuteNonQuery()
Line 224: SqlConnection1.Close()
Line 225: SqlDataAdapter2.Fill(BuyData2)
Line 226: i = BuyData2.BuyView.Count
Line 227: For n = 0 To i - 1

Source File: C:\Inetpub\wwwroot\eg\Buy.aspx.vb Line: 225

Stack Trace:

[ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.]
System.Data.DataSet.FailedEnableConstraints() +19
System.Data.DataSet.EnableConstraints() +519
System.Data.DataSet.set_EnforceConstraints(Boolean value) +31
System.Data.DataTable.EndLoadData() +300
System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +260
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +129
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
eg.Buy.Page_Load(Object sender, EventArgs e) in C:\Inetpub\wwwroot\eg\Buy.aspx.vb:225
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731One thing that's wrong right off is that you are closing your connection to the database right before calling the Fill method. Is SqlConnection1 the connection used by SqlDataAdaptor2? I'm actually guessing not, since I would expect that would have caused a different exception. But why are you closing it there?

Another odd thing is that you're calling the ExecuteNonQuery method of the SelectCommand. Why are you doing that? If it's a select statement, it returns rows, which ExecuteNonQuery won't return.

So your code is a bit confusing. Please post the complete Page_Load event procedure and any custom procedures it calls so that we can see what you're trying to do. Use the code tags to keep it formatted.

Don|||

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.UI
Public Class Buy
 Inherits System.Web.UI.Page
 Protected WithEvents Label1 As System.Web.UI.WebControls.Label
 Protected WithEvents Label2 As System.Web.UI.WebControls.Label
 Protected WithEvents Label3 As System.Web.UI.WebControls.Label
 Protected WithEvents Label4 As System.Web.UI.WebControls.Label
 Protected WithEvents QuanB As System.Web.UI.WebControls.TextBox
 Protected WithEvents QuanA As System.Web.UI.WebControls.TextBox
 Protected WithEvents StockList As System.Web.UI.WebControls.DropDownList
 Protected WithEvents AddCart As System.Web.UI.WebControls.Button
 Protected WithEvents Purchase As System.Web.UI.WebControls.Button
 Protected WithEvents ListBox1 As System.Web.UI.WebControls.ListBox
 Protected WithEvents RequiredFieldValidator1 As System.Web.UI.WebControls.RequiredFieldValidator
 Protected WithEvents CompareValidator1 As System.Web.UI.WebControls.CompareValidator
 Protected WithEvents ITQ As System.Web.UI.WebControls.TextBox
 Protected WithEvents ITT As System.Web.UI.WebControls.Label
 Protected WithEvents RmItem As System.Web.UI.WebControls.Button
 Protected WithEvents ItemAllowed As System.Web.UI.WebControls.Label
 Protected WithEvents OutletList As System.Web.UI.WebControls.DropDownList
 Protected WithEvents OutID As System.Web.UI.WebControls.TextBox
 Protected WithEvents StID As System.Web.UI.WebControls.TextBox
 Protected WithEvents ItemRM As System.Web.UI.WebControls.TextBox
 Protected WithEvents SqlDataAdapter2 As System.Data.SqlClient.SqlDataAdapter
 Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
 Protected WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
 Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
 Protected WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
 Protected WithEvents SqlSelectCommand2 As System.Data.SqlClient.SqlCommand
 Protected WithEvents SqlInsertCommand2 As System.Data.SqlClient.SqlCommand
 Protected WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
 Protected WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand
 Protected WithEvents SqlDataAdapter3 As System.Data.SqlClient.SqlDataAdapter
 Protected WithEvents SqlSelectCommand3 As System.Data.SqlClient.SqlCommand
 Protected WithEvents SqlInsertCommand3 As System.Data.SqlClient.SqlCommand
 Protected WithEvents SqlUpdateCommand2 As System.Data.SqlClient.SqlCommand
 Protected WithEvents SqlDeleteCommand2 As System.Data.SqlClient.SqlCommand
 Protected WithEvents BuyData2 As eg.BuyData
 Protected WithEvents Label5 As System.Web.UI.WebControls.Label
#Region " Web Form Designer Generated Code "
 'This call is required by the Web Form Designer.
 <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
 Me.SqlDataAdapter2 = New System.Data.SqlClient.SqlDataAdapter()
 Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand()
 Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection()
 Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand()
 Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter()
 Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand()
 Me.SqlInsertCommand2 = New System.Data.SqlClient.SqlCommand()
 Me.SqlSelectCommand2 = New System.Data.SqlClient.SqlCommand()
 Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand()
 Me.SqlDataAdapter3 = New System.Data.SqlClient.SqlDataAdapter()
 Me.SqlDeleteCommand2 = New System.Data.SqlClient.SqlCommand()
 Me.SqlInsertCommand3 = New System.Data.SqlClient.SqlCommand()
 Me.SqlSelectCommand3 = New System.Data.SqlClient.SqlCommand()
 Me.SqlUpdateCommand2 = New System.Data.SqlClient.SqlCommand()
 Me.BuyData2 = New eg.BuyData()
 CType(Me.BuyData2, System.ComponentModel.ISupportInitialize).BeginInit()
 '
 'SqlDataAdapter2
 '
 Me.SqlDataAdapter2.InsertCommand = Me.SqlInsertCommand1
 Me.SqlDataAdapter2.SelectCommand = Me.SqlSelectCommand1
 Me.SqlDataAdapter2.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping
("Table", "BuyView", New System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("StockID", "StockID"), New
System.Data.Common.DataColumnMapping("StockName", "StockName"), New
System.Data.Common.DataColumnMapping("Price", "Price"), New
System.Data.Common.DataColumnMapping("StockQuan", "StockQuan"), New
System.Data.Common.DataColumnMapping("Name", "Name"), New
System.Data.Common.DataColumnMapping("OutletID", "OutletID")})})
 '
 'SqlInsertCommand1
 '
 Me.SqlInsertCommand1.CommandText = "INSERT INTO BuyView(StockID,
StockName, Price, StockQuan, Name, OutletID) VALUES " & _
 "(@.StockID, @.StockName, @.Price, @.StockQuan, @.Name, @.OutletID); SELECT StockID, St" & _
 "ockName, Price, StockQuan, Name, OutletID FROM BuyView"
 Me.SqlInsertCommand1.Connection = Me.SqlConnection1
 Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.StockID", System.Data.SqlDbType.NVarChar, 10, "StockID"))
 Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.StockName", System.Data.SqlDbType.NVarChar, 50, "StockName"))
 Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Price", System.Data.SqlDbType.Money, 8, "Price"))
 Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.StockQuan", System.Data.SqlDbType.NVarChar, 50, "StockQuan"))
 Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Name", System.Data.SqlDbType.NVarChar, 50, "Name"))
 Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.OutletID", System.Data.SqlDbType.NVarChar, 50, "OutletID"))
 '
 'SqlConnection1
 '
 Me.SqlConnection1.ConnectionString = "data source=WINNIE;initial
catalog=NewOIS;persist security info=False;user id=sa;" & _
 "workstation id=WINNIE;packet size=4096"
 '
 'SqlSelectCommand1
 '
 Me.SqlSelectCommand1.CommandText = "SELECT StockID, StockName, Price,
StockQuan, Name, OutletID FROM BuyView"
 Me.SqlSelectCommand1.Connection = Me.SqlConnection1
 '
 'SqlDataAdapter1
 '
 Me.SqlDataAdapter1.DeleteCommand = Me.SqlDeleteCommand1
 Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand2
 Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand2
 Me.SqlDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping
("Table", "Cart", New System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("CartID", "CartID"), New
System.Data.Common.DataColumnMapping("InvoiceID", "InvoiceID"), New
System.Data.Common.DataColumnMapping("StockID", "StockID"), New
System.Data.Common.DataColumnMapping("Quantity", "Quantity"), New
System.Data.Common.DataColumnMapping("OutletID", "OutletID")})})
 Me.SqlDataAdapter1.UpdateCommand = Me.SqlUpdateCommand1
 '
 'SqlDeleteCommand1
 '
 Me.SqlDeleteCommand1.CommandText = "DELETE FROM Cart WHERE (CartID =
@.Original_CartID) AND (InvoiceID = @.Original_Inv" & _
 "oiceID OR @.Original_InvoiceID IS NULL AND InvoiceID IS NULL) AND (OutletID =
@.Or" & _
 "iginal_OutletID OR @.Original_OutletID IS NULL AND OutletID IS NULL) AND (Quantit" & _
 "y = @.Original_Quantity OR @.Original_Quantity IS NULL AND Quantity IS NULL) AND (" & _
 "StockID = @.Original_StockID OR @.Original_StockID IS NULL AND StockID IS NULL)"
 Me.SqlDeleteCommand1.Connection = Me.SqlConnection1
 Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Original_CartID", System.Data.SqlDbType.Decimal, 9,
System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "CartID",
System.Data.DataRowVersion.Original, Nothing))
 Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Original_InvoiceID", System.Data.SqlDbType.NVarChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "InvoiceID",
System.Data.DataRowVersion.Original, Nothing))
 Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Original_OutletID", System.Data.SqlDbType.NVarChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "OutletID",
System.Data.DataRowVersion.Original, Nothing))
 Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Original_Quantity", System.Data.SqlDbType.NVarChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Quantity",
System.Data.DataRowVersion.Original, Nothing))
 Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Original_StockID", System.Data.SqlDbType.NVarChar, 10,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "StockID",
System.Data.DataRowVersion.Original, Nothing))
 '
 'SqlInsertCommand2
 '
 Me.SqlInsertCommand2.CommandText = "INSERT INTO Cart(InvoiceID, StockID, Quantity, OutletID) VALUES (@.InvoiceID, @.Sto" & _
 "ckID, @.Quantity, @.OutletID); SELECT CartID, InvoiceID, StockID, Quantity, Outlet" & _
 "ID FROM Cart WHERE (CartID = @.@.IDENTITY)"
 Me.SqlInsertCommand2.Connection = Me.SqlConnection1
 Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.InvoiceID", System.Data.SqlDbType.NVarChar, 50, "InvoiceID"))
 Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.StockID", System.Data.SqlDbType.NVarChar, 10, "StockID"))
 Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Quantity", System.Data.SqlDbType.NVarChar, 50, "Quantity"))
 Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.OutletID", System.Data.SqlDbType.NVarChar, 50, "OutletID"))
 '
 'SqlSelectCommand2
 '
 Me.SqlSelectCommand2.CommandText = "SELECT CartID, InvoiceID, StockID, Quantity, OutletID FROM Cart"
 Me.SqlSelectCommand2.Connection = Me.SqlConnection1
 '
 'SqlUpdateCommand1
 '
 Me.SqlUpdateCommand1.CommandText = "UPDATE Cart SET InvoiceID = @.InvoiceID, StockID = @.StockID, Quantity = @.Quantity," & _
 " OutletID = @.OutletID WHERE (CartID = @.Original_CartID) AND (InvoiceID = @.Origin" & _
 "al_InvoiceID OR @.Original_InvoiceID IS NULL AND InvoiceID IS NULL) AND (OutletID" & _
 " = @.Original_OutletID OR @.Original_OutletID IS NULL AND OutletID IS NULL) AND (Q" & _
 "uantity = @.Original_Quantity OR @.Original_Quantity IS NULL AND Quantity IS NULL)" & _
 " AND (StockID = @.Original_StockID OR @.Original_StockID IS NULL AND StockID IS NU" & _
 "LL); SELECT CartID, InvoiceID, StockID, Quantity, OutletID FROM Cart WHERE (Cart" & _
 "ID = @.CartID)"
 Me.SqlUpdateCommand1.Connection = Me.SqlConnection1
 Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.InvoiceID", System.Data.SqlDbType.NVarChar, 50, "InvoiceID"))
 Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.StockID", System.Data.SqlDbType.NVarChar, 10, "StockID"))
 Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Quantity", System.Data.SqlDbType.NVarChar, 50, "Quantity"))
 Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.OutletID", System.Data.SqlDbType.NVarChar, 50, "OutletID"))
 Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Original_CartID", System.Data.SqlDbType.Decimal, 9,
System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "CartID",
System.Data.DataRowVersion.Original, Nothing))
 Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Original_InvoiceID", System.Data.SqlDbType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "InvoiceID",
System.Data.DataRowVersion.Original, Nothing))
 Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Original_OutletID", System.Data.SqlDbType.NVarChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "OutletID",
System.Data.DataRowVersion.Original, Nothing))
 Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Original_Quantity", System.Data.SqlDbType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Quantity",
System.Data.DataRowVersion.Original, Nothing))
 Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Original_StockID", System.Data.SqlDbType.NVarChar, 10,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "StockID",
System.Data.DataRowVersion.Original, Nothing))
 Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.CartID", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input,
False, CType(18, Byte), CType(0, Byte), "CartID", System.Data.DataRowVersion.Current,
Nothing))
 '
 'SqlDataAdapter3
 '
 Me.SqlDataAdapter3.DeleteCommand = Me.SqlDeleteCommand2
 Me.SqlDataAdapter3.InsertCommand = Me.SqlInsertCommand3
 Me.SqlDataAdapter3.SelectCommand = Me.SqlSelectCommand3
 Me.SqlDataAdapter3.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping
("Table", "Invoice", New System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("InvoiceID", "InvoiceID"), New
System.Data.Common.DataColumnMapping("CustomerID", "CustomerID")})})
 Me.SqlDataAdapter3.UpdateCommand = Me.SqlUpdateCommand2
 '
 'SqlDeleteCommand2
 '
 Me.SqlDeleteCommand2.CommandText = "DELETE FROM Invoice WHERE (InvoiceID =
@.Original_InvoiceID) AND (CustomerID = @.Or" & _
 "iginal_CustomerID)"
 Me.SqlDeleteCommand2.Connection = Me.SqlConnection1
 Me.SqlDeleteCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Original_InvoiceID", System.Data.SqlDbType.Decimal, 9,
System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0,
Byte), "InvoiceID", System.Data.DataRowVersion.Original, Nothing))
 Me.SqlDeleteCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Original_CustomerID", System.Data.SqlDbType.NVarChar, 10,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "CustomerID", System.Data.DataRowVersion.Original, Nothing))
 '
 'SqlInsertCommand3
 '
 Me.SqlInsertCommand3.CommandText = "INSERT INTO Invoice(InvoiceID, CustomerID) VALUES (@.InvoiceID, @.CustomerID); SELE" & _
 "CT InvoiceID, CustomerID FROM Invoice WHERE (InvoiceID = @.InvoiceID)"
 Me.SqlInsertCommand3.Connection = Me.SqlConnection1
 Me.SqlInsertCommand3.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.InvoiceID", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input,
False, CType(18, Byte), CType(0, Byte), "InvoiceID", System.Data.DataRowVersion.Current, Nothing))
 Me.SqlInsertCommand3.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.CustomerID", System.Data.SqlDbType.NVarChar, 10, "CustomerID"))
 '
 'SqlSelectCommand3
 '
 Me.SqlSelectCommand3.CommandText = "SELECT InvoiceID, CustomerID FROM Invoice"
 Me.SqlSelectCommand3.Connection = Me.SqlConnection1
 '
 'SqlUpdateCommand2
 '
 Me.SqlUpdateCommand2.CommandText = "UPDATE Invoice SET InvoiceID = @.InvoiceID, CustomerID = @.CustomerID WHERE (Invoic" & _
 "eID = @.Original_InvoiceID) AND (CustomerID = @.Original_CustomerID); SELECT Invoi" & _
 "ceID, CustomerID FROM Invoice WHERE (InvoiceID = @.InvoiceID)"
 Me.SqlUpdateCommand2.Connection = Me.SqlConnection1
 Me.SqlUpdateCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.InvoiceID", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input,
False, CType(18, Byte), CType(0, Byte), "InvoiceID", System.Data.DataRowVersion.Current, Nothing))
 Me.SqlUpdateCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.CustomerID", System.Data.SqlDbType.NVarChar, 10, "CustomerID"))
 Me.SqlUpdateCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Original_InvoiceID", System.Data.SqlDbType.Decimal, 9,
System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0,
Byte), "InvoiceID", System.Data.DataRowVersion.Original, Nothing))
 Me.SqlUpdateCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter
("@.Original_CustomerID", System.Data.SqlDbType.NVarChar, 10,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "CustomerID", System.Data.DataRowVersion.Original, Nothing))
 '
 'BuyData2
 '
 Me.BuyData2.DataSetName = "BuyData"
 Me.BuyData2.Locale = New System.Globalization.CultureInfo("en-US")
 Me.BuyData2.Namespace = "http://www.tempuri.org/BuyData.xsd"
 CType(Me.BuyData2, System.ComponentModel.ISupportInitialize).EndInit()
 End Sub
 Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
 'CODEGEN: This method call is required by the Web Form Designer
 'Do not modify it using the code editor.
 InitializeComponent()
 End Sub
#End Region
 Dim size, i, n As Integer
 Public citem(20) As String
 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 Response.Cookies("OIS_Cust").Value = "Poi"
 Try
 BuyData2.Clear()
 Dim str As String
 str = "SELECT DISTINCT Name FROM BuyView"
 SqlDataAdapter2.SelectCommand.CommandText = str
 SqlConnection1.Open()
 SqlDataAdapter2.SelectCommand.ExecuteNonQuery()
 SqlConnection1.Close()
 SqlDataAdapter2.Fill(BuyData2)
 i = BuyData2.BuyView.Count
 For n = 0 To i - 1
 OutletList.Items.Add(CStr(DataBinder.Eval(BuyData2, _
 "tables[BuyView].defaultview.[" & n & "].Name")))
 Next
 BuyData2.Clear()
 str = "select StockID, StockName, StockQuan, OutletID from BuyView where Name = @.OutletName"
 SqlDataAdapter2.SelectCommand.CommandText = str
 SqlDataAdapter2.SelectCommand.Parameters.Add(New _
 SqlParameter("@.OutletName", SqlDbType.NVarChar, 50))
 SqlDataAdapter2.SelectCommand.Parameters("@.OutletName").Value = _
 OutletList.SelectedItem.Text
 SqlConnection1.Open()
 SqlDataAdapter2.SelectCommand.ExecuteNonQuery()
 SqlConnection1.Close()
 SqlDataAdapter2.Fill(BuyData2)
 i = BuyData2.BuyView.Count
 QuanA.Text = CStr(DataBinder.Eval(BuyData2, _
 "tables[BuyView].defaultview.[0].StockQuan"))
 For n = 0 To i - 1
 StockList.Items.Add(CStr(DataBinder.Eval(BuyData2, _
 "tables[BuyView].defaultview.[" & n & "].StockName")))
 Next
 OutID.Text = CStr(DataBinder.Eval(BuyData2, _
 "tables[BuyView].defaultview.[0].OutletID"))
 StID.Text = CStr(DataBinder.Eval(BuyData2, _
 "tables[BuyView].defaultview.[0].StockID"))
 QuanA.Text = CStr(DataBinder.Eval(BuyData2, _
 "tables[BuyView].defaultview.[0].StockQuan"))
 Catch SqlExceptionParameter As System.Data.SqlClient.SqlException
 Console.WriteLine(SqlExceptionParameter.StackTrace)
 End Try
 End Sub
 Private Sub OutletList_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OutletList.SelectedIndexChanged
 GetItem()
 End Sub
 Private Sub StockList_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles StockList.SelectedIndexChanged
 GetItem()
 End Sub
 Public Function GetItem() As String
 Dim indx1 As Integer = OutletList.SelectedIndex()
 Dim indx2 As Integer = StockList.SelectedIndex()
 Dim str As String
 OutletList.Items.Clear()
 StockList.Items.Clear()
 BuyData2.Clear()
 str = "SELECT DISTINCT Name FROM BuyView"
 SqlDataAdapter2.SelectCommand.CommandText = str
 SqlConnection1.Open()
 SqlDataAdapter2.SelectCommand.ExecuteNonQuery()
 SqlConnection1.Close()
 SqlDataAdapter2.Fill(BuyData2)
 i = BuyData2.BuyView.Count
 For n = 0 To i - 1
 OutletList.Items.Add(CStr(DataBinder.Eval(BuyData2, _
 "tables[BuyView].defaultview.[" & n & "].Name")))
 Next
 OutletList.SelectedIndex = indx1
 BuyData2.Clear()
 str = "select StockID, StockName, StockQuan, OutletID from BuyView where Name = @.SName"
 SqlDataAdapter2.SelectCommand.CommandText = str
 SqlDataAdapter2.SelectCommand.Parameters.Add(New _
 SqlParameter("@.SName", SqlDbType.NVarChar, 50))
 SqlDataAdapter2.SelectCommand.Parameters("@.SName").Value = _
 OutletList.SelectedItem.Text
 SqlConnection1.Open()
 SqlDataAdapter2.SelectCommand.ExecuteNonQuery()
 SqlConnection1.Close()
 SqlDataAdapter2.Fill(BuyData2)
 i = BuyData2.BuyView.Count
 For n = 0 To i - 1
 StockList.Items.Add(CStr(DataBinder.Eval(BuyData2, _
 "tables[BuyView].defaultview.[" & n & "].StockName")))
 Next
 If indx2 > i Then
 indx2 = 0
 End If
 StockList.SelectedIndex = indx2
 OutID.Text = CStr(DataBinder.Eval(BuyData2, _
 "tables[BuyView].defaultview.[" & indx2 & "].OutletID"))
 StID.Text = CStr(DataBinder.Eval(BuyData2, _
 "tables[BuyView].defaultview.[" & indx2 & "].StockID"))
 QuanA.Text = CStr(DataBinder.Eval(BuyData2, _
 "tables[BuyView].defaultview.[" & indx2 & "].StockQuan"))
 End Function
 Private Sub AddCart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddCart.Click
 size = CInt(ITQ.Text)
 If size < 10 Then
 GetItem()
 size = size + 1
 ITQ.Text = size
 Dim Cookie As HttpCookie
 Dim str As String
 str = "insert into Cart values(@.InvoiceID,@.StockID,@.Quantity,@.OutletID)"
 Dim inscom As New SqlCommand(str, SqlConnection1)
 SqlDataAdapter1.InsertCommand = inscom
 SqlDataAdapter1.InsertCommand.Parameters.Add(New _
 SqlParameter("@.InvoiceID", SqlDbType.NVarChar, 50))
 SqlDataAdapter1.InsertCommand.Parameters.Add(New _
 SqlParameter("@.StockID", SqlDbType.NVarChar, 10))
 SqlDataAdapter1.InsertCommand.Parameters.Add(New _
 SqlParameter("@.Quantity", SqlDbType.NVarChar, 50))
 SqlDataAdapter1.InsertCommand.Parameters.Add(New _
 SqlParameter("@.OutletID", SqlDbType.NVarChar, 50))
 Cookie = Request.Cookies("OIS_Cust")
 ListBox1.Items.Add(StockList.SelectedItem.Text)
 SqlDataAdapter1.InsertCommand.Parameters("@.InvoiceID").Value = _
 Cookie.Value
 SqlDataAdapter1.InsertCommand.Parameters("@.StockID").Value = _
 StID.Text
 SqlDataAdapter1.InsertCommand.Parameters("@.Quantity").Value = _
 QuanB.Text
 SqlDataAdapter1.InsertCommand.Parameters("@.OutletID").Value = _
 OutID.Text
 SqlConnection1.Open()
 SqlDataAdapter1.InsertCommand.ExecuteNonQuery()
 SqlConnection1.Close()
 Else
 ItemAllowed.Text = "Exceed 10 Items Not Allowed"
 ItemAllowed.ForeColor = System.Drawing.Color.Red
 ITQ.ForeColor = System.Drawing.Color.Red
 End If
 End Sub
 Private Sub RmItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RmItem.Click
 GetItem()
 Dim Cookie As HttpCookie
 Cookie = Request.Cookies("Cart_Item")
 'Temp.Text = Cookie.Value
 ListBox1.Items.Add(Cookie.Value)
 If n <> -1 Then
 'i = (n + 1) / 3
 'Temp.Text = n
 End If
 End Sub
 Private Sub Purchase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Purchase.Click
 size = CInt(ITQ.Text)
 If size = 0 Then
 ItemAllowed.Text = "No Item in cart"
 ItemAllowed.ForeColor = System.Drawing.Color.Red
 ITQ.ForeColor = System.Drawing.Color.Red
 Else
 'Create an Invoice
 'Get the cookie
 Dim str, IID As String
 Dim Cookie As HttpCookie
 Cookie = Request.Cookies("OIS_Cust")
 str = "insert into Invoice values(@.CustomerID)"
 Dim inscom As New SqlCommand(str, SqlConnection1)
 SqlDataAdapter3.InsertCommand = inscom
 SqlDataAdapter3.InsertCommand.Parameters.Add(New _
 SqlParameter("@.CustomerID", SqlDbType.NVarChar, 50))
 SqlDataAdapter3.InsertCommand.Parameters("@.CustomerID").Value = _
 Cookie.Value
 SqlConnection1.Open()
 SqlDataAdapter3.InsertCommand.ExecuteNonQuery()
 SqlConnection1.Close()
 'Get the Invoice ID
 BuyData2.Clear()
 str = "select InvoiceID from Invoice where CustomerID = @.CID"
 SqlDataAdapter3.SelectCommand.CommandText = str
 SqlDataAdapter3.SelectCommand.Parameters.Add(New _
 SqlParameter("@.CID", SqlDbType.NVarChar, 50))
 SqlDataAdapter3.SelectCommand.Parameters("@.CID").Value = _
 Cookie.Value
 SqlConnection1.Open()
 SqlDataAdapter3.SelectCommand.ExecuteNonQuery()
 SqlConnection1.Close()
 SqlDataAdapter3.Fill(BuyData2)
 i = BuyData2.Invoice.Count
 IID = CStr(DataBinder.Eval(BuyData2, _
 "tables[Invoice].defaultview.[" & i - 1 & "].InvoiceID"))
 'Update Cart Invoice ID
 str = "Update Cart set InvoiceID=@.InID where InvoiceID=@.CustID"
 Dim updcom As SqlCommand
 updcom = New SqlCommand(str, SqlConnection1)
 SqlDataAdapter1.UpdateCommand = updcom
 SqlDataAdapter1.UpdateCommand.Parameters.Add(New _
 SqlParameter("@.InID", SqlDbType.NVarChar, 50))
 SqlDataAdapter1.UpdateCommand.Parameters.Add(New _
 SqlParameter("@.CustID", SqlDbType.NVarChar, 50))
 SqlDataAdapter1.UpdateCommand.Parameters("@.InID").Value = IID
 'Retrieve Cookie
 SqlDataAdapter1.UpdateCommand.Parameters("@.CustID").Value = _
 Cookie.Value
 SqlConnection1.Open()
 SqlDataAdapter1.UpdateCommand.ExecuteNonQuery()
 SqlConnection1.Close()
 Response.Cookies("OIS_Invoice").Value = IID
 'Response.Redirect("Invoice.aspx")
 End If
 End Sub
End Class

erm, the above is the full codes that i got. well, i'm using different sqldataadapter, indeed! i'm new to the codes, so i'm not sure. Ok,meaning i need to fill my dataset before i close my connection, is it? hope with the full codes, u might help me up, thanks!|||Okay, I think this is a bit more straightforward than I first thought. First, BuyData2 is a typed DataSet, right? In fact I know it is because you reference the "http://www.tempuri.org/BuyData.xsd" namespace. You didn't post the code that creates that (the eg.BuyData object, whatever that is), but as part of however you create it there are constraints being added to the data set. Are you creating it in the VS.NET IDE? Or some other way?

The thing that is going to cause you problems is that you are using the BuyData2 data set object over and over. It's going to be virtually impossible that every time you fill it the data will fit the constraints of the data set.

Throughout this code, you use all of these SELECT statements to fill the BuyData2 data set:

SELECT DISTINCT Name FROM BuyView
select StockID, StockName, StockQuan, OutletID from BuyView where Name = @.OutletName
SELECT DISTINCT Name FROM BuyView
select StockID, StockName, StockQuan, OutletID from BuyView where Name = @.SName
select InvoiceID from Invoice where CustomerID = @.CID

That's just not going to work with a typed dataset, since these are completely different kinds of data. You might be able to use one typed data set with the first and third SELECT statements, another for the second and fourth, and yet another for the fifth. But certainly not one for all. Calling the data set's Clear method just gets rid of the data, not the structure.

Basically, you probably created the typed dataset for either the second, fourth, or fifth SELECT statement, but because the first statement isn't providing the data the data set is expecting, the code is choking with the constraint problem. You're probably putting the wrong data type into most of the fields of the data set.

Make sense?

Don|||I realise this is a really old thread, but I encounterd this situation with .Net 2.0 and XSD strongly typed dataset between my DAL and my BL. I couldn't find any answers short of "set the EnableContstraints = false" workaround.

I found my issue was when I created I -
a) used an exisiting TableAdapter in my XSD to create a join statment
b) appon creating my join statment I realised I needed to change a column datatype from Int to Uniqueidentifire. (types could diffre but I'm sure the same will happen)
c) this change did NOT update in my TableAdapter in my XSD eventhough I did multipule "configure"'s on it.

The error was generating because upon execution the DataTable was creating a "ColumnName_1" to fill (wat .net thought) was a missing column for the join.

To fix this I had to REMOVE the TableAdapter and XSD DataTable set and then re-create them.

After they where reCreated everything worked fine and I did NOT need to set the EnableConstraints to false.