Check for duplicates before inserting into Access

**=?Utf-8?B?S2VycnkgTW9vcm1hbg==?=** · Dec 9 '07, 03:45 PM

RE: Check for duplicates before inserting into Access

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

"richardkreidl@ northwesternmut ual.com" wrote:

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(By Val sender As System.Object, ByVal e As
System.EventArg s) Handles btnADD.Click
strSQL = "INSERT INTO Project" & _
"(ProjectNa me, 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(st rSQL, connection)
cmd.ExecuteNonQ uery()
connection.Clos e()
End Sub
>

**Cor Ligthert[MVP]** · Dec 9 '07, 07:35 PM

Re: Check for duplicates before inserting into Access

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]** · Dec 9 '07, 07:35 PM

Re: Check for duplicates before inserting into Access

Richard,

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

Cor

**richardkreidl@northwesternmutual.com** · Dec 9 '07, 08:05 PM

Re: Check for duplicates before inserting into Access

On Dec 9, 1:25 pm, "Cor Ligthert[MVP]" <notmyfirstn... @planet.nl>
wrote:

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 "ProjectNam e" and "Milestones " already exist in the Access
database.

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

thanks

**richardkreidl@northwesternmutual.com** · Dec 9 '07, 08:15 PM

Re: Check for duplicates before inserting into Access

On Dec 9, 2:04 pm, "richardkre...@ northwesternmut ual.com"
<richardkre...@ northwesternmut ual.comwrote:

On Dec 9, 1:25 pm, "Cor Ligthert[MVP]" <notmyfirstn... @planet.nl>
wrote:
>

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 "ProjectNam e" and "Milestones " already exist in the Access
database.
>
Basically, the user enters data from a VB .Net form into the
database... they enter a "ProjectNam e", "Planned", "Milestones ",
"Status" and "Next" via textboxes. When they click the button "add" I
want to make sure that there isn't a "ProjectNam e" 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 "ProjectNam e" and "Milestones " already exist in the Access
database.

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

thanks

**=?Utf-8?B?S2VycnkgTW9vcm1hbg==?=** · Dec 9 '07, 10:55 PM

Re: Check for duplicates before inserting into Access

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

Page not found - Web App Test

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

Kerry Moorman

"Cor Ligthert[MVP]" wrote:

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]** · Dec 10 '07, 05:55 AM

Re: Check for duplicates before inserting into Access

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

Cor

**Cor Ligthert[MVP]** · Dec 10 '07, 05:55 AM

Re: Check for duplicates before inserting into Access

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]** · Dec 10 '07, 06:15 AM

Re: Check for duplicates before inserting into Access

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

<richardkreidl@ northwesternmut ual.comschreef in bericht
news:b3d8ff3d-0409-4818-bbe4-c010736843f0@s1 9g2000prg.googl egroups.com...

On Dec 9, 2:04 pm, "richardkre...@ northwesternmut ual.com"
<richardkre...@ northwesternmut ual.comwrote:

>On Dec 9, 1:25 pm, "Cor Ligthert[MVP]" <notmyfirstn... @planet.nl>
>wrote:
>>

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 "ProjectNam e" and "Milestones " already exist in the Access
>database.
>>
>Basically, the user enters data from a VB .Net form into the
>database... they enter a "ProjectNam e", "Planned", "Milestones ",
>"Status" and "Next" via textboxes. When they click the button "add" I
>want to make sure that there isn't a "ProjectNam e" 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 "ProjectNam e" and "Milestones " already exist in the Access
database.
>
Basically, the user enters data from a VB .Net form into the
database... they enter a "ProjectNam e", "Planned", "Milestones ",
"Status" and "Next" via textboxes. When they click the button "add" I
want to make sure that there isn't a "ProjectNam e" and "Milestones "
from another record that are the same as the oone they're trying to
enter...
>
>
thanks
>
>

**Phill W.** · Dec 11 '07, 01:35 PM

Re: Check for duplicates before inserting into Access

richardkreidl@n orthwesternmutu al.com wrote:

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.

**Cor Ligthert[MVP]** · Dec 12 '07, 06:05 AM

Re: Check for duplicates before inserting into Access

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