|
Try the following SQL statement:
SELECT Category, FIRST(Product) AS FirstOfProduct
FROM YourTable GROUP BY Category;
You may substitue FIRST() with either MIN() or MAX() if FIRST() is not supported. Hope that works...
Go2Canada
|
|
|
|
|
Your solution is here it works fine for your problem.
select distinct(category),
(select top 1 product from myTable b where b.category = a.category) as 'Product' from myTable a
|
|
|
|
|
i m using VC++ n Access as Database
if i m using hard code values of date n month n year its gives result fine
as query is
rs->Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT * FROM Record WHERE (([Record].[Date] Between #9/27/2001# And #7/30/2004#))");
But i wanaa use values of date . year n month that r input by user .. in form of String or integer in variables .. m_date . m_month .. and m_year.
how can i use these variables values in Query now
i used like this but Wrong Query message appears
rs->Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT * FROM Record WHERE (([Record].[Date] Between #m_month/m_date/m_year# And #m_month1/m_date1/m_year1#))");
I also Tried by TO_Date Function ,, but its said undefined function
how can i do it by user values ..
thanx
|
|
|
|
|
Hello,
I've been banging my head on the wall trying to figure this out.
I'm very new to Visual Studio (as you may be able to tell..)
I have a very simple C# program that calls a stored procedure on a sql server. The SP takes a bit of time, so I just wanted to do something to show that the user has actually hit the button and the system is busy processing.
I found a couple of excellent examples of what I could do here on the code project, but the problem is that no matter what I do, the system won't paint it on the screen until after the SP returns. (kind of defeating the purpose)
For simplicity, and to reduce the problem as far as possible, I even use a dummy SP that simply goes:
WAITFOR DELAY '00:00:10'<br />
select * from My_Table where Last_Name = 'Smith'
The select is just so that it'll return something, and I know it worked.
My C# program as I say is dead simple. I use sqlDataAdapter and Fill to fill my dataset.
Again to make things simple, I just put a label on my form with the text "Working.." in it and set it's visible property to false. When the user hits the run button, I set it to true and then do the Fill... but it doesn't display until after the SP returns, 10 seconds later, with the data. I just go:
label1.Visible = true;<br />
sqlDataAdapter1.Fill(dataSet11);
I found that I can at least set the cursor to hourglass.. but that seems inadequate (if the user slips the cursor off the window, it's no longer hourglass) as well as it seems like I should be able to do what I'm trying to do.
So.. well.. Any help or suggestions would be very much appreciated!
Thank you.
- Andrew
|
|
|
|
|
Rheal_Notes wrote:
When the user hits the run button, I set it to true and then do the Fill... but it doesn't display until after the SP returns, 10 seconds later, with the data.
When you set the visible property the system will fire an event to say that the visible property has changed. However the events are only processed when the application is about to become idle, which your application is not about to do.
Off the top of my head: You could call DoEvents() after setting the visible property to get the paint event to get processed. Alternatively you could force a refresh by calling Refresh() on the form.
Does this help?
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
My Blog
|
|
|
|
|
Colin,
Awesome! A most excellent answer. I tried them both and either one worked perfectly!
I cannot thank you enough, much appreciated!
Cheers,
- Andrew
|
|
|
|
|
Aargh. Reentrancy is not a fun thing. Steer well clear of DoEvents - IMO, it's dangerous.
The OP should be using a function called by a worker thread to perform the SQL query, then use Control.Invoke to get back on to the UI thread to update the UI. You can use the thread pool in .NET by calling ThreadPool.QueueUserWorkItem, passing in a WaitCallback delegate.
I don't think what you say about events is true in .NET. It was in VB6, IIRC, but events are called directly in .NET. What the poster is seeing is simply that the thread isn't processing the Windows message queue (since it's blocked doing the stored procedure call) and so isn't getting the WM_PAINT message to repaint itself. If the user brings up a window that covers the UI and then brings this app up again, whole areas won't be redrawn on some operating systems (XP caches some current displays, but IIRC not invalid areas).
Leave the UI thread solely for user interaction - do all processing on another thread, unless you're sure that it cannot possibly take less than about 0.3 seconds. More than that is noticeable delay.
If you do need to update a control synchronously, call Update.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Mike Dimmick wrote:
Steer well clear of DoEvents - IMO, it's dangerous
A few months ago someone shot me down for suggesting Refresh() and "suggested" DoEvents() instead. So, to play it safe I suggested both ways. I think Bart Simpson said it best "Your damned if you do and your damned if you don't"
However, I do agree that if there are other events waiting it can go off and do some weird stuff.
Mike Dimmick wrote:
I don't think what you say about events is true in .NET. It was in VB6
I've never used VB6 so I don't know. I come from an C++/MFC background.
Mike Dimmick wrote:
, but events are called directly in .NET. What the poster is seeing is simply that the thread isn't processing the Windows message queue
Looking back at my post, it would appear that I misused the terminology somewhat. But, like I said "events[*] are only processed when the application is about to become idle"
[*] events here are the ones that are fired off in the Application.Run method that come from the Windows Message Queue.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
My Blog
|
|
|
|
|
Mike,
Thanks for your reply. I think that once I understand what you're trying to tell me, I'll be able to have a lot more fun.
I was looking at an example here on the The Code Project:
Proper Threading in Winforms .NET
By Shawn Cicoria
http://www.thecodeproject.com/csharp/winformthreading.asp
but I'm still having trouble wrapping my mind around it.
I don't think I have any trouble with the concept or anything, but the exact syntax and calling procedures are still confusing me.
I'll keep at it.. I'm definitely enjoying this Visual Studio thing. 
While Colin's suggestion works (thanks again Colin) .. I can still only display a static message... To the user, I don't think it will make any difference at all.. but I think it would be a lot more fun to put up an animated icon or busy-bar or something... and hey, if it's safer, and it'll make my code more re-usable down the line, all the more reason to keep playing. I'm sure I'll figure it out eventually.
In the mean time, any pointers to other sample code or tips or whatever are always much appreciated.
Thanks again to both of you.
Cheers
- Andrew
|
|
|
|
|
I have to transfer a data base from old to new one in batch.
I am working with the Data transformation services at the sql server.
How can i run/excute a dts package from a stored procedure or from the query analyzer? .
Thanks in advance
Be Good...
Dudi
|
|
|
|
|
Any master in SQL Queries
Help: Complicated query
I have a table, as similar to this
ID Status Deleted
1 Repeat y
2 Repeat y
3 Repeat N
4 Repeat N
5 sUBMIT y
6 sUBMIT y
7 sUBMIT N
8 sUBMIT N
9 Pending Y
.
.
.
.
from the table like this i have to retrive distinct records where
delted = N
eg: in the above table after executing query result should be
ID Status Deleted
3 Repeat N
7 SUBMIT N
9 Pending Y
|
|
|
|
|
irshu wrote:
from the table like this i have to retrive distinct records where
delted = N
eg: in the above table after executing query result should be
ID Status Deleted
3 Repeat N
7 SUBMIT N
9 Pending Y
The query does not seem to be complicated, but it is not clear what you want. You said "deleted = N" and your result set has a record with "deleted = Y". Please explain.
My articles and software tools
|
|
|
|
|
say if i have records with same status field and with deleted ='N' and delted = 'Y' then i should select one record with deleted = 'N' and i should not select deleted = 'Y'
and say if i have records with the same status and with deleted ='y' and No records with delted='N' with the same status then i should select delted ='Y'
and one more thing in this is, ID is the primary Key
|
|
|
|
|
Wow, that is complicated, but not impossible. Here is the SQL statement that will get what you want (using union and nested query):
SELECT min(ID), Status, 'N'
FROM myTable
WHERE Status in (select distinct Status from myTable where Deleted = 'N')
GROUP BY Status
UNION
SELECT min(ID), Status, 'Y'
FROM myTable
WHERE Status in (select distinct Status from myTable where not Status in (select distinct Status from myTable where Deleted = 'N'))
GROUP BY Status;
My articles and software tools
|
|
|
|
|
Oops, the following is simpler:
<br />
SELECT min(ID), Status, 'N'<br />
FROM myTable<br />
WHERE Status in (select distinct Status from myTable where Deleted = 'N')<br />
GROUP BY Status<br />
<br />
UNION <br />
<br />
SELECT min(ID), Status, 'Y'<br />
FROM myTable<br />
WHERE not Status in (select distinct Status from myTable where Deleted = 'N')<br />
GROUP BY Status;<br />
My articles and software tools
|
|
|
|
|
Thank you very much for helping in this regard.
with regards
irsh
|
|
|
|
|
Thank you very much for helping in this regard.
with regards
irsh
|
|
|
|
|
Thank you very much for helping in this regard.
with regards
irsh
|
|
|
|
|
Does MS SQL Server support dates in 'mmyy' format?
If so, how??
|
|
|
|
|
Left(Convert(char(6),getdate(),12),4)
michanne
|
|
|
|
|
Thanks...
But i want the data to retain datetime datatype, so that i can apply datetime functions to the data.
|
|
|
|
|
Hi,
I have an ACCESS DB populate by an automation.
I am looking for reading this database and populate it in a sql database.
For the first time, when I reading the ACCESS DATABASE, I using DTS WIZARD in enterprise Manager for create the same ACCES DB in an SQL DATASE.(I would like to use a STORPROC for this).
After I would like to use TRIGGER or SQL AGENT, ... for detecting that new records were inserted in the ACCESS DB.
And populate new records to the appropriated TABLE in SQL DB.
If you have any suggestions for my question it is great.
youssef
|
|
|
|
|
I'm not sure I quite understand what you are trying to achieve. Why are you continuing to have an Access database? Why not migrate it all to SQL Server and leave it in there? If you need to access the data in Access then why not link the tables back from SQL Server?
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
My Blog
|
|
|
|
|
Thanks for your answer.
I can't change the DB because it isn't my Automation and the customer wont migrate to SQL Server (probably for license).
So, I used Linked servers in Entreprise Manager.
But How can I do for reading each table with a storproc and how can I do for create a Linked servers by storproc because the filename of the log change each day ?
youssef
|
|
|
|
|
youssef wrote:
the customer wont migrate to SQL Server (probably for license)
cough, MSDE, cough cough....
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
