none
solve the problem RRS feed

  • Question

  • vb.net SQL SERVER

    Database k1

    Table: Customer

    pk-CID | C-Name

           1  |  anzar

            2  | Orva

             3 | Anzar

            4  | Anzar

            5  | Jawad

    Table 2 Sales

    pk-Sid | C-Name | ProductPrice  | unitprice

          1  |  Anzar    |   Z                |    50

          2  |   Anzar   |   p               |   90

           3 |  Orva     |   Z                 |  10     

           4 |  jawad   |   p                 |   50

           5 |  Anzar   |    p                 |  20

    My Requirement

    I want the last unit price of product which is given to anzar  about p or z

    Vb.net Coding problem


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

    Wednesday, January 23, 2019 6:45 PM

Answers

  • Hello,

    It's customary to close a former thread here before starting a new one.

    Here is a solution for 

    My Requirement

    I want the last unit price of product which is given to anzar  about p or z

    Make sure to change the server name below from KARENS-PC and change the default catalog too for your database.

    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

    Data class

    Public Class DataOperations
        Inherits BaseSqlServerConnection
        Public Function GetUnitPrice(pName As String) As Integer
            Dim unitPrice As Integer = 0
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = "SELECT TOP(1) [unitprice] " &
                                      "FROM dbo.Sales " &
                                      "WHERE [C-Name] = @CName " &
                                      "ORDER BY [pk-Sid] DESC"
    
                    cmd.Parameters.AddWithValue("@CName", pName)
                    cn.Open()
                    unitPrice = CInt(cmd.ExecuteScalar())
                End Using
            End Using
    
            Return unitPrice
    
        End Function
    End Class

    Form code which is hard code to a specific name for this demo. Of course you can provide any name. 

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops As New DataOperations
            Dim unitPrice = ops.GetUnitPrice("Anzar")
            MessageBox.Show($"Unit price is {unitPrice}")
        End Sub
    End Class

    I tested this by creating the table, populated it then ran the code. 

    Note if by chance the requirement was to get the "highest" unit price we would use

    Public Class DataOperations
        Inherits BaseSqlServerConnection
        Public Function GetUnitPrice(pName As String) As Integer
            Dim unitPrice As Integer = 0
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = "SELECT TOP(1) [unitprice] " &
                                      "FROM dbo.Sales " &
                                      "WHERE [C-Name] = @CName " &
                                      "ORDER BY [unitprice] DESC"
    
                    cmd.Parameters.AddWithValue("@CName", pName)
                    cn.Open()
                    unitPrice = CInt(cmd.ExecuteScalar())
                End Using
            End Using
    
            Return unitPrice
    
        End Function
    End Class
    
    And in regards to p or z, don't see how that plays a part in anything unless there are missing details.


    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 23, 2019 11:44 PM
    Moderator

All replies

  • Hello,

    It's customary to close a former thread here before starting a new one.

    Here is a solution for 

    My Requirement

    I want the last unit price of product which is given to anzar  about p or z

    Make sure to change the server name below from KARENS-PC and change the default catalog too for your database.

    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

    Data class

    Public Class DataOperations
        Inherits BaseSqlServerConnection
        Public Function GetUnitPrice(pName As String) As Integer
            Dim unitPrice As Integer = 0
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = "SELECT TOP(1) [unitprice] " &
                                      "FROM dbo.Sales " &
                                      "WHERE [C-Name] = @CName " &
                                      "ORDER BY [pk-Sid] DESC"
    
                    cmd.Parameters.AddWithValue("@CName", pName)
                    cn.Open()
                    unitPrice = CInt(cmd.ExecuteScalar())
                End Using
            End Using
    
            Return unitPrice
    
        End Function
    End Class

    Form code which is hard code to a specific name for this demo. Of course you can provide any name. 

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops As New DataOperations
            Dim unitPrice = ops.GetUnitPrice("Anzar")
            MessageBox.Show($"Unit price is {unitPrice}")
        End Sub
    End Class

    I tested this by creating the table, populated it then ran the code. 

    Note if by chance the requirement was to get the "highest" unit price we would use

    Public Class DataOperations
        Inherits BaseSqlServerConnection
        Public Function GetUnitPrice(pName As String) As Integer
            Dim unitPrice As Integer = 0
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = "SELECT TOP(1) [unitprice] " &
                                      "FROM dbo.Sales " &
                                      "WHERE [C-Name] = @CName " &
                                      "ORDER BY [unitprice] DESC"
    
                    cmd.Parameters.AddWithValue("@CName", pName)
                    cn.Open()
                    unitPrice = CInt(cmd.ExecuteScalar())
                End Using
            End Using
    
            Return unitPrice
    
        End Function
    End Class
    
    And in regards to p or z, don't see how that plays a part in anything unless there are missing details.


    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 23, 2019 11:44 PM
    Moderator
  • Table: Customer

    pk-CID | C-Name

           1  |  anzar

            2  | Orva

             3 | Anzar

            4  | Anzar

            5  | Jawad

    Table 2 Sales

    pk-Sid | C-Name | ProductName  | unitprice

          1  |  Anzar    |   Z                |    50

          2  |   Anzar   |   p               |   90

           3 |  Orva     |   Z                 |  10     

           4 |  jawad   |   p                 |   50

           5 |  Anzar   |    p                 |  20

    yes i got 20 but problem still exit how can i know the last price of  z product which is 10 you give me.

    Case Study:

    Two Brother start a business,  Elder brother handle each transaction of business only condition is that product unit price change customer to customer.  In absence of elder brother, younger brother type the customer name and product name to know the last price of product which is given last time.

    Need sql query only


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

    • Marked as answer by muhammadanzar Thursday, January 24, 2019 3:21 AM
    • Unmarked as answer by muhammadanzar Thursday, January 24, 2019 3:22 AM
    Thursday, January 24, 2019 1:52 AM
  • SQL

    SELECT   TOP ( 1 ) [unitprice]
    FROM     dbo.Sales
    WHERE    ProductName = 'Z'
    ORDER BY [pk-Sid] DESC;


    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 11:11 AM
    Moderator