How to create correct SQL statement in delphi

Question

I am pretty new to delphi and I would like to ask how can I create a correct SQL statement / SQL string in delphi.

I have tried something like this:

 sql:='use [TestovaciaDb] INSERT INTO [dbo].[client]([Meno],[Priezvisko]) VALUES('+name+','+surname+')';

I am using MS SQL server 2012

But I am getting a exception there. Thank you

EDIT:

meno and priez are variables with values from TEdit1 and TEdit2:

 meno:= Edit1.Text;
 priez:= Edit2.Text;

The same way as you'd write it in your DB management application, just use parameters e.g. 'INSERT INTO client (Meno, Priezvisko) VALUES (:Meno, :Priezvisko)';. Then you'll need to fill parameter values in your query component. And, when you're asking for help, you have to provide relevant information. That you got "an exception" is pointless here. You need to provide the exact error message you got. Also would be more than useful to tell us which DB component you used (e.g. TADOQuery). — TLama
– TLama, Commented Mar 18, 2014 at 14:05

Ken White · Accepted Answer · 2014-03-18 20:45:04Z

7

Use parameterized queries. You set the database in your ConnectionString, so you don't need to `use' it in your query.

ADOQuery1.SQL.Text := 'INSERT INTO [dbo].[client] ([Meno],[Priezvisko]) ' +
                      'VALUES(:Meno, :Priezvisko)';
ADOQuery1.Parameters.ParamByName('Meno').Value := Edit1.Text;
ADOQuery1.Parameters.ParamByName('Priezvisko').Value := Edit2.Text;
ADOQuery1.ExecSQL;

edited Mar 18, 2014 at 20:45

answered Mar 18, 2014 at 14:32

Ken White

126k15 gold badges237 silver badges476 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Remy Lebeau Over a year ago

You can use AsString instead of setting ParamType and Value separately: ADOQuery1.Parameters.ParamByName('Meno').AsString := Edit1.Text;

Ken White Over a year ago

@Remy: AsString isn't available for TADOQuery.Parameters.ParamByName('Meno') when I checked it. (Just checked again in D2007, and it's not there either in XE5.) It's there for the other datasets, but not for ADO.

user2886091 Over a year ago

Thank you. It worked, but for ParamType, I get this: TParameter does not contain a member named "ParamType". So I commented out that line and it worked. So I would like to ask what is purpose of that line with ParamType:= ftString; ?Thank you

Ken White Over a year ago

@user2886091: Are you using DBExpress or ADO for database access? My answer is based on TADOQuery, which is for ADO. If you're using DBExpress, you probably don't need it.

Remy Lebeau Over a year ago

I forget that ADO is different than other DB systems. You will have to replace AsString with Value and ParamType with DataType:

ADOQuery1.Parameters.ParamByName('Meno').DataType := ftString; ADOQuery1.Parameters.ParamByName('Meno').Value := Edit1.Text;

|

AlexSC · Accepted Answer · 2014-03-18 14:47:57Z

1

Remove the use [xxx] at the begining of the statement. The connection you use must be already configured to point to the correct database. Just like many others said, avoid creating your sentences by using constants, instead, use paramenters.

answered Mar 18, 2014 at 14:47

AlexSC

1,9533 gold badges30 silver badges57 bronze badges

Comments

Sayat Ertüfenk · Accepted Answer · 2014-03-18 14:24:23Z

0

http://docwiki.embarcadero.com/Libraries/XE3/en/System.SysUtils.QuotedStr

Use QuotedStr function. For example

sql:='use [TestovaciaDb] INSERT INTO [dbo].[client]([Meno],[Priezvisko]) VALUES('+QuotedStr(name)+','+QuotedStr(surname)+')';

Use QuotedStr to convert the string S to a quoted string. A single quotation mark (') is inserted at the beginning and end of S, and each single quotation mark in the string is repeated. To remove the quotation marks from a quoted string, use the AnsiDequotedStr routine.

answered Mar 18, 2014 at 14:24

Sayat Ertüfenk

3021 gold badge3 silver badges11 bronze badges

2 Comments

Gerry Coll Over a year ago

QuotedStr would be OK for SQL Server, but not MySQL or any other database that allows \' as an escape

HeartWare Over a year ago

@GerryColl: MySQL also supports doubling quote characters to signify a single quote character, so QuotedStr will be fine.

Collectives™ on Stack Overflow

How to create correct SQL statement in delphi

3 Answers 3

9 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related