Solved

SQL Server: Correct SQL statement (run from within Delphi

Posted on 2010-08-29
13
383 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
  • 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
 

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now