Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kpizle
Starting Member
1 Post |
 Posted - 2008-11-04 : 16:17:47
|
| Using SQL compact 3.5, C#, and Visual Studio 9OK. This is my first time designing a database with SQL server (the only other databases I've built were MSAccess using VBA). I'm learning as I go. I am using a data source, basically set up by the wizards. I designed the database within VS. My db only has two tables, tblPeople, and tblData. I am trying to write a program to record a person's health data, such as weight, height, bmi, goalWeight, and goalBMI. It will be a simple desktop app with only one user (hence the use of SQL compact).My tables look like so:tblPeoplePeopleID (PK)PeopleNameOccupationtblDataDataID (PK)PeopleID (FK -> tblPeople)WeighDateCurrHeightCurrWeightCurrBMIGoalWeightGoalBMIOccupation(I am storing occupation in both tables, as it is something that can change and I would like to have a record of the occupations of the past, should the occupation change).I have two questions. I know I could bust it with Access, so I'm wondering if a) it's possible with my current setup, and b) how?I have a very specific UI to develop (indicated by the client). I am to have a browsing combo box of people names, and a text box to update the occupation. Below that, I need text boxes for a NEW record in the data table (this is necessary because some of the values will be captured via a serial port connection). Then below that, I need to display the selected person's weigh history (basically all of that person's tblData records). I plan to use a datagrid to accomplish this, with add privileges denied. So I guess I need tblData on my main form twice: once for adding records in textboxes, twice for viewing old records in a data grid.First question: Am I dreaming? I added all the text boxes (details) and datagrid to the form using the datasource window and dragging to the form. The datagrid seems to work much better than the details. Am I missing something? When I try to add a new record to tblData via the textboxes, it won't stick (I believe because of the FK PeopleID) Which leads me to my next question:How can I update a field based on another field's value. Two examples I have run into that both need this question answered:One - the textbox for Occupation on the form will be from tblPeople. I am not including the Occupation textbox for tblData becuase I want this field to update according to what is in tblPeople ONLY WHEN A NEW RECORD IS ADDED, in other words, I don't want a change to tblPeople's Occupation field to trickle into existing records in tblData.Two - I need the PeopleID to grab the current PeopleID selected in the combo box when adding a new record to tblData. I think this is why the changes won't stick. (in the datagrid, before I disallowed adding a new record, it would automatically grab the ID from the current combobox record--as desired, how can I make the text box do that too?Any help would be greatly appreciated. I am not quite sure how to go about this, and most of what I can find is about set up and not implementation. Any links, suggestions, or whatever would be great.Thanks! |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-11-12 : 04:54:08
|
| To modify one field based on insertions in another:You could do this on the application side, i.e. run an update query after the application inserts a new record. But you can also accomplish this with a TRIGGER on the table. Triggers are not available in SQL Compact but they are available in SQL Server Express which is also free. Goggle triggers or see:http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-ddl-trigger-workbench/On the table design, keeping same values in 2 tables creates problems to maintain - as you are experiencing. To store historical data you could insert datetime it was inserted / modified into new column. |
 |
|
|
|
|
|
|
|
UI setup