0

I am trying to create a procedure that will insert two columns into a table. I need to create a procedure that I could use in vb.net for creating mailboxes

The procedure that I created is:

PROCEDURE 'create_mail' (IN em VARCHAR(20),IN pas VARCHAR(20),OUT ans INT )
INSERT INTO 'mail.mailbx' ('email','pasword') VALUES ('em', ENCRYPT('pas'))

When I am executing in phpMyAdmin its work, it creates columns that I choose in 'mail.mailbx', but I need to check if em exist in 'mail.mailbx', if exist create ans as integer = 'o' or something like that, if no exist run "insert into..." and create 'ans' as integer ='1'. from vb.net I will be create msgbox's that let me know if mailbox was created or not created from parameter 'ans'.

Francisco
  • 10,918
  • 6
  • 34
  • 45
Ilya Y
  • 1
  • 1
    Why not simply create a UNIQUE index on column email.. the INSERT fails if the same email adress is being used twice – Raymond Nijland Mar 01 '17 at 15:06
  • Hi.thanks for your replay.do u have some examples how can I do it? – Ilya Y Mar 01 '17 at 17:07
  • You can run query like this - `ALTER TABLE mail.mailbx ADD UNIQUE INDEX UK1(email)`, see MySQL documentation about CREATE TABLE and ALTER TABLE. Or you can use GUI tool to do it in design mode. – Devart Mar 02 '17 at 07:27
  • hi)i read mysql documentation and some forums,and i did it :) the procedure is: CREATE PROCEDURE `create_mail` ( IN `em1` VARCHAR( 80 ) , IN `pas1` VARCHAR( 80 ) , OUT `err` VARCHAR( 30 ) ) – Ilya Y Mar 02 '17 at 14:34
  • BEGIN declare exit handler for sqlstate '23000' set err=1; iNSERT INTO `mail`.`users` (`email`, `password`) vALUES (em1,pas1); set err=0; end .So i set primary column 'email',i declared handler for duplicated culumns..work fine)in vb.net i create js alerts thas showing me if mail created or not created,and other errors i see as sql eror with TRY WITH thanks a lot – Ilya Y Mar 02 '17 at 14:48

0 Answers0