The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.

Hi all,

I got this message after intentionally trying to enter a duplicate record in to my databse, as all the new records should be unique, I'd appreciate some help coding a unique record check within the code below and a message being displayed like "user account already exists, please try again" there is alread a message label available in the code below.

Ideally the check needs to happen whilst the original page is displayed, so the user does not have to go back to the previous page to enter all the details again.
Function AddUser(ByVal userName As String, ByVal userPass As String, ByVal busName As String, ByVal busType As String) As Integer
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=has a source"
            Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)
    
            Dim queryString As String = "INSERT INTO [Users] ([UserName], [UserPass], [BusName], [BusType]) VALUES (@UserName, @UserPass, @BusName, @BusType)"
            Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
            dbCommand.CommandText = queryString
            dbCommand.Connection = dbConnection
    
            Dim dbParam_userName As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
            dbParam_userName.ParameterName = "@UserName"
            dbParam_userName.Value = userName
            dbParam_userName.DbType = System.Data.DbType.String
            dbCommand.Parameters.Add(dbParam_userName)
            Dim dbParam_userPass As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
            dbParam_userPass.ParameterName = "@UserPass"
            dbParam_userPass.Value = userPass
            dbParam_userPass.DbType = System.Data.DbType.String
            dbCommand.Parameters.Add(dbParam_userPass)
            Dim dbParam_busName As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
            dbParam_busName.ParameterName = "@BusName"
            dbParam_busName.Value = busName
            dbParam_busName.DbType = System.Data.DbType.String
            dbCommand.Parameters.Add(dbParam_busName)
            Dim dbParam_busType As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
            dbParam_busType.ParameterName = "@BusType"
            dbParam_busType.Value = busType
            dbParam_busType.DbType = System.Data.DbType.String
            dbCommand.Parameters.Add(dbParam_busType)
    
            Dim rowsAffected As Integer = 0
        dbConnection.Open()
            Try
            rowsAffected = dbCommand.ExecuteNonQuery
            Finally
                dbConnection.Close
            End Try
    
            Return rowsAffected
        End Function
    
    
    Sub Button1_Click(sender As Object, e As EventArgs)
    If txtBusType.SelectedIndex = 0
            bustypeinvalid.Text = "Please Select a Business Type"
    Else If AddUser(txtUserName.Text, txtUserPassword.Text, txtBusName.Text, txtBusType.SelectedIndex) > 0
            Response.Redirect("signed.aspx")
       Else
         message.Text = "YOU ACCOUNT HAS NOT BEEN CREATED PLEASE TRY AGAIN, IF THE PROBLEM PERSISTS TRY LATER OR CALL SUPPORT"
    End If
    End Sub
    
    Sub Button2_Click(sender As Object, e As EventArgs)
    txtusername.Text = ""
    txtuserpassword.Text = ""
    txtconfirmpassword.Text = ""
    txtbusname.Text = ""
    txtbustype.SelectedIndex = 0
    End Sub

Open in new window

LVL 3
ZoooinkAsked:
Who is Participating?
 
Obadiah ChristopherCommented:
Try using

try catch in ur function and main page both.

In the main page in the catch section, Just display the error message to the user
0
 
ZoooinkAuthor Commented:
Didin't realise it was so easy, thanks for the help :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.