none
update command to sql server

    Question

  • hi,

    this is the first time i try to update a database Table that has just 1 row

    something is wrong and I cannot find the solution (maybe a sintax error)

    I have a function that prepare the update query

    this is the code

    Public Sub SaveAttivita(ByVal A As Object)
    		Query = <sql>
    			"UPDATE Tabelle.[Attivita] 
    				SET  [RagioneSociale] = @RagSoc
    					,[RagioneSociale1] = @RagSoc1 
    					,[Indirizzo] = @Ind
    					,[NumeroCivico] = @Nc
    				WHERE [AttivitaID] = A.AttivitaID"
    				</sql>.Value
    
    		Dim Params(3) As SqlParameter
    		Params(0) = New SqlParameter("@RagSoc", "companyName")
    		Params(1) = New SqlParameter("@Ragsoc1", "companuname1")
    		Params(2) = New SqlParameter("@Ind", "address")
    		Params(3) = New SqlParameter("@Nc", "number")
    		DBO.SaveAttivita(Query, Params)
    	End Sub

    then i have the function DBO.SaveAttivita that should update the database

    Public Sub SaveAttivita(ByVal Query As String, ByVal params() As SqlParameter)
    		Using CONN As New SqlConnection With {
    				.ConnectionString = VG.FullCS}
    			Using CMD As New SqlCommand With {.Connection = CONN}
    				CMD.CommandText = Query
    				For i As Integer = 0 To params.Length - 1
    					If params(i).Value Is Nothing Then
    						params(i).Value = DBNull.Value
    					End If
    					CMD.Parameters.Add(params(i))
    				Next
    				Try
    					CONN.Open()
    					CMD.ExecuteNonQuery()
    				Catch ex As Exception
    					MessageBox.Show("Errore salvataggio Achivio")
    				End Try
    			End Using
    		End Using
    	End Sub

    everything seems rigth but I get Exception for ExecuteNonQuery

    The connection String is right (I used it to insert the first row)

    The field A:AttivitaID is 1  (I have checked it)

    So where is the mistake ??

    Help help help  ( 2 hours already spent to find it)

    Claudio

    Tuesday, January 02, 2018 10:01 PM

Answers

  • Hang in there I putting a demo together. When all is said and done, the last section of my reply is the reason for the update statement to fail so with that you should use a parameter for the WHERE condition as all should work but I also suggest using the path I laid out to keep things simple.

    Okay first off you should keep things simple easy to read, there is no reason to pass parameters to the method as it's confusing. Reuse is overrated.

    Public Class Operations
        Public LastException As Exception
        Public Property ConnectionString As String
    
        Public Function UpdateRecord(
            ByVal pRagSoc As String,
            ByVal pRagsoc1 As String,
            ByVal pInd As String,
            ByVal pNc As Integer,
            ByVal pAttivitaID As Integer) As Boolean
    
            LastException = Nothing
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = <sql>
    			        UPDATE Tabelle.[Attivita] 
    				        SET  [RagioneSociale] = @RagSoc
    					        ,[RagioneSociale1] = @RagSoc1 
    					        ,[Indirizzo] = @Ind
    					        ,[NumeroCivico] = @Nc
    				        WHERE [AttivitaID] = @AttivitaID
    				</sql>.Value
    
                    cmd.Parameters.AddWithValue("@RagSoc", pRagSoc)
                    cmd.Parameters.AddWithValue("@RagSoc1", pRagsoc1)
                    cmd.Parameters.AddWithValue("@pInd", pInd)
                    cmd.Parameters.AddWithValue("@Nc", pNc)
    
                    Try
                        cn.Open()
    
                        cmd.ExecuteNonQuery()
    
                        Return True
    
                    Catch ex As Exception
                        LastException = ex
                        Return False
                    End Try
    
                End Using
    
            End Using
        End Function
    End Class
     

    Next up, no quotes are needed as <SQL> construct delivers a string to the back end database, something I came up with years ago,

    Last but not least, this variable can not be evaluated as is, it needs to be a parameter


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites





    Tuesday, January 02, 2018 11:12 PM
    Moderator
  • the question is

    haw can I control the type of passed parameters ?

    i cannot use AddWithValue

    • Marked as answer by Claudio111 Wednesday, January 03, 2018 11:46 AM
    Wednesday, January 03, 2018 9:00 AM
  • In the data layer project, in a class say called Customer. The Customer class only (at least for this discussion) properties which represent fields in the backend database, that is it. The business layer uses the Customer class but knows nothing of the actual class containing data operations. Alternatively you could have a class project with just classes which represent the data in your database yet that way it's possible to get into circular reference issues unless careful, an entirely new bag of worms per-say. 

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Claudio111 Thursday, January 25, 2018 3:43 PM
    Wednesday, January 03, 2018 4:24 PM
    Moderator

All replies

  • Hang in there I putting a demo together. When all is said and done, the last section of my reply is the reason for the update statement to fail so with that you should use a parameter for the WHERE condition as all should work but I also suggest using the path I laid out to keep things simple.

    Okay first off you should keep things simple easy to read, there is no reason to pass parameters to the method as it's confusing. Reuse is overrated.

    Public Class Operations
        Public LastException As Exception
        Public Property ConnectionString As String
    
        Public Function UpdateRecord(
            ByVal pRagSoc As String,
            ByVal pRagsoc1 As String,
            ByVal pInd As String,
            ByVal pNc As Integer,
            ByVal pAttivitaID As Integer) As Boolean
    
            LastException = Nothing
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = <sql>
    			        UPDATE Tabelle.[Attivita] 
    				        SET  [RagioneSociale] = @RagSoc
    					        ,[RagioneSociale1] = @RagSoc1 
    					        ,[Indirizzo] = @Ind
    					        ,[NumeroCivico] = @Nc
    				        WHERE [AttivitaID] = @AttivitaID
    				</sql>.Value
    
                    cmd.Parameters.AddWithValue("@RagSoc", pRagSoc)
                    cmd.Parameters.AddWithValue("@RagSoc1", pRagsoc1)
                    cmd.Parameters.AddWithValue("@pInd", pInd)
                    cmd.Parameters.AddWithValue("@Nc", pNc)
    
                    Try
                        cn.Open()
    
                        cmd.ExecuteNonQuery()
    
                        Return True
    
                    Catch ex As Exception
                        LastException = ex
                        Return False
                    End Try
    
                End Using
    
            End Using
        End Function
    End Class
     

    Next up, no quotes are needed as <SQL> construct delivers a string to the back end database, something I came up with years ago,

    Last but not least, this variable can not be evaluated as is, it needs to be a parameter


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites





    Tuesday, January 02, 2018 11:12 PM
    Moderator
  • Hi Claudio111,

    I have tested your code at my side, and I have no error, please tell me that what error you encounter?

    May you need to name the type of parameter,

    params(0) = New SqlParameter("@Name", SqlDbType.Char)
                    params(0).Value = "DDD"

    Or you can listen to Kareninstructor's advice, using SqlParameterCollection.AddWithValue Method, AddWithValue will derive the type of the parameter of it's value, so ensure that it's the correct type. like this:

    https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue(v=vs.110).aspx

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Claudio111 Wednesday, January 03, 2018 10:04 AM
    • Unmarked as answer by Claudio111 Wednesday, January 03, 2018 3:21 PM
    Wednesday, January 03, 2018 2:54 AM
    Moderator
  • there were several mistakes in my code

    Now i used this solution but i have another question

    I still use two different functions to Update in order to have the DB Operation separate from DataAccess Layer Class ( In this way i can use the DB Operation  in different Forms without to write it again

    this is the code for DAL class

    	'DAL Class
    	Public Sub SaveAttivita(
    		ByVal pAttivitaID As Integer,
    		ByVal pRagSoc As String,
    		ByVal pRagsoc1 As String,
    		ByVal pInd As String,
    		ByVal pNc As String)
    
    		Query = <sql>
    			        UPDATE Tabelle.[Attivita] 
    				        SET  [RagioneSociale] = @RagSoc
    					        ,[RagioneSociale1] = @RagSoc1 
    					        ,[Indirizzo] = @Ind
    					        
    				        WHERE [AttivitaID] = @AttivitaID
    				</sql>.Value
    
    		Dim param As New List(Of SqlParameter)
    		param.Add(New SqlParameter("@AttivitaID", pAttivitaID))
    		param.Add(New SqlParameter("@RagSoc", pRagSoc))
    		param.Add(New SqlParameter("@RagSoc1", pRagsoc1))
    		param.Add(New SqlParameter("@Ind", pInd))
    
    		DBO.SaveAttivita(Query, param)
    
    
    	End Sub

    and thisiis the code for DB Operation Class

    'DBO class
    	Public Sub SaveAttivita(ByVal query As String, ByVal param As List(Of SqlParameter))
    		Using CONN As New SqlConnection With {
    			.ConnectionString = VG.FullCS}
    			Using CMD As New SqlCommand With {.Connection = CONN}
    				CMD.CommandType = CommandType.Text
    				CMD.CommandText = query
    				If (param IsNot Nothing) Then
    					For Each p In param
    						CMD.Parameters.Add(p)
    					Next
    				End If
    				Try
    					CONN.Open()
    					CMD.ExecuteNonQuery()
    				Catch ex As Exception
    					lastex = ex
    					MessageBox.Show("Errore salvataggio Achivio")
    					
    				End Try
    			End Using
    		End Using
    	End Sub
    

    Wednesday, January 03, 2018 8:58 AM
  • the question is

    haw can I control the type of passed parameters ?

    i cannot use AddWithValue

    • Marked as answer by Claudio111 Wednesday, January 03, 2018 11:46 AM
    Wednesday, January 03, 2018 9:00 AM
  • Hi Claudio111,

    If you want to control the type of parameters, you can take a look the following code:

     Dim params As New List(Of SqlParameter)
            params.Add(New SqlParameter("@AttivitaID", SqlDbType.Int))
            params(0).Value = pAttivitaID
            params.Add(New SqlParameter("@RagSoc", SqlDbType.NVarChar))
            params(1).Value = pRagSoc
            params.Add(New SqlParameter("@RagSoc1", SqlDbType.NVarChar))
            params(2).Value = pRagsoc1
            params.Add(New SqlParameter("@Ind", SqlDbType.NVarChar))
            params(3).Value = pInd

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Claudio111 Wednesday, January 03, 2018 10:04 AM
    • Unmarked as answer by Claudio111 Wednesday, January 03, 2018 3:20 PM
    Wednesday, January 03, 2018 9:17 AM
    Moderator
  • Thank you Karen, thank you Cherry

    everything works now ;.)))

    Wednesday, January 03, 2018 10:06 AM
  • the question is

    haw can I control the type of passed parameters ?

    i cannot use AddWithValue

    I have never seen a situation where you can't use AddWithValue. 

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, January 03, 2018 10:23 AM
    Moderator
  • I truly don't get your thinking on how you are separating your code. Both code blocks shown should be in the same class. I'm not going to waste my time with you on this as I gave you sound logic in the beginning yet get no recognition for assisting you while the really only thing that you have different is the Add vis AddWithValue where AddWithValue is the preferred method to use unless you were doing many inserts or updates at once then Add is the recommended way to go.

    Separation of logic is user interface, business logic, database operations.

      

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, January 03, 2018 10:30 AM
    Moderator
  • Karen I appreciate very much your help, and with it I solved all the problems I had

    Your replay show me my syntax mistakes and I solved the problem.

    About the Locic separation  , for now I prefer separate DAL to DB Operations only because i dont know way to put them in the same class. The code for me is much more clear but YOU are the EXPERT so please tell me the reasons to put them together. Is  the application faster at runtime ? Or something else? what kind of problem may I get in the future if I keep them separated ?

    I'm very grateful to you every time

    Claudio


    Wednesday, January 03, 2018 11:45 AM
  • Separation of concerns in this case, anything to do with user interface lives in the form or forms of the application. Business logic and validation lives in classes where the user interface passes information too this. Super simple example of business logic means you can't add a new customer without specific fields being populated for instance. If the information is there is populate a new customer the business layer passes the information to the database layer. Note there in the business layer knows nothing about who passed the information to it and when the business layer passes information to the data layer the data layer has no clue who passed the information in.

    If something goes wrong in the data layer it may throw a catchable exception back to the caller and the caller would pass the exception back to it's caller.

    What I have seen in your code is only the data layer, no business layer at all. what you have done is split the data operation into two method calls which only serves to make maintenance difficult in the future.

    Super simple example all parts shown together but in a real app we would have a class project for business logic and another class project for data operations. So you would in total in this example have three projects, one for user interface, a business logic project and a data project.

    Imports System.Data.SqlClient
    
    ''' <summary>
    ''' This would be in the business layer, in a class, not a code module,
    ''' it's only here to show how the add method could be called. There
    ''' are other ways too e.g. rather than passing in string values, we
    ''' could have used a Customer class and constructed a new customer
    ''' and passed the new customer to the add method
    ''' </summary>
    Public Module Example
        Public Sub Add()
            '
            ' Here we would do validation e.g. do all fields have values,
            ' are values acceptable for business rules e.g. is Owner
            ' a valid option for contact title etc.
            '
            Dim ops As New SqlServerDataOperations
            Dim newId As Integer = 0
            If Not ops.AddNewCustomer("My company", "Karen", "Owner", newId) Then
                If ops.HasException Then
                    MessageBox.Show(ops.LastException.Message)
                End If
            End If
        End Sub
    
    End Module
    Public Class SqlServerDataOperations
        Inherits BaseExeptions
    
        Private mConnectionString As String = "Your connection string goes here"
        Private InsertStatement As String =
            <SQL>
                INSERT INTO Customer 
                (CompanyName,ContactName,ContactTitle) 
                VALUES (@CompanyName,@ContactName,@ContactTitle); 
                SELECT CAST(scope_identity() AS int);
            </SQL>.Value
    
        Private UpdateStatement As String =
            <SQL>
                UPDATE Customer 
                SET CompanyName = @CompanyName, 
                ContactName = @ContactName,
                ContactTitle = @ContactTitle 
                WHERE Identifier = @Identifier
            </SQL>.Value
    
        ''' <summary>
        ''' Add new record, last parameter when this function returns true
        ''' will contain the new key for the newly added record.
        ''' </summary>
        ''' <param name="CompanyName"></param>
        ''' <param name="ContactName"></param>
        ''' <param name="ContactTitle"></param>
        ''' <param name="NewIdentifier"></param>
        ''' <returns></returns>
        Public Function AddNewCustomer(ByVal CompanyName As String, ByVal ContactName As String, ByVal ContactTitle As String, ByRef NewIdentifier As Integer) As Boolean
    
            Using cn As New SqlConnection With {.ConnectionString = mConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = InsertStatement
                    cmd.Parameters.AddWithValue("@CompanyName", CompanyName)
                    cmd.Parameters.AddWithValue("@ContactName", ContactTitle)
                    cmd.Parameters.AddWithValue("@ContactTitle", ContactTitle)
                    cn.Open()
                    Try
                        NewIdentifier = CInt(cmd.ExecuteScalar)
                        Return True
                    Catch ex As Exception
                        mHasException = True
                        mLastException = ex
                        Return False
                    End Try
                End Using
            End Using
        End Function
        ''' <summary>
        ''' Pass in an existing DataRow which includes the primary key which
        ''' is used in the WHERE condition to find and update the record
        ''' </summary>
        ''' <param name="row"></param>
        ''' <returns></returns>
        ''' <remarks>
        ''' If you like to use parameters rather than a DataRow e.g.
        ''' UpdateRow(ByVal pCompanyName As String etc you can do that too.
        ''' </remarks>
        Public Function UpdateRow(ByVal row As DataRow) As Boolean
            Dim Result As Boolean = False
            Using cn As New SqlConnection With {.ConnectionString = mConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = UpdateStatement
                    cmd.Parameters.AddWithValue("@CompanyName", row.Field(Of String)("CompanyName"))
                    cmd.Parameters.AddWithValue("@ContactName", row.Field(Of String)("ContactTitle"))
                    cmd.Parameters.AddWithValue("@ContactTitle", row.Field(Of String)("ContactTitle"))
                    cmd.Parameters.AddWithValue("@Identifier", row.Field(Of Integer)("Identifier"))
                    cn.Open()
                    Try
                        If CInt(cmd.ExecuteNonQuery) = 1 Then
                            Result = True
                        End If
                    Catch ex As Exception
                        mHasException = True
                        mLastException = ex
                        Return False
                    End Try
                End Using
            End Using
            Return Result
        End Function
    
        Public Function ReadCustomers() As DataTable
            Throw New NotImplementedException
        End Function
        Public Function RemoveCustomer(ByVal pIdentifier As Integer) As Boolean
            Throw New NotImplementedException
        End Function
    End Class
    

      


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, January 03, 2018 12:18 PM
    Moderator
  • This is my code Karen

    ' BLL CLASS
    
    Imports DataAccessLayer
    
    Public Class BLLAttivita
    	Public Property [AttivitaID] As Integer = 0
    	Public Property [RagioneSociale] As String = ""
    	Public Property [RagioneSociale1] As String = ""
    	Public Property [Indirizzo] As String = ""
    	Public Property [NumeroCivico] As String = ""
    
    	Private Property DAL As New DALAttivita
    	Private Property A As BLLAttivita
    
    	Public Function GetAttivita() As BLLAttivita ' return un oggeto del tipo 
    
    		Dim dsAttivita As New DataSet
    		dsAttivita = DAL.GetAttivita() 'prende il dataset
    
    		
    		For Each R As DataRow In dsAttivita.Tables("Attivita").Rows
    
    			' qui si cambiano i campi S N in checkbox
    			A = New BLLAttivita With {
    				.AttivitaID = R(0).ToString,
    				.RagioneSociale = R(1),
    				.RagioneSociale1 = R(2),
    				.Indirizzo = R(3),
    				.NumeroCivico = R(4)
    				}
    ' check every field here ---to do
    		Next
    		Return A
    	End Function
    	Public Sub SaveAttivita(ByVal A As BLLAttivita)
    
    		DAL.SaveAttivita(A)
    	End Sub
    End Class

    following the DAL class

    ' DAL Class
    
    Imports DatabaseOperation
    Imports System.Data.SqlClient
    
    
    Public Class DALAttivita
    	Private Property DBO As New DBOAttivita
    	Private Property Query As String
    	Public Property Lastex As Exception
    
    
    	Public Function GetAttivita() As DataSet
    		
    		Query = <sql>
    			SELECT 
    				 [AttivitaID]
    				,[RagioneSociale]
    				,[RagioneSociale1]
    				,[Indirizzo]
    				,[NumeroCivico]
    				FROM Tabelle.[Attivita]
    			</sql>.Value
    
    		Dim dsAttivita As New DataSet
    		dsAttivita = DBO.GetAttivita(Query)
    
    		Return dsAttivita
    	End Function
    
    	Public Sub SaveAttivita(A As Object)
    
    		Query = <sql>
    			        UPDATE Tabelle.[Attivita] 
    				        SET  [RagioneSociale] = @RagSoc
    					        ,[RagioneSociale1] = @RagSoc1 
    					        ,[Indirizzo] = @Ind
    					        ,[NumeroCivico] = @Nc
    				        WHERE [AttivitaID] = @AttId
    				</sql>.Value
    
    		Dim param As New List(Of SqlParameter) From {
    			New SqlParameter("@AttID", SqlDbType.Int),
    			New SqlParameter("@RagSoc", SqlDbType.NVarChar),
    			New SqlParameter("@RagSoc1", SqlDbType.NVarChar),
    			New SqlParameter("@Ind", SqlDbType.NVarChar),
    			New SqlParameter("@Nc", SqlDbType.NChar)
    		}
    
    		param(0).Value = A.AttivitaID
    		param(1).Value = A.ragioneSociale
    		param(2).Value = A.ragioneSociale1
    		param(3).Value = A.indirizzo
    		param(4).Value = A.NumeroCivico
    
    		
    		DBO.SaveAttivita(Query, param)
    
    	End Sub
    
    End Class


    and now DB Operations

    ' DBO - DATA BASE OPERATION
    
    
    Imports System.Data.SqlClient
    Imports System.Windows.Forms
    Imports Libreria
    
    Public Class DBOAttivita
    
    	Private Property MiaTable As DataTable
    	Private Property MioDataset As New DataSet
    	Public Property Lastex As Exception
    
    	Public Function GetAttivita(ByVal query As String) As DataSet
    		Using CONN As New SqlConnection With
    			{.ConnectionString = VG.FullCS}
    
    			Using CMD As New SqlCommand With
    				{.Connection = CONN}
    				CMD.CommandText = query
    				Try
    					CONN.Open()
    					Dim MioAdapter As New SqlDataAdapter With
    						{.SelectCommand = CMD}
    					MioAdapter.TableMappings.Add("Table", "Attivita")
    					MioAdapter.Fill(MioDataset)
    					MiaTable = MioDataset.Tables("Attivita")
    					MioAdapter.Dispose()
    				Catch ex As Exception
    					MessageBox.Show("Errore lettura Achivio")
    					lastex = ex
    				End Try
    			End Using
    		End Using
    		Return MioDataset
    	End Function
    
    	'DBO class
    	Public Sub SaveAttivita(ByVal query As String, ByVal param As List(Of SqlParameter))
    		Using CONN As New SqlConnection With {
    			.ConnectionString = VG.FullCS}
    			Using CMD As New SqlCommand With {.Connection = CONN}
    				CMD.CommandType = CommandType.Text
    				CMD.CommandText = query
    				If (param IsNot Nothing) Then
    					For Each p In param
    						CMD.Parameters.Add(p)
    					Next
    				End If
    				Try
    					CONN.Open()
    					CMD.ExecuteNonQuery()
    				Catch ex As Exception
    					lastex = ex
    					MessageBox.Show("Errore salvataggio Achivio")
    					MessageBox.Show(ex.ToString)
    				End Try
    			End Using
    		End Using
    	End Sub
    End Class

    I use a DataSet to read a ROW (for now I have just 1 row written with SSMS in the database.

    in the form I have

    A = New BLLAttivita
    A = A.GetAttivita
    
    BLLAttivitaBindingSource.DataSource = A

    A.SaveAttivita(A)

    The A object is an istance of BLL

    Every A filed has bounds to textboxs in the form

    Everything works well. The only strange thing to me is that there is an istance of BLL (A object) also in the BLL itself 


    I found this solution on the web  but is very strange to me.

    As you have already understood i'm making several test to understand how things work.


    Wednesday, January 03, 2018 2:27 PM
  • To avoid to create an istance of BLL in the BLL where should I create the class ?

    For an example  with Customer Table in Database and BLL and DAL in tow differnet projcects (with or without DB Operation separated)....where should I create the Customer Class with all the fields ?

    Wednesday, January 03, 2018 2:32 PM
  • First off this thread has gone beyond the intended purpose so you should consider starting a new discussion.

    In the mean time, how we lay things out.

    In the following example, each project name begins with the solution name.

    AcedLibrary is for only data operations with the classes defined for each type.

    AcedAPI is where the business logic resides.

    AcedAPI.Test is a unit test project to test all aspects of the two libraries above.

    Now of the above touches the user interface. There is another project for user interface (not shown) which works with RevenueKendoLibrary which is a library for interacting with the user interface components and has custom components.

    So in the above, if there was a customer class it would reside in the AcedLibrary and in our case there would are partial classes in this project too while there are DTO (Data Transfer Objects) in the AcedAPI project. you don't need DTO entities in your solution.

    In regards to the "A" object, nothing should be named what you can't figure out from it's name what it is.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, January 03, 2018 3:35 PM
    Moderator
  • KAren it isi diffucult to star a new discussion, I prefer to end this one

    Without going to other exampe, let return to your example with CompanyName, ContactTitle etc (3 hours and 40 min ago)

    In the the code documentation you say 

     There
    ''' are other ways too e.g. rather than passing in string values, we
    ''' could have used a Customer class and constructed a new customer
    ''' and passed the new customer to the add method


    The question is : Where to put the Customer Class ? In an Other Project in order that BLL or DAL can refer to it ?

    Wednesday, January 03, 2018 4:06 PM
  • In the data layer project, in a class say called Customer. The Customer class only (at least for this discussion) properties which represent fields in the backend database, that is it. The business layer uses the Customer class but knows nothing of the actual class containing data operations. Alternatively you could have a class project with just classes which represent the data in your database yet that way it's possible to get into circular reference issues unless careful, an entirely new bag of worms per-say. 

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Claudio111 Thursday, January 25, 2018 3:43 PM
    Wednesday, January 03, 2018 4:24 PM
    Moderator
  • Thank you , your replay is what is supposed.

    So I will create a new project that contains only tha Customer Class and the class contains obly properties as the database table. Non methods.

    Now I will star a new dscussion about N-Tier

    Thank you Karen, you help is very precious.

    • Marked as answer by Claudio111 Wednesday, January 03, 2018 4:43 PM
    • Unmarked as answer by Claudio111 Thursday, January 25, 2018 3:43 PM
    Wednesday, January 03, 2018 4:43 PM
  • Thank you , your replay is what is supposed.

    So I will create a new project that contains only tha Customer Class and the class contains obly properties as the database table. Non methods.

    Now I will star a new dscussion about N-Tier

    Thank you Karen, you help is very precious.


    Wednesday, January 03, 2018 4:44 PM