|
Hi
Imagine you have a table with 2 fields (name and age):
1. To find duplicate names, use SELECT [name], COUNT(*) AS TOTAL FROM [table] GROUP BY [name] HAVING COUNT(*)>1
2. If you need to check both fields, just add [age] in SELECT and GROUP BY.
HTH
Regards,
Wanderley
ps: COUNT(*) will give you the number of times your field is duplicated
|
|
|
|
|
Thanks for your response - it's a great help.
How would I adjust the query to print out the duplicate records in grouped by the duplicate names ie
Name Age Shoe Size
Tom 12 6
Tom 12 7
Tom 12 8
Steve 26 12
Steve 26 13
(Excessive amount of sample data, I know)
Cheers
James Spibey
Well cover me in honey and throw me to the lesbians!
|
|
|
|
|
Hi
Do you want just a list of unique names or you need to know the duplicated ones? From your example:
Name Age Shoe Size
Tom 12 6
Tom 12 7
Tom 12 8
Steve 26 12
Steve 26 13
John 12 9
Do you want Tom, Steve and John (without duplicates) or Tom and Steve?
Regards,
Wanderley
|
|
|
|
|
Hi Wanderley - thanks for the help so far,
I want to find all the records which are duplicated. Those which are duplicated, I want to be able to list each of the duplicated records giving the user the option to pick which one is the correct one. All the records for which there is only one entry should not be displayed. Does this make sense?
So I want to display the information as displayed above. The database would probably also contain many records which were not duplicated which have not been displayed.
Can this be done?
James Spibey
Well cover me in honey and throw me to the lesbians!
|
|
|
|
|
Hi
Assuming that you're checking for duplicated names, you could do something like this:
1. Create a combobox and fill it with all duplicated names using SELECT [Name] FROM [your table] GROUP BY [Name] HAVING COUNT(*)>1
2. Create a listbox (or listview) and fill it with all records that match with the name selected on combobox using SELECT * FROM [your table] WHERE [Name] = 'selected name on combo'
This way, you'll have all duplicated names on the combobox - whenever you select a name, you can clear and fill the listbox and select the correct one.
Is it too confusing? 
Please let me know if you still have problems.
Regards,
Wanderley
|
|
|
|
|
Thanks again Wanderley.
I understand what you said and it's pretty much what I thought in the first place. Thanks for your help.
James Spibey
Well cover me in honey and throw me to the lesbians!
|
|
|
|
|
Hi all...
How do i query the Sybase database to retrieve the "table structure" ?
i.e, if i want to have the results of the command
sp_help table_name
through a query, then what should i do ?
I'm new to Sybase.Could anybody help me out ?
Thanking u..
Renu
RS,
Software engrr ,
Pune - INDIA
|
|
|
|
|
Does anybody know, if it is possible to create a new dBase file using ADO? During my tests I had to learn that my provider does not support creation of this file. Is there any other provider? Sample code below:
Using the connection string to create an Access-file is no problem (see strcnn).
Thanks.
#include "stdafx.h"
#import "c:\winnt\system32\dllcache\\msadox.dll" no_namespace
#import "c:\winnt\system32\dllcache\\msado15.dll"
#include "iostream.h"
#include "stdio.h"
#include "conio.h"
void CreateDatabaseX(void);
int main(int argc, char* argv[])
{
printf("Start...\n");
HRESULT hr = S_OK;
hr = ::CoInitialize(NULL);
if(SUCCEEDED(hr))
{
CreateDatabaseX();
printf("\nPress any key to continue...");
getch();
::CoUninitialize();
}
return 0;
}
void CreateDatabaseX()
{
HRESULT hr = S_OK;
_CatalogPtr m_pCatalog = NULL;
//Set ActiveConnection of Catalog to this string
//_bstr_t strcnn("Provider=Microsoft.JET.OLEDB.4.0;"
// "Data source = c:\\Projekte_test\\create\\new.mdb");
_bstr_t strcnn("DSN=dBASE-files");
try
{
hr = m_pCatalog.CreateInstance(__uuidof (Catalog));
m_pCatalog->Create(strcnn);
}
catch(_com_error &e)
{
// Notify the user of errors if any.
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
printf("\nSource\t\t: %s \nDescription\t: %s \n ",(LPCSTR)bstrSource,(LPCSTR)bstrDescription);
}
catch(...)
{
cout << "Error occured in include files...."<< endl;
}
}
|
|
|
|
|
I Suggest that you try and find a OLE DB provider for dbase or (xbase) before attempting to do this.
You are trying to execute functionality for dbase with the Access OLE DB provider.
There are various third parties that sell OLE DB Provider for xbase.
|
|
|
|
|
Hello!
You need to tell the Jet engine that you're using 'dBase 5.0' then you also need to install Borland BDE otherwise you will only be able to use the database in read-only.
I always use an .udl file to specify the connection (no change of the program if I need to change database).
Hope this will help!
// Mike
PS! I have not tried to create a dBase database only reading and writing to existing databases.
|
|
|
|
|
Hi,
I, also, am tasked with creating new dBase files using C# in the Visual Studio .NET and ADO .NET.
If you got yours working, would you please share your steps and solution with me, by email?
Thanks, Don.
|
|
|
|
|
Hi,
I'm Using an SQL statement "Select * from User_info order by user_name" i am getting the results in ascending order the problem is the Null values are arriving at the last when i want then to be first in acsending order the same applies with descending the appear first when i want them last can any one help me out with this\
Thanks in Advances ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
You could filter the NULL valued records out and make this into a two stepped process
e.g. first select all the NULL values
Select * from user_info where user_name IS NULL
then
Select * from user_info where user_name IS NOT NULL order by user_name
|
|
|
|
|
Hi,
I am using db fetch for accessing data from the database, in bulk, but when my table has more than
255 columns, bulk fetch neglects the remaining columns abnd fetched only 255 of them. please advice.
these are experimental data and there are no other ways except to have them in single tables.
regards
ks
|
|
|
|
|
I want to handle the WillChangeField event in my RecordsetEventsVt derived class
One of the parameters is a VARIANT FIelds that is a SAFEARRAY.
I verify that it is a VT_ARRAY | VT_DISPATCH variant, and the parray if well filled.
But when I try to find an element whith the COleSafeArray::GetElement, the resulting variant is nothing.
Is somebody can tell me hown to trap the FieldPtr inside the SAFEARRAY Fields parameters?
|
|
|
|
|
This ADO article uses ADO Events
http://www.codeproject.com/database/isqlado.asp
You might want to look at this to get a few ideas.
|
|
|
|
|
(C++!)I tryed to insert some data into Excel table with use of Excel type librarys and figured out it is slow as hell. Then i tryed to find out some
other solution and started to play with ODBC. I opened ADOX::_CatalogPtr to Excel file and tryed to insert new table; it didnt work! Then i have tryed some other methods like SQL (CREATE TABLE...) on that connection and everything failed! Same happened when i tryed to create new column in same table with ->Append(...) and SQL method and allso both failed. Same was with inserting data into existing column...
Anyone knows how to insert some data there trough ODBC??? 
|
|
|
|
|
Hi all.
Does anyone ever encounter a skipping recordset in DTS via active-x script?
My situation is like this, I am running a dts process which uses a recordset to retrieve each record in a table (using recordset object), then pass some of the values into a ADO command object to execute a stored procedure with the parameters from the record that I retrieved. I keep doing this until the recordset hits EOF. If the process is successful, I would delete the record via adoRec.Delete 1 (adoRec is my record set object) then do adoRec.Update. Then I do a move next to retrieve the next record in the table. However, sometimes the record set does not go through each record in the table but it skips some records. The problem would gone after I restart the SQL server (version 7).
Anyone know what is the cause and solution for this problem?
|
|
|
|
|
(Beginner to SQL/MSSQL).
I have created some SQL scripts (e.g. for repopulating tables for test), and saved them to external file (e.g. repopulating tables). I want to get SQL server to execute the scripts. I do *not* want to use an interactive utility (e.g. Query Analyzer, though that's where I generated and saved the scripts), nor a command-line utility (e.g. osql, though it does precisely what I'm trying to do). I do not want to store them as stored procs in a db. I want to do it from code (actually C# & ASP.NET application, though that shouldn't matter).
The files are accessible to the SQL server. What I want is a SQL statement like:
EXEC <file-path>
but I can't find anything like this. Yes, I could write code to open the file and append all the text to a string I finally submit to an ExecuteNonQuery(), but this seems brain-damaged to me (is this in fact what osql does?).
*Surely* there's something for this?
TIA
|
|
|
|
|
Does anybody have experience in updating dBase files using ADO. In my simple project I allways receive an error telling me that I do not have the right to update while using pRecordSet->UpdateBatch(adAffectAll).
Thanks
|
|
|
|
|
Update MDAC and you can update dBase files.
|
|
|
|
|
Hi,
I just read in MSDN Library at the MS site that MS have deprecated the Jet engine:
(from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/mdac_deprecated_components.asp):
"Jet and Replication Objects (JRO)
The Microsoft Jet OLE DB Provider and other related components were removed from MDAC 2.6. Microsoft has deprecated the Microsoft Jet Engine, and plans no new releases or service packs for this component. As a result, the Jet and Replication Objects (JRO) is being deprecated in this release and will not be available in any future MDAC releases."
This is the only place on the MS site where I can find any mentioning of MS dropping the Jet Engine. As I'm currently developing an application based on Jet I'd really like to hear if anybody has any information?
If Jet is dropped I guess I'll have to go for another (free) engine. There is of course MSDE, but is it the 2000-version free as the 1.0 version was? (Provided you have VC++6.0 Enterprise)? Another possibility could be MySQL which has an OLEDB provider, does anybody have any experiences with the db or the provider?
thanks in advance
Steen.
Cheers
Steen.
"To claim that computer games influence children is rediculous. If Pacman had influenced children born in the 80'ies we would see a lot of youngsters running around in dark rooms eating pills while listening to monotonous music"
|
|
|
|
|
I dont understand why i cant type (local) to get access to my local MSDE server.
I think ther are some problem with the insallation of the MSDE server.
Working
(L"Provider=SQLOLEDB.1; Data Source=M02WS-17-051;Initial Catalog=TEMC_MEASSQL;uid=sa");
Not working
(L"Provider=SQLOLEDB.1; Data Source=(local);Initial Catalog=TEMC_MEASSQL;uid=sa");
Someone who have an ide?
|
|
|
|
|
|
Use the DataLink Dialog to open a connection using(local) as the server on a working system (not MSDE), then get the connection string and compare this against your connection string.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
