Check Whether Row Exists in multiple tables before Insert in SQL Server

nedash
 
on Jan 20, 2013 11:29 PM
2606 Views

hi

this is S_register tabel

mobile

address

Name

Id

12345

paris

sara

1

45678

india

jack

2

 

and Black_list   table

mobile

Trade_name

Name

Id

12345

paris

sara

1

45678

india

micheal

2

 

 

 

and in register.aspx page I have some TB that users can enter their data on it :

below is SP and Behind COde

 

SP

 

 

 

 

 

ALTER procedure [dbo].[insertSreg]
@Name nvarchar(40)
,@Address nvarchar(Max)
@Mobile nvarchar(20)


as

begin
 DECLARE @Result AS int
IF NOT EXISTS(SELECT id FROM S_register WHERE Mobile = @Mobile) 
begin

insert into S_register (,Name,Address,Mobile)
values(,@Name,@Address
,@Mobile)
SET @Result = 1
    END
    ELSE
    BEGIN
        SET @Result = 0
    END

    SELECT @Result
END

behind code

 

 

 protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
       
            SqlCommand _cmd = new SqlCommand("insertSreg", _cn);
            _cmd.CommandType = CommandType.StoredProcedure;
            _cn.Open();

            _cmd.Parameters.AddWithValue("@Name", txtname.Text);
   
            _cmd.Parameters.AddWithValue("@Address", txtDname.Text);
    
            _cmd.Parameters.AddWithValue("@Mobile", Txtmob.Text);


            int result = Convert.ToInt32(_cmd.ExecuteScalar());
            _cn.Close();



            if (result>0)
            {
                Response.Redirect("success.aspx");
            }
            else
            {
                LBLERROR.Text = "please enter new mobile.";
            }    

    }

here when users enter mobile number in TB if that mobile number was in S_register table it show error that "enter new mobile number"

Now I want when users enter mobile number it checked in 2 table I mean it checked in S_register and Black_list table if  in those table wasn't mobile number it insert data in S_register table

how I can do it?

Best regards

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
Mustafa
 
on Jan 21, 2013 02:57 AM

Ok Nedash change you procedure like this

ALTER PROCEDURE [dbo].[insertSreg]
@Name NVARCHAR(40),
@Address NVARCHAR(Max),
@Mobile NVARCHAR(20)
AS
BEGIN
DECLARE @Result AS int

IF NOT EXISTS(SELECT mobile FROM S_register WHERE Mobile = @Mobile) 
BEGIN
	IF NOT EXISTS(SELECT mobile FROM Black_list WHERE Mobile = @Mobile)
	BEGIN
		INSERT INTO S_register (Name,Address,Mobile)
		VALUES(@Name,@Address,@Mobile)
		SET @Result = 1
	END
END
ELSE
BEGIN
	SET @Result = 0
END
 
SELECT @Result
END

 

Mustafa
 
on Jan 22, 2013 08:11 AM

Ok try this

ALTER PROCEDURE [dbo].[insertSreg]
@Name NVARCHAR(40),
@Address NVARCHAR(Max),
@Mobile NVARCHAR(20)
AS
BEGIN
DECLARE @Result AS int
 
IF ((NOT EXISTS(SELECT mobile FROM S_register WHERE Mobile = @Mobile))  AND (NOT EXISTS(SELECT mobile FROM Black_list WHERE Mobile = @Mobile)))
BEGIN
    INSERT INTO S_register (Name,Address,Mobile)
    VALUES(@Name,@Address,@Mobile)
    SET @Result = 1
END
ELSE
BEGIN
    SET @Result = 0
END
  
SELECT @Result
END

and check whether it works