Before Insert check if exist - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Before Insert check if exist

Author

Topic

dand_dd
Starting Member

6 Posts

Posted - 2011-03-17 : 08:44:52

Hi,

The sql stored procedure for insert is:

if not exists (Select id, name from table_name where id=@id)
	INSERT INTO table_name id, name)
	Values (@id,@name)

I have a form into VB with a textbox and a insert Button
At the insert button i have the function for insert :

Dim con As New SqlConnection("Server=; ..)
con.Open()
Dim cmd As SqlCommand = New SqlCommand()
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "Stored_Procedure_Name"
cmd.Connection = mConnection
cmd.Parameters.Add(New SqlParameter("@id", Data.SqlDbType.Int)
cmd.Parameters.Add(New SqlParameter("@name", Data.SqlDbType.nchar)
cmd.Parameters("@id").Value = txtID.Text
cmd.Parameters("@name").Value = txtName.text
cmd.ExecuteNonQuery()
con.Close()
return True

Insert function work's , and when I try to insert a value that exist into database nothing happens because of the code from stored procedure.

But i want to make a check into add function:
- first: select the "id" values from table
- second: compare values if equal with value from the textbox and when are equal then msgbox error.

Thank's

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-03-17 : 10:47:52

Using the same basic code that you have, you could call another stored procedure that accepts the ID, does a comparison via a simple COUNT and returns how many matching records it found. If the return value is greater than 0, then you have duplicates.

Hey, it compiles.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources