By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,589 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

Help needed to create BLL class for storeprocedure which updates multiple tables

P: 10
Hi I am using these: ASP.Net 2.0 with VB.Net, Visual Studio 2005, SQL Server 2005

I suspect, there is something missing in BLL class.

I created the ASP.Net form also and checked whether it is working or not. When I submit after entering the data, an error comes.

Unable to cast object of type 'System.Data.DataSet' to type 'Project1'.

Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Data.DataSet' to type 'Project1'.

Line 24: productsTableAdapter.Update(MyDataSet)
Please help me to solve this problem. Thanks

The details below:

I have created a store procedure to insert data to multiple tables. I have created QueriesTableAdapter in xsd file (DAL). Right now I created BLL class for the same. Below is the VB class.

Expand|Select|Wrap|Line Numbers
  1. Imports Microsoft.VisualBasic
  2. Imports ProjectTableAdapters
  3. Imports System.Data
  4. <System.ComponentModel.DataObject()> _
  5. Public Class QueriesBLL
  6.     Private _queriesAdapter As QueriesTableAdapter = Nothing
  7.     Protected ReadOnly Property Adapter() As QueriesTableAdapter
  8.         Get
  9.             If _queriesAdapter Is Nothing Then
  10.                 _queriesAdapter = New QueriesTableAdapter()
  11.             End If
  12.             Return _queriesAdapter
  13.         End Get
  14.     End Property
  15.  
  16. <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, True)> _
  17.     Public Function AddProductFullDetails(ByVal categoryID As Nullable(Of Integer), ByVal description As String, ByVal isRound As Boolean, _
  18.     ByVal productStory As String, ByVal materialID As Integer, ByVal isActive As Boolean, ByVal featureName As String, ByVal modifiedDate As DateTime) As Boolean
  19.         Dim MyDataSet As New DataSet
  20.         Dim productsTableAdapter As New ProductsTableAdapter()
  21.         Dim featuresTableAdapter As New FeaturesTableAdapter()
  22.         Dim productsfeaturesTableAdapter As New ProductsFeaturesTableAdapter()
  23.         productsTableAdapter.Update(MyDataSet)
  24.         featuresTableAdapter.Update(MyDataSet)
  25.         productsfeaturesTableAdapter.Update(MyDataSet)
  26.     End Function
  27.  
  28. End Class
I also created proper data tables and data adapters for all the tables with CRUD store procedures. Individually If I create a ASP.Net form each to perform CRUD operation per table, It works. But I wanted to combine inserting data to all related tables. In the above BLL VB class, three tables are involved.

Below is the SQL query:

Expand|Select|Wrap|Line Numbers
  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5. ALTER PROCEDURE [Product].[AddProductFullDetails]
  6. (
  7.     @CategoryID int,
  8.     @Description varchar(50),
  9.     @IsRound bit,
  10.     @ProductStory nvarchar(1000),
  11.     @MaterialID int,
  12.     @IsActive bit,
  13.     @FeatureName varchar(500),
  14.     @ModifiedDate smalldatetime,
  15.     @ProductID int OUTPUT,
  16.     @FeatureID int OUTPUT
  17. )
  18. AS
  19. BEGIN TRANSACTION
  20. SET @ModifiedDate = getdate()
  21. SET NOCOUNT OFF;
  22. INSERT INTO [Product].[Products] ([CategoryID], [Description], [IsRound], [Story], [MaterialID], [IsActive], [ModifiedDate]) VALUES (@CategoryID, @Description, @IsRound, @ProductStory, @MaterialID, @IsActive, @ModifiedDate)
  23. SET @ProductID = SCOPE_IDENTITY();
  24. IF @@ERROR <> 0
  25.  BEGIN
  26.     ROLLBACK
  27.     RAISERROR ('Error in adding products.', 16, 1)
  28.     RETURN
  29.  END
  30. INSERT INTO [Product].[Features] ([Name], [ModifiedDate]) VALUES (@FeatureName, @ModifiedDate)
  31. SET @FeatureID = SCOPE_IDENTITY();
  32. IF @@ERROR <> 0
  33.  BEGIN
  34.     ROLLBACK
  35.     RAISERROR ('Error in adding features.', 16, 1)
  36.     RETURN
  37.  END
  38. INSERT INTO [Product].[ProductsFeatures] SELECT @ProductID, @FeatureID, @ModifiedDate;
  39. IF @@ERROR <> 0
  40.  BEGIN
  41.     ROLLBACK
  42.     RAISERROR ('Error in adding features & Products IDs.', 16, 1)
  43.     RETURN
  44.  END
  45. COMMIT
May 12 '09 #1
Share this Question
Share on Google+
6 Replies


Frinavale
Expert Mod 5K+
P: 9,731
What is a BLL class? (What does BLL stand for?)

I would take a look at the Update() method that is a member of the ProductsTableAdapter class and see what it expects as a parameter.
May 12 '09 #2

P: 10
Sorry for the misunderstanding. BLL - Business Logic Layer. Any way, I am sure you know the 3-tier architechure (Data Access Layer(DAL) | BLL | Presentation Layer (ASP.Net pages + all supporting documents)). May be this is a Microsoft term?!

But I am very much sure you can solve this problem. OK the scenario below.

After I created the SQL tables + all data modification store procedures in SQL server 2005, In Visual studio 2005, I created the data tables and data adapters for each sql tables. Then I wrote all the VB classes for CRUD operations. In that way, I created the above VB class (I know something is missing but don't know what!) for the store procedure that supports inserting data into multiple tables.

Please help.
May 12 '09 #3

Frinavale
Expert Mod 5K+
P: 9,731
Sorry I'm a little slow today.
I usually don't have any database stuff in my business logic, that's what my data access layer does...so I was a little confused when you were talking about accessing database content in your business logic layer.

Anyways, could you please post the code for the Update() method that is a member of the ProductsTableAdapter class.
May 12 '09 #4

P: 10
Now I changed the VB code to:

Expand|Select|Wrap|Line Numbers
  1.     <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, True)> _
  2.     Public Function AddProductFullDetails(ByVal categoryID As Nullable(Of Integer), ByVal description As String, ByVal isRound As Boolean, _
  3.     ByVal productStory As String, ByVal materialID As Integer, ByVal isActive As Boolean, ByVal featureName As String, ByVal modifiedDate As DateTime) As Boolean
  4.  
  5.         Dim productsTableAdapter As New ProductsTableAdapter()
  6.         Dim featuresTableAdapter As New FeaturesTableAdapter()
  7.         Dim productsfeaturesTableAdapter As New ProductsFeaturesTableAdapter()
  8.  
  9.         Dim products As New Project1.ProductsDataTable()
  10.         Dim product As Project1.ProductsRow = products.NewProductsRow()
  11.  
  12.         Dim features As New Project1.FeaturesDataTable()
  13.         Dim feature As Project1.FeaturesRow = features.NewFeaturesRow()
  14.  
  15.         Dim productsfeatures As New Project1.ProductsFeaturesDataTable()
  16.         Dim productfeature As Project1.ProductsFeaturesRow = productsfeatures.NewProductsFeaturesRow()
  17.  
  18.  
  19.         products.AddProductsRow(product)
  20.         productsTableAdapter.Update(products)
  21.  
  22.         features.AddFeaturesRow(feature)
  23.         featuresTableAdapter.Update(features)
  24.  
  25.         productsfeatures.AddProductsFeaturesRow(productfeature)
  26.         productsfeaturesTableAdapter.Update(productsfeatures)
  27.     End Function
  28.  
Now it seems to work. But because of some other error, it is not inserting. But I will update you the status. Please be with me. Thanks
May 12 '09 #5

P: 10
I couldn't find what is wrong and where by seeing the exception details. Somebody please help.

Error below:

Expand|Select|Wrap|Line Numbers
  1. Column 'ProductID' does not allow nulls
  2.  
  3. Exception Details: System.Data.NoNullAllowedException: Column 'ProductID' does not allow nulls.
  4.  
  5. Line 9190:        <Global.System.Diagnostics.DebuggerNonUserCodeAttribute()>  _
  6. Line 9191:        Public Overloads Sub AddProductsFeaturesRow(ByVal row As ProductsFeaturesRow)
  7. Line 9192:            Me.Rows.Add(row)
  8. Line 9193:        End Sub
  9.  
  10. Stack Trace:
  11.  
  12. [NoNullAllowedException: Column 'ProductID' does not allow nulls.]
  13.    System.Data.DataColumn.CheckNullable(DataRow row) +2030879
  14.    System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent) +133
  15.    System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Int32 position, Boolean fireEvent, Exception& deferredException) +265
  16.    System.Data.DataTable.InsertRow(DataRow row, Int32 proposedID, Int32 pos, Boolean fireEvent) +254
  17.    System.Data.DataRowCollection.Add(DataRow row) +20
  18.    ProductsFeaturesDataTable.AddProductsFeaturesRow(ProductsFeaturesRow row) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\Project1\7ad4c5d5\4bdb9089\App_Code.4ca2yx3k.7.vb:9192
  19.    QueriesBLL.AddProductFullDetails(Nullable`1 categoryID, String description, Boolean isRound, String productStory, Nullable`1 materialID, Boolean isActive, String featureName, DateTime modifiedDate) in C:\Project Websites\Project1\App_Code\BLL\QueriesBLL.vb:41
  20.  
  21. [TargetInvocationException: Exception has been thrown by the target of an invocation.]
  22.    System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0
  23.    System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +72
  24.    System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +371
  25.    System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29
  26.    System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +480
  27.    System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method) +38
  28.    System.Web.UI.WebControls.ObjectDataSourceView.ExecuteInsert(IDictionary values) +867
  29.    System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +72
  30.    System.Web.UI.WebControls.FormView.HandleInsert(String commandArg, Boolean causesValidation) +388
  31.    System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +602
  32.    System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95
  33.    System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
  34.    System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +109
  35.    System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
  36.    System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115
  37.    System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +132
  38.    System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
  39.    System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
  40.    System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +177
  41.    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
  42.  
May 13 '09 #6

P: 10
Looks like you're trying to insert into the products table without a ProductID and you have the column set to not allow Nulls. You need to set the column to auto increment or specify a value for it if you're going to not allow Nulls.
May 13 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.