By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,850 Members | 1,005 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,850 IT Pros & Developers. It's quick & easy.

Check for duplicates before inserting into Access

P: n/a
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
Dec 9 '07 #1
Share this Question
Share on Google+
11 Replies


P: n/a
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***********@northwesternmutual.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(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
Dec 9 '07 #2

P: n/a
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

Dec 9 '07 #3

P: n/a
Richard,

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

Cor
Dec 9 '07 #4

P: n/a
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 "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
Dec 9 '07 #5

P: n/a
On Dec 9, 2:04 pm, "richardkre...@northwesternmutual.com"
<richardkre...@northwesternmutual.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 "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
Dec 9 '07 #6

P: n/a
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
Dec 9 '07 #7

P: n/a
Be aware that the second link in this reply has a virus is spam

Cor
Dec 10 '07 #8

P: n/a
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

Dec 10 '07 #9

P: n/a
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***********@northwesternmutual.comschreef in bericht
news:b3**********************************@s19g2000 prg.googlegroups.com...
On Dec 9, 2:04 pm, "richardkre...@northwesternmutual.com"
<richardkre...@northwesternmutual.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 "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

Dec 10 '07 #10

P: n/a
ri***********@northwesternmutual.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.
Dec 11 '07 #11

P: n/a
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

Dec 12 '07 #12

This discussion thread is closed

Replies have been disabled for this discussion.