|
|
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.
|
|
|
|
|
Further to my answer to a different approach (above), This also has possibilities, as although it is an Access DB, it is being run via a VB form at the front end, so I can post the toatl back into a field.
Cheers to both of you for two different approaches.------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
|
|
|
|
|
The approach loyal ginger referred to would be to use something like:
SELECT Price, Quantity, Price * Quantity AS Total
FROM table
WHERE condition
This gives you a record set that has a total field, without saving one on the disk. For an entry form you would use something like David Mujica's suggestion for the UI. This does violate your stated requirement of saving the field to disk. You might want to explore getting that requirement changed. All database fanatics will tell you so.
According to the rules of relational databases, the total field does not belong stored in the table. It would be a violation of "normal form" (3rd normal form if I am not mistaken). Practically, it adds no information not already present and is subject to the creation of data anomalies from updates. If someone else wrote a program that uses the same database and in one place changes quantity, but forgets to update total, well, you get the picture. Triggers could be a way to deal with this, but the Access (Jet) backend doesn't have them. If you use an Access front end to a different database backend, that backend might have this capability.Please do not read this signature.
|
|
|
|
|
Good Day All
I have an Sp and UDF. the UDF cant take #tables. so i want to incoporate the functionality that is being provided by the udf and make it part of the code. the first part of my statement creates a solid table that is being used in the UDF
truncate table temp
INSERT INTO temp
SELECT MTN.ID,S.DESCR,ISNULL(MTN.CYCLETEMPLATE,C.CYCLES) AS CYCLETEMPLATE,MTN.ACTV AS [ACTV]
FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
ON C.IDL = MTN.ID
END
ELSE
BEGIN
SELECT MTN.ID,S.DESCR,ISNULL(MTN.CYCLETEMPLATE,C.CYCLES) AS CYCLETEMPLATE,MTN.ACTV AS [ACTV]
into temp
FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
ON C.IDL = MTN.ID
END
and later in my Sp i have this line of statement
SELECT DESCR, dbo.GetSortedString(Cast(NULL AS varchar(8000))) AS CycleIdList,ACTV
INTO #TempSummary
FROM temp (NOLOCK)
GROUP BY DESCR,ACTV
ORDER BY DESCR,ACTV
which has no problem and later i want to update the Filed in the #TempSummary table like this
UPDATE #TempSummary
SET CycleIdList = dbo.Concat(#TempSummary.Descr,#TempSummary.actv)
now the problem is here, the Concat is the UDF. defined like this
create FUNCTION [dbo].[Concat] (@Name varchar(50),@Actv int)
RETURNS varchar(max)
WITH EXECUTE AS CALLER
AS
BEGIN
Declare @s varchar(max)
SET @s = ''
SELECT @s = @s + IsNull(',' + Cast(Cycletemplate AS varchar), '')
FROM temp (NOLOCK)
WHERE temp.Descr = @Name And temp.Actv = @Actv
GROUP BY Cycletemplate
ORDER BY Cycletemplate
IF (@S IS NOT NULL) AND (@S <> '') SET @S = SubString(@s, 2, Len(@S)-1)
RETURN @S
END
now i want to incoporate the functionality of the UDF and get rid of it. The UDF use the Temp created in the First example code based on the two fields. and generate the string and update the table #TempSummary. How can i incoporate this functionlity without using UDF.
ThanksVuyiswa 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'm trying to use both OleDbClient and OracleClient to get Tables/Views Schema but some data is incorrect.
only OracleDataReader.GetSchemaTable(), retrieves some correct data
but still I have the following problems:
1- For views, BaseTableName is the view name not really the name of base table of the view
2- Also BaseColumnName is the column name in the view not in the base table
3- I cannot find IsAutoIncrement or IsReadOnly columns in the schema table
4- Some data are incorrect with views only, such as IsKey:
IsKey is false for Primary Key columns, although it's retrieved correctly if I'm getting schema of table directly not schema of the view
can anybody helps me??
|
|
|
|
|
What do I need to download/install to be able to create mysql db's and to access the db's? I work in a windows Visual Studio C++ environment and want to be able to create and run both the db and client on the same development machine. Later for production the db and the client may or may not run on the same box.
Any help would be appreciated.
Thanks
Al Kurlansky
|
|
|
|
|
Alan Kurlansky wrote: What do I need to download/install to be able to create mysql db's and to access the db's?
The Community Server, and optionally a connector. You can use the Workbench to execute queries against the database, a bit similar to SQL Management Studio.I are Troll
|
|
|
|
|
Hi all,
I want to write a query in such a way to find the list of records which are between two dates i.e., consider the two dates are StartDate and EndDate.
Thanks in advance.
|
|
|
|
|
This is an extremely simple query that is well documented. Have you even attempted to look? I know the language. I've read a book. - _Madmatt
|
|
|
|
|