Creating new tables in aspnetdb.mdf

I have made a new table called 'customer' which i wish to tie into the userId of the db. I have used a db diagram to do this (there are keys on each side of the link and userId is the FK) . When i put the membership/profile view onto the form alongside the new customer table nothing displays in the customer table when i run the app., i dont even see the titles - any ideas (i'm new to this sql malarky btw - so its probably something unbelievably straightforward). Any help appreciated.

Thanks

Hi,

By default, the membership/profile view will not show your additional information.

Since you have added one table into the database, and need to show it on your view, you will need to use your own query to get the user info together with the data in customer table. This can be done either by creating a View or use JOIN statement directly.

Then, you can customize the web UI to show the additional info.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

Hi, and thanks for replying.

I sort of rather optimistically and naively thought that because my new table was using pre-defined field variables including the key field (i.e. UserId (key), UserName etc.) that the system would know that these were what I required to be displayed (through the diagram), with no further programming, except to set to set the new field variables, that I've added, to new values within the program, and that the new fields would simply display the default value until I did something with them. - in another world that might have happened. After all, what have I created the diagram for?

What I didnt realise was that the aspnet.mdf tables are created, configured, and deleted through the Stored Procedures and that I needed to do the same for my table, even though there is a diagram telling the system that this 'linking in' of the aspnet_userId and my table is what I want to happen .

As I'm using the UserId as my key field I want the table to reflect the same membership as the aspnet.mdf membership tally - so I've sneaked in one or two statements within the createuser and deleteuser Stored Procedures to put in or remove UserId values from my table as new members are created or deleted respectively. I hope that that will solve my problem - it seems to have so far. I just cant help thinking that somewhere along the way my table is not going to tally with aspnet users for some reason, due to something that I'm not aware of (yet), caused by some insertion or deletion of a UserId without the createuser or deleteuser Stored Procedures being involved.

I'm sort of at a loss as to why I've created the diagram. I could now delete it and nothing would change.

Thanks again.

Creating index on two columns

I have a SQL Server 2000 table with the following structure:
TABLE Personal (
UserID (PK),
Name,
Surname,
DateOfBirth,
Citizen,
BranchCode,
RegionCode,
Division
)
and I use the following stored procedure to do a SELECT which I use on
an asp page:
CREATE PROCEDURE dbo.SelectLevel1
AS
BEGIN
SET NOCOUNT ON
SELECT UserID, Name, Surname
FROM Personal
WHERE Citizen = 'Yes'
ORDER BY Surname ASC, Name ASC
END
I'd like to add an index to the Personal table to maximize the
performance of the SELECT statement.
What index should I create on this table?
My first thought was to index the Name and Surname columns, but how?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Andy,
Yes Surname and Name columns might be good ones to use. Is this a
commonly executed query? I would also look at placing an index on the
Citizen column - how selective is that column?
Your best bet is to try different indexing and look at how efficient
each of them are. Try three or four different approaches with Surname,
Name, Citizen.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> I have a SQL Server 2000 table with the following structure:
> TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
> )
> and I use the following stored procedure to do a SELECT which I use on
> an asp page:
> CREATE PROCEDURE dbo.SelectLevel1
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
> END
> I'd like to add an index to the Personal table to maximize the
> performance of the SELECT statement.
> What index should I create on this table?
> My first thought was to index the Name and Surname columns, but how?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Citizen is not important. Surname and Name are the only ones that are
"selective".
Would it be better to create two indexes - one on the Name column and
another on the Surname column, or would one index on both columns be
better?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Andy,
My hunch is that you should create the index on Surname, Firstname, but
why not try it out and find out how many logical reads are required in
each case? You will know for sure which is best then.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> Citizen is not important. Surname and Name are the only ones that are
> "selective".
> Would it be better to create two indexes - one on the Name column and
> another on the Surname column, or would one index on both columns be
> better?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||On Fri, 21 Jan 2005 03:19:54 -0800, Andy wrote:

>I have a SQL Server 2000 table with the following structure:
>TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
> )
>and I use the following stored procedure to do a SELECT which I use on
>an asp page:
>CREATE PROCEDURE dbo.SelectLevel1
>AS
>BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
>END
>I'd like to add an index to the Personal table to maximize the
>performance of the SELECT statement.
>What index should I create on this table?
>My first thought was to index the Name and Surname columns, but how?
Hi Andy,
That index would only be useful if you would make it the clustered index
and if you define the columns in the order (Surname, Name) - not the other
way aroound.
If it's a nonclustered index, simply scanning the table (or clustered
index), then sorting the rows with the correct Citizen value would be
faster than using the index on (Surname, Name), so SQL Server should
choose that execution plan.
It would be useful if you'd make it into a covering index. Assuming that
UserID is the clustered index (for the PK), your best index for this
specific query would be (Citizen, Surname, Name) - and (Surname, Name,
Citizen) would also be quite good.
If most of the rows in Personal have Citizen not equal to 'Yes', then you
might also gain by using an index on Citizen only. But only if this index
would actually discard almost all rows from your table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Creating index on two columns

I have a SQL Server 2000 table with the following structure:
TABLE Personal (
UserID (PK),
Name,
Surname,
DateOfBirth,
Citizen,
BranchCode,
RegionCode,
Division
)
and I use the following stored procedure to do a SELECT which I use on
an asp page:
CREATE PROCEDURE dbo.SelectLevel1
AS
BEGIN
SET NOCOUNT ON
SELECT UserID, Name, Surname
FROM Personal
WHERE Citizen = 'Yes'
ORDER BY Surname ASC, Name ASC
END
I'd like to add an index to the Personal table to maximize the
performance of the SELECT statement.
What index should I create on this table?
My first thought was to index the Name and Surname columns, but how?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Andy,
Yes Surname and Name columns might be good ones to use. Is this a
commonly executed query? I would also look at placing an index on the
Citizen column - how selective is that column?
Your best bet is to try different indexing and look at how efficient
each of them are. Try three or four different approaches with Surname,
Name, Citizen.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> I have a SQL Server 2000 table with the following structure:
> TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
> )
> and I use the following stored procedure to do a SELECT which I use on
> an asp page:
> CREATE PROCEDURE dbo.SelectLevel1
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
> END
> I'd like to add an index to the Personal table to maximize the
> performance of the SELECT statement.
> What index should I create on this table?
> My first thought was to index the Name and Surname columns, but how?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Citizen is not important. Surname and Name are the only ones that are
"selective".
Would it be better to create two indexes - one on the Name column and
another on the Surname column, or would one index on both columns be
better?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Andy,
My hunch is that you should create the index on Surname, Firstname, but
why not try it out and find out how many logical reads are required in
each case? You will know for sure which is best then.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> Citizen is not important. Surname and Name are the only ones that are
> "selective".
> Would it be better to create two indexes - one on the Name column and
> another on the Surname column, or would one index on both columns be
> better?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||On Fri, 21 Jan 2005 03:19:54 -0800, Andy wrote:

>I have a SQL Server 2000 table with the following structure:
>TABLE Personal (
>UserID (PK),
>Name,
>Surname,
>DateOfBirth,
>Citizen,
>BranchCode,
>RegionCode,
>Division
>)
>and I use the following stored procedure to do a SELECT which I use on
>an asp page:
>CREATE PROCEDURE dbo.SelectLevel1
>AS
>BEGIN
>SET NOCOUNT ON
>SELECT UserID, Name, Surname
>FROM Personal
>WHERE Citizen = 'Yes'
>ORDER BY Surname ASC, Name ASC
>END
>I'd like to add an index to the Personal table to maximize the
>performance of the SELECT statement.
>What index should I create on this table?
>My first thought was to index the Name and Surname columns, but how?
Hi Andy,
That index would only be useful if you would make it the clustered index
and if you define the columns in the order (Surname, Name) - not the other
way aroound.
If it's a nonclustered index, simply scanning the table (or clustered
index), then sorting the rows with the correct Citizen value would be
faster than using the index on (Surname, Name), so SQL Server should
choose that execution plan.
It would be useful if you'd make it into a covering index. Assuming that
UserID is the clustered index (for the PK), your best index for this
specific query would be (Citizen, Surname, Name) - and (Surname, Name,
Citizen) would also be quite good.
If most of the rows in Personal have Citizen not equal to 'Yes', then you
might also gain by using an index on Citizen only. But only if this index
would actually discard almost all rows from your table.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Creating index on two columns

I have a SQL Server 2000 table with the following structure:
TABLE Personal (
UserID (PK),
Name,
Surname,
DateOfBirth,
Citizen,
BranchCode,
RegionCode,
Division
)
and I use the following stored procedure to do a SELECT which I use on
an asp page:
CREATE PROCEDURE dbo.SelectLevel1
AS
BEGIN
SET NOCOUNT ON
SELECT UserID, Name, Surname
FROM Personal
WHERE Citizen = 'Yes'
ORDER BY Surname ASC, Name ASC
END
I'd like to add an index to the Personal table to maximize the
performance of the SELECT statement.
What index should I create on this table?
My first thought was to index the Name and Surname columns, but how?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Andy,
Yes Surname and Name columns might be good ones to use. Is this a
commonly executed query? I would also look at placing an index on the
Citizen column - how selective is that column?
Your best bet is to try different indexing and look at how efficient
each of them are. Try three or four different approaches with Surname,
Name, Citizen.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> I have a SQL Server 2000 table with the following structure:
> TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
> )
> and I use the following stored procedure to do a SELECT which I use on
> an asp page:
> CREATE PROCEDURE dbo.SelectLevel1
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
> END
> I'd like to add an index to the Personal table to maximize the
> performance of the SELECT statement.
> What index should I create on this table?
> My first thought was to index the Name and Surname columns, but how?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||On Fri, 21 Jan 2005 03:19:54 -0800, Andy wrote:
>I have a SQL Server 2000 table with the following structure:
>TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
>)
>and I use the following stored procedure to do a SELECT which I use on
>an asp page:
>CREATE PROCEDURE dbo.SelectLevel1
>AS
>BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
>END
>I'd like to add an index to the Personal table to maximize the
>performance of the SELECT statement.
>What index should I create on this table?
>My first thought was to index the Name and Surname columns, but how?
Hi Andy,
That index would only be useful if you would make it the clustered index
and if you define the columns in the order (Surname, Name) - not the other
way aroound.
If it's a nonclustered index, simply scanning the table (or clustered
index), then sorting the rows with the correct Citizen value would be
faster than using the index on (Surname, Name), so SQL Server should
choose that execution plan.
It would be useful if you'd make it into a covering index. Assuming that
UserID is the clustered index (for the PK), your best index for this
specific query would be (Citizen, Surname, Name) - and (Surname, Name,
Citizen) would also be quite good.
If most of the rows in Personal have Citizen not equal to 'Yes', then you
might also gain by using an index on Citizen only. But only if this index
would actually discard almost all rows from your table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)