some problem in code

[pkakkar]

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 valuesgname)';
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 valuesname, :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;

 

[Opieo]

I think I have a handle on what you are trying to do and what is going wrong.

As long as I am understanding correctly, you are using [blue]FieldByName[/blue] to check if the [green]GroupName[/green] already exists. [blue]FieldByName[/blue] is used to access which record is currently selected. For the testing to see if it exists at all, use [blue]Locate[/blue].
An example:

if (ADOTable1.Locate ([blue]'gname'[/blue], Edit3.Text, []) = False) then begin
 ShowMessage([blue]'Group name does not exist'[/blue]);
 Exit;
end;

Either that or it could be that you have [green]'GroupName'[/green] in the [blue]FieldByName[/blue] and [green]'gname'[/green] everywhere else. Perhaps simply mistyped in that one spot? An alternative method to test if the group already exists, would be to use the query, and add just one line where you (in psuedocode):
select 'gname' from the proper table where gname = edit3.text. run the query. if query.[blue]RecordCount[/blue] = 0 then it already exists.

For a user preference idea, small suggestion to make life easier and save on indenting.
When you have an if statement for checking to kick them out, you could have it be:

 if Edit3.Text = '' then begin
 ShowMessage([blue]'Please enter a group name'[/blue]);
 Exit;
end;

That way everything below it doesn't need to be indented.
Again whether you do that or not simply boils down to preference. Just helps cut down on indenting when procedures get more complex.

I hope I understood well enough to help some.
Please let me know if I misunderstood your request.

~
Chuck Norris is the reason Waldo is hiding.

 

[pkakkar]

Hello all,

well thanks for pointing out things and for your suggestions..Yes you are right.

well i have done some modifications in my code last week 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..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'....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 valuesgname)';
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 valuesname, :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

 

[Opieo]

For validating, I can think of a couple of methods.
Each of them would require you to write a short procedure that would set how the group shows up. Would make the whole string lower case, which a quick function already exists for. Then if it is the first letter, or the previous spot is a space, capitalize that one character.

That little procedure could either be run right before you plug in the group name to Check for existance (or adding, since you would want them to be the same) OR you could lower case everything before checking / adding, and then run that when it is pulled from the database.

I believe your error results from Delphi's comparison:

 if Edit3.Text = UpperCase(Form2.MSQuery2.FieldByName('GroupName').AsString) then

not being case sensitive. However, the database is probably case sensitive so it is throwing the exception. I feel like I am stabbing at the dark at the moment. Hope this helps.

~
Chuck Norris is the reason Waldo is hiding.

 

[lespaul]

I have a database that is case sensitive and I have to use the UpperCase and UPPER database SQL function all the time to validate.



Leslie

Have you met Hardy Heron?