Results 1 to 5 of 5

Thread: Search Button in vb.net

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2017
    Posts
    12

    Search Button in vb.net

    I have Table Called "ItemsPricesTbl"

    Contain :

    ItemID
    FirstUnitWholeSalePrice
    FirstUnitShopperPrice
    FirstUnitDemotionsPrice
    FirstUnitPriceDefult
    SecondUnitWholeSalePrice
    SecondUnitShopperPrice
    SecondUnitDemotionsPrice
    SecondUnitPriceDefult
    ThirdUnitWholeSalePrice
    ThirdUnitShopperPrice
    ThirdUnitDemotionsPrice
    ThirdUnitPriceDefult
    DefultPrice


    The Stored Porc is :


    Code:
    ALTER PROCEDURE [dbo].[Get_Prices_Item_By_ID]
     
    @ItemID int
     
    as
     
    begin
     
    Select ItemID,FirstUnitWholeSalePrice,FirstUnitShopperPrice,FirstUnitDemotionsPrice,FirstUnitPriceDefult,SecondUnitWholeSalePrice,SecondUnitShopperPrice,SecondUnitDemotionsPrice,SecondUnitPriceDefult,ThirdUnitWholeSalePrice,ThirdUnitShopperPrice,ThirdUnitDemotionsPrice,ThirdUnitPriceDefult,DefultPrice from ItemsPricesTbl Where ItemID=@ItemID
     
    End

    in Vb i have DatabaseManager Class Which Have this Code :


    Code:
    Private Function exeReader(ByRef cmd As SqlCommand, ByRef dr As SqlDataReader) As Integer
            Dim retval As Integer = -1
            cmd.Connection = Me.Connection
            Try
                If cmd.CommandType = CommandType.StoredProcedure Then
                    Dim pr As New SqlParameter("@retval", SqlDbType.Int)
                    pr.Direction = ParameterDirection.ReturnValue
                    cmd.Parameters.Add(pr)
                End If
                If cmd.Connection.State = ConnectionState.Closed Then cmd.Connection.Open()
                dr = cmd.ExecuteReader()
     
                If cmd.CommandType = CommandType.StoredProcedure Then retval = cmd.Parameters("@retval").Value
            Catch ex As Exception
                Throw New Exception(ex.Message)
            Finally
                Me.close()
            End Try
     
            Return retval
        End Function

    Also I have two classes The Data Class And Business Class .

    Data Class Code :


    Code:
    Friend Function Get_Prices_Item_By_ID(ByRef cmd As SqlCommand, ByRef dr As SqlDataReader, ByVal ItemID As Integer) As Integer
                Dim retval As String
                cmd = New SqlCommand("Get_Prices_Item_By_ID")
                daa.SelectCommand.Parameters.AddWithValue("@FirstUnitWholeSalePrice", SqlDbType.Decimal)
                daa.SelectCommand.Parameters.AddWithValue("@FirstUnitShopperPrice", SqlDbType.Decimal)
                daa.SelectCommand.Parameters.AddWithValue("@FirstUnitDemotionsPrice", SqlDbType.Decimal)
                daa.SelectCommand.Parameters.AddWithValue("@FirstUnitPriceDefult", SqlDbType.Bit)
                daa.SelectCommand.Parameters.AddWithValue("@SecondUnitWholeSalePrice", SqlDbType.Decimal)
                daa.SelectCommand.Parameters.AddWithValue("@SecondUnitShopperPrice", SqlDbType.Decimal)
                daa.SelectCommand.Parameters.AddWithValue("@SecondUnitDemotionsPrice", SqlDbType.Decimal)
                daa.SelectCommand.Parameters.AddWithValue("@SecondUnitPriceDefult", SqlDbType.Bit)
                daa.SelectCommand.Parameters.AddWithValue("@ThirdUnitWholeSalePrice", SqlDbType.Decimal)
                daa.SelectCommand.Parameters.AddWithValue("@ThirdUnitShopperPrice", SqlDbType.Decimal)
                daa.SelectCommand.Parameters.AddWithValue("@ThirdUnitDemotionsPrice", SqlDbType.Decimal)
                daa.SelectCommand.Parameters.AddWithValue("@ThirdUnitPriceDefult", SqlDbType.Bit)
                daa.SelectCommand.Parameters.AddWithValue("@DefultPrice", SqlDbType.Int)
                dr = cmd.ExecuteReader()
                Return retval
            End Function
    And Business Class Code :

    Code:
    Public Function Get_Prices_Item_By_ID(ByVal ItemID As Integer) As DataTable
        Dim dt As New DataTable
        p.Get_Prices_Item_By_ID(dt)
        Return dt
    End Function

    What Code I have to Put in search Button ?

    And How To Display Record From SQL To TextBox ?

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Search Button in vb.net

    Thread moved from the 'CodeBank VB.Net' forum (which is for you to post working code examples, not questions) to the 'VB.Net' forum

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Search Button in vb.net

    There are several issues with your code so far. At this stage you would be better off ignoring the search button, and just fix the issues you have.


    First of all you showed us exeReader, which seems to be fine... but you don't use it in the other code. I assume that your intention was to call it in the Data Class, so instead of this:
    Code:
    Dim retval As String
    ...
      dr = cmd.ExecuteReader()
    ...you would have this:
    Code:
    Dim retval As Integer
    ...
      retVal = DatabaseManager.exeReader(cmd, dr)
    Next up, the stored procedure only has one parameter: @ItemID int
    ...and you are setting up lots of parameters (you should only have one), and none of them are @ItemID (which you need to set).


    Also in the Data Class, the function has a few parameters, but there is no need for cmd to be one of them - the code calling it doesn't need to declare it or use it, so instead of being a parameter it should be declared inside the function.


    In the Business Class code you also have problems, for example the line p.Get_Prices_Item_By_ID(dt) doesn't have appropriate parameters to call the Data Class function (in your code above it takes three: SqlCommand, SqlDataReader, and Integer but you pass it one: DataTable).


    Have a go at dealing with these issues, and we'll help with the next steps from there.

  4. #4

    Thread Starter
    New Member
    Join Date
    Apr 2017
    Posts
    12

    Re: Search Button in vb.net

    Great Now i have correct the Data Class Code to this

    Code:
      Friend Function Get_Prices_Item_By_ID(ByRef cmd As SqlCommand, ByRef dr As SqlDataReader, ByVal ItemID As Integer) As Integer
                Dim retval As Integer
                cmd = New SqlCommand("Get_Prices_Item_By_ID")
                daa.SelectCommand.Parameters.AddWithValue("@ItemID", SqlDbType.Int)
                retval = dm.executeReader(cmd, dr)
                Return retval
            End Function

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Search Button in vb.net

    That is a definite improvement, but there is still some way to go.

    While you now have the correct database parameters, you aren't setting the value for @ItemID. Note that when using AddWithValue, you do not specify a data type, only the name and the value.
    https://msdn.microsoft.com/en-us/lib...v=vs.110).aspx

    In addition to that, you are adding the database parameter to the wrong thing... why are you adding it to daa.SelectCommand ? I assume you have been doing a copy+paste from somewhere else, without thinking about the code you are pasting.


    You still have cmd as a parameter to the function. It would probably work like that, but is a bad idea because it not only creates extra work every time you call the function (as I implied before), but it also adds the opportunity to accidentally cause extra problems (which may happen only occasionally, and that is worse than always because it is harder to track down). It is much better to declare (Dim) it inside the function instead.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width