Getting a sqlexception on successful insert, VB.NET

Question

I'm trying to do a very simple INSERT using VB.NET. For some reason I'm getting a SqlException on every insert though. The data is inserted, but still get the following:

Violation of PRIMARY KEY constraint 'PK_User'. Cannot insert duplicate key in object 'dbo.Employee'. The statement has been terminated

When I check in SQL Management Studio, the data is succesfully inserted.

Here is the code where the problem is happening

Try
    conn.Open()
    Dim insertSQL As String = "insert into Employee(uName, firstName, lastName,
        On_Switch, On_Phone) " + "values('" & uName & "', '" & firstName & "', '" _
        & lastName & "', '" & onSwitch & "', '" & onPhone & "')"
        Dim AddCom As SqlCommand = New SqlCommand(insertSQL, conn)

        If (AddCom.ExecuteNonQuery() = 1) Then

            lblError.Text = "User Added."
            ' string urlBack = "../ViewAsset.aspx?DeptID=" + DeptID;
            ' Response.Redirect(urlBack);
        End If

        conn.Close()

    Catch ex As SqlException
        Dim ExMsg As String = ex.Message.ToString()
        lblError.Text = ExMsg

I went back and tested the same code in C# and there is no Exception thrown. It seems to be something small I'm doing in VB, but I'm lost as to what it is.

Answer 1

Two theories. Either your code is being executed twice, or there's a trigger on the Employee table that's attempting an insert following the successful insert. (Edit: @Mitchel Sellers is exactly right, if the same code works in c# it's absolutely not a trigger issue.)

My hunch is that your code is being executed twice. Try running with the debugger attached and a breakpoint set on the ExecuteNonQuery - I think you'll find that some other method calls this method multiple times.

@Mitchel Sellers - GOOD CATCH ON THE SQL INJECTION BUG! Parameters, please!

Answer 2

As a side note, I STRONGLY recommend changing to parameterized queries to prevent the risk of SQL injection that your current code is not protected from.

For the error issue, I would check to see that your code isn't being called twice in the VB version.

Answer 3

As another side note, I noticed that your code could potentially leave a sql connection open. If you're using the .NET 2.0 framework you should use the Using statement. It ensures that connections are closed and disposed even if an exception is thrown. Check this article on MSDN for more detail: http://msdn.microsoft.com/en-us/library/htd05whh.aspx. The other option would be to add the close statement in a Finally block of your try-catch handler.

Answer 4

If you are executing this code within an event of some sort make sure you have not subscribed to the event multiple times. I have had this problem in asp.net. Usually I just delete the click event handler in the code behind and the onclick attribute in the aspx file if it exists there as well and then try it again.