-
Apr 22nd, 2017, 08:34 AM
#1
Thread Starter
New Member
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 ?
-
Apr 22nd, 2017, 09:17 AM
#2
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
-
Apr 22nd, 2017, 09:47 AM
#3
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.
-
Apr 22nd, 2017, 10:16 AM
#4
Thread Starter
New Member
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
-
Apr 22nd, 2017, 11:08 AM
#5
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|