Solved

SQL Server: Correct SQL statement (run from within Delphi

Posted on 2010-08-29
13
386 Views
Last Modified: 2012-05-10
Hi,

Can anybody tell me why this query won't execute?
Error: Paramater Datum1 not found.
Database: SQL Server 2008 Express R2

When I try to compose the SQL in a view in Management Studio, it give an message the parameters are not allowed in this type of query.
With Query1 do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT MedewerkerID, DatePart(dw,Agenda.Start) AS Dagnr, DATENAME( dw, Agenda.Start) As Dag ');
    SQL.Add(', (CONVERT(VARCHAR(8), Agenda.Start, 108) ' + QuotedStr('-') + ' CONVERT(VARCHAR(8), Agenda.Einde, 108)) AS Tijden ');
    SQL.Add(', Agenda.Koptekst AS Onderwerp, Agenda.Locatie, (Sum(DateDiff(n, Agenda.Start, Agenda.Einde)) / 60) AS Uren ');
    SQL.Add('FROM Agenda ');
    SQL.Add('WHERE ((Agenda.Start) >= :Datum1) ');
    SQL.Add('  AND ((Agenda.Start) <  :Datum2) ');
    SQL.Add('GROUP BY MedewerkerID, Dagnr, Tijden, Agenda.Koptekst, Agenda.Locatie ');
    SQL.Add('ORDER BY MedewerkerID, Dagnr, Tijden ');
    Parameters.ParamByName('Datum1').DataType := ftDate;
    Parameters.ParamByName('Datum1').Value    := vMaandag;
    Parameters.ParamByName('Datum2').DataType := ftDate;
    Parameters.ParamByName('Datum2').Value    := IncDay(vMaandag,7);
    Open;
  end;

Open in new window

0
Comment
Question by:Delphiwizard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 10

Expert Comment

by:TiberiuGal
ID: 33556093
hi,
you need to create your parameters first like this:
with Query1.Parameters.Add do
begin
  DataType := ftDate;
  Value    := vMaandag;
  Name := 'Datum1';
end;

Open in new window

0
 

Author Comment

by:Delphiwizard
ID: 33556146
All other query's are setup like this.
So I think it must be something else.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 33556159
with ado you need to add the parameters yourself
in the designer this is done for you, in code ... not
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Delphiwizard
ID: 33556170
Tried it anyway, just to be certain, but I produces the same error.
0
 

Author Comment

by:Delphiwizard
ID: 33556177
ADO will automatically add the parameters if they are used in the query.
You don't have to assign these manually in the object inspector.
0
 
LVL 10

Expert Comment

by:TiberiuGal
ID: 33556179
Also you should add the paramerters before setting the query string, as it starts binding automatically.
0
 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 500 total points
ID: 33556203
this is your error:
' + QuotedStr('-') + '

i tried to change to sql.text :=  and found it:

   ' (CONVERT(VARCHAR(8), Agenda.Start, 108) + ' + QuotedStr('-') + ' + CONVERT(VARCHAR(8), Agenda.Einde, 108)) AS Tijden ');
0
 

Author Closing Comment

by:Delphiwizard
ID: 33556235
Thank you Geert.
That was it. Strange errormessage though.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 33556285
yeah,
sometimes developers are very good at finding the holes in the parsing of the select statements

usually i write my statements in 1 string
this lets the query parse the statement only once
i try to find afound adding quotedstr too (even for fixed strings)
(and have to make the exception for status strings, because of squeued loads)

first write the sql statement on its own

then i past it in toad (i'm on oracle), and this has a very neat tool
Make code statement (Ctlr-M) (reverse works too)

SQL := 'SELECT MedewerkerID, DatePart(dw,Agenda.Start) AS Dagnr, DATENAME( dw, Agenda.Start) As Dag, ' +
       '    (CONVERT(VARCHAR(8), Agenda.Start, 108) + :separ + CONVERT(VARCHAR(8), Agenda.Einde, 108)) AS Tijden, ' +
       '    Agenda.Koptekst AS Onderwerp, Agenda.Locatie, (Sum(DateDiff(n, Agenda.Start, Agenda.Einde)) / 60) AS Uren ' +
       'FROM Agenda  ' +
       'WHERE Agenda.Start >= :Datum1  ' +
       '  AND Agenda.Start < :Datum2 ' +
       'GROUP BY MedewerkerID, Dagnr, Tijden, Agenda.Koptekst, Agenda.Locatie  ' +
       'ORDER BY MedewerkerID, Dagnr, Tijden  ' ;

the reverse didn't work for your statement, so i found the problem rather quickly :)
0
 

Author Comment

by:Delphiwizard
ID: 33556363
There was some other thing wrong. Though the SQL ran in SQL Server it stil produced the same error in Delphi. It turned out that the TADOquery didn't have a TConnection attached to it (still the error on parameter).
Attaching the Connetion did the trick completely.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 33556435
i nearly said sql server is for dummies ...
but it's actually the other way around sql server was done by dummies :)
0
 

Author Comment

by:Delphiwizard
ID: 33556445
As long as we know that, it's still a great product :-)
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 33556503
lol,
i better nog get into discussion about that

it's not bad,
now and then when i have to try some complex queries on sql server
and then i really miss the analytical functions of oracle

you can't do analytical functions using a specific number or records on sql server
like an
avg(new_salary-old_salary) over (partition MederwerkId order by MedewerkerId, datefield range between 5 preceding and current row)

there is no range between functionality
and no lead and lag (not in an easy anyway)
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question