Page 1 of 1
Using existing table for simplemembership
Posted: Mon 02 Dec 2013 02:22
by garbgdev
I have simplemembership for postgres on my application and it seems connection between application and postgres works fine. However, I want to use existing userTable with simplemembership provided by dotconnect.
I have following line to connect simplemembership with my existing table.
Code: Select all
WebSecurity.InitializeDatabaseConnection("DefaultConnectionPostGres", "Contacts", "Id", "UserName", autoCreateTables: true);
This line works without a problem if I change my connection string to localsql server. But If I change my connection string to postgres server, it automatically generates a table called 'contacts', not the "Contacts".
So I changed "Contacts" to "contacts" like below line.
Code: Select all
WebSecurity.InitializeDatabaseConnection("DefaultConnectionPostGres", "contacts", "Id", "UserName", autoCreateTables: true);
now, It returining error that says
Code: Select all
Devart.Data.PostgreSql.PgSqlException: column "id" does not exist
followings are query for both table. Please help me with my problem.
Code: Select all
CREATE TABLE "Contacts"
(
"Id" serial NOT NULL,
"UserName" character varying NOT NULL,
"Age" integer,
CONSTRAINT "PK_Contacts" PRIMARY KEY ("Id")
)
CREATE TABLE contacts
(
id serial NOT NULL,
username character varying(240) NOT NULL,
CONSTRAINT contacts_pkey PRIMARY KEY (id),
CONSTRAINT contacts_username_key UNIQUE (username)
)
Re: Using existing table for simplemembership
Posted: Mon 02 Dec 2013 17:40
by Shalex
This table exists in your PostgreSQL database, doesn't it?
Code: Select all
CREATE TABLE contacts
(
id serial NOT NULL,
username character varying(240) NOT NULL,
CONSTRAINT contacts_pkey PRIMARY KEY (id),
CONSTRAINT contacts_username_key UNIQUE (username)
)
Please run this code with
enabled dbMonitor tool:
Code: Select all
new Devart.Data.PostgreSql.PgSqlMonitor() { IsActive = true };
WebSecurity.InitializeDatabaseConnection("DefaultConnectionPostGres", "contacts", "Id", "UserName", autoCreateTables: true);
Specify the exact SQL query which fails with the "Devart.Data.PostgreSql.PgSqlException: column "id" does not exist" error.
Re: Using existing table for simplemembership
Posted: Mon 02 Dec 2013 20:27
by garbgdev
Code: Select all
CREATE TABLE "Contacts"
(
"Id" serial NOT NULL,
"UserName" character varying NOT NULL,
"Age" integer,
CONSTRAINT "PK_Contacts" PRIMARY KEY ("Id")
)
above table is existing table that I want to cope with simplemembership.
Code: Select all
CREATE TABLE contacts
(
id serial NOT NULL,
username character varying(240) NOT NULL,
CONSTRAINT contacts_pkey PRIMARY KEY (id),
CONSTRAINT contacts_username_key UNIQUE (username)
)
and this table is generated by WebSecurity.InitializeDatabaseConnection() which means it does not found existing table.
However the weird part is that generated table has same name as existing table but it has lowercase table name. Thus I changed existing table name to lowercase for the test reason. This occurring column "id" does not exist" error. And I really don't want to change my table name to lowercase. I'm lost now, any suggestions?
Re: Using existing table for simplemembership
Posted: Tue 03 Dec 2013 17:35
by Shalex
garbgdev wrote:generated table has same name as existing table but it has lowercase table name
Lowercase is a default case in PostgreSQL Server. So you should quote the names of your table and its columns:
Code: Select all
new Devart.Data.PostgreSql.PgSqlMonitor() { IsActive = true };
WebSecurity.InitializeDatabaseConnection("DefaultConnectionPostGres", "\"Contacts\"", "\"Id\"", "\"UserName\"", autoCreateTables: true);
If this doesn't help, specify the text of the error and the exact SQL statement which fails to execute (for this, check
dbMonitor logs).
Re: Using existing table for simplemembership
Posted: Tue 03 Dec 2013 20:17
by garbgdev
Thanks for your respond. I've apply above code to my InitializeDatabaseConnection() method.
Now I have following error.
Code: Select all
Exception Details: Devart.Data.PostgreSql.PgSqlException: relation "Contacts" already exists
And from the dbMonitor error is detected from following line.
Code: Select all
CREATE TABLE "Contacts" ("Id" SERIAL, "UserName" VARCHAR(240) NOT NULL UNIQUE, PRIMARY KEY ("Id") );
As I know if table is already exists, InitializeDatabaseConnection() does not create a new table. But it seems not from above result.
So I have change autoCreateTables: true to autoCreateTables: false.
Then I have this error.
Code: Select all
Devart.Data.PostgreSql.PgSqlException: relation "webpages_membership" does not exist
thanks in advance for your help
Re: Using existing table for simplemembership
Posted: Tue 03 Dec 2013 21:14
by garbgdev
I finally manage to work it. But it leads to another problem.
When I register a new user, I wanted to add a value to my column "Age" with createUserAndAccount() method. "Age" column is already existing in my Contacts table.
Code: Select all
WebSecurity.CreateUserAndAccount(model.UserName, model.Password, new { Age = 21});
However I'm getting following error.
Code: Select all
System.Configuration.Provider.ProviderException: Invalid column name Age
Re: Using existing table for simplemembership
Posted: Fri 06 Dec 2013 17:16
by Shalex
We are investigating the issue. We will notify you about the result as soon as possible.
Re: Using existing table for simplemembership
Posted: Wed 11 Dec 2013 13:28
by Shalex
The bug with inserting quoted user-defined fields via WebSecurity.CreateUserAndAccount method of SimpleMembership provider is fixed. We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.
Re: Using existing table for simplemembership
Posted: Thu 12 Dec 2013 14:07
by Shalex
New build of dotConnect for PostgreSQL 7.2.55 is available for download!
It can be downloaded from
http://www.devart.com/dotconnect/postgr ... nload.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to
http://forums.devart.com/viewtopic.php?f=3&t=28503.