Table Relationships

[pinman]

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.

[ck9663]

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

[pinman]

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

[ck9663]

Here, read this...

Happy Coding!!!

~~ CK

[pinman]

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?

ALTER PROCEDURE dbo.Desposit_Funds
	(
	@UserName varchar (256),
	@Amount money,
	@DepositId uniqueidentifier
	)
	
AS

DECLARE @UserId uniqueidentifier
SELECT @UserId = UserId FROM aspnet_Users WHERE UserName = @UserName

DECLARE @currentbalance money
SELECT @currentbalance = AccountBalance FROM UserFunds WHERE @userId = UserId

UPDATE UserFunds
SET LastDepositDate = GETDATE(),
	AccountBalance = (@currentbalance + @Amount)
	WHERE UserId = @UserId
	
INSERT INTO UserDeposits(DepositId, UserId, DepositDate, Amount)
VALUES(@DepositId, @UserId, GETDATE(), @Amount)

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

try{
            ConnectionStringSettings constring = ConfigurationManager.ConnectionStrings["ConnectionDB"];

            SqlConnection connect = new SqlConnection(constring.ConnectionString);
            using(connect)
            {
                SqlCommand deposit = connect.CreateCommand();
                deposit.CommandType = CommandType.StoredProcedure;
                deposit.CommandText = "Deposit_Funds";

                //SqlParameter paramet1 = deposit.CreateParameter();
                //paramet1.ParameterName = "@UserName";
                //paramet1.Value = current.UserName;

                //SqlParameter paramet2 = deposit.CreateParameter();
                //paramet2.ParameterName = "@Amount";
                decimal t;
                decimal.TryParse(TextBox1.Text, out t);
                //paramet2.Value = t;

                Guid ident = Guid.NewGuid();
                
                //SqlParameter paramet3 = deposit.CreateParameter();
                //paramet3.ParameterName = "@DepositId";
                //paramet3.Value = ident;


                deposit.Parameters.Add("@UserName", SqlDbType.VarChar).Value = current.UserName.ToString();
                deposit.Parameters.Add("@Amount", SqlDbType.Money).Value = t;
                deposit.Parameters.Add("@DepositId", SqlDbType.UniqueIdentifier).Value = ident;
                connect.Open();
                int count = deposit.ExecuteNonQuery();
                
            }
        }

[pinman]

sweet jesus. i've spelt the name of stored procedure wrong. chalk that one up to a simple typo lol

[ck9663]

Does the stored proc execute in Query window? If it does, it could be on your front-end.

Good Luck!!!

~~ CK

[pinman]

it does run in query window in visual studio. what do you mean by front end? cheers

[Frinavale]

To me front end would mean your ASPX page :)

[pinman]

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...?

[ck9663]

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