Check for duplicates before inserting into Access

  • Thread starter Thread starter richardkreidl
  • Start date Start date
R

richardkreidl

I'm trying to check for duplicates before I do an INSERT into the
Access database table. I basically want to alert the user if the
'ProjectName' and the 'MileStones' are already in the table..


thanks
Code Below:
Private Sub btnADD_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnADD.Click
strSQL = "INSERT INTO Project" & _
"(ProjectName, Planned, Actual, MileStones, Status,
[Next], Comments)VALUES " & _
"('" & txtProject.Text & "'," & _
"'" & txtPlanned.Text & "','" & txtActual.Text & "'," & _
"'" & txtMile.Text & "','" & txtStatus.Text & "'," & _
"'" & txtNext.Text & "','" & txtComment.Text & "')"
connection.Open()
Dim cmd As New OleDbCommand(strSQL, connection)
cmd.ExecuteNonQuery()
connection.Close()
End Sub
 
Richard,

One option would be to select the count of the number of rows where
projectname and milestones equal the values you are about to insert. Use
executescalar to return the count. If the count is greater than zero then you
know a row with those values already exists in the table.

I would also recommend that you use parameters to supply values to your sql
statements instead of concatenating textbox values into the sql statement.
The techinque you are using can lead to so-called sql injection attacks.

Kerry Moorman
 
Kerry,
I would also recommend that you use parameters to supply values to your
sql
statements instead of concatenating textbox values into the sql statement.
The techinque you are using can lead to so-called sql injection attacks.
Click to expand...
I am always curious how that can happen at an Access (Jet) Database, which
can not direct be connected to internet.

As this can be a problem in an Intern Lan or an DataBase direct on the own
drive, then there are for sure more important lecks to close.

Cor
 
Richard,

What is the part that has not to be insterted as duplicate in the database?

Cor
 
Richard,

What is the part that has not to be insterted as duplicate in the database?

Cor
Click to expand...

Actually, I wouldn't want the whole record inserted into the database
if the "ProjectName" and "Milestones" already exist in the Access
database.

Basically, the user enters data from a VB .Net form into the
database... they enter a "ProjectName", "Planned", "Milestones",
"Status" and "Next" via textboxes. When they click the button "add" I
want to make sure that there isn't a "ProjectName" and "Milestones"
from another record that are the same as the oone they're trying to
enter...

thanks
 
Actually, I wouldn't want the whole record inserted into the database
if the "ProjectName" and "Milestones" already exist in the Access
database.

Basically, the user enters data from a VB .Net form into the
database... they enter a "ProjectName", "Planned", "Milestones",
"Status" and "Next" via textboxes. When they click the button "add" I
want to make sure that there isn't a "ProjectName" and "Milestones"
from another record that are the same as the oone they're trying to
enter...

thanks
Click to expand...

Actually, I wouldn't want the whole record inserted into the database
if the "ProjectName" and "Milestones" already exist in the Access
database.

Basically, the user enters data from a VB .Net form into the
database... they enter a "ProjectName", "Planned", "Milestones",
"Status" and "Next" via textboxes. When they click the button "add" I
want to make sure that there isn't a "ProjectName" and "Milestones"
from another record that are the same as the oone they're trying to
enter...


thanks
 
Kerry,

A access database can be used by ASP. or ASP.Net, however not direct by its
IP address like database servers as SQL Server can. (or its dns name on the
Lan)

Your action to an Access (Jet) database reach never the Internet you have
always something between it, what by instance can be a webservice.

By the way, in my idea the first link is SQL script injection mixing up with
hacking a database by its table, in my idea not what is SQL script
injection. (Where I here not write what that is, I am not giving help in
hacking).

Cor
 
I'm trying to check for duplicates before I do an INSERT into the
Access database table. I basically want to alert the user if the
'ProjectName' and the 'MileStones' are already in the table..
Click to expand...

I wouldn't.

Preventing duplicates should be the database's job, not the application's.

Set up your database tables with primary keys/unique indexes as required
to ensure the required uniqueness, then just do the insert - if it
/fails/ then the user will know that they've got duplicated data.

It may seem a bit drastic but, as soon as you move into a
/multiple/-user environment, you'll start to feel the benefits.

Also, when building SQL dynamically like this, make sure you allow for
dodgy, user-entered data; things like single quotes:

strSQL = "INSERT INTO Project" & _
.. . .
VALUES " & _
"('" & MakeSafe( txtProject.Text ) & "'," & _
"'" & MakeSafe( txtPlanned.Text ) & "',"
.. . .

Private Function MakeSafe( _
ByVal sText as String _
) As String
Dim sResult as String _
= sText.Replace( "'", "''" )

Return sResult
End Function

HTH,
Phill W.
 
Phil,

In my idea is as you give the example only crashing when there is a
duplicate.

Maybe better to give a complete advice including the client side code
including at least the ID and 2 datafields, then something that does not
work without a bunch of error catching code and extra updates around the
Update part.

Cor
 
Back
Top