|
I have an table like this:
Date | Priority | PrimaryKey
====== ========= ======
12/2/2002 101 1
12/3/2002 101 2
12/4/2002 101 3
12/5/2002 101 4
12/6/2002 111 5
12/11/2002 111 6
12/12/2002 111 7
12/13/2002 111 8
12/4/2002 121 9
12/14/2002 121 10
12/15/2002 121 12
12/16/2002 121 13
I want to make query command to get ONLY PRIMARYKEY of minimum date for each group of prirority:
12/2/2002 101 1
12/6/2002 111 5
12/4/2002 121 9
If I use this command:
select min(Date),PrimaaryKey from table3 where (Date< GetDate() ) group by Priority
I'll get two column but I have to only get PrimaryKey column because I want to use in WHERE of UPDATE command.
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
What database are you using? SQL Server 7.0, SQL Server 2000, MS Access, Oracle, etc...
If you are using SQL Server then you could do something like this:
Select a.Date, a.Priority, a.PrimaryKey
From table as a INNER JOIN
(Select Priority, Min(Date) as Date
From table
Group by Priority) as b
on a.Priority = b.Priority
and a.Date = b.date
Let me know if this doesn't work or if you are using a different database.
Jeremy Oldham
|
|
|
|
|
Thanks for reply.
joldham wrote:
What database are you using?
SQLServer 2000.
I'm not at my desk to test your command but I got the point from it.Thanks again.
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
I am playing around with a DataSet and a relational database. The following code does work...
MessageBox.Show (profileSet.Tables["Profiles"].Rows[0]["TextureID"].ToString());
My questions are:
Does the DataSet contain a table with the Text value for the TextureID key?
If yes, what would the code look like to access it?
Thanks
Paul
|
|
|
|
|
Paul Silvernail wrote:
Does the DataSet contain a table with the Text value for the TextureID key?
No.You have to cast to string.
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
Guess I should have been a little more clear. I wanted to look up the value that the TextureID identified in a Textures table. From what I can tell, I need to make another adapter to look up the value.
I know the dataset has a collection of tables and I thought (more like hoped) that tables were added for each column that was a key.
|
|
|
|
|
Hi All,
I was trying to read a memo field with the following code ....
string strComment = TestpitReader.GetString(4);
Could someone correct what I am doing wrong ??
Thanks
Paul
|
|
|
|
|
What kind of error are you getting? Return the field as an object and check the type. Is it possible it isn't what you expect?
|
|
|
|
|
I just found the error.
My database did not have an empty string for the default value. When I tried to read a memo with no value I got a "Specified cast is not valid" error.
Thank you for responding !!
Paul
|
|
|
|
|
Hi
I let SQL create me a create script for some stored procedures that has changed. The problme is how do I run this. I thought dropping this as CommandText for a SqlCommand would be OK, but this is not the case. Lotsa errors with GO statement. This what I get from SQL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SelectUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SelectUser]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.SelectUser
@id bigint
AS
SET NOCOUNT ON;
SELECT ID, Username, Password, Email, Name, Sex, HomeTel, Language, WorkTel, CellTel, Address, City, Province, IDNr, PostalCode, JoinDate, Views,
HomeShow, WorkShow, CellShow
FROM Users
WHERE (ID = @id)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I have pasted this code in query analyser and it runs flawlessly. What am I doing wrong?
Cheers
DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET
|
|
|
|
|
|
You should be calling a stored proc instead of trying build the script each time and call it.
|
|
|
|
|
Mark Nischalke wrote:
You should be calling a stored proc instead of trying build the script each time and call it.
I'm trying to run a create script (that Enteprise Manager creates ) so changes in my stored procedures on the development machine can be reflected on the production machine. As simple as that! Just a one time run to make sure the production server is up to date really.
After that, the webserver can call the modified procedures properly.
DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET
|
|
|
|
|
Just so I understand. You are creating stored procs on your development machine and want them reflected on the production machine?
Ever heard of replication?
|
|
|
|
|
Mark Nischalke wrote:
Just so I understand. You are creating stored procs on your development machine and want them reflected on the production machine?
No, I am changing, and perhaps creating new ones...
Mark Nischalke wrote:
Ever heard of replication?
How do you propose I use replication over the web? Especially when the SQL server is provided by the ISP?
DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET
|
|
|
|
|
leppie wrote:
How do you propose I use replication over the web?
I don't know. I guess all the updating I've been doing is being completed by the SQL Server fairies. If you ISP isn't provding you access via EM or some other control panel type app then you seriously need to look elsewhere. I would also suggest you do a little more research on SQL Server and how to use it.
|
|
|
|
|
|
Your parents must be proud that you have reinvented the wheel. Eventually it may even turn.
Go away, I say, go away boy you bother me. -- Foghorn Leghorn
|
|
|
|
|
|
Hey Leppie. The @RETURN_VALUE parameter is auto-created, as you guessed, by SQL Server when you Return something. For Example:
ALTER PROCEDURE uspIsValidUser
@UserName VarChar(20)
AS
BEGIN
IF (SELECT COUNT(*) FROM tblUsers WHERE UserName = @UserName) = 1
BEGIN
SELECT UserName, Address, City, State, ZipCode
FROM tblUsers
WHERE UserName = @UserName
RETURN True
END
ELSE
RETURN False
END
In your code, you can then check the value of @RETURN_VALUE value to see what was returned. It's possible to return datasets at the same time due to the fact that return VALUES are automatically stored in @RETURN_VALUE.
Jamie Nordmeyer
Portland, Oregon, USA
|
|
|
|
|
|
Interesting. You write an app to generate stored procs but still need to ask what @RETURN_VALUE is and what it's for.
|
|
|
|
|
Mark Nischalke wrote:
Interesting. You write an app to generate stored procs but still need to ask what @RETURN_VALUE is and what it's for.
I'm pretty new to the SQL world, so thats why I'm asking... I dont really every have had the need to use it (yeah, I know its a bit more effiecient, but I prefer to do what I know works).[edit]And the fact that VS.NET creates it automatically [/edit]
Can I ask you this? Did you even bother to read the article and download the project? Have you tried it perhaps on a database of your own and see the results?
Cheers
DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET
|
|
|
|
|
I'm not quite interested in reading an article titled "SQL Stored Procedure Wrapper" written by someone who knows nothing about stored procedures.
|
|
|
|
|
Mark Nischalke wrote:
I'm not quite interested in reading an article titled "SQL Stored Procedure Wrapper" written by someone who knows nothing about stored procedures
Thats poor attitude! Who says I know nothing of SP's? YOU, because I dont know what @RETURN_VALUE does, and why it get auto generated by VS.NET. Why not try being a bit helpful, and less ignorant?
And please, being all knowing (as it appears), why not give me an example of the generated @RETURN_VALUE usage? MSDN sure doesnt prove to be helpful (in this aspect).
DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET
|
|
|
|