Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server: Correct SQL statement (run from within Delphi

Posted on 2010-08-29
13
Medium Priority
?
389 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 2000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
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…

705 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