473,320 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL Server 2005 database - Problem

Hello!

I have a problem, and I looking for help if someone can handle this.
I use asp.net 2.0, and I create web site which support users from internet
(Web Site Administration Tool) and with that option i get database named
ASPNETDB.MDF.
When someone via page is creating account, database creates a new record in
aspnet_users table.
I made another table (table User_Data, columns UserID, Level, Points, ...),
and i want that these data are set to some default values automatically when
user press Create Account button (something like this, Columns Level to 1,
column Points to 0, ...)
Which relationship i must use between two tables, and what else i must to do
to be able make this.
I made relathionship FK_aspnet_Users_Users_Data, and set UPDATE statement to
Set Default, and i was set default values for columns in User_Data table,
and when user press Create Account on page, i get error.

I don't know if you understand what i want, but i want to, when user create
new account, that data in other tables are populated automatically for that
user, with some default values, that i specified earlier.

Thanks
Oct 10 '07 #1
1 2990
Zeljko Bilandzija (za*****@email.t-com.hr) writes:
I have a problem, and I looking for help if someone can handle this.
I use asp.net 2.0, and I create web site which support users from internet
(Web Site Administration Tool) and with that option i get database named
ASPNETDB.MDF.
When someone via page is creating account, database creates a new record
in aspnet_users table.
I made another table (table User_Data, columns UserID, Level,
Points, ...), and i want that these data are set to some default values
automatically when user press Create Account button (something like
this, Columns Level to 1, column Points to 0, ...)

Which relationship i must use between two tables, and what else i must
to do to be able make this.

I made relathionship FK_aspnet_Users_Users_Data, and set UPDATE
statement to Set Default, and i was set default values for columns in
User_Data table, and when user press Create Account on page, i get
error.

I don't know if you understand what i want, but i want to, when user
create new account, that data in other tables are populated
automatically for that user, with some default values, that i specified
earlier.
You would need a trigger to add data to User_Data when a new row is
added to aspnet_users:

CREATE TRIGGER add_user_data ON aspnet_Users FOR INSERT AS
INSERT User_Data(UserID, Level, Points, ...)
SELECT UserID, 1, 0, --- more defaults goes here
FROM inserted

What you set up was a foreign-key relation, which you indeed should have
one, but it serves a different purpose. When you do:

ALTER TABLE user_data ADD
CONSTRAINT fk_user_data_aspnet_users FOREIGN KEY(UserID)
REFERENCES aspnet_Users (UserID)

you state that whenever a row is added to user_data, there must be a
row in aspnet_Users with that UserID. As written above, the constraint
also prohibits deleting a row in aspnet_users if there is a referencing
UserID in user_data. Same applies if you try to change the user id in
aspnet_Users.

When you added:

ON UPDATE SET DEFAULT

this changes what happens if you update a UserID in aspnet_Users. Instead
of getting an error, the referencing UserID in User_data will be set to
its default value, if it has one. This is a very rare thing to do.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Oct 10 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: AlessanBar | last post by:
Hello Friends !! I have a strange problem, and I need to know what would be the source of this. I have a laptop computer with the following configuration: Pentium III Brand : Toshiba Speed :...
17
by: Danieltbt05 | last post by:
just installed SQL server 2000 and using my client , i can't locate the server. I used SQL query analyzer to search but no servers were found. Error message is as below Server : Msg17,level...
8
by: nick | last post by:
I have only SQL Server 2005 installed on my PC. And I tried to add the following rows in web.config to use SQL Server 2005 instead of Express: <connectionStrings> <clear /> <add...
3
by: Arpan | last post by:
I recently installed SQL Server 2005 (Management Studio Express) along with SQL Server 2005 Express (Configuration Manager) in my Win2K Pro m/c & use IIS 5.0 to run ASPX projects. I could add my...
2
by: Jobs | last post by:
Download the JAVA , .NET and SQL Server interview with answers Download the JAVA , .NET and SQL Server interview sheet and rate yourself. This will help you judge yourself are you really worth of...
2
by: sqlgirl | last post by:
Hi, We have 2 servers. Server1 is a 2000 box, with SP3 (yes I know it is not up to date). Server2 is a 2005 box, SP2. I set up Server1 (2000) to have a linked server to Server2 (2005). The...
3
by: Lee T. Hawkins | last post by:
I am having a number of problems over the last two full days trying to get an ASP.NET 2.0 application to connect to a SQL Server 2005 database... First off, I built this application w/ Visual...
2
by: samadams_2006 | last post by:
Hello, I have a problem that I'm hoping someone will be able to help me resolve. 1) I have a C# Web Site in which I connect to the database: "Install Microsoft SQL Server 2005 Express...
1
by: bimowidhi | last post by:
Hi, I'm a newbie here, and also in PHP. Sorry for my bad English. I've walked around to find the answer of my problem, but I still can't get it. I develop PHP 5.2.5 Application, using SQL...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.