Solved

Delphi 7 :: MS SQL 2005 :: INSERT SQL Statement & Params

Posted on 2007-11-15
6
6,152 Views
Last Modified: 2013-11-23
Hi Experts,

I am building a Delphi 7 app using MS SQL 2005 Express db.
Can you please show me how a correct INSERT, UPDATE and SELECT statement should look if
using ADO components?

I know the SQL syntax, more interested in whether one should use transactions etc?
Please show me complete Delphi code example when inserting a new row using ParamByName() etc...

All advice welcome.

Thank you
0
Comment
Question by:Marius0188
[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
  • 3
  • 2
6 Comments
 

Author Comment

by:Marius0188
ID: 20287656
What I would like to do is exactly as when using TIBQuery component:


IBQuery.SQL.Text := 'INSERT INTO TBLTABLE1 (Name, Surname, BirthDate) VALUES(:Name, :Surname, :BirthDate)';
IBQuery.ParamByName('Name').AsString := 'Marius';
IBQuery.ParamByName('Surname').AsString := 'NA';
IBQuery.ParamByName('BirthDate').AsDateTime := Now;
IBQuery.ExecSQL;

With ADO Query components you don't have "ParamByName().AsDateTime" etc...
How do you insert a date etc type?

And then please remember to answer whether your should use transactions etc...

Thanks!
0
 
LVL 25

Accepted Solution

by:
imitchie earned 250 total points
ID: 20287941
pretty much same as TIBQuery. except parameters.parambyname, and use Value (variant) for everything.  You can even specify datatype if required, just parameters.parambyname('fkd').datatype... (or fieldtype, drawing from memory now)

ADOQuery.SQL.Text := 'INSERT INTO TBLTABLE1 (Name, Surname, BirthDate) VALUES(:Name, :Surname, :BirthDate)';
ADOQuery.Parameters.ParamByName('Name').Value := 'Marius';
ADOQuery.Parameters.ParamByName('Surname').Value := 'NA';
ADOQuery.Parameters.ParamByName('BirthDate').Value := Now;
ADOQuery.ExecSQL;

make the same changes for select, update.  borland has done a good job at keeping things the same.  i would use transactions wherever you use them for ib, because they're both transactional dbs.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20287949
you don't really have to specify parameter types. they are usually self discovered.  as with tdatabase, tibdatabase (tibconnection?), the transaction is managed at the tadoconnection level.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 21

Assisted Solution

by:developmentguru
developmentguru earned 250 total points
ID: 20288774
 Usually I only worry about using transactions when you need to be sure that more than one update succeeds (or fails) as a group.  If you are writing a purchase order to the database it would do no good to store the header without the items, the items without the header, a partial line item list, etc.  Wrap such a set of record updates in a transaction.

  As far as the query parameters go, I have had my own issues using them.  For one thing, you can't see the query that results from the parameter replacement, this can make it more difficult to debug when you have an issue.  I have run into the problem of having a colon inside text I want to insert into a table that gets recognized as a parameter.  I have run into issues where only one parameter is replaced when I needed it multiple places (you need to use a separate routine to replace multiple).  In general I have started using my own parameter replacement routine.  This gives me the opportunity to examine the resulting query before it gets handed to the datatbase and gets me around some of the sticky parameter issues that arise from using the built in functionality.  One other thing this has allowed me to do is do partial parameter replacement in certain routines, and finish the parameter replacement in others (Object hierarchy where some handle the basic paramters and the parent objects handle the table name).

  As far as using the ADO components, the syntax of the statements is ruled by the database you are connecting to, not the ADO components.

Let me know if you need more, I hope this helps.
0
 

Author Comment

by:Marius0188
ID: 20311613
Can one please show me an example of starting and ending a transaction with MS SQL 2005?

Thanks a lot!
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20311628

ADOQuery.Connection.BeginTrans;
try
  ADOQuery.SQL.Text := 'INSERT INTO TBLTABLE1 (Name, Surname, BirthDate) VALUES(:Name, :Surname, :BirthDate)';
  ADOQuery.Parameters.ParamByName('Name').Value := 'Marius';
  ADOQuery.Parameters.ParamByName('Surname').Value := 'NA';
  ADOQuery.Parameters.ParamByName('BirthDate').Value := Now;
  ADOQuery.ExecSQL; 
  ADOquery.Connection.CommitTrans;
except
  ADOquery.Connection.RollbackTrans;
  raise;
end;

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month3 days, 21 hours left to enroll

630 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