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

[nzkks]

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.

Imports Microsoft.VisualBasic
Imports ProjectTableAdapters
Imports System.Data
<System.ComponentModel.DataObject()> _
Public Class QueriesBLL
    Private _queriesAdapter As QueriesTableAdapter = Nothing
    Protected ReadOnly Property Adapter() As QueriesTableAdapter
        Get
            If _queriesAdapter Is Nothing Then
                _queriesAdapter = New QueriesTableAdapter()
            End If
            Return _queriesAdapter
        End Get
    End Property
 
<System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, True)> _
    Public Function AddProductFullDetails(ByVal categoryID As Nullable(Of Integer), ByVal description As String, ByVal isRound As Boolean, _
    ByVal productStory As String, ByVal materialID As Integer, ByVal isActive As Boolean, ByVal featureName As String, ByVal modifiedDate As DateTime) As Boolean
        Dim MyDataSet As New DataSet
        Dim productsTableAdapter As New ProductsTableAdapter()
        Dim featuresTableAdapter As New FeaturesTableAdapter()
        Dim productsfeaturesTableAdapter As New ProductsFeaturesTableAdapter()
        productsTableAdapter.Update(MyDataSet)
        featuresTableAdapter.Update(MyDataSet)
        productsfeaturesTableAdapter.Update(MyDataSet)
    End Function
 
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:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [Product].[AddProductFullDetails]
(
    @CategoryID int,
    @Description varchar(50),
    @IsRound bit,
    @ProductStory nvarchar(1000),
    @MaterialID int,
    @IsActive bit,
    @FeatureName varchar(500),
    @ModifiedDate smalldatetime,
    @ProductID int OUTPUT,
    @FeatureID int OUTPUT
)
AS
BEGIN TRANSACTION
SET @ModifiedDate = getdate()
SET NOCOUNT OFF;
INSERT INTO [Product].[Products] ([CategoryID], [Description], [IsRound], [Story], [MaterialID], [IsActive], [ModifiedDate]) VALUES (@CategoryID, @Description, @IsRound, @ProductStory, @MaterialID, @IsActive, @ModifiedDate)
SET @ProductID = SCOPE_IDENTITY();
IF @@ERROR <> 0
 BEGIN
    ROLLBACK
    RAISERROR ('Error in adding products.', 16, 1)
    RETURN
 END
INSERT INTO [Product].[Features] ([Name], [ModifiedDate]) VALUES (@FeatureName, @ModifiedDate)
SET @FeatureID = SCOPE_IDENTITY();
IF @@ERROR <> 0
 BEGIN
    ROLLBACK
    RAISERROR ('Error in adding features.', 16, 1)
    RETURN
 END
INSERT INTO [Product].[ProductsFeatures] SELECT @ProductID, @FeatureID, @ModifiedDate;
IF @@ERROR <> 0
 BEGIN
    ROLLBACK
    RAISERROR ('Error in adding features & Products IDs.', 16, 1)
    RETURN
 END
COMMIT

[Frinavale]

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.

[nzkks]

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.

[Frinavale]

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.

[nzkks]

Now I changed the VB code to:

    <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, True)> _
    Public Function AddProductFullDetails(ByVal categoryID As Nullable(Of Integer), ByVal description As String, ByVal isRound As Boolean, _
    ByVal productStory As String, ByVal materialID As Integer, ByVal isActive As Boolean, ByVal featureName As String, ByVal modifiedDate As DateTime) As Boolean
 
        Dim productsTableAdapter As New ProductsTableAdapter()
        Dim featuresTableAdapter As New FeaturesTableAdapter()
        Dim productsfeaturesTableAdapter As New ProductsFeaturesTableAdapter()
 
        Dim products As New Project1.ProductsDataTable()
        Dim product As Project1.ProductsRow = products.NewProductsRow()
 
        Dim features As New Project1.FeaturesDataTable()
        Dim feature As Project1.FeaturesRow = features.NewFeaturesRow()
 
        Dim productsfeatures As New Project1.ProductsFeaturesDataTable()
        Dim productfeature As Project1.ProductsFeaturesRow = productsfeatures.NewProductsFeaturesRow()
 
 
        products.AddProductsRow(product)
        productsTableAdapter.Update(products)
 
        features.AddFeaturesRow(feature)
        featuresTableAdapter.Update(features)
 
        productsfeatures.AddProductsFeaturesRow(productfeature)
        productsfeaturesTableAdapter.Update(productsfeatures)
    End Function
 

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

[nzkks]

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

Error below:

Column 'ProductID' does not allow nulls
 
Exception Details: System.Data.NoNullAllowedException: Column 'ProductID' does not allow nulls.
 
Line 9190:        <Global.System.Diagnostics.DebuggerNonUserCodeAttribute()>  _
Line 9191:        Public Overloads Sub AddProductsFeaturesRow(ByVal row As ProductsFeaturesRow)
Line 9192:            Me.Rows.Add(row)
Line 9193:        End Sub
 
Stack Trace:
 
[NoNullAllowedException: Column 'ProductID' does not allow nulls.]
   System.Data.DataColumn.CheckNullable(DataRow row) +2030879
   System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent) +133
   System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Int32 position, Boolean fireEvent, Exception& deferredException) +265
   System.Data.DataTable.InsertRow(DataRow row, Int32 proposedID, Int32 pos, Boolean fireEvent) +254
   System.Data.DataRowCollection.Add(DataRow row) +20
   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
   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
 
[TargetInvocationException: Exception has been thrown by the target of an invocation.]
   System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0
   System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +72
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +371
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29
   System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +480
   System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method) +38
   System.Web.UI.WebControls.ObjectDataSourceView.ExecuteInsert(IDictionary values) +867
   System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +72
   System.Web.UI.WebControls.FormView.HandleInsert(String commandArg, Boolean causesValidation) +388
   System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +602
   System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
   System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +109
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
   System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115
   System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +132
   System.Web.UI.WebControls.LinkButton.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) +177
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
 

[shiznit770]

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.