Wanted: Sql Shopping Cart Resources
Sep 30, 2005
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!
-SelArom
View 6 Replies
ADVERTISEMENT
Feb 5, 2008
protected void Buy_Click(object sender, EventArgs e)
{SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
try
{String connStr = ConfigurationManager.ConnectionStrings["project_DataFile"].ConnectionString;
conn.ConnectionString = connStr;
conn.Open();
cmd.Connection = conn;ArrayList cart = (ArrayList)Session["Cart"];
{String oldSel = "SELECT Qty_warehouse FROM Producttable";
Int32 sel = 0;Int32.TryParse(oldSel, out sel);
string num = cart.Count.ToString();Int32 numQty = 0;
Int32.TryParse(num, out numQty);int nQty_warehouse = 0;
nQty_warehouse = sel - numQty;String sql = "UPDATE Producttable" + " SET Qty_warehouse =" + "'"+ nQty_warehouse +"'";
cmd.CommandText = sql;}
}catch (Exception ex)
{
Response.Write(ex.ToString());
}
finally
{if (conn.State.Equals(ConnectionState.Open))
{
conn.Close();
Response.Write("end.aspx");
}
}
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.
View 3 Replies
View Related
Jun 23, 2004
After a user adds items to the cart he doesn't checkout but leaves the site.
The next user on the same computer then opens another browser and sees the same items in the cart.
How do I Empty temp shopping cart database after leaving the site just like if I were using cookies.
Thx.
View 1 Replies
View Related
Dec 29, 2005
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
View 2 Replies
View Related
Jan 2, 2007
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.
View 1 Replies
View Related
Dec 11, 2007
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()
View 1 Replies
View Related
Mar 16, 2008
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())
Below is checkout.aspx1 <%@ Import Namespace ="System.Data.SqlClient"%>
2 <%@ Import Namespace ="SW.Commerce"%>
3 <%@ Page Language="VB" MasterPageFile="~/SWSHOP.master" AutoEventWireup="false" CodeFile="CheckOut.aspx.vb" Inherits="CheckOut" title="Untitled Page" %>
4
5 <%@ Register Src="SWShoppingCart.ascx" TagName="SWShoppingCart" TagPrefix="uc1" %>
6 <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
7
8 <asp:Label id="NoCartlabel" runat="server" visible="false">
9 There are no items in your cart. Visit the shop to buy items.
10 </asp:Label>
11
12 <div style="float:right">
13 </div>
14
15
16 <asp:Wizard ID="Wizard1" runat="server" ActiveStepIndex="1" Width="274px">
17 <WizardSteps>
18 <asp:WizardStep runat="server" Title="Login">
19 <asp:Login ID="Login1" runat="server">
20 </asp:Login>
21 </asp:WizardStep>
22 <asp:WizardStep runat="server" Title="Delievery Address">
23 <asp:checkbox id="chkUseProfileAddress" runat="server" autopostback="True"
24 text="Use membership address"
25 OnCheckedChanged="chkUseProfileAddress_CheckedChanged"></asp:checkbox><br />
26 <table border=�0�>
27 <tr><td>Name</td><td><asp:textbox id="txtName" runat="server" /></td></tr>
28 <tr><td>Address</td><td><asp:textbox id="txtAddress" runat="server" /></td></tr>
29 <tr><td>City</td><td><asp:textbox id="txtcity" runat="server" /></td></tr>
30 <tr><td>
31 Country</td><td><asp:textbox id="txtCountry" runat="server" /></td></tr>
32 </table>
33 </asp:WizardStep>
34 <asp:WizardStep runat="server" Title="Payment">
35 <asp:DropDownList id="lstCardType" runat="server">
36 <asp:ListItem>MasterCard</asp:ListItem>
37 <asp:ListItem>Visa</asp:ListItem>
38 </asp:DropDownList>
39 <br />
40 Card Number: <asp:Textbox id="txtNumber" runat="server" Text="0123456789" ReadOnly="True"/>
41 <br />
42 Expires: <asp:textbox id="txtExpiresMonth" runat="server" columns="2" />
43 /
44 <asp:textbox id="txtExpiresYear" runat="server" columns="4" />
45 </asp:WizardStep>
46 <asp:WizardStep runat="server" Title="confirmation">
47 <uc1:SWShoppingCart ID="SWShoppingCart1" runat="server" />
48 <br />
49 <br />
50 Please confirm amount you wish to have deduct from your credit card.
51 </asp:WizardStep>
52 <asp:WizardStep runat="server" Title="Complete">
53 Thank you for your order.</asp:WizardStep>
54 </WizardSteps>
55 </asp:Wizard>
56 </asp:Content>
57 <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder3" Runat="Server">
58 <asp:LoginView ID="LoginView1" Runat="server">
59 <AnonymousTemplate>
60 <asp:passwordrecovery id="PasswordRecovery1" runat="server" />
61 </AnonymousTemplate>
62 </asp:LoginView>
63 </asp:Content>
View 2 Replies
View Related
Dec 15, 2005
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.
View 3 Replies
View Related
Oct 29, 2007
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
View 9 Replies
View Related
Mar 3, 2004
Good Afternoon
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.
Any ideas?
Thanks,
Ed
View 3 Replies
View Related
Jul 23, 2005
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
View 4 Replies
View Related
Oct 17, 2006
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.
View 2 Replies
View Related
Nov 19, 2007
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?
THanks for your help!
View 1 Replies
View Related
Aug 2, 2006
Ok guys I have read everything in my previous post but unfortunately can not seem to get it to work properly. Would anyone like to do this 10 minutes work (I am sure). Obviously I dont expect it to be free but if I continue I am going to get a sledge hammer to this now.
Thanks in advance
View 20 Replies
View Related
Nov 30, 2006
Hi, does somebody recognize the problem with my code? -Thanks!
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately. Parser Error Message: This is an unexpected token. The expected token is 'NAME'. Line 58, position 52.Source Error:
<sessionState mode="SQLServer"stateConnectionString="Jensen"sqlConnectionString=
"datasource=Database;userid=username;password=pass"cookieless="false"timeout="60"/>
View 1 Replies
View Related
Sep 23, 1999
Always looking for people who are strong with microsoft technology for new oppurtunities in the Seattle area. If you are good, we can help you find your ideal next position.
don't be shy. shoot me an e-mail.
thanks
Pat Copeland
View 2 Replies
View Related
Sep 16, 1999
I am looking for strong SQL developers and/or DBA's for some really sweet companies here in Seattle. Anyone interested that is good and wants to hear about new oppurtunities? I would love to help you out.
don't be shy just shoot me an e-mail.
View 2 Replies
View Related
May 8, 2002
Please help to find an example of the code written on VC++ for an extended stored procedure (dll) for SQL Server 2000.
Thanks
View 1 Replies
View Related
Mar 19, 2004
OK...this is driving me nuts....
In the First DELETE and bcp I was getting the thread being launched by xp_cmdshell was being blocked by the parent thread...
put in WAITFOR...sometime it worked...started with an empty table..it worked....left the 28k rows, blocked...
Now, put SELECT COUNT(*)...works each and every g-d damn time...
HUH?
Now I get to the bcp out..
added the same code WAITFOR/SELECT *...
blocks each and ever g-d damn time....
I'm very reticent to COMMIT and start another tranny block...
Anyone have any ideas?
SET NOCOUNT ON
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wrk_DataHold]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[wrk_DataHold]
GO
CREATE TABLE wrk_DataHold(Col1 varchar(8000))
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wrk_OldNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[wrk_OldNew]
GO
CREATE TABLE wrk_OldNew(Old varchar(255),New varchar(255))
GO
INSERT INTO wrk_OldNew(Old,New)
SELECT 'SEVERAL EE~S', ''
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_ModifyRows]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_ModifyRows]
GO
CREATE PROC usp_ModifyRows
@Path sysname
, @FName sysname
AS
SET NOCOUNT ON
BEGIN TRAN
DECLARE @cmd varchar(8000), @Servername sysname, @rc int, @error int, @rowcount int
, @Old varchar(255), @New varchar(255), @x int
CREATE TABLE #bcpLog(Col1 varchar(8000))
SET @rc = 0
DELETE FROM wrk_DataHold
SELECT @error = @@error, @rowcount = @@ROWCOUNT
IF @error <> 0
BEGIN
SET @rc = -1
GOTO usp_ModifyRows_Error
END
SELECT @x=COUNT(*) FROM wrk_DataHold
WAITFOR DELAY '000:00:10'
SET @cmd = 'bcp wrk_DataHold in ' + @Path + @FName + ' -S ' + @@SERVERNAME + ' -U -P -c'
INSERT INTO #bcpLog(Col1) EXEC master..xp_cmdShell @cmd
DECLARE OldNew CURSOR FOR SELECT Old, New FROM wrk_OldNew
OPEN OldNew
FETCH NEXT FROM OldNew INTO @Old, @New
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE wrk_DataHold
SET Col1 = REPLACE(Col1,@Old,@New)
WHERE Col1 LIKE '%'+@Old+'%'
SELECT @error = @@error, @rowcount = @@ROWCOUNT
IF @error <> 0
BEGIN
SET @rc = -1
GOTO usp_ModifyRows_Error
END
INSERT INTO #bcpLog(Col1)
SELECT 'REPLACE "'+ RTRIM(@Old) + '" With "' + RTRIM(@New)+ '"' UNION ALL
SELECT '('+CONVERT(varchar(25),@rowcount)+' row(s) affected)'
FETCH NEXT FROM OldNew INTO @Old, @New
END
CLOSE OldNew
DEALLOCATE OldNew
SELECT @x=COUNT(*) FROM wrk_DataHold
WAITFOR DELAY '000:00:10'
SELECT @FName = SUBSTRING(@FName,1,CHARINDEX('.',@FName)-1)+'.new'
INSERT INTO #bcpLog(Col1)
SELECT 'Preparing to Write out new file '+ @Path + @FName
/*
SET @cmd = 'bcp wrk_DataHold out ' + @Path + @FName + ' -S ' + @@SERVERNAME + ' -U -P -c'
INSERT INTO #bcpLog(Col1) EXEC master..xp_cmdShell @cmd
SET @cmd = 'bcp #bcpLog out D:cpLog.txt -S ' + @@SERVERNAME + ' -U -P -c'
INSERT INTO #bcpLog(Col1) EXEC master..xp_cmdShell @cmd
*/
COMMIT TRAN
usp_ModifyRows_Exit:
SELECT * FROM #bcpLog
DROP TABLE #bcpLog
SET NOCOUNT OFF
RETURN @rc
usp_ModifyRows_Error:
CLOSE OldNew
DEALLOCATE OldNew
ROLLBACK TRAN
GOTO usp_ModifyRows_Exit
GO
SET NOCOUNT OFF
View 5 Replies
View Related
Feb 8, 2006
I have a SQL Server (Express) database that I use in a Visual Studio (Visual Basic) 2005 application.
The application is of the Decision Support type, that routine deletes the contents of some of the database tables and then re-populates them with new data. There is no reason for keeping the "old" contents and no reason to restore the old content.
(It does this using "BULK INSERT" statements, as in:
"BULK INSERT SharePrices FROM "C:SharePrice.txt")
Because of all the data replacing, the log file gets huge quickly. I don't need the log file at all.
(I suspect that the logging is also taking up time unncessarily.)
Is there any way I can set the database not to have a log file at all, or to have a small log file?
(I've tried deleting the log file from SQL Server Management SQL Express, but get a message that there has to be a log file.)
When I restrict the log file size, my application returns error messages that the log file is too small, and the application can't do what it needs to do.
I have already made sure that in the database that the recovery mode is set to "Simple" and that "AutoShrink" is set to true.
I've tried manual "Shrinks". These work. However, as soon as the application accesses the database, the log file gets huge again.
I realise I may have to do (unnecessary) backups before/after my bulk insert statements. If so, I'd appreciate some help on how to do this from within a Visual Basic 2005 application. (I'm actually using the Data Access block from the Enterprise Library for my data access.)
I'd really appreciate your help.
Kind regards
Reg Bust
View 3 Replies
View Related
Oct 31, 2007
Dear all,
Can anybody provide sample t-sql procedures coding to do the following tasks?
I want to call a sql stored procedures with parameter passing. I write down the pseudo codes as follows:
In the SQL server side, write a stored procedure which performs the following:
With input parameter "ABCDE";
execute SQL statement "select * from table A where tableA.field B = "ABCDE";
For each record from the result of the above SQL statement,
"select * from tableB where tableB.field C=tableA.fieldX"
if the record can be found in tableB,
"update tableB set tableB.fieldD=something";
if the record cannot be found in tableB,
"insert a new record in tableB"
My actual application requirement is that I have an input file of over hundred thousand records which acts as a primary file to update or to add records to another file. My client side application, which is VB.NET, call a sql procedures once to perform this task. I don't want the VB.NET program to loop through all the input records and call a sql procedure a hundred thousand times!
thanks in advance!
View 8 Replies
View Related
Aug 26, 2004
Hi
I have a table called tblsample, where i have information stored row wise. Ther four quarter information is stored for many years. I want those information column wise for a given year.
say
select col1, col2 from tblsample where rqtr=1 and ryear = 2000
select col1, col2 from tblsample where rqtr=2 and ryear = 2000
select col1, col2 from tblsample where rqtr=3 and ryear = 2000
select col1, col2 from tblsample where rqtr=4 and ryear = 2000
i want information like
for the Year 2000
1 qtr 2 qtr 3 qtr 4 qtr
How to acheive this in MSSQL 2000
View 3 Replies
View Related
Jul 20, 2005
I am looking for sql server certifcation questions any one hasany.like dumps or some thing
View 1 Replies
View Related
Jun 26, 2007
Hello,
In the ErrorLog of my Sql Server , i found this line :
2007-06-26 05:35:18.37 Serveur The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
Operating System XP Home SP2
SQL Server 2005 Express Edition with Advanced SP1 )
( idem for another workstation with XP Pro SP2 and same version of SQL Server 2005 Express
My problem is :
i want use the windows authentification but my computers are on Worhkgroup linked by a router ( no window server )
i have read that's possible to connect from a remote computer to a computer having a SQL Server 2005 Express through SPN
How can i do it ? ( activating NTLM ? but how ?)
I'm writing a C# program which must be executing on several computers with a SQL Server 2005 Express installed on a particular computer. These computers will belong to a domain of Windows Server 2003.
As i can't connect to this "normal" network, i am trying to simulate this network at home because i want to test this program and especially the possible locks problems.
I don't know whether i'm querying with the "correct" forum.
Sorry for my poor english.
I shall appreciate any help about this problem
Have a nice day
View 1 Replies
View Related
Apr 27, 2006
Hi!
I'm new to SSIS (and quite new to SQL Server). I have a process which I'd like to automize via SSIS - just don't know how and couldn't figure it out yet by playing around with the program. Shouldn't be too difficult though.
First of all, that's the process as I do it now:
1) Load several flatfile sources (dumps of SQL tables) into an SQL database.
2) Add identifier rows (to some tables), set the primary and foreign keys so the database is "recreated" and I can work on it.
3) Do several simple transformations, aggregations and selects across tables and finally write a new table containing information for reporting stuff.
I succeded in loading flatfiles within the data flow view, doing some transformations and saving the output to a flatfile. What I didn't find out: how can I "recreate" the database enabling me to perform "SELECT/FROM/WHERE" statements across tables? Will I have to write the imported files to tables within a db (how?) or can I avoid this step?
A little guide (newbie friendly) would be great help!
View 11 Replies
View Related
Apr 16, 2007
I created a report which has duplicates on one field. I want the report to display the duplicates, but cannot figure out how. Anywhere I have found where there is a "Hide Duplicates" option I have it turned off (in matrix properties), but the report matrix still hides the duplicates. I don't understand why this would happen since the other fields on the same record are not duplicates. I tried grouping but that was no help.
The query in the dataset DOES return the duplicates as expected.
Anyone know how to fix this, or if it is a bug?
Thanks,
Chris
View 1 Replies
View Related
Apr 10, 2002
I have three tables, Employer, Customer and CustomerEmployer. The employer table contains all employers and the customeremployer table contains customer records for an employer. For example
Employer Table
EmployerID Name Address
1 ABC Company 123 Main Street
2 CDE Company 1 South Street
3 JJJ Company 3 Timothy Street
4 ZZZ Company 2 Rust Street
Customer Employer Table
ID EmployerID CustomerID
1 3 1
2 3 2
Customer Table
ID Name
1 Joe Smith
2 Jane Thomas
3 Tim James
I would like to run a select statement which lists all the employers and whether the employer has already been assigned to a customer. For example, for Joe Smith I would like to see all the employers listed AND some marker indicating that Joe is already assigned to EmployerID 3, JJJ Company.
Results
EmployerID Name Address CustomerID
1 ABC Company 123 Main Street
2 CDE Company 1 South Street
3 JJJ Company 3 Timothy Street 3
4 ZZZ Company 2 Rust Street
I have tried unions and many types of joins but none seem to work correctly. I was using the following select statement however only employers that are contained in the customeremployer table appear.
SELECT Employer.EmployerID, Employer.Name, Address, Employer.City,
CustomerEmployer.CustomerID
FROM CustomerEmployer RIGHT OUTER JOIN
Employer ON CustomerEmployer.EmployerID = Employer.EmployerID
WHERE (CustomerEmployer.CustomerID = @customerid) OR (CustomerEmployer.CustomerID IS NULL)
ORDER BY Name
Any ideas?
Thanks.
View 1 Replies
View Related
Jan 18, 2006
Can someone help me parsing this ms-access PIVOT sql-statement to ams-sql-server sql-statement?Many thanks in advanceTRANSFORM Count(KlantenStops.id) AS AantalVanidSELECT KlantenStops.Uitvoerder, KlantenStops.KlantFROM KlantenStopsGROUP BY KlantenStops.Uitvoerder, KlantenStops.KlantPIVOT DatePart("m",leverdatum,1,0) In("1","2","3","4","5","6","7","8","9","10","11","12");
View 3 Replies
View Related
Apr 14, 2006
SQL Server Usability Enrollment
Your opinions count.
The SQL Server Design and Usability team invites you to spend a few hours with us and our products (SSMS, SSIS, SSAS, SSRS, BIDS and etc) and tell us what you think. We are interested in how you interact with the products. The information we gather is directly translated into product design improvements, so your feedback makes a difference.
We periodically conduct lab research on campus as well as site visits on various aspects of SQL Server. Your involvement is extremely valuable to us. If you are working with
administering SQL Server databases, building database applications, performing BI
related tasks - we'd like to hear from you.
Please take a couple of minutes to fill out the SQL Server Usability Enrollment form. Your contact information is extremely confidential and will only be used by our team for usability and research purposes. Thanks for your help to make the SQL Server even better!
Microsoft SQL Server Design and Usability
View 1 Replies
View Related
Jan 28, 2008
I have created an order form for my business. I placed a submit button at the bottom. I wanted all the clients information to go into my emailbox. Someone told me about MS SQL Server. I got excited and downloaded the 2005 Express version with all the trimmings. Now I am more confused than I was before. Someone please explain to me what I should do first. Redo the form with the studio then work with the server? I think I may have deleted something important from the server trying to figure it out. Help!!!!!!!!!!
View 7 Replies
View Related
Jan 16, 2008
In my SQL Server 2005, we stored multiple application tables in a single database. All of them is using the dbo schema in the past.
Now, we would like to change this practice and adopt a better one. However, we got some problems in the configuration.
If I want to create such a database, should I first create multiple logins and multiple users for all the application. The mulitple logins are used in the connectionstring of my web. While each login can only have 1 user in a database, I need to create a user for each login as well. (Am I correct in doing so? It seems a bit messy.)
The other problem is what should the database role of all that users? If I don't grant them the db_owner. They can't create the required tables. If I grant it, it voliate the intension of separating different application...
Please help. I am a bit lost on this now. Thanks!
View 1 Replies
View Related
Apr 14, 2006
SQL Server Usability Enrollment
Your opinions count.
The SQL Server Design and Usability team invites you to spend a few hours with us and our products (SSMS, SSIS, SSAS, SSRS, BIDS and etc) and tell us what you think. We are interested in how you interact with the products. The information we gather is directly translated into product design improvements, so your feedback makes a difference.
We periodically conduct lab research on campus as well as site visits on various aspects of SQL Server. Your involvement is extremely valuable to us. If you are working with
administering SQL Server databases, building database applications, performing BI
related tasks - we'd like to hear from you.
Please take a couple of minutes to fill out the SQL Server Usability Enrollment form. Your contact information is extremely confidential and will only be used by our team for usability and research purposes. Thanks for your help to make the SQL Server even better!
Microsoft SQL Server Design and Usability
View 1 Replies
View Related
Jul 28, 2001
Hi all,
After I setup the merge replication in SQL SERVER 7.0, there is a rowguid column (datatype uniqueidentifier) inserted into each table in the both the source and destination database.
I need to get rid of the rowguid column in all tables. I deleted the replication, but the column still existed.
Is there an easy way to get rid of all the rowguid column in the database? Thanks for your reponse in advanced.
View 2 Replies
View Related
