|
I'm writing my first .Net app (old ASP/VBScript developer). Using VS2003 I set up a SQLConnection & SQLDataAdapter. I use a SQLCommand to fill a Dataset. I'm displaying the Dataset in a DataGrid that has Edit enabled. I'm trying to figure out how to use the generated Update command in the SQLDataAdapter in my Update event handler. I have all the code to pick up the values from all the cells, but all the examples I've seen for the SQL Update code create a new connection, adapter and hard-code an Update command, set all the command parameters and execute the command. The SQLDataAdapter already has a parameterized Update command. How do I reference the parameters of that Update command to set them to the values of my cells and execute the command?
|
|
|
|
|
i make a query on my database.
I want the image data inside a row.
I select it and i start to read data.
The problem is that sometimes there is no data inside, so if i try to read i get exception.
Can i check if there is data inside that field before start ot read?
That field doesn't support NULL default value.
i use Microsoft Access database .mdb and ADO.NET
|
|
|
|
|
Or you could just wrap your code to read that field in a Try/Catch block. If it fails, there wasn't anything there to read.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
i've created an application that register and make login the users.
When the user register himself enter the following texts:
name: Marco
password: project
Then if the user try to login with this value it get login either write Marco and mArCO, project and PROJECt
i use the following code:
string query = "SELECT * FROM Table WHERE Name = '" + textBoxName.Text + "' AND Password = '" + textBoxPassword.Text + "'";
It is not case sensitive, why?
|
|
|
|
|
Sounds like the collation sequence for your database is set to case insensitive. You can change the collation sequence on a column-by-column basis if you like, but I find that has many problems when comparing data between columns with different collation.
Once set, you cannot change the collation sequence of the database as a whole as it has to be set at creation time.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
i could create a new database...
how can set the property that handle case-sensitive in query statment?
|
|
|
|
|
I have a question - I am trying to design a database - contacts - and I have seen many around but do not serve my purposes - so I started with my requirements:
The logical breakdown where:
- the adress stored could belong to an individual or company.
- the relationship of person to company could be one person can own multiple companies but one company could be the employer of many persons.
- Thus - the address could be the address of the company but also of the person's work address or the address could be the persons personal address (and have nothing to do with the company - say an unemployed alcoholic who is a good friend).
I am gong in circles - any suggestions? I have not even started into the phone table yet!
|
|
|
|
|
Sounds like you need some many-to-many joins
Okay - Let's try and put this in atomic bullet points.
* Address can belong to an individual
* Address can belong to a company
* Person can own 0, 1 or more companies
* Company can employ 0,1 or more persons
* Person can have a work address
* Person can have a home address
* A person can be employed by 0, 1 or more companies.
So, you have a person table, a company table and and address table. You also have some many-to-many joins which require intermediate tables, so you have a person_owns_company table and a company_employs_person table. These intermediate tables take the primary key from each side of the join and combine them together to make a compound primary key of its own.
So, if you have a PersonID and and CompanyID your person_owns_company table will have a compound primary key of PersonID and CompanyID. They also remain foreign keys back to their originating tables.
A person can have a homeAddressID column which will be a foreign key to the address table
A company can have an addressID column which, again, will be a foreign key for the address table.
You may wish to extend this futher as companies can have more than one location and the addressID may be better placed on a company_location table.
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
This helps. Interesting that you brought in the location...I thought about it after I posted the question and ended with a headache. How would you handle if you wanted to specify the location for the person's workplace. Say Jill works for company foo, which have 5 locations, and Jill is in location 3. In other words, how would you tie person with company location using the above scenario?
And Thanks!
|
|
|
|
|
Two ways I can think of depending on what sort of data you need.
1. The person has a join to company_location rather than company (the company can be infered from the location)
2. If you have the idea that many companies can occupy the same location, for example a number of small companies in a business centre. The you would join the person to the company and the location.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thanks Colin,
This helps a lot. The clear, logical and objective perspective is what i needed.
And I looked at your Blog - browsed quickly through Normalising the data model. Looks very informative, will go and read it again. Its cool.
|
|
|
|
|
|
Colin Angus Mackay wrote:
so you have a person_owns_company table and a company_employs_person table
Question: Would it be better to add the above two tables or one one table called "PersonCompany" with foreignkeys: PersonID and CompanyID and one field called "RelationType" eg: IsEmployee or IsOwner. (kind of like a crossreference). Of course this would also be two tables if I introduce a separate table called "TypeTable" and use it to lookup the "RelationType". Any suggestions?
|
|
|
|
|
hp108 wrote:
Would it be better to add ... one table called "PersonCompany" with foreignkeys: PersonID and CompanyID and one field called "RelationType"
I wouldn't have that as one table with a RelationType column unless there were going to be many queries that didn't care what the relation was, just that they were related in some way. The reason for this is that these are two distinct logical relationships and the physical side should match unless there are performance problems. In otherwords, design the model as normalised as possible, then denormalise it afterwards if you find performance problems.
If you do decide to put it only one table, remember that the RelationType column is most likely to become part of the Primary Key if you can have a person that shares both types of relation with the company.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I believe you have a point because:
(quick type, excuse mistakes - just to make a point)
select p.*, c.companyname from person p inner join personcompany pc on p.personid = pc.personid
inner join company c on pc.companyid = c.companyid where
pc.relationtype="IsEmployee"
group by c.companyid
would give the same result as the following which does not use the where clause:
select p.*, c.companyname from person p inner join company-employs-person pc on p.personid = pc.personid
inner join company c on pc.companyid = c.companyid
group by c.companyid
I'll experiment with both and see which is faster.Thanks.
|
|
|
|
|
Colin Angus Mackay wrote:
A person can have a homeAddressID column which will be a foreign key to the address table
A company can have an addressID column which, again, will be a foreign key for the address table.
Sorry for the bother! Does this mean that "homeAddressID" in the address table will be null when the address belongs to the company ( and vice versa ) ?
|
|
|
|
|
No, the address table would have an AddressID column which would be its primary key. The Address table does not need to know that it is a home address or office address. It is just an address.
The person table may have a homeAddressId column (indicating that it is the home address of the person). A person may have a workAddressID as well (if that is your design - although it may be inferred by their links to a company or companyLocation)
The Address table would be part of a one-to-many join, for every one address there are many people or many companies or both. The Address table cannot track that, so the tracking goes to the person or company table. Something like this:
ParentTable (Address) ChildTable (Person)
--------------------- -------------------
ParentId <----\ ChildID
OtherColumns \---> ParentID
OtherColumns
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Design wise this makes sense, but in practice this would mean that I have to add the address record before I could refer to it from the person or company table, which seems backward as it would allow me to add addresses without requiring it be used. So I could end up with addresses which are orphaned (as no one refers to them). I am new at this so I do not know if this is normal pattern?
|
|
|
|
|
Normally I use stored procedures for everything, so in a situation like this the stored procedure would determine if an existing address existed and join to that, or if a new address needed to be created and then create and join to the new address. It would do this all in one transaction, so that if the later part of the transaction failed (for example creating a new row in the Person table) then the whole thing would be rolled back and it would be as if there never was an Address record.
In the reverse situation, you could always add a trigger to the person or company/companyLocation table so that in the event that it is being deleted the trigger can see if there is anything else joined to the address and remove the address if it would be orphaned.
Personally, I'm not a big fan of triggers (I've only used them for building audit information) as I prefer all access through stored procedures.
There will always be weak points in a design but you you'll know how you intend to use it and can compromise on certain things. For example, if you will be mostly reading (SELECTing) data then you can optimise it by putting indexes on the columns most often referenced in your WHERE clauses, however, by doing this you will slow down INSERT, UPDATE, DELETES.
I hope this helps (I may have gone off at a tangent a little in my last paragraph).
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thanks! You have clarified a number of things that always confused me. Appreciate it.
|
|
|
|
|
I think you are taking normalization too far.
Dont concern yourself as to storing addresses more than once.
The address in each table I create is usually just an attribute.
This means I would have an address for each company and person.
Same with phone.
Although it is possible that a company and person might have the same address or phone it isnt important that they are the same physical record in some table.
"Owning" a company - you work for it.
I see three tables in your entire post.
companies -> companies_people <- people
This way people can be part of a company or NOT.
Company table would have a main phone, fax etc and address(s)
People would have their own ID, address and phone.
YES you might have duplicated data but it certainly could not be called a transitive dependancy.
http://www.jasncab.com/huberblog :: Jason Huber
|
|
|
|
|
Thanks. I agree that the database does not heve to be normalised to the smallest detail as it would effect perfomance and make the queries more complex. But like Colin pointed out:
In otherwords, design the model as normalised as possible, then denormalise it afterwards if you find performance problems.
I want to take this one step further - not denormalise it but use its structure to dynamically create only the required tables/relations.
It might be a good idea to kind of explain what I am trying to do. The model I am trying to build will not actually contain any data, but its structure will be used (schema - which could be in xml format) to generate a database that will only contain the tables and structure required by the user. For example: User may want to store a telephone list - name and all the related phone numbers - so I would pick only the necessary tables (person,phonegroup,phone) and their relationships from the schema and create the database. Then he might want another DB to have all company info...
So the idea is to "morph". For this I need all the conceivable possibility for the schema. Now I know this may not be the best approach for this kind of application and suggestions are welcome.
Thanks!
|
|
|
|
|
HAs anybody out here used the Oracle 10G. I want to know the feasibility related to the this version of oracle and the ASP.NET compatibility with it.
|
|
|
|
|
I use 10G regularly in two environments installed on Windows 2003.
Works just fine. ASP.NET and the driver you currently use will work fine.
http://www.jasncab.com/huberblog :: Jason Huber
|
|
|
|
|
Hi
i have a db on sqlserver 2000;
How i can export tables and relations to a access db ?
I need it cause access has db documentor;
Thanks;
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
