none
invoice he can order one product only single time, it mean in invoice there are many product but they are distinct. RRS feed

  • Question

  • Hi

    i Want vb.net Code with Sql Query that can chive following target like

    when a customer order for a product, in  invoice he can order one product only single time,  it mean in invoice there are many product but they are distinct.

    Hope you help me. 

    thanks 


    MUHAMMAD ANZAR E-mail : muhammadanzar@hotmail.com Mobile # :0092-3215096959

    Thursday, January 24, 2019 8:47 PM

All replies

  • Using NorthWind database we have a customer who has orders and orders has order details which point to products table. I start at Order details which I got to via having a customer id and order id plus a value to add to a new record.

    I would use SQL such as the following where the PRINT would be a INSERT. So if the conditions are met a record would be updated, otherwise a new record would be added.

    The DECLARE statements in code represent a parameter for a command object.

    DECLARE @OrderIdentifier AS int = 10249
    DECLARE @AdditionalItem AS int = 2
    DECLARE @ProductIdentifier AS int = 14
    
    IF EXISTS
    (
        SELECT *
        FROM dbo.[Order Details] o
        WHERE o.OrderID = @OrderIdentifier
              AND o.productId = @ProductIdentifier
    )
        UPDATE dbo.[Order Details]
          SET
              dbo.[Order Details].Quantity = [Order Details].Quantity + @AdditionalItem
        WHERE OrderID = @OrderIdentifier
              AND ProductID = @ProductIdentifier;
    ELSE
    	PRINT 'Add new record'


    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

    Thursday, January 24, 2019 9:54 PM
    Moderator
  • how to apply this code in vb.net kindly do all work in public form 1 class

    MUHAMMAD ANZAR E-mail : muhammadanzar@hotmail.com Mobile # :0092-3215096959

    Friday, January 25, 2019 12:25 PM
  • Note, the following is the pattern to use, I used the same base classes as done in at least one of your other questions.

    Call TryUpdate, if the record is found the qty is updated, if the record is not found the return value indicates it was not found and at that point do nothing or call the method InsertRecord which inserts a new record and returns the new primary key.

    NOTE: If something does not exactly fit your requirements then by all means modify the code as this code sample is meant only to provide a pattern and not a drop into your code.

    Public MustInherit Class BaseSqlServerConnection
        ''' <summary>
        ''' This points to your database server
        ''' </summary>
        Protected DatabaseServer As String = "KARENS-PC"
        ''' <summary>
        ''' Name of database containing required tables
        ''' </summary>
        Protected DefaultCatalog As String = "ForumExample"
        Public ReadOnly Property ConnectionString() As String
            Get
                Return $"Data Source={DatabaseServer};Initial Catalog={DefaultCatalog};Integrated Security=True"
            End Get
        End Property
    End Class
    Public Enum ResultTypes
        FoundUpdated
        RequiresInsert
    End Enum
    Public Class SqlServerOperations
        Inherits BaseSqlServerConnection
        ''' <summary>
        ''' Insert new record, return new primary key
        ''' </summary>
        ''' <param name="orderId"></param>
        ''' <param name="productId"></param>
        ''' <param name="unitPrice"></param>
        ''' <param name="quantity"></param>
        ''' <returns></returns>
        Public Function InsertRecord(
            orderId As Integer,
            productId As Integer,
            unitPrice As Decimal,
            quantity As Integer) As Integer
    
            DefaultCatalog = "Your database name"
    
            Dim insertStatement = <SQL>
                                      INSERT INTO dbo.OrderDetails 
                                        (
                                            OrderID,
                                            ProductID,
                                            UnitPrice,
                                            Quantity,
                                            Discount) 
                                       VALUES 
                                            (
                                                @OrderID, 
                                                @ProductID,
                                                @UnitPrice,
                                                @Quantity,
                                                @Discount
                                            );
                                        SELECT CAST(scope_identity() AS int);
                                  </SQL>.Value
    
            Using cn = New SqlConnection(ConnectionString)
                Using cmd = New SqlCommand() With {.Connection = cn, .CommandText = insertStatement}
    
                    cn.Open()
    
                    cmd.Parameters.AddWithValue("@OrderID", orderId)
                    cmd.Parameters.AddWithValue("@ProductID", productId)
                    cmd.Parameters.AddWithValue("@UnitPrice", unitPrice)
                    cmd.Parameters.AddWithValue("@Quantity", quantity)
    
                    ' return new primary key
                    Return CInt(cmd.ExecuteScalar())
    
                End Using
            End Using
    
        End Function
        ''' <summary>
        ''' If record exists update, if not return needs an insert
        ''' </summary>
        ''' <param name="orderIdentifier"></param>
        ''' <param name="additionalItem"></param>
        ''' <param name="productIdentifier"></param>
        ''' <returns></returns>
        Public Function TryUpdate(
            orderIdentifier As Integer,
            additionalItem As Integer,
            productIdentifier As Integer) As ResultTypes
    
            '
            ' We have gone over DefaultCatalog in one of your prior questions
            '
            DefaultCatalog = "Your database name"
            Dim selectExistsStatement = <SQL>
                                            SELECT * 
                                            FROM 
                                                dbo.OrderDetails o 
                                            WHERE 
                                                o.OrderID = @OrderIdentifier AND o.productId = @ProductIdentifier
                                        </SQL>.Value
    
            Dim updateStatement = <SQL>
                                      UPDATE dbo.OrderDetails 
                                      SET dbo.OrderDetails.Quantity = OrderDetails.Quantity + @AdditionalItem 
                                      WHERE OrderID = @OrderIdentifier AND ProductID = @ProductIdentifier;
                                  </SQL>.Value
    
    
    
            Using cn = New SqlConnection(ConnectionString)
                Using cmd = New SqlCommand() With {.Connection = cn, .CommandText = selectExistsStatement}
    
                    cn.Open()
    
    
                    cmd.Parameters.AddWithValue("@OrderIdentifier", orderIdentifier)
                    cmd.Parameters.AddWithValue("@ProductIdentifier", productIdentifier)
    
                    Dim reader = cmd.ExecuteReader()
                    If reader.HasRows Then
    
                        reader.Close()
                        cmd.Parameters.Clear()
    
                        cmd.CommandText = updateStatement
    
                        cmd.Parameters.AddWithValue("@AdditionalItem", additionalItem)
                        cmd.Parameters.AddWithValue("@OrderIdentifier", orderIdentifier)
                        cmd.Parameters.AddWithValue("@ProductIdentifier", productIdentifier)
    
                        cmd.ExecuteNonQuery()
    
                        Return ResultTypes.FoundUpdated
    
                    Else
                        '
                        ' Insert new record
                        '
                        Return ResultTypes.RequiresInsert
    
                    End If
    
                End Using
            End Using
    
        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

    Friday, January 25, 2019 1:00 PM
    Moderator