Thread: Check for existing data in field using SP

Hi people
I have a registration form, where i need unique email id from users.
Right now i have the Stored Procedure to insert data, can anybody help me to modify it to do that check in the SP only, i think it can be done.

Code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_Register`(
   In FirstName varchar(50),
   in LastName varchar(50),
   in EmailId varchar(100),
   in Gender varchar(20),
   in Mobile varchar(25),
   in Pswd varchar(20))
BEGIN
   Insert into tblUserMaster (First_Name, Last_Name, Email_Id, User_Gender, User_Mobile,
   Registration_Date, LastLogin_Date, User_Pswd)
   Values (FirstName, LastName, EmailId, Gender, Mobile, now(), now(), Pswd);
END

MySql
VS.NET 2005

Thanx

thanx for that J, i'll check it.......but rite now something other urgent has come up
i'll get back to u on this in couple of days

J, that seem to work
now the second question which is, now can i get the status to my .net application that whether the record was added or not!!

If not then i'll have to tell user that the email already exists!!
Btw, do u think thats how it shud be done??
Thanx

What do you mean by getting the status? You mean whether the INSERT was succesful or not?

yes RR, i mean if the email id already exists in db, then it wont insert and will have to let user know that he needs to enter another one.

and the insert is successful, then also i shud know so that i can redirect him accordingly!!

You can use an OUT parameter to send a value back.

If you just need to know if it was successul, you cuold have a CHAR field that returns Y or N depending on if it was successful. It's been a while since I did it, but possibly something like this:-

Code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_Register`(
   In FirstName varchar(50),
   in LastName varchar(50),
   in EmailId varchar(100),
   in Gender varchar(20),
   in Mobile varchar(25),
   in Pswd varchar(20),
   Out Result CHAR(1))
BEGIN
   IF NOT EXISTS(SELECT Email_Id FROM tblUserMaster WHERE Email_Id = EmailId) THEN
       Insert into tblUserMaster (First_Name, Last_Name, Email_Id, User_Gender, User_Mobile,
       Registration_Date, LastLogin_Date, User_Pswd)
       Values (FirstName, LastName, EmailId, Gender, Mobile, now(), now(), Pswd);
       Result="Y";
   ELSE
       Result="N";
   END IF;
END

I think in your .NET code, you create an instance of a parameter and set it to the OUT parameter from the SP.

Hope that helps.

thanx RR, let me try

ok i have to change SP little to do this

Code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_Register`(
   In FirstName varchar(50),
   in LastName varchar(50),
   in EmailId varchar(100),
   in Gender varchar(20),
   in Mobile varchar(25),
   in Pswd varchar(20),
   out Result varchar(1))
BEGIN
   IF NOT EXISTS(SELECT Email_Id FROM tblUserMaster WHERE Email_Id = EmailId) THEN
       Insert into tblUserMaster (First_Name, Last_Name, Email_Id, User_Gender, User_Mobile,
       Registration_Date, LastLogin_Date, User_Pswd)
       Values (FirstName, LastName, EmailId, Gender, Mobile, now(), now(), Pswd);
       Set Result="Y";
   ELSE
       Set Result="N";
   END IF;
END

but now i cant figure how to get it in my .net code
need help

I think it should be something like:-

Code:

Dim ResultParam as New MySqlParameter
ResultParam.Direction = Data.ParameterDirection.Output
ResultParam.ParameterName = "Result"
.
.
' execute command
.
.
Dim result as String = ResultParam.Value