Solved

SQL Server: Correct SQL statement (run from within Delphi

Posted on 2010-08-29
13
388 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 38

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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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 38

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 38

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 38

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 38

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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 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