Simple SQL Server Insert Question

• • Reply

  14 years ago

  by materialised

  Mick Walker

  Northern Ireland, United Kingdom

  Joined 14 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

  Report abuse
• • Reply

  14 years ago

  by Amjath

  Amjathkhan

  , India

  Joined 15 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
  
  
  
  
  
  
  
  Report abuse
• • Reply

  14 years ago

  by windows_mss

  windows

  , India

  Joined 14 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
  Report abuse
• • Reply

  14 years ago

  by materialised

  Mick Walker

  Northern Ireland, United Kingdom

  Joined 14 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
  

  Report abuse