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 ?

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

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.

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

---

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.