|
Thanks that is a GOOD Idea. I have resolved it with the Following statement
SELECT DESCR,ACTV,
[CYCLETEMPLATE] = STUFF((SELECT ',' + CAST(CYCLETEMPLATE AS VARCHAR(MAX))
FROM #temp
WHERE DESCR = t.DESCR
AND ACTV = t.ACTV Order by CYCLETEMPLATE
FOR XML PATH('')),1,1,'')
into #TempSummary FROM #temp t
GROUP BY DESCR,ACTV
Look at the Bold part it sorts the String.
Thanks GuysVuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Vuyiswa Maseko wrote: STUFF((SELECT ',' + CAST(CYCLETEMPLATE AS VARCHAR(MAX))
I TOLD you there was a cute way of doing this, just could not remember it. Please add this to tips and tricks with concatenate in the keywords for next time.....Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You are right.
Thanks Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
I found this link[^], take a look at the recursive CTE method used.
I was sure there was a cute way of doing this but dammed if I can find it now.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SOrry, I posted this question earlier but it was in the wrong area , so i had to repost it here
mysql_query(connection, "INSERT INTO main (Name , FriendName) VALUES('Mark', 'John')");
This coding works fine when i run it in my Netbeans.
What i would like to do is instead of having to hardcore the inserted values into the query, i would like it to read from a variable. For exmaple the data to be inserted is from a string... like....
string NewName= "Monaco";
string NewFriendName = "Polo";
How do i edit my sql statements so variables can be inserted into the Insert statement so the query will read the variables and then store their values in the database. Really need some help in this
|
|
|
|
|
If you are using .NET, you may want to look into the "Parameters" property of the command object.
|
|
|
|
|
Sorry i am using netbeans. Is there a way to allow the insert query to read stored variables instead of only having to hardcode it? for example a user enters in the input he wants to store, and then the query will use the variables to be put inside the insert statement.
|
|
|
|
|
why don't you edit your original question stating your current environment so that people know what you are using and what are your problems and where you want to go.
now, it's like watching a bad ping-pong game.This signature was proudly tested on animals.
|
|
|
|
|
|
hmm no, im using c++ actually in netbeans.
|
|
|
|
|
Rather than guessing this time, let me ask straight out: what library are you using for the SQL functionality? Is it mysqlclient?
As far as I am aware mysqlclient has support for prepared statements, at least since version 5, I think. I don't think it supports binding parameter variables otherwise (I could be wrong, so it might be best to RTFM on that one).
If you don't want to or can't use prepared statements, then the question pretty much boils down to a straightforward question about string handling in C++.
|
|
|
|
|
First, an apology to CPers; I suggested moving this query to the Database forum as it seemed to be getting bogged down in the C++ forum.
The OP is using C++ (ignore NetBeans, it's just an IDE) and wants to use the above SQL statement but substitute (local)variables for the value fields. I am sure I have seen this done somewhere else something like:
string NewName= "Monaco";
string NewFriendName = "Polo";
mysql_query(connection, "INSERT INTO main (Name , FriendName) VALUES($1, $2)", NewName, NewFriendName);
I know that the above is not it but I hope you get the idea; I'm sure one of you SQL experts knows the answer.MVP 2010 - are they mad?
|
|
|
|
|
Please take a look in both where clause in store procedure
USE [INVENTORY_ MANAGEMENT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
Create PROCEDURE [dbo].[Engagement_Get_StandardsByCriteria ]
-- Add the parameters for the stored procedure here
(
@ClientName NVARCHAR(255)=NULL,
@Year FLOAT=NULL,
@Application NVARCHAR(255) =NULL,
@Database NVARCHAR(255)=NULL,
@OS NVARCHAR(255)=NULL,
@QuickName NVARCHAR(MAX)=NULL
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
---This query gives me 985 rows when i didn't pass any values to parameter
SELECT [Client Name] AS Client,[Year],[Application],
[Database],OS,[MLP Heading] As Heading,
[Standard Observation] AS Observation,[Standard Impact]AS Impact,
[Standard Recommendation] AS Recommendation
FROM Engagement_Standards
WHERE (@ClientName IS NULL OR [Client Name]=@ClientName)
And
(@Year IS NULL OR [Year]=@Year)
And
(@Application IS NULL OR [Application]=@Application)
And
(@Database IS NULL OR [Database]=@Database)
And
(@OS IS NULL OR OS=@OS)
And
(@QuickName IS NULL OR [Quick Name]=@QuickName);
-- <big>But this query gives me 956 rows on same criteria mentioned above why it is so</big>
SELECT Std_MLPID,[Client Name] AS Client,[Year],[Application],
[Database],OS,[MLP Heading] As Heading,
[Standard Observation] AS Observation,[Standard Impact]AS Impact,
[Standard Recommendation] AS Recommendation
FROM Engagement_Standards
WHERE isNull(@ClientName, [Client Name]) = [Client Name]
AND isNull(@Year, [Year]) = [Year]
AND isNull(@Application, [Application]) = [Application]
AND isNull(@Database, [Database]) = [Database]
AND isNull(@OS, [OS]) = OS
AND isNull(@QuickName, [Quick Name]) = [Quick Name];
END
|
|
|
|
|
Here is how I would test this.
Change the content to * select * from table this will remove any field requirements
comment one of the where lines in each query until you find the discrepancy
Once you have identified the clause causing the problem you then need to inspect the data to find the cause.
That is a weird where clause, I have never used the 2nd style of isnull, I'd be interested in the resolution you come up with.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Could it be that any of the columns in your where clause contains Null values? In that case the second query will not return that rows because the where clause will say
Where null = null . The firs query however is will say
where null is null or null = null , which will work.Wout Louwers
|
|
|
|
|
|
yes it contains null values
Can u explain me , how sql server evaluates below clause, if the column contains null value
where null is null or null = null
and in the case isNull(@Year, [Year]) = [Year]
|
|
|
|
|
Where null is null or null = null
Evaluates to
Where true or false
Evalutes to
Where true
where isNull(@Year, [Year]) = [Year]
evaluates to (is @year is null and [year] is null)
where IsNull(Null, Null) = Null
evaluates to
Where Null = Null
evaluates to
Where false
I hope this helpsWout Louwers
|
|
|
|
|
Hey guys
I want to insert values into a MySql database. The following line works fine:
mysql_query(connection, "INSERT INTO main (NumofShows , Title) VALUES('12', 'Home')");
What i would like to do is instead of having to hardcore the inserted values into the querty, i would like it to read from a variable. For exmaple the data to be inserted is from a string... like....
string NewTitle = "Monaco";
string NumofShows = "2";
How do i edit my sql statements so variables can be inserted into the Insert statement so the query will read the variables and then store their values in the database. Really need some help in this
|
|
|
|
|
Use a parameters with the command object so you don't hard code values in the SQL statement.
|
|
|
|
|
Im not sure what you mean. Could u edit my example to show me what you mean?
|
|
|
|
|
I am designing a form for a Purchase Order System.
All is in place, data tables created and linked etc.
What I need is one field in a record to be the product of the preceding two fields.
IE I have a field that gives Price and a field that the User inputs Quantity (These are the Field Names).
I want the next field (Total) to auto fill the result of Price * Quantity.
Racking Brains has produced no result, anyone got a clue for a poor delusional chap?------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
|
|
|
|
|
I think the value can be created on the fly in a query and don't need a specific field to store it.
In case you really want the field, I think you have to use some update query to put the number in the field. The query can be executed when the focus leaves the quantity box. This is just a quick thought.
|
|
|
|
|
I do need the field, and thanks for the advice, I will try that.
It didn't occur to me to try that approach, I naturally (and wrongly it seems), assumed that there was some maths processing ability in Access.------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
|
|
|
|
|
If I understand you correctly, you just want to have a field on your form which represents the Price*Quantity. This data will not be saved to your database table.
You can achieve this by creating a textbox on your form and placing the formula in the control source.
1) From the toolbox, click on Textbox, and create a new textbox on your form.
2) Right click on the textbox, choose properties, click on the data tab.
3) In the control source field, you will enter a formula like:
"=[PO]![Price]*[PO]![Quantity]" (no quotes, but use the equal sign)
The [PO] represents the table name of where your data is stored, followed by a bang (!), then the field name.
Hope this helps.
|
|
|
|