• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6610
  • Last Modified:

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

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
Marius0188
Asked:
Marius0188
  • 3
  • 2
2 Solutions
 
Marius0188Author Commented:
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
 
imitchieCommented:
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
 
imitchieCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
developmentguruPresidentCommented:
 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
 
Marius0188Author Commented:
Can one please show me an example of starting and ending a transaction with MS SQL 2005?

Thanks a lot!
0
 
imitchieCommented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now