New database developer needing serious guidance

[kpizzle]

Using SQL compact 3.5, C#, and Visual Studio 9

OK. 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:

tblPeople
PeopleID (PK)
PeopleName
Occupation

tblData
DataID (PK)
PeopleID (FK -> tblPeople)
WeighDate
CurrHeight
CurrWeight
CurrBMI
GoalWeight
GoalBMI
Occupation

(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!

[toraj58]

the way you have posted your problem make it hard and confusing for others to response to you; whati understood from your post is that your porblem is very easy so i don't have intent to give you the full solution but i have some clue for you.

you should have basic of SQL language (select, insert, update, delete, joining tables) aslo you should have basic knowldge of ADO.Net, Disconnected dababase architecture, Data Binding and Data Controls.
you should know how to use some mandtaroy and optional classes base on your approach (Disconnected or direct access) which some of them are as following:

SqlConnection SqlCommand SqlDataReader SqlCommand.Parameters (important for preventing SQL Injection) SqlDataAdapter DataSet DataRelation (for implementing master-child realtionship if you don't want use SQL Join instead)

also some important Data Control are:


GridView
DetailsView
FormView


for your problem is suggest you that investigate how to use DataReader and GridView and apply them to your problm.

for sure you should have at least basic Knowledge of C#.

Touraj Ebrahimi [toraj_e] [at] [yahoo] [dot] [com]

[cjard]

Read the DW2 link in my signature, section "Creating a Simple Data App"

Be aware that DW2 is for 2005, youre using 2008, so look for the panel in the top right that indicates theres a page specific to your version of .NET to make sure youre following the most up-to-date advice. I don't think much has changed from2005 -> 2008 though

[cjard]

DataRelation (for implementing master-child realtionship if you don't want use SQL Join instead)

The two are very different, avoid advocating that their design intention is interchangeable

GridView
DetailsView
FormView

Nothing about the OP's post leads me to believe this is a WebForms app? The OP's statement of "It will be a simple desktop app" leads me to believe this will be a WinForms app

how to use DataReader

Don't; DR is too low level. Read the DW2 link

[toraj58]

i think that for his problem DataRelation and SQL Join are interchangable.

[TheCPUWizard]

i think that for his problem DataRelation and SQL Join are interchangable.

NO, they are radically different. In the first case, the relationship only exists inside the application. In the second case, there is only a single entity in the application and the merge occurs within the database.

The differences between these two are quite significant. Each is applicable to DIFFERENT situations.

Because of the updating in the OP's requirements, it appears that keeping them as distinct entities (ie jusing a DataRelation and NOT a join) is the more appropriate.

[kpizzle]

Thank you everyone for your replies.

Toraj58:

the way you have posted your problem make it hard and confusing for others to response to you;

I'm sorry that it was that way, I was trying to include as much information as necessary, and still probably did not successfully describe my problem

you should have basic of SQL language (select, insert, update, delete, joining tables) aslo you should have basic knowldge of ADO.Net, Disconnected dababase architecture, Data Binding and Data Controls.
you should know how to use some mandtaroy and optional classes base on your approach (Disconnected or direct access) which some of them are as following:

SqlConnection
SqlCommand
SqlDataReader

SqlCommand.Parameters (important for preventing SQL Injection)

SqlDataAdapter
DataSet
DataRelation (for implementing master-child realtionship if you don't want use SQL Join instead)

I am not sure if I know what disconnected database architecture is (using a dataset?) and the data binding I have done is a result of the drag and drop from the datasource window onto my form. I have working knowledge of the SQL language, however I am at a loss on how to execute one within C# code, weaving around the generated code from using wizards and drag and drop.

also some important Data Control are:

GridView
DetailsView
FormView

Not encountered a form view yet. What is that?

for sure you should have at least basic Knowledge of C#.

I would be in a lot of trouble if I didn't

i think that for his problem DataRelation and SQL Join are interchangable.

For the record, I'm a her.

cjard:

Read the DW2 link in my signature, section "Creating a Simple Data App"

This is the exact document I used to setup what I've got so far.

The OP's statement of "It will be a simple desktop app" leads me to believe this will be a WinForms app

Correct.

CPUWizard:

Because of the updating in the OP's requirements, it appears that keeping them as distinct entities (ie jusing a DataRelation and NOT a join) is the more appropriate.

Ok. This must have something to do with the relationship. I established the relationship in the data set designer, would the generated code be via the DataRelation or has a join been established somewhere?

Let me see if I can clarify a few things:

I have added an attachment of a jpeg I just put together to help illustrate what I am trying to say.

The middle section is for adding only -- and it doesn't work well, all text boxes are bound. I am not displaying the dataID, or personID textboxes for the table in this section. So, when I try to add a new record, the personID doesn't trickle in.

These textboxes came from the datasource window...I found the tblData listed UNDER tblPeople in the data source so they would be linked, I clicked the table, selected details, and drug it the form. I did the same thing only using a datagrid for the bottom section. However, when I try to add a record to the datagrid of tblData, bottom section, (even though this is NOT intended at all, using the datagrid to add records--just for testing purposes), the PeopleID does trickle in. I'm confused on why this is so. Perhaps because I am not displaying the textbox for the ID fields??! Maybe I should have it on the form, visible= false? I'm trying that as soon as I get done writing this post.

At any rate, my main dilemma (occupation stuff I was talking about in initial post):

How can I grab a value from 'occupation' in tblPeople (or textbox in my case) for a certain ID, and have it update to the 'occupation' field when adding a new record in tblData. Reason I ask: The middle section only contains textboxes for the following fields from tblData:

WeighDate
CurrHeight
CurrWeight
CurrBMI
GoalWeight
GoalBMI

The other fields:
DataID (PK)
PeopleID (FK -> tblPeople)
Occupation

DataID is a identity field which should increment itself. PeopleID should (since the tables are linked with referential integrity enforced) be assigned according to whichever person is selected in the combo box. Occupation (tblData) should match whatever Occupation (tblPeople) is. (There should be another control in the top section for occupation, a text box).

So, my logic here: Occupation in tblPeople is the person's CURRENT occupation. Occupation in tblData is a record of that person's occupation at that time. When adding a new record, occupation (tblData) should rely on the value in occupation (tblPeople).

Now if I can figure out how to get a value in a field based on another table's value, then I can tweak it to fix all my problems described in this thread (however vague, despite my best efforts).

So...the big question:

How can I update one field's data on one table, based on a field in a different table?

OR, speaking in terms of UI,

how can I update one textbox, based on the value in another?

If the answer involves using a SQL statement, how do implement one in C#?

(This sounds like I've done nothing myself to figure it out, but, I have...everything I've found on using SQLs within C# sharp also involves defining the connection string, connecting to the database, and using classes that I've used -- however the wizard generated my code, and I've tried to get at using the SQL commands, however i have ran into scary (might destroy my life) situations when using 'sounds-good' intellisense results -- guess I need to build a testApp to figure this out)

I know it can be done in Access, but the environment is WAY different...and I haven't been able to figure a workaround.

Thank you all for trying to help, hopefully I didn't boggle things up worse. You've left me some good ideas to go play with...

I'd really like a "Eureka" right now.

Thanks!!!

kpizzle

[toraj58]

NO, they are radically different. In the first case, the relationship only exists inside the application. In the second case, there is only a single entity in the application and the merge occurs within the database.

The differences between these two are quite significant. Each is applicable to DIFFERENT situations.

Because of the updating in the OP's requirements, it appears that keeping them as distinct entities (ie jusing a DataRelation and NOT a join) is the more appropriate.

if we ignore the performance for his simple problem would you please give me the clue why they are radically different?