Hello everyone! Can somebody please help me with this... I have a VB program that inputs Name and Address. I've made a database in MS Access using ADO Control:
Table Name: Contacts
Fields are: ID, CompanyName, CompanyAddress
My only problem is that how can I find duplicate entries in the CompanyName field. I have an ADD command button when adding new Company Name from a textbox. What I would like to do is that before the program add the Company Name in the textbox to the database, I wanted to check first if there is any duplicate. Ive made a code but it wont work...: - Private Sub cmdAdd_Click()
-
-
adoWC.Recordset.MoveFirst
-
-
while not adoWC.Recordset.EOF
-
-
if txtCompanyName.text = adoWC.Recordset("CompanyName") then
-
Msgbox("Duplicate Entry")
-
Exit Sub
-
else
-
adoWC.Refresh
-
adoWC.Recordset.MoveFirst
-
adoWC.Recordset.AddNew
-
adoWC.Recordset("CompanyName") = txtCompanyName.Text
-
adoWC.Recordset("CompanyAddress") = txtCompanyAddress.Text
-
adoWC.Recordset.Update
-
adoWC.Recordset.MoveFirst
-
Exit Sub
-
end if
-
-
adoWC.Recordset.MoveNext
-
-
End Sub
Can somebody help me... Thanks...
5 13899
if you just want that no body enter duplicate name of company
just make company name field in access as a primary key.
Hello everyone! Can somebody please help me with this... I have a VB program that inputs Name and Address ...
Why do you need to find duplicates? I agree with the previous poster, declaring a primary key in Access is the easiest way to prevent this. However, if there's a fair amount of data, this could corrupt some fields.
The other way to do it is nested loops. The pseudo code is below: (letters correspond to indents (loops))
1. FOR EACH ITEM
2a. store next item in a string variable (call it strCurrent).
3a. FOR EACH ITEM
4b. compare current item with strCurrent (above)
5b. if duplicate, set a boolean to true (break loops)
6c. (if needed) add to string of duplicates
7b. else, get next
8b/a. END, END
Depending on the amount of data, this may take a while to run but it's quicker then 1 guy sitting reading through everything.
The 2nd part to this is adding a new record.
1. FOR EACH ITEM
2a. compare with input, if same - break loop, error
3a. else - add to DB
4. END
Hope this helps.
- LB
No need to go for all the tricky logics . I will give you a simple solution.
When the user clicks add read value from textbox and compare it with database records using count() . If count is 0 insert the record if 1 then you exit and display a message saying the entry already exists.
hi muddasirmunir, LacrosseB0ss, debasisdas thanks to all your reply and help.
i have a sample code here debasisdas is this what you mean? - Dim noDUP as integer
-
-
nuDUP = "SELECT COUNT(CompanyName) FROM Contacts WHERE CompanyName = txtCompanyName.text"
-
-
if nuDUP = 0 then
-
adoWC.Refresh
-
adoWC.Recordset.MoveFirst
-
adoWC.Recordset.AddNew
-
adoWC.Recordset("CompanyName") = txtCompanyName.Text
-
adoWC.Recordset("CompanyAddress") = txtCompanyAddress.Text
-
adoWC.Recordset.Update
-
adoWC.Recordset.MoveFirst
-
else
-
Msgbox("Already Exists.")
-
-
End if
Thanks Again...
everything here looks good except for keeping txtCompanyName. text in the quotations in your SQL statement will look for that string, not the value in the text box. You'll need to say " ... WHERE CompanyName = " & txtCompanyName. text & ";".
Other than that, looks good. By the way, nice catch debasisdas. Much easier solution. (Y)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Barbara |
last post by:
Hi,
I have an sql database that has the primary key set to three fields,
but has not been set as unique(I didn't create the table).
I have 1 record that has 2 duplicates and I am unable to delete the
duplicate entries.
If I try to delete any of the three records(they are identical) I get
the message 'key column is insufficient or incorrect. Too many rows
were affected by update'.
I am trying to do this within Enterprise Mgr.
Any...
|
by: Brian |
last post by:
I'm trying to find a way to search multiple tables for the same record.
Say I have 3 tables all with a name column, I need to search all 3
tables and find matching names. Is there an easy way to accomplish this?
|
by: steevp |
last post by:
Hi,
Please excuse the newbie type question, but I am wracking my brains to
solve what should be a simple query.
I have three fields in a table F1, F2, F3. Any of the fields may
contain duplicate data, but I am only interested in where only the
first two fields match. I want to query and get only the records (all
three fields) where there are duplicates in the first two fields, e.g.
|
by: Jameel |
last post by:
how do i find duplicate records in ASP.net/ADO.net , i know how to do it in
classic asp, anything appreciated code,tutorial links etc..
(if rs.eof then
add record
else
error message
end if
)
|
by: Booster |
last post by:
Hi
I am a beginner
working with VB.net
Ho to:
find data in another column using the index number "ParamID", also the
possibility to updata the data in "ParamVal".
This is a Parameter table "tblParameter" in an access BE-database
| |
by: Carroll |
last post by:
I'm looking for a way in SQL to find duplicate records in a single
table, that are the same based on 3 columns, regardless of what is in
the other columns in the duplicate records. I would like to keep both
records (or it could be more than 2 as well) where duplicate records
are found. Also, I am interested in selecting all columns from the
duplicate records.
Thanks,
Carroll Rinehart
|
by: nethravathy |
last post by:
Hi,
The following table namely elcbtripselect contains 5147 records.I want to know wether this table contains duplicate records or not.
I tried with following query
1)SELECT elcbtripselect.ELCBTRIP_voltsMIN, elcbtripselect.ELCBTRIP_voltsMAX, elcbtripselect.ELCBTrip_is_partwinding, elcbtripselect.ELCBTrip_is_ydelta, elcbtripselect.ELCBTRIP_starter_size, elcbtripselect.ELCBTRIP_UnitFunction, elcbtripselect.ELCBTRIP_strcb_speedi_frame_ty,...
|
by: mabubakarpk |
last post by:
Hello every one,
I Have table e.g
EmpID TDate Status
***************************************
1 01-01-08 AB
1 02-01-08 AB
1 03-01-08 AB
1 04-01-08 PR
|
by: anantha raman |
last post by:
Hai,
I am a beginer in Developing ASP.NET webpage.
I want to restrict duplicates while uploading the excel data and to inform the user that the perticular cell in excel contains duplicate value which is already exist in Sql Database.(Duplicates should be check in Excel (EMP_ID) against SQL (EMPLOYEE_ID)
SQL DATABASE
Table : Employees
FiledName:EMPLOYEE_ID
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |