|
As the error message says, any column in your grouped SELECT statement must be in an agregate function (MIN, MAX, SUM, etc), or in the GROUP BY clause. Both of the examples in my previous post satisfy this condition.
If you need to return other columns from the row with the maximum WPID, you could use a sub-query:
SELECT
I.WPID,
G.WNum,
I.OtherColumn,
I.MoreInfo,
...
FROM
WP_General_Info As I INNER JOIN
(
SELECT
WNum,
Max(WPID) As MaxWPID
FROM
WP_General_Info
GROUP BY
WNum
) As G
ON I.WPID = G.MaxWPID And I.WNum = G.WNum
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
Richard!
Great! That's what I needed. Thanks so much!
|
|
|
|
|
Hai,
How i can know a stored procedure is existing in the databse or not by writing code in the vb.net?Any way is there?I am sure somebody can show my way.
Thank You,
Rahul.P.Menon.
SoftwareDeveloper(.NET)
|
|
|
|
|
This query should point you in the right direction.
SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES
|
|
|
|
|
This will work or something like it in SQL Server. If you wish to use this from .NET wrap this into a Stored Proc and call from ADO.NET.
#########################################
DECLARE @SPS varchar(200)
SET @SPS = 'my_stored_proc'
if exists (select * from dbo.sysobjects where id = object_id(@SPS)
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
SELECT 'Yes it existed  '
END
ELSE
BEGIN
SELECT 'Cant find Proc 
END
###########################################
|
|
|
|
|
Hai Michael Potter,Knarf_scot
Really great.Thanks much for your fantastic help and showed me the right way.
Thank You,
Rahul.P.Menon.
SoftwareDeveloper(.NET)
|
|
|
|
|
Hi, this is a database/schema design question. Should I move image column to a different table, possible to a different file group on a different disk, would it improve performance (appreciable improvement)?
NOW:
<br />
CREATE TABLE tblInventoryItem (<br />
ItemID INTEGER IDENTITY(1,1),<br />
ItemName NVARCHAR(30),<br />
ItemDescription NVARCHAR(30),<br />
...<br />
pic IMAGE<br />
)<br />
Would this (following) improves performance?
Code:
<br />
CREATE TABLE tblInventoryItem (<br />
ItemID INTEGER IDENTITY(1,1),<br />
ItemName NVARCHAR(30),<br />
ItemDescription NVARCHAR(30),<br />
...<br />
)<br />
<br />
-- Two columns only:<br />
CREATE TABLE tblEmployee (<br />
ItemID INTEGER IDENTITY(1,1),<br />
pic IMAGE<br />
)<br />
Thanks in advance.
Norman Fung
|
|
|
|
|
Keep your data where it belongs. If the pic column is dependent upon the ItemID and there is only one of them per ItemID, it belongs in the tblInventoryItem table.
Just rememeber that the pic column is in the table when you construct your queries. Don't use SELECT * when a more restricted query will do. The image data is already stored in another section of the database. SQL only stores a pointer in the actual table and does the grunt work when you need the image.
|
|
|
|
|
|
hai
I had a Dataset With table Product . Whose Fields are
productID int
Name string
Price int
I had populated the Data Set with records.
From the Data Set i need to found
+ Maximum Price
+ Minimum Price
+ Sum of All Price
Is there any way to solve this with out much coding
Plese help me to solve this
Thanks & Regards
|
|
|
|
|
You can split in several queries or do it in just one:
SELECT max(Price), min(Price), sum(Price) from Product
then access the corresponding field of the result set.
Marc Soleda.
... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.
|
|
|
|
|
i don't think your select will work as there isn't any Group By statement for the agregate functions you use (max(), min(), sum())...
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
Obviously is better to split in several queries or group them but depending on the table design it'll work. Try it with the table that anig1234 provided. I did it.
Marc Soleda.
... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.
|
|
|
|
|
Try this
*** Max Price *****
select top 1 * from Product
order by price DESC
*** Min Price *****
select top 1 * from Product
order by price
*** Sum of all Prices ***
select sum(price) from Product
|
|
|
|
|
Hi folks,
My asp.net app is having trouble updating a table in Access 2K. I don't get any errors but the data is not updated. When I run the same query in Access Query IDE it runs fine. Any idea on what might be going on?
Thanks
Mike
private void btnSubmit_Click(object sender, System.EventArgs e)<br />
{<br />
OleDbConnection myConnection = new OleDbConnection(ConfigurationSettings.AppSettings["ConnectionString"]);<br />
<br />
OleDbCommand myCommand = new OleDbCommand("UPDATE tblZtest SET PropertyType = " +<br />
"@Type WHERE zTest = @PropertyID", myConnection);<br />
<br />
myCommand.CommandType = CommandType.Text;<br />
<br />
OleDbParameter parameterPropertyId = new OleDbParameter("@PropertyID", OleDbType.Integer, 4);<br />
parameterPropertyId.Value = Convert.ToInt32(txtPid.Text);<br />
myCommand.Parameters.Add(parameterPropertyId);<br />
<br />
OleDbParameter parameterType = new OleDbParameter("@Type", OleDbType.Integer, 4);<br />
parameterType.Value = Convert.ToInt32(txtID.Text);<br />
myCommand.Parameters.Add(parameterType); <br />
<br />
try <br />
{<br />
myConnection.Open();<br />
myCommand.ExecuteNonQuery(); <br />
myConnection.Close();<br />
<br />
<br />
lblMessage.Text = "Done";<br />
<br />
<br />
}<br />
catch ( OleDbException myException )<br />
{<br />
string errorMessages = "";<br />
for (int i=0; i < myException.Errors.Count; i++)<br />
{<br />
errorMessages += "Index #" + i + "\n" +<br />
"Message: " + myException.Errors[i].Message + "\n" +<br />
"NativeError: " + myException.Errors[i].NativeError + "\n" +<br />
"Source: " + myException.Errors[i].Source + "\n" +<br />
"SQLState: " + myException.Errors[i].SQLState + "\n";<br />
}<br />
lblMessage.Text = errorMessages;<br />
<br />
}
}
|
|
|
|
|
It looks like your trying to mix Stored Procedure with SQL on the ADO.NET side.
Try this simpler code. It uses SQLconnection rather than OLE so you may have to slightly adjust your connection string.
Note: this has no try catch to simplify the code.
###########################################
string myType = "Type"; ' varchar on SQL Server
string myPropID = "12"; ' Int on SQL Server
string SQLstring = "UPDATE tblZtest SET PropertyType = '" + myType + "' " +
" WHERE zTest = " + myPropID
SqlConnection myConnection = new SqlConnection(ConnectionString);
myConnection.Open();
SqlCommand myCommand = new SqlCommand( SQLstring, myConnection );
myCommand.ExecuteNonQuery();
myConnection.Close();
##############################################
|
|
|
|
|
Change your SQL statemente to this:
<br />
OleDbCommand myCommand = new OleDbCommand("UPDATE tblZtest SET PropertyType = " +<br />
"? WHERE zTest = ?", myConnection);
The Oledb classes expect '?' placeholers for parameters, not the names. The parameters collection must be filled in the same order as the '?' placeholders. The OledbParameter objects still need unique names.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
I have a stored procedure that takes two parameters
(1) insert statement
(2) user name
It just executes the insert statement.
There is only one database user in the database.
My requirement is
(1) I need a way for the insert trigger to access the user name for inserting data into audit trail tables.
(2) In case when many users are logged in at the same time, how do I know which user name to use.
In our firm we have the constraint of one database user per database.
If there is any other solution please advise.
Live Life King Size
Alomgir Miah
|
|
|
|
|
Alomgir Miah wrote:
I need a way for the insert trigger to access the user name for inserting data into audit trail tables.
Why? you are inside a stored procedure, let it do the work.
Alomgir Miah wrote:
In our firm we have the constraint of one database user per database.
Unless you have a very good reason for this then that is a crazy constraint. How do you share information? (Which is one of the major advantages of having a database in the first place)
Also, if you only have one user per database then what is the problem? Surely you know who's using what database.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thanks a lot for your reply.
I think I was not very clear in my questions.
I am passing
(1) An insert query string
(2) UserName
to the stored procedure. As soon as the insert happens, the insert trigger is fired. In the insert trigger I am auditind the data by storing the changes(insert in this case) into an
audit trail table.
The answers to your questions are
(1) The trigger can access the database user only through user_name
INSERT INTO AuditTrail
SELECT user_name(),getdate(),'Fund','FamilyID',@rowid,'Insert',null,convert(varchar,FamilyID)
FROM inserted
But I need a way to access the parameter passed into SP.
(2) I know the constraint sucks. This is enforced for security reasons. More users means more activity tracking for all users. This means more work for the support groups and security holes in the application. ( Frankly, I dont agree to this at all like you said. ). But it does not means one user. It means many kerberos user and only one database user.
So the only solution I can think of is having a LastUpdatedBy column in the tables we are auditing. So the insert statement in the Trigger changes to
INSERT INTO AuditTrail
SELECT LastUpdatedBy,getdate(),'Fund','FamilyID',@rowid,'Insert',null,convert(varchar,FamilyID)
FROM inserted
and I wont need the stored procedure in discussion at all.
Is it the best way to go keeping in mind the constraints we have.
Live Life King Size
Alomgir Miah
|
|
|
|
|
Having a LastUpdatedBy column in the table(s) would be a possible solution as the trigger can get that information.
But, why are you passing an insert statement into the stored procedure. You'd need to do something like EXEC(@insertStatement) which kind of blows away the purpose of a stored procedure if that is all you are doing. It is much better, and more secure, if you create a stored procedures (plural) for your data access and modification needs so that the each stored procedure can do sanity checking on the data and also it means that only the actions that the stored procedures can perform are available to outside applications (assuming you revoke access to the tables and views directly for the database user that is being used by the application). The stored procedures can also update the relevant audit tables for you. That would mean that you no longer need to have a LastUpdatedBy column in your tables.
Assuming those above you are serious about security - but their one user for the whole database seems inherently insecure, they should have set up roles so that each person can only access the data they need. I know the application is probably stopping that kind of access, but there could be a weakness in the application and it is better to have multiple levels of security. That is why if you look at ancient castles that still exist today then you'll see they have many layers of protection. If an invader breaks down one wall or gate then the occupants can retreat and fight from the next gate.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
This really helped. I will be exploring the possibilities based on your inputs.
I got rid of the SP. Its purpose was just to accept the username. I was just trying to use it.
Well I am using Object Oriented Database with a Class per table. I have a Query Generator too which generates dynamic SQL queries on the fly. So I dont use SP's to do Select's , Insert's,
Delete's and Update's. The business rules are implemented in the Data Access Layer.
Live Life King Size
Alomgir Miah
|
|
|
|
|
What is the difference between DataRow Class and Insert command in ADO.Net?
Sometimes I use Insert command or DataRow class. Which one is faster than others. Both of them are used for inserting the new value of Row in the table.
Thanks.
|
|
|
|
|
I've been asking around about this specific problem I have comparing times,
but as you know dates are stored in the following format: 2005/07/21 03:18:10 PM.
I want to select all between 08:10:00 and 08:55:00, disregarding the actual date. This will
then tell me when the labs are the busiest during the day.
this is the query:
select count (*) from User_Lab_Access
where LabID = 'R229'
and DateTime >= '2005/07/21 08:10:00'
and DateTime <= '2005/07/21 08:55:00'
* Remember disregarding the yyyy/mm/dd
|
|
|
|
|
I do not know if it is the best solution, but this should work:
select count (*) from User_Lab_Access<br />
where LabID = 'R229'<br />
and DateTime -floor(cast(DateTime as float)) between '08:10:00' and '08:55:00'<br />
Wout Louwers
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
