|
DECLARE @TheYear INT
SELECT o.customerid,
od.orderid,
SUM(od.quantity*od.unitprice) AS price
FROM Orders o, [Order Details] od
WHERE Year(o.orderdate) = @TheYear
AND od.orderid=o.orderid
GROUP BY o.customerid, od.orderid
ORDER BY o.customerid
SET @TheYear = 1998
SELECT CASE GROUPING(o.customerid) WHEN 0 THEN o.customerid ELSE '(Total)' END AS MyCustomerID,
CASE GROUPING(od.orderid) WHEN 0 THEN od.orderid ELSE -1 END AS MyOrderID,
SUM(od.quantity*od.unitprice) AS price
FROM Orders o, [Order Details] od
WHERE Year(orderdate) = @TheYear
AND od.orderid=o.orderid
GROUP BY o.customerid, od.orderid WITH ROLLUP
ORDER BY o.customerid, price
check now. it will work.
|
|
|
|
|
No. It won't.
You declare @TheYear, use @TheYear, and then SET @TheYear. What is the value of @TheYear in the first SELECT statement.
As the question was already answered correctly, I'm left wondering why you also answered.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
I actually got my answer from Pankaj
sasi
|
|
|
|
|
I've updated a connectionString manually on IIS and restarted Web site,
but previously working INSERT is not working to the new data source.
New connectionString is okay (see details below).
I know I can have multiple connectionStrings, but don't know
how to select one of them on the fly.
I don't know how to handle this situation.
Any help will be very appreciated.
Thanks in advance!
Eugenio
DETAILS:
I'm running an ASP.NET Web Service (IIS 5.1).
Web service INSERTs a couple of records successfully into a DataSet
created using DataSet Wizard on VS2005 to a SQLEXPRESS on local machine.
I've the same database on a remote SQLSERVER (that I can access thru
MS SQLServer Mgmt Studio Express), and wanna INSERT data to it.
So I manually changed Web.config to the new connectionString on IIS and
restarted the Web site that I'm using. Data obviously did not go to the
old database, but it did not go to the new too! (although my code haven't
thrown any exception). Data has been lost.
Second turn, I configured the code to use an ODBC System DSN to the
old and okay database. Worked fine. Changed the DSN settings to the
remote database (connectionString remained the same) as restarted
Web site on ISS. The same behaviour got before: data was lost!
Eugenio
|
|
|
|
|
Hi everybody!
I've got a problem: (C# 2005)
I'm writing an applitcation that menage a database. I want to use the timestamp method to make the update to the database. I've alreay added to each relation in my database a column with a timestamp value. I know i must modify the text of the update command in the table adapter but i dont know how...
please help me!
thank you in advance
Enrico
VentoEngine corp.
Program your life ^^
|
|
|
|
|
I'm using ADO to connect via ODBC. Somehow there are two connections being opened at the database server. At first I thought that the problem was connection pooling. After reading many articles from MSDN and others I have tried the solutions to no avail.
I have read that to turn off connection pooling in .NET you use Pooling=False in the connection string. In VB6 you use OLE DB Services=-4. I have also read of other folks using OLE DB Services=-2.
Our DB2 database vendor supplied us with Hummingbird BI-Query. (A modeling, query, reporting tool application). This app does not have the connection problems I am having. I contacted hummingbird for some help with the connection string. They had a trace log but that did not reveal the connection string.
When I step through the code there are 2 connections opened on the cn.Open method. When the cn.Close method is executed only one of the connections is closed. In .NET I have tried to force garbadge collection. This has not worked. However, when the .NET app closes the connection closes. The VB6 app does not do this.
As a test in in VB.NET very simply I do:
<br />
Dim cn As OdbcConnection<br />
<br />
cn = New OdbcConnection<br />
<br />
cn.ConnectionString = "DSN=MyDSN" _<br />
& "; uid=MyUID; pwd=MyPwd; Pooling=False"<br />
<br />
cn.Open()<br />
cn.Close()<br />
In VB6 very simply I do:
<br />
Dim cn As ADODB.Connection<br />
<br />
Set cn = New ADODB.Connection<br />
<br />
cn.ConnectionString = "DSN=MyDSN" _<br />
& "; uid=MyUID; pwd=MyPwd;OLE DB Services=-2"<br />
<br />
cn.Open<br />
cn.Close<br />
<br />
set cn = Nothing<br />
<br />
Thanks for your help,
Steve
|
|
|
|
|
We developed an asp .net application that connects to a sql database. To go live with the application we need to move the sql database to another server. How can this be done?
|
|
|
|
|
Two ways I can think of.
1. Detach the database move the files and then attach at the other end. (You'll then have to wire up the logins again as the login ids will be different because they are stored in the master db)
2. Create a backup of the database, move the backup file to the new system, then restore the backup (you'll again have to wire up the logins to accounts for the same reasons given above).
Personally, I'd go with the second option. I've had problems with the first option when it is being attached again and SQL Server declaring it as "suspicious". The second is also easier as the backup will place everything in one (very large) file, while the detach/attach will require you to move all the files.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Colin Angus Mackay wrote:
(You'll then have to wire up the logins again as the login ids will be different because they are stored in the master db)
what does it mean?? i mean how can this b done??
May God help u solv ur problems as well,thx in advance!!
|
|
|
|
|
|
Hi ALL,
I have a table where data is stored in a Hierarchical manner. For example
User1 supervise User2 and User3
User 2 supervises User3 and User4...
How can I loop through the hierarchy get all users supervised by user1 dynamically? That is in the example above (User1, 2, 3 and 4)?
|
|
|
|
|
Is User3 supervised by multiple people (User1 and User2) or that is just a typo?
Damodar Periwal
Software Tree, Inc.
Simplify Data Integration
http://www.softwaretree.com
|
|
|
|
|
I am using Datatable. I can get DataType but could not get SqlType.
-- modified at 7:20 Monday 30th January, 2006
|
|
|
|
|
I need to generate a sql 2000 db from a xml file how do a do this. I use sql 2000 and vb.net on vs2003
modified 2-Feb-21 21:01pm.
|
|
|
|
|
hi there ,
in my program i have the Order Form (win Form)
in this form i have some textbox and grid
know the grid is bound the a dataset on the grid i have some filds like productID,Qunataty,UnitPrice,Total
i am filling all the data on the Form and the order is insert well to the db
now if i want to change something in this particular order i select the order detiles from the db by OrderId num
an all the order detiles are displayd on the Form
the problem is when i want to add products to the order
the product detiles that is bound to the datagrid is updated but the OrderId is not
this is the code :
<br />
if(dsOrdersDetiles1.HasChanges()==true)<br />
{<br />
<br />
<br />
AdOrdersDetiles.Update(dsOrdersDetiles1.Tables[0]);
<br />
<br />
}<br />
how can i update order detiles when the filds are not bound ?
-- modified at 21:59 Saturday 28th January, 2006
|
|
|
|
|
Is it possible to read the first 20 records from a table in one SQL statement
|
|
|
|
|
Use the TOP keyword right after the SELECT
SELECT TOP 20 * FROM MyTable
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Hi everybody, I'm using ASP.NET VB / C# application with MS Access. After successful insertion from my register.aspx page into Customers table, the CustomerID field (autonumber field) gets a randomly generated number which I'm using as the primary key. But sometimes, it generates a negative integer which is not appropriate for using as customer ID. Can anyone suggest me how to get only a postive integer. Also, it doesn't always generate a fixed-length integer. I want to be able to generate a customer ID with a fixed length. Please do suggest me a solution on these issues.
Is there an alternative way to generate this non-negative, fixed-length, unique customer ID through .aspx page itself? Could you help me with code please! Thank you.
Anjani Shiwakoti
Computer programs and social cultures are the same.
|
|
|
|
|
Hi,
If you don't want Randomly Generated Number then
The primary key i.e. CustomerID Datatype should be Long Integer/Double. Having "Increment" property set to 'Yes' with Increment Seed '1'.
Pankaj Kulkarni
-- modified at 0:50 Tuesday 31st January, 2006
|
|
|
|
|
Hi Pankaj,
While I do appreciate your suggesting using increment property instead of random, I've tried that before but I decided to use random number as customer ID because those random numbers are hard to guess and they can be used to retrieve customer's password in case he/she forgets one. My only problem is sometimes the autonumber creates negative integers, and also the numbers aren't always of a fixed length. If you (or anyone else reading this) could suggest me a better solution, I'd greatly appreciate it. Thanks.
Anjani Shiwakoti
Computer programs and social cultures are the same.
|
|
|
|
|
And what happens if suddenly you get a random number that already exists?
Use an autonumber column and keep the ID private to the application. And to let the user retrieve his password, ask him for an email address during signup and send it to that email address.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
Hi Luis,
While I do appreciate your suggestion, I've decided to seed a random postive integer with a fixed length right from my .aspx page, instead of letting MS Access create it for me. I guess that takes care of it. BTW, I don't think that random autnumber will 'suddenly' create a number that already exists in the database. It guarantees that every number it creates will be unique.
Anjani
Computer programs and social cultures are the same.
|
|
|
|
|
 Hi Guy's
i have problem in finding highest value from two column
eg.
i have table 'MyTable' with Column 'ID','Column1','Column2'
which contained data as
ID Column1 Column2
01 100 102
02 50 60
I want to find out the highest value of 'ID' 02 of this two column (i.e. 60) in SQL Query
Sasmi
|
|
|
|
|
SELECT MAX(ID) FROM MyTable
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
i dont want the maximum value from the ID column
i want to find out the highest value for a perticuler row in different columns i.e. if i have table with two columns Column1 and column2
Column1 has three rows with values 100, 45, 50
Column2 has three rows with values 101, 25, 40
now i want to find the higest value in first row of Column1 and Column2 which is 101 similarly for second row highest value is 45 and theird row highest value is 50.
so how do i go about doing this
Sasmi
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
