Accepted Practice To Query The Built-in Aspnet Membership Tables ?
Jul 17, 2007
I'm a relative newbie to ASP.NET development (web development in general) so please forgive the ignorance. ;-)
Ok, I'm using VWD to design and test my application and I'm using the standard membership provider system. I've read in various places not to muck around with the aspnet_* tables as it can cause havoc hehe. Therefore, I've created my own Member table to accommodate more fields that the aspnet_Users table does not have (since I don't want to mess around with that table) such as address, city, town, zip, birth date, etc. Obviously, I have a field in Member that corresponds to the userId primary key in aspnet_Users. Now, there are times when I need to display the username of a person's profile on a page.
SELECT aspnet_Users.UserName<br>FROM aspnet_Users, Member<br>WHERE querystringid=Member.memberId AND Member.userId = aspnet_Users.UserId
Now, this seems fine and dandy when there are few entries in the aspnet_Users table, but what if I tried that query when aspnet_Users grew to a hypothetical one million entries?? Does anyone know if those fields in aspnet_Users are properly indexed? I can't seem to determine one way or another with VWD's database explorer. I don't want to run the risk of a full table scan. But if those fields aren't indexed, I don't know if I should modify that table in any way.
What to do?
TIA
View 4 Replies
ADVERTISEMENT
Mar 15, 2008
I know this may sound like a silly question but, my project currently uses two databases, the aspnet membership db and our application db. What I'm wanting to know is if there is a way to combine them without lossing any current functionality from either one? Also what if any gotchas there might be?
View 5 Replies
View Related
May 7, 2008
Hello everyone! Following are the things that I have to do :1) Create a "Friendlist" of every user registered. ( one user can have many friends)2) Create "Communities" which can have various users registered to them. How do I go about doing this in ASP.net..?! Please help!
View 10 Replies
View Related
May 26, 2007
Hi, I have limited control on the server I use. I can not create data bases but can add tables and edit the data base that has been created for me. So what I would like to do is be able to use a tool such as aspnet_regsql through SQL Server Express to add the tables that Membership would automatically create. Any help in pointing me in the right direction would be great
Thanks
howlinhuskie
View 6 Replies
View Related
Nov 15, 2007
Hi, this might be a newbie question as I don't know too much about databases.My web host only lets me have 1 database, so I am trying to rename all the tables with a prefix, then import them to the server. I'm using MsSql-2005, VWD, Management Studio Express.My error when I run the .sql script is:Msg 208, Level 16, State 1, Procedure vw_aspnet_MembershipUsers, Line 3Invalid object name 'dbo.aspnet_Membership'.Msg 208, Level 16, State 1, Procedure vw_aspnet_Users, Line 3Invalid object name 'dbo.aspnet_Users'.Msg 208, Level 16, State 1, Procedure vw_aspnet_Profiles, Line 3Invalid object name 'dbo.aspnet_Profile'....I suspect that it is because I renamed the "aspnet_Membership" table to "Company1_aspnet_Membership" (I made a database diagram, then renamed the aspnet tables in Properties). Has anyone run into this before? Does anyone know how to fix it?
View 5 Replies
View Related
Jan 15, 2006
I have a website that uses an SQL 2005 Express database, with the added aspnet_users, aspnet_roles tables etc' inside it.
(That is not an .mdf file, but in the databse itself)
Are there any guidelines for moving this kind of database to the deployment server, which also has SQL Express ?
Is it just a case of detach + attach ?
View 1 Replies
View Related
Oct 31, 2007
Kimberly Tripp describes a recipe for switching partitions in and out, thru the use of staging tables, when it comes time to "slide the window" on a partitioned table. She says that the clustered index (on staging) must be the same as that chosen for the partitioned table itself but she doesnt discuss whether or not all of the non clustered indexes need to be the same too once the
ALTER TABLE Orders
SWITCH PARTITION 1
TO OrdersOctober2002
and
ALTER TABLE OrdersOctober2004
SWITCH TO Orders PARTITION 24
run. For the data being switched out, I wouldnt want to do anything extra. For the data being switched in, I'd like to understand if she is implying that all other indexes would be built automatically as a result of the 2nd ALTER statement?
Kimberly's article is at http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm#_Toc79339965
View 1 Replies
View Related
Apr 10, 2007
Hi, all experts here,
Thank you very much for your kind attention.
I am having some questions on indexed views and aggregate tables.
My question is: To improve the performance of the queries, is it better to use indexted views or aggregates tables for those aggregates which are often queried?
I am looking forward to hearing from you.
Thank you very much in advance for your help.
With best regards,
Yours sincerely,
View 6 Replies
View Related
Sep 12, 2007
Dear Friends,
I am working on search customer information page.
I have 5 search options,
Name,
Email,
Order Number,
Product Name,
Order Date
I am using check boxes, I need to allow admin to enter above information and click on search,
How will I make my query and sends to DB server to pull up records which satisfies where clause:
For example,
Select * from orders where Email = #Email#
This is simple, but I can not hardcode all queries, I don€™t know in advance what different search option ADMIN may choose.
Any suggestion for logic or query make up,
Thanks,
Fahim.
View 1 Replies
View Related
Apr 26, 2008
Classic problem..
Say I have two simple tables (one with members the other with status
changes):
CREATE TABLE [dbo].[member](
[memkey] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mname] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[state] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dob] [datetime] NULL,
[effective] [datetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[memberstatuschange](
[memkey] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[transtype] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[date] [datetime] NOT NULL
) ON [PRIMARY]
sorry missing referential for simple example.
select m.memkey,m.effective,s.transtype, s.date from member m left
join memberstatuschange s
on m.memkey = s.memkey
produces:
001 2006-11-01 00:00:00.000 1 2007-12-01 00:00:00.000
001 2006-11-01 00:00:00.000 2 2007-02-02 00:00:00.000
001 2006-11-01 00:00:00.000 1 2007-02-05 00:00:00.000
002 2006-11-02 00:00:00.000 1 2007-06-01 00:00:00.000
003 2006-11-03 00:00:00.000 1 2007-07-01 00:00:00.000
003 2006-11-03 00:00:00.000 2 2007-08-02 00:00:00.000
004 2006-01-01 00:00:00.000 NULL NULL
005 2007-01-01 00:00:00.000 NULL NULL
006 2007-02-05 00:00:00.000 NULL NULL
transtype 1 = stop, 2 = restart.
effective date marks the start the original start of membership. So
in the case of memkey 001 he started on 2006-11-01, stopped on
2007-12-01, restarted on 2007-02-02 and stopped again on 2/5/2007.
memkeys 004,005 and 006 don't have NO status changes.
presuming that we should never have a status code of restart without a
stop but that the data may have violations of that.
How can I query my tables to return every date of ACTIVE participation
during two dates?
So for example if I say give participations criterias of 11/7/2006
and 2/7/2007, it should pull two records for memkey 001 as follows
memkey, days, start, stop
001, 6, 11/1/2006, 11/7/2006
001, 2, 2/5/2007, 2/7/2007
Thank you for any help or information!!.
View 1 Replies
View Related
Aug 2, 2007
Hi,
I am trying to filter data within my report by a date range (FromDate - ToDate), which is using a cube as a datasource.
My Issue:
I have the filtering working ok but if i select a date which is outside the range of the data within my cube for example if i select the starting date for the range as 1/Jan/1965 but by data starts from 15/Jan/1965 then no data is returned.
Within the MDX query within the STRTOSET function i am using 'constrained' which is around the date parameter i.e. StartDate for Range.
My question is has anyone or is it possible to use date values outside of the range of the data within my cube and get a correct dataset returned. If so could you please explain how with an example.
Many Thanks
View 2 Replies
View Related
Feb 18, 2008
I have VS2008 Express and I am using Sql Server Compact Edition 3.5 which is distributed with it.
As I experienced, there is no possibility to use this database from Sql Server Management Studio (because it is version 3.5 and Management Studio demands version 3.1).
So, the only possibility to design a query is to use the built in Query Builder in Visual Studio Express. This query builder does not save the Diagram Pane content layout. I mean, when a query has 10 tables, I place these tables in the Diagram Pane in some order that makes sense (e.g. to make all relations visible). After saving and reopening the query in Query Builder, all tables are on the left side, in a complete disorder.
Is it possible to somehow save the Diagram Pane content along with the SQL ??
View 1 Replies
View Related
Mar 11, 2008
I am used to run SQL server 2005 RDBMS with Visual studio 205 for development. recently, i wanted to try it with the inbuilt sql server express and being able to run the membership query / membership functionality. the proble is that i couldn't run the query using 'aspnet_regsql' from command prompt. It always throw error. please, can anybody help me out. Any suggestion will be highly appreciated and welcome. Kayode Banjo
View 2 Replies
View Related
Apr 2, 2008
We have a sort of complex user structure in the sense that depending on the type of user the data resides in different tables. Therefor I needed a stored procedure that finds out what table to look for a certain column in. Below is such a stored procedure and it works like it should but my problem is that I don't know how to retrieve the result (which should be a string so can't use RETURN).
I've tried using an OUTPUT variable but since I just run EXEC (@statement) in the end I can't really set an output variable the common way (as in EXEC @outputVariable = PMC_User_GetUserValue(arg1, arg2..)) or can I?
I have also tried to use SELECT to catch the result somehow but no luck and Google didn't help either so now I'm hoping for one of you... Notice that you don't have to bother about much of the code except for the end of it where I want it to return somehow or figure out a way to call this stored procedure and retrieve the result.
Thanks in advance
ripern
-- Retrieves the value of column @columnName for credential id @credID
ALTER PROCEDURE [dbo].[PMC_User_GetUserValue]
@credID int,
@columnName nvarchar(50)
AS
DECLARE @userDataTable nvarchar(50)
DECLARE @userDataID int
DECLARE @statement nvarchar(500)
SET @statement = ' '
SET @userDataID =
(SELECT PMC_UserMapping.fk_userDataID
FROM PMC_UserMapping
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)
SET @userDataTable =
(SELECT PMC_UserType.userDataTable
FROM PMC_UserType
INNER JOIN PMC_UserMapping ON PMC_UserType.id = PMC_UserMapping.fk_usertype_id
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)
SET @statement = 'SELECT ' + @columnName + ' AS columnValue FROM ' + @userDataTable + ' WHERE id=' + convert(nvarchar, @userDataID)
-- Checks whether the given column name exists in the user data table for the given credential id.
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@userDataTable AND COLUMN_NAME=@columnName )
BEGIN
EXEC (@statement)
END
View 12 Replies
View Related
Oct 25, 2007
I have been trying to open a job properties and then step where its executing sql server 2005 integration services package and when I try to look at the configuration of that package in the job step properties window it asks for the password used to secure the package configuration and even on entering right password its not accpted. Is it a bug or what?
View 2 Replies
View Related
Mar 4, 2008
Measurement min ave max
% Disk Time (PhysicalDisk _Total): 0.053 1.863 63.109
% Processor Time (Processor _Total): 0.0 37.543 98.84
Average Latch Wait Time (ms) (SQLServer|Latches): 0.0 1.537 16
Average Wait Time (ms) (SQLServer|Locks _Total): 0.0 0.193 16
Current Disk Queue Length (PhysicalDisk _Total): 0.0 0.365 67
Log Growths (SQLServer|Databases _Total): 0.0 0.0 0.0
Page Faults/sec (Memory): 9.206 172.064 8612.257
Pages/sec (Memory): 0.0 7.313 815.411
Pool Nonpaged Bytes (Memory): 38195200 38352765.277 38547456
Private Bytes (Process _Total): 1516933120 1663239600.632 1785290752
Processor Queue Length (System): 0.0 0.489 17
Threads (Objects): 922 938.353 950
can anyone tell me what is the accepted value for performance on sql server 2005
this report is generated from mercury load runner
View 1 Replies
View Related
May 7, 2008
In my site, when a user registers, I need to create rows in additional tables besides aspnet_Users. So, I need to be able to pass the generated userId guid to subsequent SqlCommands. I'm having a terrible time with this. What's the correct way to set up a SqlParameter so that it will accept a guid? I keep getting this error: "Conversion failed when converting from a character string to uniqueidentifier."
I've tried creating the parameter both with and without a SqlDbType.
cmd.Parameters.AddWithValue(paramName, guid);
and
SqlParameter p = new SqlParameter(paramName);p.SqlDbType = SqlDbType.Guid;cmd.Parameters.Add(p);
and I get the same error either way.
Driving me nuts! Any help appreciated.
View 1 Replies
View Related
Aug 8, 2015
just trying to run the install and cant get passed the service account page as unsure of the details.
im using the Clustered Instance lab/guide where it says that the account details are as follows:
SQL2K8WKSSQL2K8R2SVC for database agent
SQL2K8WKSSQLAgent2K8R2SVC for agent
but get errors when use these.
View 3 Replies
View Related
Dec 13, 2005
Hi,
I tried to use the RETURN_VALUE of a stored procedure in updata / delete commands with a sql-datasource and a stored procedure.
The thing works fine with the insert command.But it fails with "too many arguments..." in delete / update.
I also tried to handle the updating event like this:
protected void sqldsReportSelect_Updating(object sender, SqlDataSourceCommandEventArgs e) { SqlParameter sqP = new SqlParameter("RETURN_VALUE", SqlDbType.Int); sqP.Direction = ParameterDirection.ReturnValue; e.Command.Parameters.Add(sqP);}
It brings the same error.
Do I make something wrong or is this a bug?
Regards
Manfred
View 2 Replies
View Related
Jan 28, 2015
I have 2 tables:
tbl1: InstituteID, name
tbl2 ApplicantID, InstituteID ,Applicant_Name, address, city, state, status *
* Status field contains value A for acceptance or R for rejected
listing Institute names where more than half of applicants were accepted.
View 2 Replies
View Related
Jan 21, 2008
I'm using an ObjectDataSource in Visual Studio to retrieve records from a SQL Server 2005 database.
I have a very simple dilemma. In a table I have fields FirstName, Surname, Address1, Address2, Address3 etc. None of these are mandatory fields.
It is quite common for the user not to enter data in Address2, Address3, so the values are <null> in the SQL table.
In Visual Studio 2005 I have an aspx form where users can pass search parameters to the ObjectDataSource and the results are returned according to the passed in parameters.
The WHERE clause in my Table Adapter is:WHERE (Address1 LIKE @Address1 + '%') AND (Address2 LIKE @Address2 + '%') AND (Address3 LIKE @Address3 + '%') AND (FirstName LIKE @FirstName + '%') AND (Surname LIKE @Surname + '%')
If, for example, I simply want to search WHERE FirstName LIKE ‘R’, this does not return any results if the value of Address3 is <null>
My query is this: Could someone please show me the best way in Visual Studio 2005 to return records even if one of the Address fields is <null>.
For reference, I have tried: Address3 LIKE @Address3 + '%' OR IS NULLThis does work, however itsimply returns every instance where Address3 is <null> (accounting for about 95% of the records in the database). Thanks in advance Simon
View 9 Replies
View Related
Jan 25, 2005
Hi
When I am trying to connect to MS Sql Server 2000 through Asp.net using C# , I am getting the error : “ Login Failed for HTASPNET �. Although I have added permission for this account to access the database . I would be very grateful to you if you could kindly explain to me what is the problem. Thank You.
MessageConnection = new SqlConnection("server=HT; Integrated Security = SSPI; Database=Forums");
.
.
MessageConnection.Open();
.
.
View 1 Replies
View Related
Jul 26, 2001
I recently came across this built-in role in MSDB. It is not documented anywhere except sp_create role stuff. Does anyone know what this role is used for? Is this what gives users that are not dbo or sa access to see jobs?
View 1 Replies
View Related
Aug 15, 2007
Hi,
Is there some kind of built in function in SQl to look for the presence of certain characters in a table collumn.I have to replace the text in those collumns with different text or just use substr, instr functions.
Thanks in advance
View 4 Replies
View Related
Jun 13, 2006
I have built a simple package using the Microsoft OLE DB provider for DB2 with SSIS Developer. If I schedule this on a Standard SQL2005 machine the package will not run. The error is blank, just says it had an error. Can I not run this package because the provider is not installed on this machine? If so is there a work around for this?
View 2 Replies
View Related
Mar 24, 2008
I installed SQL Server 2005 and Visual Studio 2005 and have discovered that the ASPNET machine account was not added as a user when going into "Computer Management". I obviously need this to run ASP.net apps.
I tried going to the Administrators group to add user ASPNET, but the system can't find this user.
How can I install the ASPNET account?
makimix
View 3 Replies
View Related
Jun 10, 2004
I just moved my website to a shared host, and I face some trouble.
Since there is more than one DB on the SQL server they don't want to create a ASPNET user. Is there a way to make my SQLCommand use my user name instead of ASPNET ?
Otherwise, can anyone explain to me the result of the ASPNET user on other website who will use the same server as me security wise ?
View 2 Replies
View Related
Mar 1, 2006
I seem to remember that when using VS2003 to create a website which connected to a MSDE database, I needed to explicitly grant access to the database for ASPNET machine account using the following SQL commands from within a .sql script:
EXEC sp_grantlogin '<machine>ASPNET'EXEC sp_grantdbaccess '<machine>ASPNET'
With VS2005, it sppears that upon creating an .MDF database in the App_Data folder this is no longer necessary.
I'd be interested to know why this is so. Does VS2005 automatically do this when the database is created?
If anybody could shed some light on this I'd be interested.
Thanks,Wayne.
View 2 Replies
View Related
Aug 21, 2000
Does anyone know if SQL has a built in function for returning increments of rows. For instance; I have a table with 100,000 rows, but would like to return 512 rows at a time. I've looked at the documentation and haven't came across anything that will do this. Any help is appreciated.
View 4 Replies
View Related
Jan 28, 2004
Hi.
I am a beginner of SQL server.
I have a table namely Customer with a column "CustomerName" with data type varchar and length 50.
Column Name-Customer Name
Data Type- varchar
Length- 50.
May I know how to ENSURE that every customer's name only contain ALPHABET and strictly avoid the numeric character and
other characters such as "&, !,#....".
Please help. Thank you.
View 6 Replies
View Related
Jun 20, 2008
Hi,
I have created one login with windows authentication.
after that i have deleted the built-in-administrator login.
now i am not able to login thru windows authentication mode.
how to rectify it..
i have tried to create one new login with windows authentication mode..still i am not able to connect thru windows authentication.
View 5 Replies
View Related
Dec 28, 2006
Hi,
where can i find the file SQLServer2005_load.sql? I would like to copy the whole script from that file.I am using MS SQL server 2005. please help me.
View 1 Replies
View Related
Apr 12, 2007
I have followed the steps outlined in the knowledge base article http://support.microsoft.com/kb/913668 for effecting Xml Serialization within the SQL CLR. That is, I have
1. Prebuilt the serialization assembly X.Serializers for the types in assembly X and,
2. Registered both assemblies with SQL Server via the create assembly directive
Yet, when I attempt to create an XmlSerializer on the basis of one of the types defined in X, SQL CLR ignores the pre-built serialization assembly and attempts to dynamically create/load the assembly. Since dynamic loading is disallowed, this fails with the expected exception:
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(Com
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at Cypress.Services.Client..
What do I need to do the force the runtime to load the pre-built serialization assembly instead of dynamically trying to create one?
Thank You,
Chris.
View 11 Replies
View Related
