I.T. Discussion Community!
-Collapse +Expand
Delphi
Search Delphi Group:

Advanced
-Collapse +Expand Delphi To/From
To/FromCODEGuides
-Collapse +Expand Delphi Store
PRESTWOODSTORE

Prestwood eMagazine

May Edition
Subscribe now! It's Free!
Enter your email:

   ► MB LobbyPascal and Delphi Coding BoardDelphi VCL Topic   Print This     

problem with the code- dont know where

problem with the code- dont know where in Delphi VCL topic (part of our Pascal and Delphi Coding group).

Quick Search: problem   problem code   problem code dont   know where  
pkakkar
-- FI
hello ,

I have two tables 'contacts' (which has name, email and group name, groupname is foreign key) and 'groups'(which has groupname and is a primary key). 'Groups' is a master table.  Also, I have two forms where in I have to let the user enter name and email. and two radio buttons - 'add contact to group' and 'create a new group.' and a button 'add contact'. when user chooses option 2 that is 'add contact to group', an editbox will appear and the user has to add a new group

now there are few validations with this:
a) if the editbox is empty then showmessage 'please enter groupname'
b) check if the groupname entered is not same as in database . because you are creating a new group. so showmessage 'groupname already exists'and exit

in another form i have database connection, 2 msquery and 2 datasources ..all are interlinked.

below is the code : if I check RadioButton2
(the problem is
a) if I just enter name and email, click on Radiobutton2 and press 'add contact 'button, it doesnt show me 'groupname is not entered.'
b) if i enter name, email, select RadioButton2 and enter a groupname which is similar in my groups table, and press 'add contact' button, it throws voilation related to primary key instead of showing message'group name already exists'

And i dont know where problem is. But if I give a unique groupname along with name and email and press 'add contact' button..then it adds all details to 'contacts' table as well as 'groupname'  to groups table.


if RadioButton2.Checked then
begin
       //check if name is entered
       if Edit3.Text<>'' then begin
        //check if there is no duplicate entry of groupname
        if Edit3.Text<>Form2.MSQuery2.FieldByName('GroupName').AsString then begin
       //add the new groupname to groups table
       Form2.MSQuery2.Close;
       Form2.MSQuery2.SQL.Text:= 'Insert into groups values(:gname)';
       Form2.MSQuery2.ParamByName('gname').Value:=Edit3.Text;
       Form2.MSQuery2.Execute;
     // also add the details to contacts table
       Form2.MSQuery1.Close;
       Form2.MSQuery1.SQL.Text:='Insert into contacts values(:name, :mail, :gname)';
       Form2.MSQuery1.ParamByName('name').Value:=Edit1.Text;
       Form2.MSQuery1.ParamByName('mail').Value:=Edit2.Text;
       Form2.MSQuery1.ParamByName('gname').Value:=Edit3.Text;
       Form2.MSQuery1.Execute;
       showMessage('the new group: '+edit3.Text+ ' and contact details are added.');
       exit;
       end
       else begin
        showMessage('Please enter a group name');
       exit;
       end;

  end;
  end;


Regards
Pooja
 Posted 12 years ago (Thread Starter)
Comment Quote
About pkakkar -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #12726, 7 replies
Thread Started 5/8/2008 3:40:09 AM
View Counter=17712
Last Reply Posted 3/10/2017 4:21:54 AM)
Location=-- FI 
Joined=12 years ago   MB Posts=2  
William Pantoja
Woodinville, WA, USA

First of all, let me correct a few things:

1.  Microsoft SQL Server is neither case sensitive nor case insensitive.  The collation used is what determines whether case sensitivity is being used.  The default collation for Microsoft SQL Server is case insensitive.  Wes, no offense, but I would suggest you spend some time with Microsoft SQL Server Books online as you have apparently been misinformed about some very basic features of Microsoft SQL Server.  If you wish, I would be more then happy to answer questions or you and make sure anything you have been incorrectly lead to believe is set straight.

2.  Wes is entirely correct when speaking about radio buttons and field focus.  However, there are instances where radio buttons are not initially checked forcing the user to pick one of the options before continuing.  An excellent example of this is a customer survey.  Multiple choice questions will start without any radio buttons checked.  However, this does add the addition burden of extra field validation on the programmer's end to make sure at least one radio button has been checked.

A for a solution to your problem, you have several options:

1. Change your collation to a case insensitive one (not all database engines support this).

2. Create a query that does a case insensitive comparison.  The conversion of the name searched for can be done in the SQL query (as in the example) or in the code.

SELECT * FROM Groups WITH (NOLOCK) WHERE (UPPER(Name) = UPPER('My Name'))

3. Create a view that converts the name column to upper (or lower) case.  For example:

CREATE VIEW [dbo].[vwGroups]
AS
SELECT ID, UPPER(Name) AS Name FROM Groups
GO

You would use the view to find your group converting the inputed name to upper (or lower) case when builing the query.

4. Create a stored procedure that adjusts the case of the group name.  For example:

CREATE PROCEDURE [dbo].[spFindGroup]
	@Name [VAR_CHAR(]50)
AS
SELECT * FROM Groups WITH (NOLOCK) WHERE (UPPER(Name) = UPPER(@Name))
GO

5. Create a computed column that holds an all upper (or lower) case version of the name column.

6. Create a non-computed column where you store an all upper (or lower) case version of the name when a group record is either inserted or deleted.

Selecting the right solution:

Picking the right solution will depend on your development language's features, performance requirements, space requirements, the featues available in your database engine, and the ability (or lack thereof) to be able to modify the database schema.

Some of the solutions above are more performant then others.  Some require additional disk space.  And some will require support of a particular feature (such as stored procedures or views) by the database engine (Microsoft SQL Server supports all of the above).  In the end, you will have to choose the best solution for your application.

Additional Reccomendations:

As a rule of thumb, you should always create an ID field for your tables.  Doing so ensures that you have a unique value that you can use to identify a row with.  In addition, you often gain some performance out of your database when joining tables together (comparing numbers or GUIDs is faster then comparing text).  However, this does take up some additional storage space.  There are some instances where a seperate ID field is not necessary when the table contains a column that is already unique (your groups table is a good example based on how you appear to be using it) however you still may get performance gain out of adding the ID field if your unique column is not an integral data type. 

---
William Pantoja

Consultant/Software Engineer
ForceOne Technologies, Inc.

 Posted 12 years ago
Comment Quote
About William Pantoja -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #12741 (Level 1.1)  Reply to 12726
Thread Started 5/12/2008 4:36:53 PM
Location=Woodinville, WA, USA  
Joined=19 years ago   MB Posts=163   KB Posts=1  
Daniel Fought
Prestwood IT
Home office in Fresno, CA.

it looks to me like the error message for issue a is in the wrong place.  I think it needs to be the else clause for the Edit3.Text<>'' clause

issue b: is a little more difficult.  My first thought is that MSQuery2 isnt returning a value.

Dan Fought
Senior Programmer Analyst
Prestwood IT Solutions
http://www.prestwood.com

 Posted 12 years ago
Comment Quote
About Daniel Fought -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Daniel Fought
Danial Fought is a senior programmer analyst with Prestwood IT where he develops custom Windows software and custom websites. When Dan is not coding for clients, he participates in this online community. Coding specialties include Paradox/ObjectPAL, MS Access, Visual Basic, and VS.Net/VB.Net.

Post ID #12727 (Level 1.2)  Reply to 12726
Reply Posted 5/8/2008 1:16:38 PM
Location=Home office in Fresno, CA. 
Joined=18 years ago   MB Posts=401   KB Posts=12   KB Comments=4  
Daniel Fought
Prestwood IT
Home office in Fresno, CA.

First of all you should really study Wes's post.  There is a huge amount of very sound programing advise in it. 

Secondly delhi belly is not the same as Delhi Belly.  Rather than restricting the user input you could convert string from the text box and the string from the query to upper or lower case.  I'm kind of surprised that you havent simply initiated a query using the value from the text field as a parameter and let the db engine do the compare.  

Dan Fought
Senior Programmer Analyst
Prestwood IT Solutions
http://www.prestwood.com

 Posted 12 years ago
Comment Quote
About Daniel Fought -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Daniel Fought
Danial Fought is a senior programmer analyst with Prestwood IT where he develops custom Windows software and custom websites. When Dan is not coding for clients, he participates in this online community. Coding specialties include Paradox/ObjectPAL, MS Access, Visual Basic, and VS.Net/VB.Net.

Post ID #12736 (Level 1.3)  Reply to 12726
Reply Posted 5/12/2008 10:04:04 AM
Location=Home office in Fresno, CA. 
Joined=18 years ago   MB Posts=401   KB Posts=12   KB Comments=4  
Wes Peterson
Prestwood IT
Prestwood IT office in Citrus Heights, CA

Before addressing your specific problems, I'd like to ask a question.

Clearly, many individuals can belong to a "group." Is it possible for a given individual to belong to:

A: No group at all, and

B: More than one group?

Based on what you've told us, I'll assume that a person must belong to one and only one group.

A little re-thinking of your design might yield a significant improvement. That's what I'm going to propose.

One thing I like to do in my Delphi database applications is provide a TDataModule in which I have methods like:

procedure AddNewGroup(const: _groupName); 
function GroupExists(const : _groupName : string) : Boolean; 
procedure AddNewPerson(const: _name,  _email , _groupName);
proceedure UpdatePerson(const: _name, _email, _groupName);
funcion PersonExists(const: _name) : Boolean;

SQL, similar to what you've implemented, would be used in the methods, above.

Actually, I create a TDataModule descended from a base TDataModule. The base data module declares all the methods as virtual; abstract;

The descendant (real) datamodule redeclares the methods with the override directive, and contains the actual implementation of the methods. Doing this simplifies the task of changing the application's underlying database from, say, Access to SQL Server. You just create a new descendant data module that satisfies the new database's requirements.

One more thing I often do is implement the descended data module as a Singleton pattern. This ensures that one, and only one, instance of the data module will exist during the lifetime of the program.

One function, "Dm" returns the data module instance.  If the data module has not yet been created, this function creates it.  So the first place in your application that "Dm" appears will ensure creation of the data module.  Thereafter, all "Dm" calls will be going to the same place.  Any form in your application can use the datamodule simply by adding it to the form's Uses clause.

If you're not familiar with the Singleton pattern, just have a peek at clipboard.pas in the Delphi source. Delphi has several other Singletons, like Printer.

All that said, the above methods could just as easily be implemented on another form - or on the form in question, here.

Tip: Your code, and mine, will be much easier to read if we take the trouble to give our controls more meaningful names than the default "Edit3" that Delphi provides. I'd suggest:

NameEditBox,
EmailEditBox, and
GroupEditBox.

Now, with the method declarations above, your form's validation becomes much easier.

First, though, let's imagine a simpler dialog with just three edit boxes, plus OK and Cancel buttons; but no radio buttons. As you review the code, below, you'll see that one, simple form, can easily handle your requirements.

Here's how you might code the OnClick event for the OK button:

procedure TForm1.OkButtonClick(Sender: TObject);
var
mr : word;
begin
// Validate required data
if Length(NameEditBox.Text) < 1 then
begin
MessageDlg('Name is required.', mtError, [mbOK], 0);
NameEditBox.SetFocus;
Exit;
end;

if Length(EmailEditBox.Text) < 1 then
begin
MessageDlg('Email is required.', mtError, [mbOK], 0);
EmailEditBox.SetFocus;
Exit;
end;

if Length(GroupEditBox.Text) < 1) then
begin
MessageDlg('Group is required.', mtError, [mbOK], 0);
GroupEditBox.SetFocus;
Exit;
end;

// if we get this far, we can begin the database validations

if not Dm.GroupExists(GroupEditBox.Text) then
begin
mr := MessageDlg(Format('Group %s does not yet exist.'+#13+#10+''+
#13+#10+'Would you like to create group %s?',
[GroupEditBox.Text, GroupEditBox.Text]), mtConfirmation,
[mbYes, mbNo], 0);

if mr = mrYes then
Dm.AddNewGroup(GroupEditBox.Text)
else
begin
// clear the group edit box
GroupEditBox.Text := '';
MessageDlg('Please specify a group.', mtWarning, [mbOK], 0);
GroupEditBox.SetFocus;
Exit;
end;
end;

if Dm.PersonExists(NameEditBox.Text) then
Dm.UpdatePerson(NameEditBox.Text, EmailEditBox.Text, GroupEditBox.Text)
else
Dm.AddNewPerson(NameEditBox.Text, EmailEditBox.Text, GroupEditBox.Text);

Close;
end;

I've intentionally omitted exception handling to keep the code simple. Naturally, the data module methods we've declared could be coded to raise exceptions, and your data entry form could trap and handle them. as required.

There is another neat way to handle required data validation: Keep the OK button disabled until the user has entered all the required data. Check out the OnUpdate event of TActionList as a good way to do this with very little effort.

Wes Peterson
Senior Software Engineer
Prestwood IT Solutions
http://www.prestwood.com

 Posted 12 years ago
Comment Quote
About Wes Peterson -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Wes Peterson

Wes Peterson is a Senior Programmer Analyst with Prestwood IT Solutions where he develops custom Windows software and custom websites using .Net and Delphi. When Wes is not coding for clients, he participates in this online community. Prior to his 10-year love-affair with Delphi, he worked with several other tools and databases. Currently he specializes in VS.Net using C# and VB.Net. To Wes, the .NET revolution is as exciting as the birth of Delphi.


Post ID #12730 (Level 1.4)  Reply to 12726
Reply Posted 5/9/2008 1:20:27 PM
Location=Prestwood IT office in Citrus Heights, CA 
Joined=16 years ago   MB Posts=158   KB Posts=163   KB Comments=34   BLOG, Topics=20  
Wes Peterson
Prestwood IT
Prestwood IT office in Citrus Heights, CA

Thanks for the kind words, Dan. It's not that there's really a huge amount of advice in there - it's that I believe prople will have the best results with Delphi if they use established best practices.

Data modules (think of an invisible form), coupled with the Singleton pattern, will seem, at first, like a bunch of extra trouble. In a trivial application, they are. But, once a project goes beyond trivial, they save so much work elsewhere, that I wouldn't live without them.

----------------------------

>I have to make some validation in edit box to enter words in upper /lower case and then fetch the group name from database also in the same 'upper / lower case'...what do u say??

[WP] Some databases allow indexes to be declared as case-insensitive, and this is a wonderful feature that save tons of work, because you never have to worry about - or deal with - data-entry case-sensitivity. Alas, it looks like you're using MS SQL Server, and it doesn't appear to support that feature (that I can see).

So, as things presently stand, you hvae to resort to a brute-force scan of all rows in your Groups table, converting the data to all upper-case, or all lower-case (your for loop). This is pretty expensive in terms of processing time!

Here's a trick some developers use to overcome this problem: In the Groups table, they have a second column, "GroupNameUpper," and, whenever a row is inserted or updated, they store the uppercase version of the group name. This is often called a "shadow column." Naturally, if they do this, they also create database triggers to manage the uppercase copy.

But there's a better way in SQL Server:  See below.

Now, instead of having to scan all rows, you can simply convert the data-input value to upper case, and use it in the WHERE clause of a SELECT statement. This will return either zero or one row; no need to scan.

A cleaner approach, if it's permissible in your requirements, is to simply set your group name edit boxes to automatically convert characters to upper-case as the user types - or you can convert to upper-case before applying the value to the database. That way, you store only upper-case group names in the database.

For display and reporting purposes, you can use functions to convert upper case to proper case. (This does not work well with names of people! Consider "Werner von Braun" . If stored as upper case, then "fixed" with a proper case function, it becomes "Werner Von Braun," which is wrong.)

A nicer way in SQL Server: Declaring a a "computed column," and putting an index on it.  Your computed column would simply be the uppercase version of group name. 

Some observations about your validation code:

1: You are saving the worst data-entry offense for last: No radio button checked. I would make that the first test. Alternately, you could eliminate this test entirely by choosing the most commonly used option, and setting that radio button to checked by defalut. Proper use of radio buttons ensures that one, and only one, button will be checked at any time.

2: As a courtesy to end users, I always use the SetFocus method of TEdits, etc., to put focus back on the offending control. You tell the user what's wrong, then you put them back at the right place.

3: Developers that are religious about structured coding detest the Exit command and, instead, end up writing a bunch of "if, esle if, esle if, end" code. I'm not that religious. I find that using discrete tests, with a warning dialog, SetFocus, and Exit, makes my code much easier to understand and maintain. See my previous example validation code.

Wes Peterson
Senior Software Engineer
Prestwood IT Solutions
http://www.prestwood.com

 Posted 12 years ago
Comment Quote
About Wes Peterson -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Wes Peterson

Wes Peterson is a Senior Programmer Analyst with Prestwood IT Solutions where he develops custom Windows software and custom websites using .Net and Delphi. When Wes is not coding for clients, he participates in this online community. Prior to his 10-year love-affair with Delphi, he worked with several other tools and databases. Currently he specializes in VS.Net using C# and VB.Net. To Wes, the .NET revolution is as exciting as the birth of Delphi.


Post ID #12737 (Level 1.5)  Reply to 12726
Reply Posted 5/12/2008 12:20:43 PM
Location=Prestwood IT office in Citrus Heights, CA 
Joined=16 years ago   MB Posts=158   KB Posts=163   KB Comments=34   BLOG, Topics=20  
Most Recent Post
Wes Peterson
Prestwood IT
Prestwood IT office in Citrus Heights, CA

William,

Thanks for your corrections, and additional insights.

>Wes, no offense, but I would suggest you spend some time with Microsoft SQL Server Books online as you have apparently been misinformed about some very basic features of Microsoft SQL Server. If you wish, I would be more then happy to answer questions or you and make sure anything you have been incorrectly lead to believe is set straight.

[WP] No offense taken. Over the years, I've worked with many databases, including SQL Server, but it is obviously not my strongest. As it turns out (so far), most of my projects have required lesser, or "desktop" databases.

Fortunately I have several colleagues here at Prestwood that are SQL Server gurus. I do like the books online - a great resource. Thank you, too, for your offer of assistance. I may take you up on it. I've already learned a thing or two from your examples.

>Multiple choice questions will start without any radio buttons checked. However, this does add the addition burden of extra field validation on the programmer's end to make sure at least one radio button has been checked.

[WP] You're right on the money about radio buttons. It wasn't my intent to imply that one should be checked by default in all cases. However, from what we know of the original poster's requirements, a default checked button seems reasonable. The downside is that lazy users may not pay attention.

It's nice that you added examples of a case-insensitive SELECT statement, plus views and stored procedures.

Because the discussion seemed to be primarily focused on validation logic, that's where I put most of the effort in my replies. Your additional suggestions, of course, support that need.

I also agree with you when it comes to the use of surrogate keys. While not necessary for the Groups table, I'd favor one anyway; precisely because of the potential performance benefits you mention. A cascading update of a Group name is a good example of an integral, surrogate key, taking quite a load off the database.

I'm glad you jumped in.

Best regards.

Wes Peterson
Senior Software Engineer
Prestwood IT Solutions
http://www.prestwood.com

 Posted 12 years ago
Comment Quote
About Wes Peterson -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Wes Peterson

Wes Peterson is a Senior Programmer Analyst with Prestwood IT Solutions where he develops custom Windows software and custom websites using .Net and Delphi. When Wes is not coding for clients, he participates in this online community. Prior to his 10-year love-affair with Delphi, he worked with several other tools and databases. Currently he specializes in VS.Net using C# and VB.Net. To Wes, the .NET revolution is as exciting as the birth of Delphi.


Post ID #12742 (Level 1.6)  Reply to 12726
Reply Posted 5/12/2008 6:08:28 PM
Location=Prestwood IT office in Citrus Heights, CA 
Joined=16 years ago   MB Posts=158   KB Posts=163   KB Comments=34   BLOG, Topics=20  
pkakkar
-- FI

Hello all,

well thanks for pointing out things and for your suggestions..You were right. Issue 'b' was actually not returning anything...

well i have done some modifications in my code and its working fine (as per what i wanted) ..except one thing..if I have a group name as 'Delhi Belly' in my groups table. but user enters 'delhi belly' ..in the edit box.(can u see the difference in letters)...it throws primary key exception ..but if user enters 'Delhi Belly' in the edit box then it gives the error message 'The group name already exists'..this is what i wanted....In my opinion, I have to make some validation in edit box to enter words in upper /lower case and then fetch the group name from database also in the same 'upper / lower case'...what do u say??

here is the code:

if RadioButton2.Checked then
begin
//check if name is entered
if Edit3.Text='' then begin
showMessage('Please enter a group name');
end
else
begin
//check if there is no duplicate entry of groupname
b := false;
for i:=0 to Form2.MSQuery2.RecordCount-1 do
begin
if Edit3.Text = UpperCase(Form2.MSQuery2.FieldByName('GroupName').AsString) then
begin
b := true;
end;
Form2.MSQuery2.Next;
end;

if b then begin
showMessage('Group already exists.');
end

else
begin
//add the new groupname to groups table
//Form2.MSQuery2.Close;
Form2.MSQuery2.SQL.Text:= 'Insert into groups values(:gname)';
Form2.MSQuery2.ParamByName('gname').Value:=Edit3.Text;
Form2.MSQuery2.Execute;
// also add the details to contacts table
//Form2.MSQuery1.Close;
Form2.MSQuery1.SQL.Text:='Insert into contacts values(:name, :mail, :gname)';
Form2.MSQuery1.ParamByName('name').Value:=Edit1.Text;
Form2.MSQuery1.ParamByName('mail').Value:=Edit2.Text;
Form2.MSQuery1.ParamByName('gname').Value:=Edit3.Text;
Form2.MSQuery1.Execute;
showMessage('the new group: '+edit3.Text+ ' and contact details are added.');
exit;
end;
end;

end;
if not (RadioButton1.Checked) and not (RadioButton2.Checked) then
showMessage('Please select the options through RadioButtons to add contact');
end;

Regards

Pooja

 Posted 12 years ago (Thread Starter)
Comment Quote
About pkakkar -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #12735 (Level 1.7)  Reply to 12726
Reply Posted 5/12/2008 3:50:33 AM
Location=-- FI 
Joined=12 years ago   MB Posts=2  

Revive Thread!

Add a comment to revive this old thread and make this archived thread more useful.

Write a Comment...
Full Editor
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1296A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #12726 Counter
17712
Since 5/8/2008

Follow PrestwoodBoards on: 


©1995-2020 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech