By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,581 Members | 1,966 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,581 IT Pros & Developers. It's quick & easy.

Table Relationships

P: 20
hi abit of a noob question but here goes. i've using visual studio 2008 and sql 2005 express. i have a users table(part of asp.net 3.5 membership framework) and a userfunds table that records account balance. i have set up the primary key and foreign key constraint between the 2 tables with the UserId column and set Enforce Foriegn Key constriant to true. this means a 1 to 1 relationship right?

next i have a user deposits table which i want to record every deposit made by a user so i will have a UserId column (and amount and date). this will have multiple records showing deposits for each user so will be a 1 to many relationship with multiple entries for UserId. how do i set this up? do i create a primary key for UserId and set up a relationship but not set it to be enforced? thanks for any help.
May 16 '10 #1

✓ answered by ck9663

Here, read this...

Happy Coding!!!

~~ CK

Share this Question
Share on Google+
10 Replies


ck9663
Expert 2.5K+
P: 2,878
Try creating a unique key in your deposit table (ie Transaction Number), that will be your primary key. You're going to need a UserId foreign key to link it your users datable. A foreign key means you may have duplicate on the child table but must have a unique on the parent table.

Happy Coding!!!

~~ CK
May 17 '10 #2

P: 20
hi mate. have just changed the depsoits table to add a transaction number as the primary key just like you said but about 10 minutes before i read your post lol. i've got userId but haven't yet set the foriegn key in it.(does primary key mean no duplicate entries)?
in visual studio i create the foriegn key constraint but am unsure as to what it means. there is an option that says "enforce Foriegn Key Constraint" does this stop you from a 1 to many realtionship? or does that mean any changes in the parent table will delete the entry in the child table(if you set it yes)? but set to no will not alter this child table? cheers
May 18 '10 #3

ck9663
Expert 2.5K+
P: 2,878
Here, read this...

Happy Coding!!!

~~ CK
May 18 '10 #4

P: 20
cool thanks. i've set up the foriegn keys and written a stored procedure and code to execute it in asp.net but it's not working. not sure if it's my procedure or the asp.net code. any chance i could get some pointers please. i'm using the aspnet membership framework to store and retriee user information. is it possible to put a breakpoint in the SQL stored procedure so execution stops when the procedure runs?

Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE dbo.Desposit_Funds
  2.     (
  3.     @UserName varchar (256),
  4.     @Amount money,
  5.     @DepositId uniqueidentifier
  6.     )
  7.  
  8. AS
  9.  
  10. DECLARE @UserId uniqueidentifier
  11. SELECT @UserId = UserId FROM aspnet_Users WHERE UserName = @UserName
  12.  
  13. DECLARE @currentbalance money
  14. SELECT @currentbalance = AccountBalance FROM UserFunds WHERE @userId = UserId
  15.  
  16. UPDATE UserFunds
  17. SET LastDepositDate = GETDATE(),
  18.     AccountBalance = (@currentbalance + @Amount)
  19.     WHERE UserId = @UserId
  20.  
  21. INSERT INTO UserDeposits(DepositId, UserId, DepositDate, Amount)
  22. VALUES(@DepositId, @UserId, GETDATE(), @Amount)
  23.  
and my code in asp.net c# to run this procedure is below. i've tried the 2 ways below to get my parameter objects sorted out(first way i've commented out
Expand|Select|Wrap|Line Numbers
  1. try{
  2.             ConnectionStringSettings constring = ConfigurationManager.ConnectionStrings["ConnectionDB"];
  3.  
  4.             SqlConnection connect = new SqlConnection(constring.ConnectionString);
  5.             using(connect)
  6.             {
  7.                 SqlCommand deposit = connect.CreateCommand();
  8.                 deposit.CommandType = CommandType.StoredProcedure;
  9.                 deposit.CommandText = "Deposit_Funds";
  10.  
  11.                 //SqlParameter paramet1 = deposit.CreateParameter();
  12.                 //paramet1.ParameterName = "@UserName";
  13.                 //paramet1.Value = current.UserName;
  14.  
  15.                 //SqlParameter paramet2 = deposit.CreateParameter();
  16.                 //paramet2.ParameterName = "@Amount";
  17.                 decimal t;
  18.                 decimal.TryParse(TextBox1.Text, out t);
  19.                 //paramet2.Value = t;
  20.  
  21.                 Guid ident = Guid.NewGuid();
  22.  
  23.                 //SqlParameter paramet3 = deposit.CreateParameter();
  24.                 //paramet3.ParameterName = "@DepositId";
  25.                 //paramet3.Value = ident;
  26.  
  27.  
  28.                 deposit.Parameters.Add("@UserName", SqlDbType.VarChar).Value = current.UserName.ToString();
  29.                 deposit.Parameters.Add("@Amount", SqlDbType.Money).Value = t;
  30.                 deposit.Parameters.Add("@DepositId", SqlDbType.UniqueIdentifier).Value = ident;
  31.                 connect.Open();
  32.                 int count = deposit.ExecuteNonQuery();
  33.  
  34.             }
  35.         }
  36.  
May 18 '10 #5

P: 20
sweet jesus. i've spelt the name of stored procedure wrong. chalk that one up to a simple typo lol
May 18 '10 #6

ck9663
Expert 2.5K+
P: 2,878
Does the stored proc execute in Query window? If it does, it could be on your front-end.

Good Luck!!!

~~ CK
May 18 '10 #7

P: 20
it does run in query window in visual studio. what do you mean by front end? cheers
May 18 '10 #8

Frinavale
Expert Mod 5K+
P: 9,731
To me front end would mean your ASPX page :)
May 18 '10 #9

P: 20
i get you now. you mean the fault is on the front end. but i just said it was working :) cheers. any pointers for the code itself structure of store proc etc...?
May 18 '10 #10

ck9663
Expert 2.5K+
P: 2,878
If it's running in SQL Query Analyzer, the problem is on your front-end code. Post it on the java/c forum. They might be able to help you more...

Good Luck!!!

~~ CK
May 19 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.