I'm looking for any help/tricks on creating an item numbering system in a MSSQL database. The object is to number the items in a cart from 1 to whatever and have the ability for the user to change that sequence. The client wants the ability to order the items any way they choose by changing the sequence number. This process would require.
1. Starting at one (1) for the first item in the cart and incrementing from there.
2. If I have 10 items and I change item 3 to 5, items 4 and 5 have to increment down by one and 6-10 would stay the same. (and vise versa moving item 3 to item 1)
3. If I have 10 items and I delete item 4, 5-10 must incremet down by one.
On 1 my first though in adding items is doing a select count to see if any items exist in the cart. If they don't assign the number one. if it does do a max on the sequence column and increment by one.
on 3 I would simply delete the item and do an update on all items with seq# > then the deleted Seq#
on 2 I'm not sure yet.
Am I on the right track and are there any tricks to doing this? Any help would be appreciated. This would all be done in stored procedures.
Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record. My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam ALTER PROCEDURE [dbo].[UpdateInvoiceSummary] @Invoice_ID intAS DECLARE @Amount intBEGIN SELECT Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX FROM InvoiceItems AS items GROUP BY Invoice_ID HAVING (Invoice_ID = @Invoice_ID) Update Invoices SET Amount = items.Amount WHERE Invoice_ID =@Invoice_IDEND
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode 1A 1B 24 28 26 310 312 410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10 - 2 and 11 - 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). Â In some cases, items can be components of another item (called "Kits"). Â In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". Â In our Items table, we have a field called "Parent_Item_Id". Â Records for Child Items contain the Item Id of the parent. Â So a sample of my database would be the following:
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. Â So an example of the report would be the following:
Name | Available Qty -------------------------- Kit A | 15 Kit B | 25 Item 4 | 100
from this code, i want to reduce product's quantity in database. when I click buy button,it reduce old quantity and new quantity (quantity from cart when I want to buy it) in auto. please advise me.
hi! I'm still fairly wet behind the ears when it comes to databases, especially sql server 2000. But I just got a copy from my University and I really want to spend some time messing with it. The first thing I want to try is making a shopping cart that stores the items in the database rather than in the session. I'm using asp.net (VB) and I had previously made a simple shopping cart for a SMALL site that stored all the information in a dataset stored in the session... but I've been told that isn't a good idea and reccomended I store the data in the sql database. so that's what I want to do!
I'm looking for resources that can help me get this started. These are the first questions I have about this approach, and any advice from you experts would be most appreciated!
1) do I store the data in a temporary table or do I use the regular table and make temporary rows? if the former how do I transfer the rows from the temp table to the order table? if the latter how do I change it from temporary to peromanent? in either case, how do I eliminate rows if the user abandons their cart?
2) how do I first initialize an order and keep track of the orderID for each user and ensure that no one is assigned a duplicate orderID?
those are my biggest confusions about this approach. I've looked for books on this but most are either too much about sql syntax and big-time server management, or they are too general and not specific to sql server. I've tried looking online, but I can't find much information on doing this using asp.net 2.0. thanks again for your help!
Hello, I'm in the progress of developing a shopping cart system that operates with an SQL database in Visual Web Developer 2005. I've managed to successfully add items to the cart and display them, but I'm having trouble providing the user with the option of removing items from the cart. My understanding so far is that I've got to adjust the DELETE SQL statement of the data source. At first I was thinking along the lines of a simple statement: DELETE * FROM ShoppingCart WHERE CartID=@CartID AND CategoryID=@CategoryID AND ProductID=@ProductID However, I've realised that the parameters I need for this SQL query aren't automatically passed in when a user clicks the "Delete" text of the Delete field (at least I think this is the problem). The error message which I'm getting when I try to remove an item from the cart is as follows: Incorrect syntax near '*'. If anyone could let me know where I'm going wrong and point me in the right direction I'd be really grateful. Thank you, Luke
I'm trying to figure out how to associate users in my ASPNETDB.MDF to create a shopping cart.IE: I have 3 tables, for a list of existing orders, list of products, and list of specific orders.The idea is for users who've signed up with the built-in user creation control, can then place orders from the shopping cart, and view their existing previous orders. This would normally be done with a unique UserID # to associate orders with specific users, however I don't BELIEVE there's anything like UserID's associated with each user in the ASPNETDB.MDF, it just uses usernames as the unique identifiers right?Anyway I'm not sure I'm explaining what I'm trying to do perfectly and I apologize, but I don't think it's an un-common problem I'm having. Basically I'm trying to avoid having to have existing ASPNETDB users create a sort of SECOND user that would associate them into a seperate Customers table, it seems like extra user steps that shouldn't be necessary.
Im making a shopping cart website for a school project in ASP.net with VB. I need help subtracting the quantity purchased (its saved in a session) from the stock number saved in a database.I know this:UPDATE inventory SET stock = stock - <quantity_purchased> WHERE id = <inventory_id>But I dont understand how to get the quantity purchased from the session to <quantity_purchased>. I tried putting the name of the session there and I got an error, i tried saving the session into a dim didnt work either.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [stock] FROM [product]" InsertCommand="INSERT INTO [product] ([stock]) VALUES (@stock)" UpdateCommand="UPDATE product SET stock = (stock - @Quantity) WHERE (productID = @productID)"> <InsertParameters> <asp:Parameter Name="stock" Type="Int16" /> </InsertParameters> <UpdateParameters> <asp:SessionParameter Name="Quantity" SessionField="Quantity" Type="Int32" /> <asp:SessionParameter Name="productID" SessionField="productID" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource> and I have than in my VB code on submit : SqlDataSource1.Update()
Hi all, There is nothing happen when I finished my checkout process, I expect the data will be saved to order and orderitem table in my SQL database, but no data found on order and orderitem table and no error messages display during operation!!! Below is my checkout.aspx.vb code, the whole code line number around 138, I captured the part from 1~ 64 line number, I suspect line 35 - 48 have a problem, can somebody help me, many thanks. 1 Imports System 2 Imports System.Data.SqlClient 3 Imports SW.Commerce 4 Partial Class CheckOut 5 Inherits System.Web.UI.Page 6 Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 7 If Not Page.IsPostBack Then 8 If Profile.Cart Is Nothing Then 9 NoCartlabel.Visible = True 10 Wizard1.Visible = False 11 End If 12 If User.Identity.IsAuthenticated Then 13 Wizard1.ActiveStepIndex = 1 14 Else 15 Wizard1.ActiveStepIndex = 0 16 End If 17 End If 18 End Sub 19 Sub chkUseProfileAddress_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) 20 21 ' fill the delivery address from the profile, but only if it’s empty 22 ' we don’t want to overwrite the values 23 24 If chkUseProfileAddress.Checked AndAlso txtName.Text.Trim() = "" Then 25 txtName.Text = Profile.Name 26 txtAddress.Text = Profile.Address 27 txtcity.Text = Profile.City 28 txtCountry.Text = Profile.Country 29 End If 30 End Sub 31 Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) 32 33 ' Insert the order and order lines into the database 34 35 Dim conn As SqlConnection = Nothing 36 Dim trans As SqlTransaction = Nothing 37 Dim cmd As SqlCommand 38 Try 39 conn = New SqlConnection(ConfigurationManager.ConnectionStrings("swshop").connectionstring) 40 conn.Open() 41 trans = conn.BeginTransaction 42 cmd = New SqlCommand() 43 cmd.Connection = conn 44 cmd.Transaction = trans 45 46 ' set the order details 47 48 cmd.CommandText = "INSERT INTO Order(MemberName, OrderDate, Name, Address1, Address2, Country, Total) VALUES (@MemberName, @OrderDate, @Name, @Address, @city, @Country, @Total)" 49 cmd.Parameters.Add("@MemberName", Data.SqlDbType.VarChar, 50) 50 cmd.Parameters.Add("@OrderDate", Data.SqlDbType.DateTime) 51 cmd.Parameters.Add("@Name", Data.SqlDbType.VarChar, 50) 52 cmd.Parameters.Add("@Address", Data.SqlDbType.VarChar, 255) 53 cmd.Parameters.Add("@City", Data.SqlDbType.VarChar, 15) 54 cmd.Parameters.Add("@Country", Data.SqlDbType.VarChar, 50) 55 cmd.Parameters.Add("@Total", Data.SqlDbType.Money) 56 cmd.Parameters("@MemberName").Value = User.Identity.Name 57 cmd.Parameters("@OrderDate").Value = DateTime.Now() 58 cmd.Parameters("@Name").Value = CType(Wizard1.FindControl("txtName"), TextBox).Text 59 cmd.Parameters("@Address").Value = CType(Wizard1.FindControl("txtAddress"), TextBox).Text 60 cmd.Parameters("@City").Value = CType(Wizard1.FindControl("txtCity"), TextBox).Text 61 cmd.Parameters("@Country").Value = CType(Wizard1.FindControl("txtCountry"), TextBox).Text 62 cmd.Parameters("@Total").Value = Profile.Cart.Total 63 Dim OrderID As Integer 64 OrderID = Convert.ToInt32(cmd.ExecuteScalar())
I am having trouble getting the results I am looking for from this query:
select ROW_NUMBER() OVER(PARTITION BY TransTime, DriverNo ORDER BY TransTime) as CustCount , ROW_NUMBER() OVER(PARTITION BY Left(ToName,2), TransTime, DriverNo ORDER BY TransTime) As DelCount , DriverNo , Convert(VarChar(8), TransTime, 108) As TransTime , Left(ToName,2) As AirPortCode from transactions
The results I am trying to get would be like this:
The CustCount field should read 1 if there is only one unique AirPortCode during the TransTime with the specific DriverNo, but the query is counting both rows during that transtime. Any ideas?
I have table with 10-20 rows with field P6 which is empty. I want toupdate numbers to P6 starting 1 and increasing by 1. I suppose it isdone by triggers but I don't know how to do that. Help :-)
I created a simple report for getting the top selling items of a store. I am getting the report well but my team mate requested to include a number at the first column that will hold the ranking of the item. Is there a function for including a rowrank or someway that i can add a column to my query that will provide the rowrank. Please show me how... c",)
I've upsized my database from access to SQL server, now im very new to SQL etc and i've been reding up on sqential numbering etc and my mind is buzzing with ideas. This is my problem.
I have a table and a colum called PIR ID this colum needs to start the numbering at 372 and increment by 1 each time. Now initially this was o.k by using identify. However it's a problem when 2 users open the form in access they are given the same number.
Now my though on this was to initially have a seperate table called new PIRID and hold the next bumber in there, so that a new number is taken from the list and entered into the record on save ( does that make sense) now the code i've got up to so far is that i've created the table and i get stuck from there.
Can anyone help me with the code of format of code i'm meant to be using, i'm under the understanding that to perform this funtion i may have to use a trigger of some sort.
Hello,For analyses and reporting I would like to add a 'ranking' to atable/view.Example:Using the 'order by desc' clause in query I get a list of Customersordered by Turnover (descending). I would like to add that rankingnumbers (same as recordnumbers) in the query. I would like to have thefollowing result:Cust_nr Cust_Name Turnover_2004 Ranking002234 Bayer 139.000 1003456 Rentokill 123.456 2001231 Air France 105.000 3etc.When the 'ranking' is part of the query/table I can use this ranking inan other query.Important: This questions is not about making an (empty) table structurefor filling in by an application and generating a new unique number eachtime a record is added.I hope you can help me.Thanks,Hans*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Is there a quick way to number a query?ie I want to number the records returned by a recordset consecutively.This seems like it should be simple but I haven't figured out how to do ityet.Any help is appreciated!TIACarter
I have created an ID column for auto numbering, but in the properties, they didn't have the option of setting it to auto numbering, and how am i suppose to set or code it to autonumbering.
Hi,I'm trying to insert records from one table into another table. The destination table has a ROWID field which cannot be an identity key, but needs to 'act like' an identity key and have its value populated with (Max(ROWID) + 1) for each row added to the table.To my thinking, simply using (Max(ROWID) + 1) in my SELECT statement will not work as it will only be evaluated once so if I am adding 1000 records and Max(ROWID) is 1234, all 1000 entries will end up having a ROWID of 1235.Is there a way to accomplish this?Thanks
I know its a simple question. But couldnt find a solution of my own. My SQL 2005 table has 2 fields, namely - id (primary key), comments(varchar). I want to automatically increment the value of 'id', starting from 1000. Is there any DataType which i can specify at the time of table designing, so that the value of the id field will be automatically incremented, upon inserting each new record. Im using SQL 2005 Express Edition. Can anyone tell, how to do that ? Thanks
I'm creating an application to track project issues. I would like to have the issue number start over at 1 for each project. I would like to have the issue numbers assigned to each issue in sequential order for each project. Below is a brief example of what I need it to do. I don't know where to even start to try to figure this out. Thanks ProjectNumber, IssueNumber TEST0001, 1TEST0001, 2TEST0002, 1TEST0003, 1TEST0003, 2TEST0001, 3TEST0004, 1TEST0003, 3TEST0002, 2TEST0005, 1
I'm trying to number rows in a resultset by a grouping.
e.g. if my data looks like this:
personname persondata ----------- ----------------- person1 data person1 more data person1 other data person2 stuff person2 more stuff person2 even more stuff person2 lots of stuff person2 last bit of stuff person3 info person3 more info
I want it to return this, with x as a sub-numbered value (like an identity for each grouping):
x personname persondata -- ----------- ----------------- 1 person1 data 2 person1 more data 3 person1 other data 1 person2 stuff 2 person2 more stuff 3 person2 even more stuff 4 person2 lots of stuff 5 person2 last bit of stuff 1 person3 info 2 person3 more info
I'm trying to get a MSSQL view to return some data marked with a week number based on a date (which is present on every row in the source table). Since I reside in Denmark, I would like the numbering to follow the rule '1. week of a year is the first week with a least 4 days in it'. My instance of MSSQL however is convinced that the rule '1. week starts 1. January'.
Are there any ways to configure my way out of this problem??
Examples: This year 1. and 2. January is returned as week=1, 3. January is returned as week 2. I would like 1. and 2. to be week=53 and 3. to be week=1.
I've created a sproc that will provide a recordset for an Access report via a pass-thru query. The report is a production schedule. Some of the runs on the schedule have a note associated with them. I need to be able to number these notes, so that they can be displayed in the report bibliography style. When I populate the data in the sproc, if the "notes" field in my table contains any data I display a 1, else it's a 0 Like:
SELECT NOTE_FLAG = CASE WHEN ISNULL(SCHED_NOTE,'')<>'',1,0 FROM MASTER_SCHEDULE
The problem is, I really need to display sequential numbers instead of 1's
INSERT INTO #TMPRST SELECT 1, 'ABC123', 4, 0 UNION ALL SELECT 4, 'DEF123', 5, 1 UNION ALL SELECT 5, 'ABC456', 12, 0 UNION ALL SELECT 13, 'PQR789', 10, 1
How do I go back and convert the note_flag column to read ... 0 ... 1 ... 0 ... 2
We're performing an insert and the column that has identity is numbering the records without a problem, but it's assigning duplicate ID's on the records. Does Autoincrement or Autonumbering need to be turned on somewhere?
