Simple SQL Server Insert Question

[Materialised]

Hi Folks,
Just a quickie, I am inserting some records (individually) into a SQL
Server Table. I am wondering how can I detect primary key violations on
this data which I am inserting into the table.


My Insert code is as follows (VB.Net):

Public Function InsertNewUser(ByVal _ConnString As String, ByVal
UserAccess As UserAccess.Access) As Integer
Dim conn As New SqlConnection
Dim cmd As New SqlCommand
conn.ConnectionString = _ConnString
cmd.Connection = conn
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "dbo.Access_Insert"
With cmd.Parameters
.AddWithValue("@Username", UserAccess.Username)
.AddWithValue("@RealName", UserAccess.Realname)
.AddWithValue("@accesslevel", 1)
.AddWithValue("@Email", UserAccess.Email)
.AddWithValue("@Webpage", UserAccess.Webpage)
.AddWithValue("@Sig", UserAccess.Sig)
.AddWithValue("Occupation", UserAccess.Occupation)
.AddWithValue("@Personal", UserAccess.Personal)
.AddWithValue("JoinDate", UserAccess.JoinDate)
.AddWithValue("@Password", UserAccess.Password)
End With
Try
conn.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
Return -1
End Try
Return 0 ' success
End Function
As you can see this function returns 0 on success and -1 on failure. I
would like to be able to return some other integer value upon a primary
key conflict.

If anyone can help me out with this i'd be really gratful.
Kind Regards

 

[bruce barker]

the exception has an Errors collection. you spin thru it looking for a
2627 message. you could also trap in the proc and pass back a flag.

also you have a connection leak in the sample code. you should have
finally clause that closes the conection.

-- bruce (sqlwork.com)

 

[Guest]

Yes you can, two ways:

1. Quick approach:
http://forums.asp.net/thread/1545938.aspx
2. Check if the primary key is violeted in stored proc i.e.
check if record with given index value (for instance username or email)
exists, add OUTPUT parameter to your stored procedure i.e. @exists int OUTPUT,
set it to nonzero value or constant code in case record with somecriteria
exists,
obtain its value after ExecuteNonQuery() and you're done

 

[Materialised]

Yes you can, two ways:

1. Quick approach:
http://forums.asp.net/thread/1545938.aspx
2. Check if the primary key is violeted in stored proc i.e.
check if record with given index value (for instance username or email)
exists, add OUTPUT parameter to your stored procedure i.e. @exists int OUTPUT,
set it to nonzero value or constant code in case record with somecriteria
exists,
obtain its value after ExecuteNonQuery() and you're done

Thanks for your reply Milosz,

I think the 2nd option is a lot more robust for the type of insert I am
working on. However I a a total newbie when it comes to doing this.
My stored procedure is as follows:

CREATE PROCEDURE [dbo].[Access_Insert]
@ID int,
@Username varchar(30),
@RealName varchar(100),
@AccessLevel int,
@Email varchar(50),
@Webpage varchar(50),
@Sig varchar(1000),
@Occupation varchar(200),
@Personal varchar(1000),
@JoinDate datetime,
@Password varchar(30)
AS
Insert into dbo.Access
(
Username,
RealName,
AccessLevel,
Email,
Webpage,
Sig,
Occupation,
Personal,
JoinDate,
Password
)
Values
(
@Username,
@RealName,
@AccessLevel,
@Email,
@Webpage,
@Sig,
@Occupation,
@Personal,
@JoinDate,
@Password
)

How would I implement something like what you suggested within this
procedure?
I understand that this is not a SQL Server programming group, but even
if you have a few small pointers I'm sure they will come in handy.

Kind Regards

 

[Cor Ligthert [MVP]]

Materialased.

Have a look in the ex code in your program what part you want to return.

return ex. (and have a look at the intelisense after you typed the dot)

Cor

 

[Materialised]

Materialased.

Have a look in the ex code in your program what part you want to return.

return ex. (and have a look at the intelisense after you typed the dot)

Cor

Thanks for that Cor,

Any ideas where I can find a list of error codes and their meanings? I
tried MSDN but cannot find such a list.

Regards

 

[Guest]

Hi there,

CREATE PROCEDURE [dbo].[Access_Insert]
@ID int OUTPUT,
@Username varchar(30),
@RealName varchar(100),
@AccessLevel int,
@Email varchar(50),
@Webpage varchar(50),
@Sig varchar(1000),
@Occupation varchar(200),
@Personal varchar(1000),
@JoinDate datetime,
@Password varchar(30),
@Exists BIT OUTPUT
AS

-- two ways of checking, i assume the only unique index is on [username] and
primary key [id]
-- first way
IF Exists(select [ID] from dbo.Access where [Username] = @Username)
BEGIN
@Exists = 1
RETURN;
END
ELSE @Exists = 0

-- another way
set @Exists = CAST( ISNULL((select 1 from dbo.Access where [Username] =
@Username), 0 ) as bit )
if @Exists <> 0 return;

Insert into dbo.Access
(


Username,
RealName,
AccessLevel,
Email,
Webpage,
Sig,
Occupation,
Personal,
JoinDate,
Password
)
Values
(
@Username,
@RealName,
@AccessLevel,
@Email,
@Webpage,
@Sig,
@Occupation,
@Personal,
@JoinDate,
@Password
)

-- one more thing, i changed @ID parameter to be OUTPUTed as
-- you didn't return any information about inserted id

SET @ID = Scope_Identity()
--
Milosz

Yes you can, two ways:

1. Quick approach:
http://forums.asp.net/thread/1545938.aspx
2. Check if the primary key is violeted in stored proc i.e.
check if record with given index value (for instance username or email)
exists, add OUTPUT parameter to your stored procedure i.e. @exists int OUTPUT,
set it to nonzero value or constant code in case record with somecriteria
exists,
obtain its value after ExecuteNonQuery() and you're done

Thanks for your reply Milosz,

I think the 2nd option is a lot more robust for the type of insert I am
working on. However I a a total newbie when it comes to doing this.
My stored procedure is as follows:

CREATE PROCEDURE [dbo].[Access_Insert]
@ID int,
@Username varchar(30),
@RealName varchar(100),
@AccessLevel int,
@Email varchar(50),
@Webpage varchar(50),
@Sig varchar(1000),
@Occupation varchar(200),
@Personal varchar(1000),
@JoinDate datetime,
@Password varchar(30)
AS
Insert into dbo.Access
(
Username,
RealName,
AccessLevel,
Email,
Webpage,
Sig,
Occupation,
Personal,
JoinDate,
Password
)
Values
(
@Username,
@RealName,
@AccessLevel,
@Email,
@Webpage,
@Sig,
@Occupation,
@Personal,
@JoinDate,
@Password
)

How would I implement something like what you suggested within this
procedure?
I understand that this is not a SQL Server programming group, but even
if you have a few small pointers I'm sure they will come in handy.

Kind Regards

 

[Guest]

It was in the site link in my first post:
try
{
....
}
catch (SqlException ex)
{
switch (ex.Number)
{
case 4060: // Invalid Database
....
break;

case 18456: // Login Failed
....
break;

case 547: // ForeignKey Violation
....
break;

case 2627: // Unique Index/ Primary key Violation/ Constriant Violation
....
break;

case 2601: // Unique Index/Constriant Violation
....
break;

default:
....
break;
}
}