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

[=?Utf-8?B?S2VycnkgTW9vcm1hbg==?=]

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
"ri***********@ 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]]

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]

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]

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==?=]

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/...07/28/170.aspx

http://www.webapptest.org/ms-access-...-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]]

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

<ri***********@ northwesternmut ual.comschreef in bericht
news:b3******** *************** ***********@s19 g2000prg.google groups.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