WHERE statement problem for MSSQL
lazarus / 16-Databases

----------------------------------------------------------------------
stanks · 2010-01-11 14:02

hi

when i use:

query.SQL.Text := 'SELECT * FROM dg';
query.Open;

everything is ok, but when i add this (S is string that i get from
edit box):

query.SQL.Text := 'SELECT * FROM dg WHERE sifra LIKE "' + S + '*' +
'"';
query.Open;

query fails with error: "Could not execute statement. ODBC error
details: LastReturnCode: SQL_ERROR; Record 1: SqlState: 07002;
NativeError: -3010; Message: [Microsoft][ODBC Microsoft Access Driver]
Too few parameters. Expected 1.;"

What i am doing wrong?

thanks

----------------------------------------------------------------------
theo · 2010-01-11 14:12

http://www.w3schools.com/SQl/sql_like.asp

----------------------------------------------------------------------
stanks · 2010-01-11 14:16

so you wanted to tell me what?

----------------------------------------------------------------------
clauslack · 2010-01-11 14:17

You can use this too
Code: [Select]SQL.add('select * from listado where nomtit like
'+QuotedStr(trim(Edit1.text)+'%'))

Regards

----------------------------------------------------------------------
ik · 2010-01-11 14:18

You have a very bad habit here. You can have Cross Site Scripting by
the way you concat the string into the query.
you should use either named based binding or anonymous ones instead,
and it will also be easier for you to add such LIKE statement to the
query.

It should be something like so:

'SELECT * FROM dg WHERE sifra LIKE '#32':String%'#32

where ":String" is a named based binding.

----------------------------------------------------------------------
theo · 2010-01-11 14:26

Quote from: ik on January 11, 2010, 03:18:48 pmYou can have Cross Site
Scripting by the way you concat the string into the query.

I don't think this code is for a web application. You mean SQL
Injection?

----------------------------------------------------------------------
ik · 2010-01-11 15:08

Quote from: theo on January 11, 2010, 03:26:03 pmQuote from: ik on
January 11, 2010, 03:18:48 pmYou can have Cross Site Scripting by the
way you concat the string into the query.

I don't think this code is for a web application. You mean SQL
Injection?

Yes, sorry

----------------------------------------------------------------------
SteveF · 2010-01-11 16:43

Quote from: stanks on January 11, 2010, 03:02:54 pmhi

when i use:

query.SQL.Text := 'SELECT * FROM dg';
query.Open;

everything is ok, but when i add this (S is string that i get from
edit box):

query.SQL.Text := 'SELECT * FROM dg WHERE sifra LIKE "' + S + '*' +
'"';
query.Open;

query fails with error: "Could not execute statement. ODBC error
details: LastReturnCode: SQL_ERROR; Record 1: SqlState: 07002;
NativeError: -3010; Message: [Microsoft][ODBC Microsoft Access Driver]
Too few parameters. Expected 1.;"

What i am doing wrong?

thanks

I think you're running into a case where the native MS Access SQL
syntax is not ANSI-compliant.  While the native syntax works fine
within Access itself, ANSI syntax needs to be used when connecting
through ODBC.
The difference is in the wild-card character used in the LIKE clause. 
You need to use a percent-sign ( instead of an asterisk (*).  Try
reworking your SQL statement so that it looks like this:

SELECT * FROM tablename WHERE column LIKE 'string%'

On a slightly different subject, would you mind very much if I asked
you for a copy of your application?  While I know my way around Access
quite well, I'm still a real newbie when it comes to Lazarus and
getting connected to external databases.  So far I haven't found
documentation that helps for me.  An actual working example would
probably go a long way.

TIA, Steve

----------------------------------------------------------------------
stanks · 2010-01-11 18:45

Quote from: clauslack on January 11, 2010, 03:17:02 pmYou can use this
too
Code: [Select]SQL.add('select * from listado where nomtit like
'+QuotedStr(trim(Edit1.text)+'%'))

Regards
Thank you clauslack. This works ok