|
In order to fill in the parameters you would use the same type of code from the INSERT statement generation:
<br />
command.Parameters.Add(this._userDataSet.tblUsers.ID_NAMEColumn.ColumnName,<br />
OleDbType.VarChar,<br />
this._userDataSet.tblUsers.ID_NAMEColumn.MaxLength, <br />
"ID_NAME");<br />
<br />
command.Parameters.Add(this._userDataSet.tblUsers.ID_PASSWORDColumn.ColumnName,<br />
OleDbType.VarChar,<br />
this._userDataSet.tblUsers.ID_PASSWORDColumn.MaxLength,<br />
"ID_PASSWORD");<br />
<br />
You would change the OleDbType to the appropriate type for the parameter. And you change the last parameter to the name of the field. Does that make sense?
Hopefully this helps. I answered based on what I could gather from your questions.
|
|
|
|
|
Hello,
I need to insert the newly inserted/Updated/Deleted records into History table in following format.
ID - Field Name - Old Value - New Value - Date
So what would be the best way to achive this. Should i go for Trigger? Write insert statement in each insert/Update/Delete stored procedure?
Demo code would help more.
As described above,
There would be a table names "History" having columns,
ID - TableName - Field Name - Old Value - New Value - Date - AdminID [Id of user who is modifing field]
Now Table name and field name would be the values indicating the inserted/modified/delete field name. Oldvalue coloumn would be showing old value if any and new value would show current value.
So all the database table would have single History table.
Hope this made more clear. Thanks.
Thanks in advance.
modified on Thursday, June 16, 2011 12:46 AM
|
|
|
|
|
It all depends on what database you are using. You can use a trigger, or you can simply update your code to call another stored procedure to put an entry into the History table.
Clearly if you are deleting a record, you will need to make a copy of it before you actually delete it!!
The power is in your hands!!
|
|
|
|
|
What are the business requirements?
Will end-users see/use the data, a silent audit log,.. ?
What will it be used for?
Brad
|
|
|
|
|
Depends on other information.
Also you might want to verify if the user that originated the change must be tracked.
Are you sure you want to keep a record by column versus just keeping the entire row? Your solution adds complexity and your actual use cases and retention might not make it worthwhile.
|
|
|
|
|
Make sure you build in a process for archiving this History table on a periodic basis. It will grow by leaps and bounds in no time. Also, consider a method for turning this auditing off ... there may be a case where you have a large update job that you don't really want to audit.
Just a thought from someone who has been there ... 
Good luck.
|
|
|
|
|
If your using SQL Server 2005 or greater have a look at the TSQL Output clause[^]
this will give you access to the deleted and inserted tables that you normally get with a trigger.
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
|
|
|
|
|
Is it possible to get information about whether a child table has reference to a parent table through a certain record?
For example, tblParent has a record whose primary key value is 5. I want to know whether tblChild_A, tblChild_B and tblChild_C have one or more records in them where the foreign key value is 5. Well it's definitely possible by querying each of the child tables individually but I want to know whether there's any system query or stored procedure or whatever that produces a tabular output from all those tables together.
If it's possible in MS SQL Server then please also inform what's its counterpart in MS Access. Regards.
modified on Tuesday, June 14, 2011 3:16 AM
|
|
|
|
|
It's a very simple query to return how many records in a child table match a parent's ID, however, you haven't really made it clear what you want to achieve.
How many child tables do you have? Why don't you want to query them individually?
|
|
|
|
|
Well as I said, if possible, I want to get a tabular output showing which tables are related to a certain primary table and the related data in them all-together. Please refer to the second paragraph in my OP. Feel free to ask if I'm still not clear.
|
|
|
|
|
Have a read through this article on sp_help[^]
There is a section in the article that shows the tables a given table references.
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
|
|
|
|
|
While there are system views which will identify the FKs between tables this is not a system issues but a data problem. You will need to craft a query that get the data you want the way you want it. There is no 'system' query/function etc that will service this requirement.
It is a fairly simple excercise using either sub queries or left joins depending on the required output.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How can I select records from a single sql table that have common data in two of their columns.
Thanks,
i.e.
table customer {Name, Address, Phone Number)
How can I select all records from customer where address and phone number are the same even though the records are unique?
|
|
|
|
|
Ugly way that works:
select distinct a.* from Customer a, Customer b where a.Address = b.Address
and
a.[Phone Number] = b.[Phone Number]
and a.Name <> b.Name
order by Address, [Phone Number], Name
There must be a prettier way to do it...but at least it works in MS SQL. 
|
|
|
|
|
You could try:
SELECT *
FROM customer
WHERE Phone = Address
My advice is free, and you may get what you paid for.
|
|
|
|
|
No, that will return customers who have the same information stored in the phone and address fields of a single record, rather than two different customers who have the same phone number and the same address...
|
|
|
|
|
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. 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
