Prevent duplicate entry to MS Access database

M

mmazid

I would like to know how to prevent entering the same data as a primary
key to an MS Access database and return with an error message
confirming the data already exists in ASP.Net (VB.Net).

Function AddFault(ByVal faultID As Integer, ByVal username As
String, ByVal title As String, ByVal desc As String) As Integer
Dim connectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data
Source=C:\Documents an"& _
"d Settings\mazid\My Documents\Helpdesk\Helpdesk.mdb"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "INSERT INTO [Fault] ([FaultID],
[Username], [Title], [Desc]) VALUES (@FaultID, @U"& _
"sername, @Title, @Desc)"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_faultID As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
dbParam_faultID.ParameterName = "@FaultID"
dbParam_faultID.Value = faultID
dbParam_faultID.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_faultID)
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_title As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
dbParam_title.ParameterName = "@Title"
dbParam_title.Value = title
dbParam_title.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_title)
Dim dbParam_desc As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
dbParam_desc.ParameterName = "@Desc"
dbParam_desc.Value = desc
dbParam_desc.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_desc)

Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try

Return rowsAffected
End Function

Sub Page_Load(sender As Object, e As EventArgs)
If (Request.IsAuthenticated = true) Then
Username.Text = "User " & User.Identity.Name & " is currently
logged in."
End If
End Sub

Sub Submit_Click(sender As Object, e As EventArgs)
AddFault(faultID, Username.Text, txtTitle.Text, txtDesc.Text)
End Sub

in the above addfault I want to make sure that the faultID is not the
same for every fault. e.g. if user1 submits a faultID - 10001, the
next user should insert faultID 10002, the next user 10003 etc.

I would appreciate if anyone can assist in helping me complete some
code for scanning the database to scan for the same faultID.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,755
Messages
2,569,536
Members
45,007
Latest member
obedient dusk

Latest Threads

Top