Data Access Layer - Sql 2005 - Error in code

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

I'm a new bee to Visual Studio and writing an ERP for my Co.
Refering to a book, I'm preparing the code of DATA ACCESS LAYER (four layer architecture), but it gives error as given below.

Please help me to correct the code if time permits you to do so. Thanks.

01. One Namespace DataService.
02. Two classes DataServer & GeneralParameters.
03. Two additional functions: ConvertToSqlParameters & makeclear.

Errors:
01. privateParameterList is not declared (Error in makeclear function & Sub BuildParameter of General Parameter)
02. privateExceptionMessage is not declared (Error in makeclear function)
03. SQLDataType is not declared (Error in Sub BuildParameter of General Parameter)
04. ConvertToSqlParameters is not declared (in function runTransaction of DataServer)

VB

'myVbDataAccessLayer
'Import ADO.net namespaces
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Xml
Imports System.Collections
Imports System.Diagnostics


'Declare the namespace DataService and the Class DataServer
Namespace DataService
    Public Class DataServer

        Private myConnectionString As String
        Private myConnection As SqlConnection
        Private myCommand As SqlCommand
        Private myDataReader As SqlDataReader
        Private myXmlReader As XmlReader
        Private mySqlDataAdapter As SqlDataAdapter
        Private myDataSet As DataSet
        'The variable PrivateParameterList holds parameters which are passed from the Business Layer
        Private privateParameterList As ArrayList = New ArrayList()
        Private Const privateExceptionMessage As String = "Data Application Error"

        'SqlDataType (Enum)

        'constructor of DataServer class
        Public Sub New(ByVal ConnectionString As String)
            myConnectionString = ConnectionString
        End Sub

        'class methods in the DataServer class

        Public Function returnDataSet(ByVal SPName As String, Optional ByVal TableName As String = Nothing) As DataSet
            'Accepts a stored procedure and a table name and returns a dataset.
            Dim privateUsedParameter As GeneralParameter
            Dim privateParameter As SqlParameter
            Dim usedEnumerator As IEnumerator = privateParameterList.GetEnumerator()

            Try
                myConnection = New SqlConnection(myConnectionString)
                Dim PrivateDataSet As New DataSet()
                myCommand = New SqlCommand(SPName, myConnection)
                myCommand.CommandType = CommandType.StoredProcedure

                Do While (usedEnumerator.MoveNext())
                    privateUsedParameter = Nothing
                    privateUsedParameter = usedEnumerator.Current
                    privateParameter = ConvertToSqlParameters(privateUsedParameter)
                    myCommand.Parameters.Add(privateParameter)
                Loop

                mySqlDataAdapter = New SqlDataAdapter(myCommand)
                If TableName = Nothing Then
                    mySqlDataAdapter.Fill(PrivateDataSet)
                Else
                    mySqlDataAdapter.Fill(PrivateDataSet, TableName)
                End If

                Return PrivateDataSet

            Catch ExceptionObject As Exception
                Throw New Exception(privateExceptionMessage, ExceptionObject)
            Finally
                myConnection.Close()

            End Try


        End Function



        Public Function runTransaction(ByVal SPName As String) As ArrayList

            Dim privateUsedParameter As GeneralParameter
            Dim privateParameter As SqlParameter
            Dim usedEnumerator As IEnumerator = privateParameterList.GetEnumerator()
            Dim outputParameters As New ArrayList()
            Dim privateParameterOut As SqlParameter
            Dim privateConnection As SqlConnection   '************ additionally added
            Dim privateCommand As New SqlCommand    '************ additionally added
            Dim privateConnectionString As String  '************ additionally added


            Try
                privateConnection = New SqlConnection(privateConnectionString)
                privateCommand = New SqlCommand(SPName, privateConnection)
                privateCommand.CommandType = CommandType.StoredProcedure

                Do While (usedEnumerator.MoveNext())
                    privateUsedParameter = Nothing
                    privateUsedParameter = usedEnumerator.Current
                    privateParameter = convertToSqlParameters(privateUsedParameter)
                    privateCommand.parameters.add(privateParameter)
                Loop

                privateConnection.open()
                privateCommand.ExecuteNonQuery()

                For Each privateParameterOut In privateCommand.parameters
                    If privateParameterOut.Direction = ParameterDirection.Output Or privateParameterOut.Direction = ParameterDirection.ReturnValue Then outputParameters.Add(privateParameterOut.Value)
                    'End If
                Next
                Return outputParameters

            Catch ExceptionObject As Exception
                Throw New Exception(privateExceptionMessage, ExceptionObject)
            Finally
                privateConnection.close()
            End Try

        End Function


        'Public Function returnArrayList(ByVal SPName As String) As ArrayList
        'End Function
    End Class


    '*******************
    'General Parameter Class begins

    Public Class GeneralParameter
        Public Name As String
        Public value As Object
        Public DataType As SqlDataType
        Public Size As Integer
        Public DirectionUsed As ParameterDirection



        Sub New(ByVal pName As String, _
            Optional ByVal pValue As Object = Nothing, _
            Optional ByVal pSqlType As SqlDataType = Nothing, _
            Optional ByVal pSize As Integer = Nothing, _
            Optional ByVal pDirection As ParameterDirection = ParameterDirection.Input)

            Name = pName
            value = pValue
            DataType = pSqlType
            Size = pSize
            DirectionUsed = pDirection

        End Sub


        Public Sub BuildParameter(ByVal ParameterName As String, _
                                  Optional ByVal Value As Object = Nothing, _
                                  Optional ByVal SQLType As SQLDataType = Nothing, _
                                  Optional ByVal Size As Integer = Nothing, _
                                  Optional ByVal Direction As ParameterDirection = ParameterDirection.Input)
            Dim buildDataType As SqlDbType
            Dim buildParameter As GeneralParameter = Nothing

            Select Case SQLType
                Case SQLDataType.SqlString
                    buildDataType = SqlDbType.VarChar
                Case SqlDataType.SqlChar
                    buildDataType = SqlDbType.Char
                Case SQLDataType.SqlInt
                    buildDataType = SqlDbType.Int
                Case SQLDataType.SqlBit
                    buildDataType = SqlDbType.Bit
                Case SQLDataType.SqlDateTime
                    buildDataType = SqlDbType.DateTime
                Case SQLDataType.SqlDecimal
                    buildDataType = SqlDbType.Decimal
                Case SQLDataType.SqlMoney
                    buildDataType = SqlDbType.Money
                Case SQLDataType.SqlImage
                    buildDataType = SqlDbType.Image
            End Select

            buildParameter = New GeneralParameter(ParameterName, Value, buildDataType, Size, Direction)
            privateParameterList.Add(buildParameter)

        End Sub



        Private Function ConvertToSqlParameters(ByVal passedParameter As GeneralParameter) As SqlParameter
            Dim returnSqlParameter As SqlParameter = New SqlParameter()
            returnSqlParameter.ParameterName = passedParameter.Name
            returnSqlParameter.Value = passedParameter.value
            returnSqlParameter.SqlDbType = passedParameter.DataType
            returnSqlParameter.Size = passedParameter.Size
            returnSqlParameter.Direction = passedParameter.DirectionUsed
            Return returnSqlParameter
        End Function

        Public Sub makeClear()
            Try
                privateParameterList.Clear()
            Catch parameterException As Exception
                Throw New Exception(privateExceptionMessage &amp; "Parameter List did not clear", parameterException)
            End Try
        End Sub


    End Class

End Namespace

Posted 28-Dec-10 21:28pm

kumarsajith01

Updated 9-Jan-11 21:35pm

Dalek Dave

v3

Add a Solution

Comments

Sandeep Mewara 29-Dec-10 3:52am

Use PRE tags to format code part. It makes the question readable.

Dalek Dave 10-Jan-11 3:35am

Edited for Readability.

4 solutions

Solution 4

Hi!

When I try to call BUILDPARAMETER (from another namespace & class), it says the same is not a member of DataService.DataServer. Could you correct the code.

*******************************

Imports myVbDataAccessLayer.DataService

Namespace BusinessObjects

---------
---------
---------
---------
---------
---------
---------
---------

VB

Public Function GetAccountDetail(ByVal cat As String) As DataSet
         Dim localDSOutput As DataSet
         Dim ParamsStoredProcedure As String = "GetAccountByCat"
         Try
             Dim localOutPutServer As New DataServer(PrivateConnectionString)
             localOutPutServer.BUILDPARAMETER( _
             "@AccCat", cat, SqlDataType.SqlChar, , ParameterDirection.Input)
             localDSOutput = localOutPutServer.returnDataSet(paramsStoredProcedure)
             Return localDSOutput
         Catch exceptionObject As Exception
         Finally
         End Try
     End Function

End Class

End Namespace

Posted 9-Jan-11 20:39pm

kumarsajith01

Comments

Dalek Dave 10-Jan-11 3:36am

Good Call.

Solution 3

Dear vivekse & jerrykid,

Thank you very much for the guidance. I'll go through the code provided by jerrykid. Thanks.

One more question pls. See the following method (adapted from the code pasted earlier) - Is it OK if I declare SQLDataType as ENUM containing types: varChar, char, int, bit, datetime, decimal, money, and image?

**************

Public Sub BuildParameter(ByVal ParameterName As String, _
                                  Optional ByVal Value As Object = Nothing, _
                                  Optional ByVal SQLType As SQLDataType = Nothing, _
                                  Optional ByVal Size As Integer = Nothing, _
                                  Optional ByVal Direction As ParameterDirection = ParameterDirection.Input)
            Dim buildDataType As SqlDbType
            Dim buildParameter As GeneralParameter = Nothing

            Select Case SQLType
                Case SQLDataType.SqlString
                    buildDataType = SqlDbType.VarChar
                Case SqlDataType.SqlChar
                    buildDataType = SqlDbType.Char
                Case SQLDataType.SqlInt
                    buildDataType = SqlDbType.Int
                Case SQLDataType.SqlBit
                    buildDataType = SqlDbType.Bit
                Case SQLDataType.SqlDateTime
                    buildDataType = SqlDbType.DateTime
                Case SQLDataType.SqlDecimal
                    buildDataType = SqlDbType.Decimal
                Case SQLDataType.SqlMoney
                    buildDataType = SqlDbType.Money
                Case SQLDataType.SqlImage
                    buildDataType = SqlDbType.Image
            End Select

            buildParameter = New GeneralParameter(ParameterName, Value, buildDataType, Size, Direction)
            privateParameterList.Add(buildParameter)

        End Sub

Posted 30-Dec-10 0:44am

kumarsajith01

Updated 30-Dec-10 19:46pm

v3

Comments

jerrykid 30-Dec-10 7:07am

You can define any ENUM which you like, but in a Class/Function, you must specify correctly data type to use it :)

TweakBird 31-Dec-10 1:46am

Add <pre> tag for your code blocks.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

vivekse · Accepted Answer · 2010-12-28T22:13:00

Hi,

1. privateParameterList is not declared (Error in makeclear function & Sub BuildParameter of General Parameter)

sol:- privateParameterList is declared in class DataServer and trying to use in method of GeneralParameter class.

2:- privateExceptionMessage is not declared (Error in makeclear function)

Sol: same problem of scope as discusses above.

3. SqlDataType is not declare in your code.

4. ConvertToSqlParameters is not declared in class DataServer .

I thik you should refer this link.

Object Oriented Programming In VB.NET

jerrykid · Accepted Answer · 2010-12-28T22:23:00

Hi,

I try to make for codes to fix bugs, and you must verify the logics inside.

VB

'myVbDataAccessLayer
'Import ADO.net namespaces
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Xml
Imports System.Collections
Imports System.Diagnostics


'Declare the namespace DataService and the Class DataServer
Namespace DataService
    Public Class DataServer

        Private myConnectionString As String
        Private myConnection As SqlConnection
        Private myCommand As SqlCommand
        Private myDataReader As SqlDataReader
        Private myXmlReader As XmlReader
        Private mySqlDataAdapter As SqlDataAdapter
        Private myDataSet As DataSet
        'The variable PrivateParameterList holds parameters which are passed from the Business Layer
        Private privateParameterList As ArrayList = New ArrayList()
        Private Const privateExceptionMessage As String = "Data Application Error"

        'SqlDataType (Enum)

        'constructor of DataServer class
        Public Sub New(ByVal ConnectionString As String)
            myConnectionString = ConnectionString
        End Sub

        'class methods in the DataServer class

        Public Function returnDataSet(ByVal SPName As String, Optional ByVal TableName As String = Nothing) As DataSet
            'Accepts a stored procedure and a table name and returns a dataset.
            Dim privateUsedParameter As GeneralParameter
            Dim privateParameter As SqlParameter
            Dim usedEnumerator As IEnumerator = privateParameterList.GetEnumerator()

            Try
                myConnection = New SqlConnection(myConnectionString)
                Dim PrivateDataSet As New DataSet()
                myCommand = New SqlCommand(SPName, myConnection)
                myCommand.CommandType = CommandType.StoredProcedure

                Do While (usedEnumerator.MoveNext())
                    privateUsedParameter = Nothing
                    privateUsedParameter = usedEnumerator.Current
                    privateParameter = GeneralParameter.ConvertToSqlParameters(privateUsedParameter)
                    myCommand.Parameters.Add(privateParameter)
                Loop

                mySqlDataAdapter = New SqlDataAdapter(myCommand)
                If TableName = Nothing Then
                    mySqlDataAdapter.Fill(PrivateDataSet)
                Else
                    mySqlDataAdapter.Fill(PrivateDataSet, TableName)
                End If

                Return PrivateDataSet

            Catch ExceptionObject As Exception
                Throw New Exception(privateExceptionMessage, ExceptionObject)
            Finally
                myConnection.Close()

            End Try


        End Function



        Public Function runTransaction(ByVal SPName As String) As ArrayList

            Dim privateUsedParameter As GeneralParameter
            Dim privateParameter As SqlParameter
            Dim usedEnumerator As IEnumerator = privateParameterList.GetEnumerator()
            Dim outputParameters As New ArrayList()
            Dim privateParameterOut As SqlParameter
            Dim privateConnection As SqlConnection   '************ additionally added
            Dim privateCommand As New SqlCommand    '************ additionally added
            Dim privateConnectionString As String  '************ additionally added


            Try
                privateConnection = New SqlConnection(privateConnectionString)
                privateCommand = New SqlCommand(SPName, privateConnection)
                privateCommand.CommandType = CommandType.StoredProcedure

                Do While (usedEnumerator.MoveNext())
                    privateUsedParameter = Nothing
                    privateUsedParameter = usedEnumerator.Current
                    privateParameter = GeneralParameter.ConvertToSqlParameters(privateUsedParameter)
                    privateCommand.parameters.add(privateParameter)
                Loop

                privateConnection.open()
                privateCommand.ExecuteNonQuery()

                For Each privateParameterOut In privateCommand.parameters
                    If privateParameterOut.Direction = ParameterDirection.Output Or privateParameterOut.Direction = ParameterDirection.ReturnValue Then outputParameters.Add(privateParameterOut.Value)
                    'End If
                Next
                Return outputParameters

            Catch ExceptionObject As Exception
                Throw New Exception(privateExceptionMessage, ExceptionObject)
            Finally
                privateConnection.close()
            End Try

        End Function


        'Public Function returnArrayList(ByVal SPName As String) As ArrayList
        'End Function
    End Class


    '*******************
    'General Parameter Class begins

    Public Class GeneralParameter
        Public Name As String
        Public value As Object
        Public DataType As SqlDataType
        Public Size As Integer
        Public DirectionUsed As ParameterDirection
        Private privateParameterList As ArrayList = New ArrayList()
        Private Const privateExceptionMessage As String = "Data Application Error"

        Sub New(ByVal pName As String, _
            Optional ByVal pValue As Object = Nothing, _
            Optional ByVal pSqlType As SqlDataType = Nothing, _
            Optional ByVal pSize As Integer = Nothing, _
            Optional ByVal pDirection As ParameterDirection = ParameterDirection.Input)

            Name = pName
            value = pValue
            DataType = pSqlType
            Size = pSize
            DirectionUsed = pDirection

        End Sub


        Public Sub BuildParameter(ByVal ParameterName As String, _
                                  Optional ByVal Value As Object = Nothing, _
                                  Optional ByVal SQLType As SQLDataType = Nothing, _
                                  Optional ByVal Size As Integer = Nothing, _
                                  Optional ByVal Direction As ParameterDirection = ParameterDirection.Input)
            Dim buildDataType As SqlDbType
            Dim buildParameter As GeneralParameter = Nothing

            Select Case SQLType
                Case SQLDataType.SqlString
                    buildDataType = SqlDbType.VarChar
                Case SqlDataType.SqlChar
                    buildDataType = SqlDbType.Char
                Case SQLDataType.SqlInt
                    buildDataType = SqlDbType.Int
                Case SQLDataType.SqlBit
                    buildDataType = SqlDbType.Bit
                Case SQLDataType.SqlDateTime
                    buildDataType = SqlDbType.DateTime
                Case SQLDataType.SqlDecimal
                    buildDataType = SqlDbType.Decimal
                Case SQLDataType.SqlMoney
                    buildDataType = SqlDbType.Money
                Case SQLDataType.SqlImage
                    buildDataType = SqlDbType.Image
            End Select

            buildParameter = New GeneralParameter(ParameterName, Value, buildDataType, Size, Direction)
            privateParameterList.Add(buildParameter)

        End Sub

        Public Shared Function ConvertToSqlParameters(ByVal passedParameter As GeneralParameter) As SqlParameter
            Dim returnSqlParameter As SqlParameter = New SqlParameter()
            returnSqlParameter.ParameterName = passedParameter.Name
            returnSqlParameter.Value = passedParameter.value
            returnSqlParameter.SqlDbType = passedParameter.DataType
            returnSqlParameter.Size = passedParameter.Size
            returnSqlParameter.Direction = passedParameter.DirectionUsed
            Return returnSqlParameter
        End Function

        Public Sub makeClear()
            Try
                privateParameterList.Clear()
            Catch parameterException As Exception
                Throw New Exception(privateExceptionMessage & "Parameter List did not clear", parameterException)
            End Try
        End Sub


    End Class

End Namespace

Data Access Layer - Sql 2005 - Error in code

4 solutions

Solution 1

Solution 2

Solution 4

Solution 3

Add your solution here

Preview 0