MS SQL Server :: How To Find OrderID And Maximum-price Unit Of Each Order

Transact SQL :: Function To Return Unit Price Based On Various Criteria

Apr 30, 2015

We were asked to create an SQL function to return a unit price based on various criteria. The function works fine except for the tiered pricing (use of BillingPriceTable) calculation. What we need to do is break up the total quantity passed to the function and return the total of prices found. In our example, we passed a quantity of 9,721 units and need to return a total price of 231.92 using the table below.

Low Qty    High Qty    Fee        Actual Qty        Price
0                  7500        0.025            7500           187.50
7501           15000        0.020            2221          44.42

Below is the table definition that we have to work with (ugghh).

CREATE TABLE [dbo].[BillingPriceTable](
[PriceTableID] [int] IDENTITY(1,1) NOT NULL,
[entity] [varchar](4) NULL,
[PriceTableCode] [varchar](10) NULL,
[PriceTableName] [varchar](40) NULL,

[Code] ....

What we have so far is shown below. The columns that start with bdxx are the "High Qty" values and the columns that start with prxx are the price for that quantity range. So, the current SELECT is shown below and it returns the price based on the entire qty of 9,721 and returns a unit price of 0.020 and should return 0.023857628

The current SELECT is shown below and is returning 0.020 which is the fee for the total rather than calculating the fee twice, once for the 0-7500 and again for the 7501-15000 (actually 7501-9721). Two things came to mind, one was a WHILE loop and the other was possibly a ranking function of some sort.

ALTER FUNCTION [dbo].[fn_GetPrice]
(
@plincdvarchar(3),
@pgrpcodevarchar(4),
@pitmcodevarchar(4),
@qtydecimal(10,1) = 1,
@corpnbrvarchar(9)
)

[Code] ....

View 9 Replies View Related

Obtain Unit Percent With Unit Count Divided By Total Count In Query

Aug 21, 2007

The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.

Here is a test code snippet:

--Test Count/Count subquery

declare @Date datetime

set @date = '8/15/2007'

select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select

from tblVGD1_Master m

left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID

Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9

and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0

and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)

and v.[Date] between DateAdd(dd,-90,@Date) and @Date

group by substring(m.PTNumber, 3,3)

order by AUPct Desc

Thanks. Dan

View 1 Replies View Related

Select Price For A Product Based On Price Break?

Dec 12, 2014

I have to add the unit price on the order acknowledgement for products on our shelf.

Each product has different price breaks stored in a table called MaterialUnitCost.

I don't know how to pull the correct price based on the order quantity.

Let's say the customer orders 200 pieces,

I sell 1 pcs @ $20
50 pcs @$15
200 pcs @$10.

My order acknowledgement should pull a unit price of $10, but it pulls $20 instead, because in my select statement I have

select materialunitcost.unitcost.

I thought I should do a loop or use the row_number function, but I am new to SQL, and I never used any of these two.

View 1 Replies View Related

Add Calculated Field In Order Table Based On Price Column In Product Table

Nov 18, 2014

I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?

this query returns the values i want in the table.

select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID

View 17 Replies View Related

Find Order By Date Range Or Order Id

May 8, 2007

hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL
SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))">
but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!
Thanks
Jez

View 4 Replies View Related

How To Find The Maximum Possible Value Of A Given Datatype

Jun 27, 2007

Does anyone know of a built-in function to return the maximum possiblevalue of a given datatype? I have to return the biggest value for asmalldatetime or datetime in a view if the field is null, but can'tfind such a function. The closest I've come is:select datalength(cast(getdate() as smalldatetime))....but that only return the number of bytes, not the value itself,which is '6-6-2079 11:59'I know I could create my own lookup table and function, but I washoping that Transact-SQL would have a built-in solution--John Hunter

View 3 Replies View Related

How To Find Maximum Number From Two Tables

Feb 19, 2012

My db contains two table Employee_detail and Student_Detail.Employee_detail has Emp_id and Student_Detail has stud_id.Now my problem is that i want to get one maximum no from these two table.

View 11 Replies View Related

SQL Statement To Find The The Maximum Record

Oct 12, 2007

I have a table with member information. Each member has multiple effective dates. I would like to query to find the record for each member that has the maximum effective date. A sample table is below:

MEMBERID EFECTIVEDATE
------------ ------------------
699361401 20070101
699361401 20070501
732612701 20070101
732612701 20070501
575424301 20070101
575424301 20070501
192939801 20070101
192939801 20070501
458645001 20070101
458645001 20070501

View 4 Replies View Related

Price X Quantity - Total Price

Jun 19, 2000

I am developing basket and need a simple way to get price x quantity + total price.

I get back a recordset which has a varying number of items, each item has a price and quantity. Using
SELECT ((productprice)*(productquantity)) AS subtotal, product name, productid

I don't know how to add all subtotals to get a total, I have tried putting in SUM but but cant get it to work.

Any help would be well appreciated.

View 1 Replies View Related

Syntax Help With Orderid % 2 = 1 In WHERE Clause

Nov 6, 2007

I started reading a very informative book (Inside Microsoft SQL Server 2005: T-SQL Querying) that uses the following example.SELECT orderid, customerid,
COUNT(*) OVER(PARTITION BY customerid) AS num_orders
FROM dbo.Orders
WHERE customerid IS NOT NULL
AND orderid % 2 = 1;
Being new to T-SQL, I don't understand what is going on in the last line "orderid % 2 = 1". I tried searching "Books On Line" with no answer. Is there a good reference or textbook out there I can use to find the answers to specific syntax questions?

View 3 Replies View Related

Updating Price Column Based On Another Price Column?

Aug 13, 2013

My dataset looks like this:

IDBuyDotComPriceCompanyIDShadowOf
AB CIRCLE PRO199.99203
AB CIRCLE PRO-b2199.99203AB CIRCLE PRO
AB CIRCLE PRO-TB249.99344AB CIRCLE PRO
AB CIRCLE PRO-TB-S10344AB CIRCLE PRO

I need to update the price of an item where the CompanyID is 344 and the ShadowOf is not null. The value in ShadowOf is the same as the ID that I want to get the BuyDotComPrice for. It should be simple, but I keep getting errors.

I use Microsoft SQL 2008

View 1 Replies View Related

How To Pass Master Reports (fields!OrderId) Value To A Subreport As A Parameter Value

May 11, 2008

How to pass master reports (fields!OrderId) value to a subreport as a parameter value.

if i right click the subreport control on the main report layout, i do see parameters tab, under that would like to pass the (fields!orderID).

Thanks for the info.

View 4 Replies View Related

Transact SQL :: Error - Maximum Row Size Exceeds Allowed Maximum Of 8060 Bytes

Sep 12, 2015

I have some code I build 2 weeks ago which I’ve been running daily but it’s suddenly stopped working with the following error.

“The table "tbl_Intraday_Tmp" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit” When I google this there seems to be a related to tables with vast numbers of columns.

My table tbl_Intraday_tmp is relatively small. It has 7 columns. 1 of varchar(5), 3 of decimal(9,3) and 2 of decimal(18,0). The bit I’m puzzled with is it was working and stopped.

I don’t recall changing anything but I wouldn’t rule that out. I ‘ve inspected the source files and I don’t believe they have changed either.

DECLARE
@FileName varchar(50),
@Path varchar(50),
@SqlCmd varchar(1000)
= '',
@ASXCode varchar(5),
@Offset decimal(18,0),

[code]....

View 5 Replies View Related

Find Logic Flaw, Order Number Generator

Mar 15, 2000

This procedure has been returning duplicate numbers. (Tested with scripts that called this proc and put value in a table.)

How can it return duplicates? Does the transaction Begin/Commit not guarantee transactional consistency?

CREATE PROCEDURE sp_UpdateOrderNumber @customer int AS
DECLARE @NewOrderId int,
@nSQLError int,
@nRowCount int
BEGIN TRAN
UPDATE CUSTOMERS
SET ORDER_NUMBER=ORDER_NUMBER + 1
WHERE COMPANY_ID=@customer
SELECT@nSQLError = @@error,
@nRowCount = @@rowcount
If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */
Begin
Rollback Tran
Return -999
End
SELECTORDER_NUMBER
FROMCUSTOMERS
WHERECOMPANY_ID=@customer
SELECT@nSQLError = @@error,
@nRowCount = @@rowcount
If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */
Begin
Rollback Tran
Return -998
End
COMMIT TRAN

View 1 Replies View Related

SQL Server 2008 :: How To Find Order Of Lock Taken And Released

Feb 16, 2015

I have express edition [advance] of sqlserver 2008 r2 , is it possible to trace every event with out using profiler as u know it does not ship with it.

Basically i want to see how locks are taken and released in each isolation level when query is executed. I am using

SELECT resource_database_id, t.resource_type , t.resource_associated_entity_id,partitions.index_id,blocking_session_id,
indexes.name as index_name,o.name,
t.request_status, t.request_mode,t.request_session_id,
t.resource_description

[code]....

But i am not sure of the order becuase total_elapsed_time is same for many locks

like

resource_typeresource_associated_entity_idrequest_session_idrequest_modeindex_namenameresource_description
DATABASE051SNULLNULL
OBJECT209427886651Sch-SNULLwt
OBJECT209427886652IXNULLwt
PAGE7205759715088793652IXPK_wtNULL1:204815
KEY7205759715088793652XPK_wtNULL(4fb98fd9bfad)
DATABASE052SNULLNULL

View 5 Replies View Related

SQL Server 2008 :: Find Out Duplicate Order Sequence

Jun 30, 2015

In my asp.net project there are about 100 drop down list.I created a table to store data for drop down list in which including [DropdownID],[Order Sequence] and [Description] three columns. The sample like below. Data was input manually by a user. How to code to find out duplicate [OrderSequence]?

DropdownID--OrderSequence--Description
1-------------0--------------AAA
1-------------1--------------BBB
2-------------0--------------YYY
2-------------1--------------XXX
2-------------2--------------QQQ 'DUPLICATE OrderSequence
2-------------2--------------WWW 'DUPLICATE OrderSequence
2-------------3--------------RRR

View 2 Replies View Related

Error - Maximum Row Size Exceed Allowed Maximum Of 8060 Bytes

Apr 20, 2012

I am using MS SQL server 2008, and i have a table with 350 columns and when i m trying to create one more column its giving error with below message -

Warning: The table XXX has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.

INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

how can i resolve this?

View 14 Replies View Related

Unit Testing

Oct 1, 2005

I've started researching on Unit Testing and I must admitI had never heard of Unit Testing until a couple of monthsago. Obviously I am interested in Unit Testing StoredProcedures.I read the TSQLUnit documentation (not all of it) and i also raninto a newsgroup post saying TSQLUnit is very small comparedto NUnit. The conclusion I am making out of this post is thatI should rather spend time resarching/reading about NUnit thanTSQLUnit. Is that a good assessment?I would like to you what you use and if you use actuallyUnit Testing or some other method? I ran into White Box/Black BoxQA testing. All these are new to me. Any good place to read about"Extreme Programming"? I ran into one link that I saved it at work.That's one place i will read more.Any links, documentation or books you would suggest?I searched Amazon and I didn't find anything interestingregarding SQL Server and Stored Procedures.Thank you

View 1 Replies View Related

Unit Testing

Jun 20, 2005

Well was wondering this weekend. How do you do unit testing with SSIS? Is anyone trying it?

View 9 Replies View Related

How To Use Unit Test In SSIS

May 8, 2007

I build a SSIS Package in Visual Studio 2005 , but i don't know how to test it automated?

View 7 Replies View Related

...Contains More Than The Maximum Number Of Prefixes. The Maximum Is 3.

Oct 10, 2005

SQL Server 2000 8.00.760 (SP3)I've been working on a test system and the following UDF worked fine.It runs in the "current" database, and references another database onthe same server called 127-SuperQuote.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = [127-SuperQuote].dbo.tblCompany.Address1FROM[Work] INNER JOIN[127-SuperQuote].dbo.tblCompany ON [Work].ClientID =[127-SuperQuote].dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDSo now the system has gone live and it turns out that the live"SuperQuote" database is on a different server.I've linked the server and changed the function as below, but I get anerror both in QA and when checking Syntax in the UDF builder:The number name 'Zen.SuperQuote.dbo.tblCompany' contains more than themaximum number of prefixes. The maximum is 3.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = Zen.SuperQuote.dbo.tblCompany.Address1FROM[Work] INNER JOINZen.SuperQuote.dbo.tblCompany ON [Work].ClientID =Zen.SuperQuote.dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDHow can I get round this? By the way, I've rather simplified thefunction to ease readability. Also, I haven't posted any DDL because Idon't think that's the problem!ThanksEdward

View 2 Replies View Related

Adding Columns With Different Time Unit

Oct 29, 2014

I have three different columns, hour(s), min(s), sec(s)

I can add it up, but will like to convert it into. hrs, mins and sec.

this is how, i am adding it up into seconds.

SELECT ((TotalTimeSpentHrs*60*60)+(TotalTimeSpentMin*60)+(TotalTimeSpentSec))AS totaltime
FROM EST1

How can I convert the total seconds, so that i can input the result in a new column.

View 1 Replies View Related

Custom Component And Unit Testing

Nov 11, 2007

Hello.

I want to test my custom component with unit tests and i thought i must only initilize the component to play around with it. But when i calling the ProviderComponentProperties method and there the RemoveAllInputsOutputsAndCustomProperties method a NullReference exception is thrown. After debugging the test i had seen that the ComponentMetaData of the component is null. Is there a way to initilize the ComponentMetaData?

The Code of the Component looks like this:

Code Block
[DtsPipelineComponent(
DisplayName = "TestSourceAdapter",
ComponentType = ComponentType.SourceAdapter,
IconResource = "TestSourceAdapter.TestSourceAdapter.ico"
)]
public class TestSourceAdapter: PipelineComponent
{

public override void ProvideComponentProperties()
{

RemoveAllInputsOutputsAndCustomProperties();

IDTSOutput90 output = ComponentMetaData.OutputCollection.New();
output.Name = "TestSourceAdapter";

ComponentMetaData.OutputCollection

["TestSourceAdapter"].ExternalMetadataColumnCollection.IsUsed = true;

ComponentMetaData.ValidateExternalMetadata = false;
ComponentMetaData.UsesDispositions = true;
ComponentMetaData.Name = "TestSourceAdapter";
ComponentMetaData.Description = "TestSourceAdapter";

ReinitializeMetaData();
}

...
}

And in test i call only this

Code Block
TestSourceAdapter testAdapter = new TestSourceAdapter();
_testAdapter.ProvideComponentProperties();

i hope anyone can help

regards
kai

View 1 Replies View Related

File Allocation Unit Size

Mar 20, 2007

This is a question that has always intrigued me: what is the ideal File Allocation Unit Size for a disk holding only data or index pages on a server running SQL Server? It seems to me that 8,192 would be the ideal size as it would enable the system to gobble up an entire page in one go. Any ideas?

View 18 Replies View Related

Time Format - What's The Smallest Unit SQL 2005 Can Take?

May 2, 2007

saw my AS400 has this time stamp: 2007-05-02-11.29.56.811195

is SQL2005 able to have time stamp to this details?

View 1 Replies View Related

Breakout Records Based On UNIT Total Without Cursor

Jan 30, 2008

I have a set of revenue records where there is a UNIT column and a REVCHARGE column. What I need to do is breakout the records into single records where the unit count is > 1 and calc the actual charge:

Ex:

Units REVCHG FIELD_A FIELD_B .....
3 3.00 ABCD EFGH

Needs to be converted to:

Units REVCHG FIELD_A FIELD_B .....
1 1.00 ABCD EFGH
1 1.00 ABCD EFGH
1 1.00 ABCD EFGH

The calc is obvious but how can I do this with a cursor but would like to do it without a cursor if possible? Anybody got an idea?

Thanks.

View 6 Replies View Related

Clearing All Messages From A Queue - For Automated Unit Test

Jun 4, 2007

Hi,

I am developing automated .Net Unit Tests, and as a prerequisite of each test, I would like to clear the service broker queues of any messages. Executing the

RECEIVE * FROM statement appears to only return a message at a time, and not all as I expected. Any ideas on how to make this happen, besides not having to delete the queues and then having to rebuild them?

Thanks,Eugen

View 3 Replies View Related

What Are To Be Installed At The Production Unit To Deploy A Package Created ?

Nov 14, 2006

Hi friends,

Can anybody tell what all are required for the deployment of a package.

I read from some article that to deploy ur package SSIS runtime and .Net framework should be installed first

and 1)it is not necessary to install sql server 2005. if so Is it True .?

2)whether the ssis runtime is freely available as independent installation package like .Net framework

View 5 Replies View Related

SQL 2012 :: Error Occurred While Attempting To Drop Allocation Unit

May 4, 2015

Since a couple days, we are getting this message the errorlog of one of our SQL2012 server

LogEntry: Error [36, 17, 145] occurred while attempting to drop allocation unit ID 451879652360192 belonging to worktable with partition ID 451879652360192.
(version Microsoft SQL Server 2012 - 11.0.5058.0 (X64))

I am wondering what is the best way trying to troubleshoot this issues? I do not know from which of out database this is coming.

View 6 Replies View Related

Cannot Insert The Value NULL Into Column 'OrderID' -- BUT IT IS NOT NULL!

Apr 2, 2007

I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." -- But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.

Server Error in '/' Application.

Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.Source Error:

Line 89: sContact.Phone = sPhone.Text.Trim
Line 90: sContact.Email = sEmail.Text.Trim
Line 91: sContact.Save()
Line 92:
Line 93: Dim bContact As Contact = New Contact()Source File: F:InetpubwwwrootOutman KnifeCheckout.aspx.vb Line: 91 Stack Trace:

[SqlException (0x80131904): Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +214
System.Data.SqlClient.SqlDataReader.Read() +9
System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) +39
System.Data.SqlClient.SqlCommand.ExecuteScalar() +148
SubSonic.SqlDataProvider.ExecuteScalar(QueryCommand qry) +209
SubSonic.DataService.ExecuteScalar(QueryCommand cmd) +37
SubSonic.ActiveRecord`1.Save(String userName) +120
SubSonic.ActiveRecord`1.Save() +31
Checkout.btnCheckout_Click(Object sender, EventArgs e) in F:InetpubwwwrootOutman KnifeCheckout.aspx.vb:91
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

View 8 Replies View Related

ExecuteScalar --&> How To Get The OrderID(Identity) From A Table To Another Table ??

Mar 23, 2006

I am new to asp.net and studying on book.. currently i am stuck with a problem which not understand what is it !! Can anyone help me ?? I trying a shopping cart "Check Out" method, and when i am done the process.. My order_lines Table can update the OrderID which just generated !! What wrong with the statement ??

Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles Wizard1.FinishButtonClick        ' Insert the order and order lines into the database        Dim conn As SqlConnection = Nothing        Dim trans As SqlTransaction = Nothing        Dim cmd As SqlCommand
        Try            conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)            conn.Open()            trans = conn.BeginTransaction            cmd = New SqlCommand()            cmd.Connection = conn            cmd.Transaction = trans
            ' set the order details            cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@MemberName, @OrderDate, @Name, @Address, @City,@State, @PostCode, @Country, @Total)"            cmd.Parameters.Add("@MemberName", Data.SqlDbType.VarChar, 50)            cmd.Parameters.Add("@OrderDate", Data.SqlDbType.DateTime)            cmd.Parameters.Add("@Name", Data.SqlDbType.VarChar, 50)            cmd.Parameters.Add("@Address", Data.SqlDbType.VarChar, 255)            cmd.Parameters.Add("@City", Data.SqlDbType.VarChar, 50)            cmd.Parameters.Add("@State", SqlDbType.VarChar, 50)            cmd.Parameters.Add("@PostCode", Data.SqlDbType.VarChar, 15)            cmd.Parameters.Add("@Country", Data.SqlDbType.VarChar, 50)            cmd.Parameters.Add("@Total", Data.SqlDbType.Money)
            cmd.Parameters("@MemberName").Value = User.Identity.Name            cmd.Parameters("@OrderDate").Value = DateTime.Now()            cmd.Parameters("@Name").Value = CType(Wizard1.FindControl("txtName"), TextBox).Text            cmd.Parameters("@Address").Value = CType(Wizard1.FindControl("txtAddress"), TextBox).Text            cmd.Parameters("@City").Value = CType(Wizard1.FindControl("txtCity"), TextBox).Text            cmd.Parameters("@State").Value = CType(Wizard1.FindControl("txtState"), TextBox).Text            cmd.Parameters("@PostCode").Value = CType(Wizard1.FindControl("txtPostCode"), TextBox).Text            cmd.Parameters("@Country").Value = CType(Wizard1.FindControl("txtCountry"), TextBox).Text            cmd.Parameters("@Total").Value = Profile.Basket.Total
            Dim OrderID As Integer            OrderID = Convert.ToInt32(cmd.ExecuteScalar())  <-- Is it wrong or need to add wat ?            ' change the query and parameters for the order lines            cmd.CommandText = "INSERT INTO OrderLines(OrderID, ProductID,Quantity, Price) VALUES (@OrderID, @ProductID, @Quantity, @Price)"            cmd.Parameters.Clear()            cmd.Parameters.Add("@OrderID", Data.SqlDbType.Int)            cmd.Parameters.Add("@ProductID", Data.SqlDbType.Int)            cmd.Parameters.Add("@Quantity", Data.SqlDbType.Int)            cmd.Parameters.Add("@Price", Data.SqlDbType.Money)            cmd.Parameters("@OrderID").Value = OrderID
            For Each item As CartItem In Profile.Basket.Items                cmd.Parameters("@ProductID").Value = item.ProductID                cmd.Parameters("@Quantity").Value = item.Quantity                cmd.Parameters("@Price").Value = item.UnitPrice                cmd.ExecuteNonQuery()            Next            ' commit the transaction            trans.Commit()        Catch SqlEx As SqlException            ' some form of error - rollback the transaction            ' and rethrow the exception            If trans IsNot Nothing Then                trans.Rollback()            End If            ' Log the exception            Throw
        Finally            If conn IsNot Nothing Then                conn.Close()            End If        End Try        ' we will only reach here if the order has been created successfully        ' so clear the cart        Profile.Basket.Items.Clear()    End Sub

View 4 Replies View Related

Getting The Right Price

Feb 21, 2008

Hello all, I have a question. I have two tables, one called Products which is comprised of various lookup values for IDs. The sceond table is a join table where I enter through a tool that I built another series of lookup IDs for Product, Size, Color, Quantity and Price. Each product in the join table can have multiple entries because there is not one price for each product. Instead, based on the product size, color there are price breaks at certain quantities - for example if someone orders one dozen widgets then the price will be one amount, while if they order two dozen they will get a different price.

Here is an example:

Product table
ProductID ProductName Description
1 Widget This is a widget description...

ProductPrice tbl
ProductID ColorID SizeID QuantityID Price
1 1 1 1 10.00
1 1 1 2 9.50
etc....

Quantity tbl
ID Description
1 12 - or one dozen
2 24

So what I want to do is get a complete list of products with the MAX price for each product returned. This will be displayed on a page where I will have the Product Name, Description, etc and the Price will state something like 'Starting at $10.00'

Here is what I have so far but I am not getting any results....

SELECT p.ProductID,
p.ProductName,
pc.ProductCategoryName,
p.ProductImageID,
SUBSTRING(p.Description, 1, 150) + '...' AS Description,
(SELECT DISTINCT MAX(Price)
FROM ProductColorSizeQuantityPrice
WHERE ProductId = 1) AS 'Price'
FROM Products p
INNER JOIN ProductColorSizeQuantityPrice pcs
ON pcs.ProductID = p.ProductID
INNER JOIN ProductCategory pc
ON pc.ProductCategoryID = p.ProductCategoryID
The area highlighted in blue is what I am trying to do logically but this is not getting any results. If I removed this section I get all the fields I want but I need to include this price. Any ideas?

View 6 Replies View Related