Check for duplicates before inserting into Access

[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

 

[Kerry Moorman]

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

 

[Cor Ligthert[MVP]]

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.

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

 

[Cor Ligthert[MVP]]

Richard,

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

Cor

 

[richardkreidl]

Richard,

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

Cor

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

 

[richardkreidl]

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

 

[Kerry Moorman]

Cor,

I have never understood what you mean when you say that Access databases are
not vulnerable to sql injection attacks because they cannot be directly
connected to the internet. Are you saying that Access can't be used with ASP
or ASP.Net?

At any rate, here are a couple of urls for you to look at:

http://portal.spidynamics.com/blogs/spilabs/archive/2006/07/28/170.aspx

http://www.webapptest.org/ms-access-sql-injection-cheat-sheet-EN.html

Kerry Moorman

 

[Cor Ligthert[MVP]]

Be aware that the second link in this reply has a virus is spam

Cor

 

[Cor Ligthert[MVP]]

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

 

[Cor Ligthert[MVP]]

Richard,

To do what you need you need first your textboxes have working.

Have a look at this tip

http://www.vb-tips.com/BindingSource.aspx


Cor

 

[Phill W.]

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..

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.

 

[ddvelzen]

Wiki about SQL Injection
http://en.wikipedia.org/wiki/SQL_injection

How To: Protect From SQL Injection in ASP.NET
http://msdn2.microsoft.com/en-us/library/ms998271.aspx

some sources I think you can trust.. and worthy reading

 

[Cor Ligthert[MVP]]

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