Hello. I have a database with a table containing multiple prices for a single product and there are multiple products in the table.
I need to get a specific price depending and the amount of product. If a customer has 100lbs he gets 3.50 a product if he has more he gets 3.45...
i need it to read a txtbox for the amount Then get the right price from the database. But i have no clue how to do it. Im not sure if it must be done from the Sql statement or in the .vb file.
You can write a stored procudure to deal with this kind of problem——
Create Procdure XXX
@amount int,
@commodityId
As
declare @price decimal
if(@amount=100)
select @price= top 1 Price100 from xxx where commodityId=@commodityId
if(@amount=501)
select @price= top 1 Price500 from xxx where commodityId=@commodityId
else
select @price= top 1 Price501 from xxx where commodityId=@commodityId
select @price
And then plz use SqlCommand to call the Stored procdure and get the result，for this plz see：
Not sure of your database structure, but if you don't store the price per differing amounts then you have to calculate the values in your business logic. It would probably be some type of percentage based on quantity. So we need to know if you store the
$ amouns for differing quantities in the DB. Then we can go from there
dkba27
Member
17 Points
18 Posts
Getting Data Depending On Control
Feb 10, 2012 06:37 PM|LINK
Hello. I have a database with a table containing multiple prices for a single product and there are multiple products in the table.
I need to get a specific price depending and the amount of product. If a customer has 100lbs he gets 3.50 a product if he has more he gets 3.45...
i need it to read a txtbox for the amount Then get the right price from the database. But i have no clue how to do it. Im not sure if it must be done from the Sql statement or in the .vb file.
Database sql textbox
prasadbcs
Member
28 Points
4 Posts
Re: Getting Data Depending On Control
Feb 10, 2012 07:30 PM|LINK
you can call a STORED PROCEDURE to return the price for the amount you read from the textbox
Database sql textbox
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Getting Data Depending On Control
Feb 12, 2012 01:33 AM|LINK
What's your table structure？And what's the calculation rule？
Reguards！
dkba27
Member
17 Points
18 Posts
Re: Getting Data Depending On Control
Feb 13, 2012 05:25 PM|LINK
This is My table... the prices aren't official yet so they'll be changed later.
what i need it to do is watch my textbox and change the value in a dropdownlist.
So if the textbox value is 100 it should get the price from Price100...
If the value is 500 then the price selected should be Price500..
I need a VB Statement that will do this... Or an Sql Statement.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Getting Data Depending On Control
Feb 14, 2012 12:12 AM|LINK
Hello：）
You can write a stored procudure to deal with this kind of problem——
Create Procdure XXX
@amount int,
@commodityId
As
declare @price decimal
if(@amount=100)
select @price= top 1 Price100 from xxx where commodityId=@commodityId
if(@amount=501)
select @price= top 1 Price500 from xxx where commodityId=@commodityId
else
select @price= top 1 Price501 from xxx where commodityId=@commodityId
select @price
And then plz use SqlCommand to call the Stored procdure and get the result，for this plz see：
http://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.71).aspx
pnoneal
Member
353 Points
166 Posts
Re: Getting Data Depending On Control
Feb 15, 2012 06:13 PM|LINK
Not sure of your database structure, but if you don't store the price per differing amounts then you have to calculate the values in your business logic. It would probably be some type of percentage based on quantity. So we need to know if you store the $ amouns for differing quantities in the DB. Then we can go from there
Paul