Failed to retrieve data for this request.

I am trying to load data into a SQL Server table through a web page. I am getting error like this:

Error: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

I am doing the same for other tables in the same db its working fine. but its working for one table alone. The table name is like this (DTD_PUMA_DeliveryPerformance)

Is it cos of table name?

I tried with import/export wizard also its working fine but still its not working for web page.

I don t think its cos of network or OS!! Its with table or some connection property.

Pls help me in solving this.

There is nothing wrong with the tablename.

Have you checked the connection string and compared it to the one on pages that are successful?

|||

Thanks for the reply.

Identified the problem, its cos of the excel sheet.

There is a row in the excel sheet with the description ("Get Keys"), when i removed tht row its working

So i need to know how to exclude the first row from the excel sheet while selecting (select * from [Sheet1$]).

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.

Failed to call SSIS package from ASPX C# Web Page

Hi, Everyone:

I am getting the following error message when I try to execute a SSIS package from an asp.net page written in C# 2.0. What I am trying to do is basically just click on a button in the web page and it will execute the package. The code to execute the package is pretty simple. I pass the path of the DTS package stored in the local folder. This works fine in the machine where SQL2005 is installed locally. But it fail when I have a seperate Web Server and SQL Server. Any ideas? Do I have to install SSIS or SQL2005 on a web server as well?

Thanks

static public string Execute_SSIS_DTS(string DTS_Path)

{

Microsoft.SqlServer.Dts.Runtime.Application app;

app = new Microsoft.SqlServer.Dts.Runtime.Application();

Package package = app.LoadPackage(DTS_Path, null);

DTSExecResult result = package.Execute();

return result.ToString();

}

Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.

Line 226: static public string Execute_SSIS_DTS(string DTS_Path)

Line 227: {

Line 228: Application app = new Application();

Line 229: Package package = app.LoadPackage(DTS_Path, null);

Line 230: DTSExecResult result = package.Execute();

[COMException (0x80040154): Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.]
Microsoft.SqlServer.Dts.Runtime.Application..ctor() +43

[DtsPipelineException: Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.]
Microsoft.SqlServer.Dts.Runtime.Application..ctor() +169
Utilities.Execute_SSIS_DTS(String DTS_Path) in c:\Inetpub\wwwroot\MasterTables\App_Code\Utilities.cs:228
MasterTables_Admin_MasterTables_LOINC_External.btn_SyncLISTest_Click(Object sender, EventArgs e) in c:\Inetpub\wwwroot\MasterTables\MasterTables_Custom\MasterTablesCustom_LOINC_External.aspx.cs:98
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +114
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +141
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3215

Yes, to use SSIS object model and to execute package you do need to install SSIS on this machine, of course.

See my blog entry for some more details
http://blogs.msdn.com/michen/archive/2006/08/11/package-exec-location.aspx

Failed Login for a WorkGroup when attempting to run a query

Hi all,
I have an application which works with SQL Server. When the applicationattempts to load a page it needs to run certain queries. When itattempts to run a particular query it fails but I catch the exceptionand then log it. The following is logged:
System.Data.Ole.Db.OleDbException: Login failed for user 'CWAMB01DWH01\ASPENT'.
Now what I don't understand is that this is the work group that ASPNETis run under, and I am running other queries to the database via SQLServer authentication. Why am I getting a failed login for thisworkgroup? Do I need to create a new Login for the Server in SQLServer, and then create a new user for the database with the sameusername as the Workgroup name? If so, then how does the password workfor the SQL Server as the workgroup (CWAMB01DWH01\ASPENT) obviouslydoesn't have a password.
Thanks, and I hope I have explain my problem clear enough.
Tryst
There are two permissions in SQL Server database permissions in the database and server permissions you create under security in Enterprise Manager. Hope this helps.

Fail to Invoke Job from ASP .NET

Hi,

I have an ASP .NET page which invoke a SQL Agent Job. It works fine in debugging but when I run the web application in deplyment environnment it does not work. The Web Application is configured in Basic Authentication. The error message is not very detailed: "Invoke failed to job XXX".

I tried to add credentials information in my code using:

myServer.ConnectionContext.LoginSecure = false;
myServer.ConnectionContext.Login = "DOMAIN\Login";
myServer.ConnectionContext.Password = "pwd";

or

myServer.ConnectionContext.LoginSecure = false;
myServer.ConnectionContext.ConnectAsUserName = "DOMAIN\Login";
myServer.ConnectionContext.ConnectAsUserPassword = "pwd";

but it does not change anything.

Any help will be appreciated.

When you specify LoginSecure=false you're telling the server to use a SQL login, then you're supplying a domain login. You need to establish a SQL Login with the necessary credentials on the server in this circumstance.|||Hi,

if you want to impersonate the curretn thread with another security token than the actual it is running on (as you told the basic authentication) you will have to create an impersonation context, if you want to use the current security context your can just set the LoginSecure = true.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Extremely high number of pages/sec

Hello!
I have noticed very high number of page/sec in perfmon. It is averaging over
1000 pages/sec.I would like to isolate this issue to determine what process
is causing excessive pagination and possibly SQL server SPID. Is there an
easy way to troubleshoot the source of pagination?
Thanks,
Igor
First of all, I would like to rule out OS as primary suspect. Is there a way
to pinpoint SQL Server SPID as a source?
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:09f001c5146a$630ad3b0$a401280a@.phx.gbl...[vbcol=seagreen]
> You can run profiler, and include textdata, writes/reads
> columns in the trace. You'll be able to see what SPs/SQL
> commands have intensive io operations.
> OJ
> It is averaging over
> determine what process
> SPID. Is there an

Extremely High number of page/sec

Hello!
I have noticed very high number of page/sec in perfmon. It is averaging over
1000 pages/sec.I would like to isolate this issue to determine what process
is causing excessive pagination and possibly SQL server SPID. Is there an
easy way to troubleshoot the source of pagination?
Thanks,
Igor
Igor Marchenko wrote:
> Hello!
> I have noticed very high number of page/sec in perfmon. It is
> averaging over 1000 pages/sec.I would like to isolate this issue to
> determine what process is causing excessive pagination and possibly
> SQL server SPID. Is there an easy way to troubleshoot the source of
> pagination?
> Thanks,
> Igor
I should have added that high Read counts should be looked at as well.
David Gugick
Imceda Software
www.imceda.com
|||Igor Marchenko wrote:
> Hello!
> I have noticed very high number of page/sec in perfmon. It is
> averaging over 1000 pages/sec.I would like to isolate this issue to
> determine what process is causing excessive pagination and possibly
> SQL server SPID. Is there an easy way to troubleshoot the source of
> pagination?
> Thanks,
> Igor
Look for transactions with high CPU and/or high duration. You can use
Profiler for this or a number of SQL performance profiling tools (we
offer Coefficient).
David Gugick
Imceda Software
www.imceda.com

Extremely High number of page/sec

Hello!
I have noticed very high number of page/sec in perfmon. It is averaging over
1000 pages/sec.I would like to isolate this issue to determine what process
is causing excessive pagination and possibly SQL server SPID. Is there an
easy way to troubleshoot the source of pagination?
Thanks,
IgorIgor Marchenko wrote:
> Hello!
> I have noticed very high number of page/sec in perfmon. It is
> averaging over 1000 pages/sec.I would like to isolate this issue to
> determine what process is causing excessive pagination and possibly
> SQL server SPID. Is there an easy way to troubleshoot the source of
> pagination?
> Thanks,
> Igor
I should have added that high Read counts should be looked at as well.
--
David Gugick
Imceda Software
www.imceda.com|||Igor Marchenko wrote:
> Hello!
> I have noticed very high number of page/sec in perfmon. It is
> averaging over 1000 pages/sec.I would like to isolate this issue to
> determine what process is causing excessive pagination and possibly
> SQL server SPID. Is there an easy way to troubleshoot the source of
> pagination?
> Thanks,
> Igor
Look for transactions with high CPU and/or high duration. You can use
Profiler for this or a number of SQL performance profiling tools (we
offer Coefficient).
--
David Gugick
Imceda Software
www.imceda.com

Extremely High number of page/sec

Hello!
I have noticed very high number of page/sec in perfmon. It is averaging over
1000 pages/sec.I would like to isolate this issue to determine what process
is causing excessive pagination and possibly SQL server SPID. Is there an
easy way to troubleshoot the source of pagination?
Thanks,
IgorIgor Marchenko wrote:
> Hello!
> I have noticed very high number of page/sec in perfmon. It is
> averaging over 1000 pages/sec.I would like to isolate this issue to
> determine what process is causing excessive pagination and possibly
> SQL server SPID. Is there an easy way to troubleshoot the source of
> pagination?
> Thanks,
> Igor
I should have added that high Read counts should be looked at as well.
David Gugick
Imceda Software
www.imceda.com|||Igor Marchenko wrote:
> Hello!
> I have noticed very high number of page/sec in perfmon. It is
> averaging over 1000 pages/sec.I would like to isolate this issue to
> determine what process is causing excessive pagination and possibly
> SQL server SPID. Is there an easy way to troubleshoot the source of
> pagination?
> Thanks,
> Igor
Look for transactions with high CPU and/or high duration. You can use
Profiler for this or a number of SQL performance profiling tools (we
offer Coefficient).
David Gugick
Imceda Software
www.imceda.com