Solved

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

Posted on 2007-11-15
6
5,997 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
  • 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

914 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

20 Experts available now in Live!

Get 1:1 Help Now