|
Okay, I see how I may have misinterpreted the requirement.
I thought he wanted to find mistakes where say a phone number was filled into the address field.
My advice is free, and you may get what you paid for.
|
|
|
|
|
When done, it'd be wise to put a unique-constraint on that phone-number to prevent this from happening again.
Bastard Programmer from Hell 
|
|
|
|
|
What happens if he wants to track 2 people at the same residence (and don't give me any crap about mobiles, not everyone has one).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
That would lead to another design; either it's correct to have multiple records or it isn't. Since he's asking how to identify them, I'm assuming that he doesn't want them. The end-user has to be able to identify wheter his/her item is in that list, and they need something to uniquely identify the tupel, otherwise they'll end up putting the data in there twice, creating redundancy.
To answer your question; the telephone number shouldn't be an attribute of the residency. Without that, we wouldn't have to model database, would we? We'd simply hook up everything to a autonumber and we'd be done - but Access proves that it ain't that simple 
Bastard Programmer from Hell
|
|
|
|
|
Eddy Vluggen wrote: he's asking how to identify them, I'm assuming that he doesn't want them.
See....I read it as probably a case of he wanted to find all the people who share a residence & phone. Traditional married is easy, based on just the last name and address, but people don't necessarily share names anymore. Thus address + phone number.
|
|
|
|
|
No, you can't read it as you like; it depends on how it's used. If the table can contain two people on the same address, it would be holding people, not addresses. How can one uniquely identify a "human"? Well, the only way that the end-user can be sure, is by using a non-changing and unique attribute of the entity.
The phone-number is not an attribute that uniquely identifies a person, nor a household. We've got normalization-rules for a reason
Bastard Programmer from Hell
|
|
|
|
|
Eddy Vluggen wrote: No, you can't read it as you like; it depends on how it's used.
 After years and years and years of seeing things like this, I can almost guarantee that what I described is what was being looked for at this time.
Now, totally redesigning what he's working with is another thing, which should be addressed. 
|
|
|
|
|
GenJerDan wrote: After years and years and years of seeing things like this, I can almost guarantee that what I described is what was being looked for at this time.
Most people assume they can, which explains the high number of databases with redundant and even incorrect data. There wouldn't be a reason to search the dupes, if it had been analyzed correctly - it would have had a constraint to prevent them from being entered in the first place.
Bastard Programmer from Hell
|
|
|
|
|
Hmm. Search for dupes. But they're not dupes as such. At least not in the sense of "I want to find the dupes to eliminate them". Maybe more like "I want to find the people who share an address so the company only has to send out one phone book to each household."
Yes, you can do that with a normalized database, too. 
|
|
|
|
|
GenJerDan wrote: Search for dupes. But they're not dupes as such.
Your assumption isn't logical, whether or not they are dupes (of the same record) is determined by the key. It seems as if he's trying to find duplicate records, not merely records that have the same value on a given attribute, isn't it?
Your description doesn't sound very logical either; you're referring to clients, their addresses (some clients could have multiple, in theory), and their phones (could be multiple per location, or even independant of the location)
GenJerDan wrote: Yes, you can do that with a normalized database, too.
I never stated that it wouldn't be
Bastard Programmer from Hell
|
|
|
|
|
Eddy Vluggen wrote: Your assumption isn't logical, whether or not they are dupes (of the same
record) is determined by the key. It seems as if he's trying to find duplicate
records, not merely records that have the same value on a given attribute, isn't
it?
Not to me.
The way I read it is "Find records that have the same address and phone number, but different names", which does fit the share-a-house scenario.
|
|
|
|
|
You shouldn't look at what fits, but at what's required. We model reality, you can't expect reality to follow your model.
It might be true that there are multiple entities at an address, and it might be false - but any assumption is false by definition.
Bastard Programmer from Hell
|
|
|
|
|
Do a group by on the fields you need to test and a count.
Select count(*), Name,Phone,Address from SomeTable
Group By Name,Phone,Address
having count(*) > 1
This will give you the dupes, then join the results back to the original table on these 3 fields to get the actual records. Standard deduping excercise.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No, this will only give you results where the name is also the same, not just the phone and address...
|
|
|
|
|
True, removing the name will do the job then.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have one table in excel and I would like to send the data from excel to sql server. The excel data are not fixed. The columns name varies depending on the user. so let say if i have one table in excel named StuentInfo. Then the columns in that table may includes the field like ID, studentID,student ID like that along with other detail info.
For example
table field may be
studentID
studentName
studentDetails
or
ID
studentName
studentDetails
or
ID
student_Id
studentName
studentDetails
so since ID can be studentID,ID or student_Id, i would need to check which of them the table contains. Is it anyway I can check the columnName in the table ? In one of the table, there are two Ids viz ID and student_Id. In that case, we are considering student_Id as the correct one? Is there any query expression to check the column name of the table?
suchita
|
|
|
|
|
I'd use an EnumTransmogrifier<T>
[^].
Make an enum like:
enum ColumnName
{
[System.ComponentModel.DescriptionAttribute("ID")]
[System.ComponentModel.DescriptionAttribute("student_Id")]
studentID
etc.
studentName
etc.
studentDetails
}
Then, when you open a file, parse the actual column names to a logical column names and cache the indices.
Slick as snot.
|
|
|
|
|
Hi Suchita,
You can get column names in SQl Server using the following query:
--This will give you all the column names in the customer table
Select COLUMN_NAME From <Your Database Name>.INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME = 'Customer'
Hope this solves your problem.
Thanks,
Kaifee
|
|
|
|
|
Will that work with Excel?
Using a DataReader is simpler and works with any ADO.net-compatible database.
|
|
|
|
|
Can I get the same output if I have an excel file ? Actually I want the data from the excel file and insert it into the sql server. Is there anyway I can get the same output with the excel file?
suchita
|
|
|
|
|
Have you thought about using the openrowset functionality of SQL Server? and letting SQL check to see if the values in the spreadsheet are in the database? rather than Excel doing the work!
MSDN: Openrowset[^]
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Hi I don't have choice. My clients or users uploads their file in excel and from that excel file, i have to read the data and insert it into the sql server.
suchita
|
|
|
|
|
Hey Guys,
Saying I'm a rookie at databases would be giving me too much credit. That said, I'm working on a project where a manager can create a job for his employees. Each employee is stored in a table tblUsers and each job is stored in a table tblJobs. Now I know I have a few problems with the way my DB is set up. tblUsers has a primary key of UserName and tblJobs has a primary key of Job Descriptions. Each entry in tblJobs has a linked key, UserName from tblUsers is linked to Project lead from tblJobs.
I have two ArrayLists, jobs and copyOfJobs. When a manager signs in, copyOfJobs is passed to what I call ManagerForm. In ManagerForm, the manager can make alterations to copyOfJobs, and once he exits, I want to compare jobs with copyOfJobs. The contents of jobs is pulled from an Access Database, then copied to copyOfJobs. Once ManagerForm is closed, I want to compare jobs to copyOfJobs. Durring the comparison I want to check if things such as project name, project lead, duedate for a particular assignment has changed. If changed, I want it removed from the database and reinserted with the new values. I'm completely stuck! Any advice/comments would be greatly appreciated.
Here is some code to prove I've actually messed around with this before coming to you guys.
public void GetUsers(ArrayList users)
{
object[] userData = new object[4];
OleDbCommand dbCommand = new OleDbCommand("SELECT * FROM tblUsers", this._dbConnection);
try
{
OleDbDataReader dbReader = dbCommand.ExecuteReader();
while (dbReader.Read())
{
User currentUser = new User();
dbReader.GetValues(userData);
currentUser.Name = userData[0].ToString();
currentUser.Password = userData[1].ToString();
string temp = userData[2].ToString();
int rights = Convert.ToInt16(temp);
currentUser.Rights = (User.EN_RIGHTS)rights;
users.Add(currentUser);
}
dbReader.Close();
}
catch (Exception e)
{
String temp = String.Format("DBManager::GetUsers: {0}", e.Message);
MessageBox.Show(temp);
}
}
pretty simple, probably not the best way.
and here is the method stub that I'm working on so far
public void UpdateJobs(ArrayList copyOfJobs, ArrayList originalJobs)
{
ArrayList copy = new ArrayList();
ArrayList original = new ArrayList();
copy = copyOfJobs;
original = originalJobs;
string temp = "";
if (!copy.Equals(original))
{
temp = string.Format("");
for (int i = 0; i < copyOfJobs.Count; i++)
{
}
}
}
[Insert Witty Sig Here]
|
|
|
|
|
Okay, so that's just a lot of text, and the question seems to be drowning in it. That's why nobody is answering I guess.
So I will answer what I am guessing is the core question here:
The basic idea is to get the list with sql, compare stuff and change values with C# and finally update these values with sql.
So first of all you'll want to use an UPDATE query instead of removing and reinserting each row.
OleDbCommand dbCommand = new OleDbCommand("SELECT * FROM tblUsers", this._dbConnection);
OleDbCommand dbUpdateCommand = new OleDbCommand("UPDATE tblUsers " _
"SET FieldOne = @ParameterOne " _
", FieldTwo = @ParameterTwo " _
"WHERE KeyField = @ParameterThree ", this._dbConnection);
dbUpdateCommand.Parameters.Add("@ParameterOne", _
SqlDbType.NVarChar).Value = SomeVariableHoldingTheValueInQuestion;
dbUpdateCommand.ExecuteNonQuery;
N.B. You may have to use a second connection for the update (I don't remember for sure)...
N.B. I used the syntax for MS SQL. You may have to adapt it to MS Access syntax, as it often uses its own slightly different version. You can check this by building a simple update query with the MS Access Query wizard / query builder.
A coding tip: try to avoid using index numbers for database fields in your code:
currentUser.Name = userData["UserName"].ToString();
is better than
currentUser.Name = userData[0].ToString();
because otherwise when you make a change in your database table or in the query, that will change the order of the fields, and then your code will run amok. Or you will be forced to edit your code all the time, and figure out which field is which index number over and over.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Dear all,
My hosting server is using SQL server 2008 and it is using replication. I have copied the databases from hosting to restore in my laptop then i see all tables are attached with "rowguid" column, i want to remove it from all tables but it did not allow me to remove at all. How to resolve this problem???
please see the error message below:
========================================================
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for Column 'rowguid'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Column&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
It is invalid to drop the default constraint on the rowguid column that is used by merge replication.
The schema change failed during execution of an internal replication procedure. For corrective action, see the other error messages that accompany this error message.
The transaction ended in the trigger. The batch has been aborted. (Microsoft SQL Server, Error: 25005)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=25005&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
=================================================
I am looking forward to hear from you.
regards,
Han
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
