problem with the code- dont know where

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. 

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.

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.  

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.

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.

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.

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.

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