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.
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!
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 have a stored procedure that Inserts data from one table to the next, I need to add a delete statement to it. I jusually just use the delete option in Access 2003 but I have decided it would be easier to just delete from original table through the sp, only problem is I dont rememeber how to incorporate it into my SP, although at one time I did have it in there then I took it out
REATE PROCEDURE InsertTerms AS INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #], [FirstName], [LastName], [SocialSecurityNumber], [DateHired], [Status], [Title], [DepartmentName], [Pictures])
SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS FROM EmployeeGamingLicense AS a WHERE a.STATUS = 'TERMINATED' IF @@Error <> '0' RETURN
Hi there, I have a simple data flow from OLE DB to OLE DB that writes data from a view into a table. Is it possible to delete the table or the rows within the table before wirting the new data???
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.
Ok, we need to purchase a license enterprise license. However, I got confused on what to pick. We have one machine with a single processor. Does anyone have any idea on what to get? This is apparently my first time and there are a vast number of different options. thanks in advance
First time shopper on this board...hopefully you can help me.
I am trying to figure out how to schedule a DTS package located on a Server to run at a certain time. The DTS package will contain the path to a V/B 6 executable file residing on the same server.
So to test my methodology, I made a package on a server with code to run the V/B 6 executable off of my local machine. When executing the package manually it works fine. However, when I schedule it the job does not start. I could not find a way to edit the job I had previously scheduled so I made another one in case I "fat fingered" something. The same thing happens...no job starts.
How should I set up a database to be able to efficiently maintainassociations between related items?Example: Users shopping for Lord Of The Rings trilogy were alsointerested in The Hobbit.There will be many (20000+) items for sale and I need to do thisefficiently, but I don't have an idea how could I do it.TIA
After a customer decides to buy a shopping list, there is generally aneed to store/insert one master record and a variable number of childdetail records, preferably all wrapped in a transaction. There are lotsof ways to do this and I am wondering if anyone knows which is mostefficient.One approach is to use ADO.NET's transaction capabilities, definesingle-record insert procs for the master and detail tables, and callthe detail insert in a loop from the web page. This has N+1 trips tothe server, which is not too attractive.Another approach is to concatenate all the data into a bigstring/varchar variable and pass it to a decoder proc that would thencall the single record insert procs via a loop inside the decoder proc.This second approach would use T-SQL's transaction capability and haveonly one trip to the server, but it is more effort to code on the webpage and in the decoder proc.Surely this is a common problem. Are there any more elegant/efficientmethods that anyone can suggest? Can one pass an array to a proc? Isthis a place for a user defined data type?Any advice is much appreciated.
Dear all, I am trying to create a recipe database and one of the funtions is to generate shopping list based on the selected recipes.
at the moment, I have a table --recipe, a table--ingredient, and a cross over table between these two, reIng. Does anyone know how can I write a procedure with cursor that no matter how many recipes the user selected, a list of ingredients will be created based on that? Another things I can't solve is if an ingredient appears twice, how can I make them sum together..
I can write a simple procedure with one parameter so that I can get the ingredients for a certain recipe.( the parameter is the recipeID ). But I get struck when the possibility is that user will select more than one recipe and wish to get a list of the ingredients. should I use a cursor to do that? Or I should create a temporary table to store the selected recipe?
nevermind. please ignore this. theres too much extra data that id have to provide, but it would confuse the question so much that it couldnt be answered properly.
We are evaluating DB mirroring for our production box. But we have some queries with the operating mode available for mirroring.
1) I set the database miroring operation mode to full safety mode which is with synchronous. And Then executed a command to insert data in bulk in principal, the changes were commited to principal even when they are still getting mirrored to mirror server.
But as per the mirroring documentation from microsoft says this
"If transaction safety (or just 'safety') is set to FULL, the principal and mirror servers operate in a synchronous transfer mode. As the principal server hardens its principal database log records to disk, it also sends them to the mirror. The principal then waits for a response from the mirror server. The mirror responds when it has hardened those same log records to the mirror's log disk."
Is this a bug or I am doing anything wrong.
2) Mirroring documentation also says that when the transaction is on full safety mode, and if mirror and witness goes down then principal goes to read only mode. But again I saw deviation from the documentatin because when I turned mirror and witness off, principal went to "In recovery" mode.
Please help incase anybdy have faced this before or can help me if I am interpreting it wrong.
HI, I am working on a Family tree portal which need tree functionality to display family members in tree structure. on click on any node the adding option should be displayed for this i need a table and procedure to complete family tree Thanks @mbi
I am using LIKE operator for a query to get wild card searched data.
for example if i give AN, should get for %AN% only. But it is returning data for %NA% data too. I cross checked by giving simple data. Please help me on this.
I have a table in one database (source) and an equivalent table in another (destination) and I need to move from my source to my destination database.
When I move the data I need to do the equivalent of an updategram, so for instance compare primary keys and if it exists in both do an update, if it is in source but not destination do an insert, if it is in destination but not source do a delete.
Is there already a process for doing this, as it seems pretty fundamental but I've not really found anything that gives the result I need?
If not, is there a way of doing this with what is available within Data Flows or should I just go ahead and go through the pain of creating my own data flow destination object?
I have 3 tables that i gather a single field from each and put them into another table.. simple enough you might think
but when i start using a lookup funciton to check if i have already entered an item (this package could be run a number of times) some duplicates slip though, and as i continue to run the pakcage less and less appear, but never reaching 0
very strange..
doing select statements on the target table reveal strange behaviour.. after the first 'iteration' there are only unique entries in the target table (exactly what i want). After the 2nd, 3rd, 4th etc duplicates appear of upper and lower case
i can only assume that there is something wrong with my query,,, but running it in SQL Manager reveals ~40,000 rows no matter how many times i run it...
more magically appear when hte same statement is ran in SSIS!!
I have tried a view, and using test tables (rather the live ones i am working on) and the reuslt is the same...
Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.
currently have my website that is functional...but would like to add some navigation to my photos which are displayed using a repeater tied to a SQL database. (live example can be viewed here) the above link shows a gallery page that displays all the photos within a given shoot (using a querystring to display the unique ID of the photo shoot). When you click on one of the images within the shoot a page called is called displaying the selected photo by passing the shoot ID and the unique ID of the photo itself through a querystring. I would like to add navigation to the page displaying the photo that would allow the user to go to the next photo, previous photo, first and last phot within the specific SHOOT ID. FirstNextPreviousLast I am using a repeater to display the photo with the following sqlDataSource: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Connection %>"SelectCommand="SELECT usr_Photos.*, usr_PhotoShoot.* FROM usr_Photos INNER JOIN usr_PhotoShoot ON usr_Photos.Shoot_ID = usr_PhotoShoot.Shoot_ID WHERE usr_Photos.Photo_ID = @ID AND usr_Photos.Shoot_ID = @Shoot"><SelectParameters><asp:QueryStringParameter DefaultValue="" Name="ID" QueryStringField="ID" /><asp:QueryStringParameter DefaultValue="" Name="Shoot" QueryStringField="Shoot" /></SelectParameters> </asp:SqlDataSource> I am having trouble figuring out a way to add the functionality i would need to change photos within the specific photo shoot without having to go back to the gallery page. any help or ideas you could point me in would be great...thanks in advance...
In the BOL about Designing a Backup and Restore Strategy, it is mentioned there is a Base Functionality Script that should be run after SQL Server restored. What is Base Functionality Script? Just DBCC CHECKDB? Anything else?
Hi,I've used the minus functionality which is available in Oracle andi would like to use it in SQL server, but i don't know how to. Thefolllowing is how it works in OracleSelect symbols from symbol_tableminusselect tsymbols from tradeIt returns a list of all the symbols from symbol_table which are notpresent in trade.Similarly, the intersect will return only those which are common toboth.I was wondering if someone throw some light on this problem for me.Thanks in advance,Sumanth
I'm not seeing any built-in functionality for Report Builder to get Top N values. Am I missing something? Is there a way to do this? If not, is there a way to simulate this functionality?
There is a view on our server that has fields for project name and then 25 months, each row contains the descriptive name and the forecasted budget in each month. I need to create a chart showing the total budget for month and a running total for the year to date. In Excel this is easy with a Pivot Chart, how do I do this in SSRS 2005?
In one of my packages, I am trying to create functionality that is similar to the fuzzy grouping transformation in that it takes a set of records and returns a subset of unique records. However, rather than fuzzy-matching columns which creates significant overhead, I just want to compare columns exactly, similar to how the lookup transformation works. At this point, data has already been fuzzy unique/duplicate seperated, this step is to only keep one record for each exactly similar dupe for an alias table.
At the moment, I am doing this (with lack of scalability foresight) with a fuzzy grouping transformation with all columns set to exact except for one which is set to fuzzy with a minimum similarity of 0.99. While this worked during development when I was testing with a small subset of data, it is unreasonable for the ~5mil records of production data it needs to operate on.
I am sure there is an efficient way to do this. I have thought about using a lookup transformation or combinations thereof, but I have not come to a definite solution yet.
Does anyone have any experience doing something similar to what I am trying or have any suggestions/ideas/questions/etc? Any help will be greatly appreciated.
