Simple SQL Server Insert Question

  • 10 years ago

    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 as the table is set to not allow duplicated.

    My Insert code is as follows:

    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

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

    Kind Regards

  • 10 years ago
    Hi
    i didnt get u, wat u want, want to get the primarykey voilation error.
    then check for the exceprion message.
    Plz brief ur Question, if u dont mistake me

    -Amjath






  • 10 years ago
    Dim recPre
    recPre = false
    IF request.form("someName") <> "" THEN
    'check for an existing duplicate
    sql = "SELECT .... Where [existing val = new val];"
    set checkInfo = conn.execute(sql)
    if checkInfo.EoF then
    'not found so insert
    sql = "insert into tablename ......;"
    conn.execute(sql)
    else
    'duplicate found so dont insert
    recPre = true
    end if
    end if

    Now in your page detect:

    if recPre = true then
    Some message saying did not insert, record already present
    end if

    if u want then use javascript to raise alert script
  • 10 years ago

    I managed to figure out a way to check for a primary key violation. My code modifications follow:

    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", DateTime.Now)
                    .AddWithValue("@Password", UserAccess.Password)
                End With
                Try
                    conn.Open()
                    cmd.ExecuteNonQuery()
                Catch ex As SqlException
                    Return ex.ErrorCode
                Finally
                    conn.Close()
                End Try
                Return 0 ' success
            End Function

    As you can see on a error I now return the SqlException ErrorCode, within the calling function I can now check for a primary key violation using the following code:

            Select Case (Result)
                Case 0
                    ' Insert was successful
                    Exit Sub
                Case -2146232060
                    ' We have a primary key violation, do something to handle it
                    Exit Sub
            End Select

    Using this method I find no matter what the error, as long as I know the error number and the meaning of the error I can handle the code in my applications
    I hope this helps someone in the same situation as me out

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“There's no test like production” - Anon