Solved

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

Posted on 2007-11-15
6
6,110 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Magic Software info 18 156
Installshield for Embarcadero EX 10.1 Berlin 4 93
scroll down TListBox component in Delphi 1 44
migrate this code to work on android 1 46
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

710 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