Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server: Correct SQL statement (run from within Delphi

Posted on 2010-08-29
13
Medium Priority
?
390 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:Stef Merlijn
  • 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:Stef Merlijn
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

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

Author Comment

by:Stef Merlijn
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:Stef Merlijn
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:Stef Merlijn
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:Stef Merlijn
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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

824 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