Solved

Check the type of a field in a query

Posted on 2006-07-05
8
284 Views
Last Modified: 2010-08-05
I have several questions...

1)
I'm trying to use a variable in a query similar to the following

Query1.SQL.Text :=  'SELECT name FROM Table WHERE Value = :MyVal';
Query1.ParamByName('MyVal').Value := EditBox.Text;

My problem is that if Value is an integer then my Query won't accept the value and will give me an error.  I'm not sure of the best solution to this I think I need to check the type of 'Value' and assign the variable MyVal a value based on the type.  Is there a way I can check the type of a particular field in a db?

2)    
I'm getting input from EditBoxes and using it similar to above for things that should be integers I only want to have users able to enter numeric data into the field or check whether what they entered is a number before I assign the value to my query is there a function that does this?

3) if Value above in part 1 is a string and the text in my EditBox contains apostrophes do I need to edit the field at all so that the Query doesn't directly interpret the apostrophes.  
0
Comment
Question by:ICPooreman
[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
8 Comments
 
LVL 12

Accepted Solution

by:
Ivanov_G earned 100 total points
ID: 17042801
1) It was a long time ago when I used this, but I think there was something like:
Value.AsInteger
Value.AsString

2) use StrToInt() function in try-catch block, if it fails - then the input is not integer
You can also have a look at: http://www.festra.com/eng/snip05.htm

3) QuoteStr()
0
 
LVL 4

Assisted Solution

by:JeePeeTee
JeePeeTee earned 100 total points
ID: 17043028
Try Query1.ParamBtName('YourValue').DataType ... Maybe you have to call Query1.Prepare;

Use Prepare to allocate resources for the query and to perform additional optimizations.
0
 
LVL 9

Assisted Solution

by:sun4sunday
sun4sunday earned 150 total points
ID: 17043436
1.
You can use it like
QueryName with the field name  like
Query1FIELDNAME.Value := Edit1.Text;   But the field in that query should a field type which can hold the String values like String.


If you the value from the edit box is only numbers then you can use it like
Query1FIELDNAME.AsInteger := Edit1.Text;

2.
put the code under editbox key press, it will allow only the numbers and backspace

procedure TForm1.Edit1KeyPress(Sender: TObject; var Key: Char);
begin
  if (Key in ['0'..'9', #8]) then  //#8 for backspace
  else
  Key := #0;
end;


sun4sunday
0
Industry Leaders: 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 9

Expert Comment

by:sun4sunday
ID: 17043745
3.
Query1FIELDNAME.Value := Edit1.Text;
or
Query1FIELDNAME.AsString := Edit1.Text;

sun4sunday
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 17043828
setting the correct Type of a Parameter:

in case of using BDE -> use .AsString instead of .Value (some suggestions from other experts)
in Case of using ADO -> as JeePeeTee suggested: Query1.ParamByName('MyVal').DataType := ftString;

the last one, may applyable for BDE too

meikl ;-)
0
 
LVL 9

Expert Comment

by:sun4sunday
ID: 17043927
meikl, u r right.
you here  ?!?!?!?

I thought you are in Germany football Stadium to watch the Portugal Vs France
(>>i am working as software-developer and database-designer in a german-company. From your member profile
)

:))

sun4sunday
0
 

Author Comment

by:ICPooreman
ID: 17044136
Thanks this has been really helpful!

I think I'm set with part 2 and 3


I have most of what I need for part 1 too but have one more question.    

Query1.SQL.Text :=  'SELECT name FROM Table WHERE Value = :MyVal';

Query1.ParamByName('MyVal').DataType := ftString;
works for what I need it to do but if this is possible I would like to instead have it assign the datatype based on the field like below.
Query1.ParamByName('MyVal').DataType := Query1.FieldByName('Value').DataType;

I'm trying to write a function which will do all of the changes needed to MyVal no matter how many params are included, but need to extract the DataType MyVal is supposed to be first.  

My question is can I figure out what field a param is associated with without knowing the name of the Field?  Say I didn't know 'Value' was my field name is it possible to derive the DataType of the field name associated with the param MyVal.  
0
 
LVL 27

Assisted Solution

by:kretzschmar
kretzschmar earned 150 total points
ID: 17044309
>I thought you are in Germany football Stadium to watch the Portugal Vs
>France

:-)) no no, a visit in a football Stadium is too expensive, additional
the Stadium is too far away (about 400 km) from me. i will look at the play at tv. well, germany has lost yesterday against italy. was a good and fair play, italy was the luckier team at the end.

>My question is can I figure out what field a param is associated with without
>knowing the name of the Field?  Say I didn't know 'Value' was my field
>name is it possible to derive the DataType of the field name associated with
>the param MyVal.  

not really, most treating the parameter as string will work (except for date-types and blob-types), because this will implicit converted by the database then.

Another possible would be to to execute the query forced with no result-set like
select * from atable where 0 = 1
after executing you can read out the datatype of the needed field
and execute your real query after setting the datatype for the parameter.

meikl ;-)
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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

734 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