Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
I am beginner and developing a silverlight navigation application with SQL Server 2008.

I have a 'Registration' table where initially users request for membership are stored. The admins will verify them and later the particular user details are moved to 'Members' table from 'Registration'. Moreover when the term/period of membership comes to end the same details are finally moved to 'PastMembers' table from 'Members' table.

Now what I wanted is that before the registration details are submitted it should be checked in all three tables(Registration,Members,PastMembers) for username specified and if any record is found it should restrict user by submitting details to avoid duplicate userids.


I searched and tried to count the rows but even though I have a record with same userid I get count=0

Please let me know if any other better option or any rectification in this logic (please be a more brief since I am a beginner)


Checking in just first table

Code in submit button click handler
myDomainContext objctx1 = new myDomainContext();
var query1 = objctx1.GetregistrationsByIDQuery(userid_txtbx.Text);
objctx1.Load(query1);
var count = (from c in objctx1.registrations where c.userid == userid_txtbx.Text select c).Count();
// To see how many rows there
MessageBox.Show(count.ToString());
// Code for restricting details to be submitted



Function in myDomainService.cs
// Query to get usernames from registrations table
   public IQueryable<registration> GetregistrationsByID(string id)
   {
           return this.ObjectContext.registrations.Where(s => s.userid == id);
   }




consider sample fields in tables as:

Tables: Registration,Members,PastMembers having

userid,
fullname,
contact
Posted
Updated 15-Sep-12 0:30am
v2

Hi Vikram, looks like you're on the right lines. From what I can see you're kind of doing the same thing twice. There are two lines which aren't used, but maybe you've put them because they're used later?

This is the bare minimum you need to get a count:

C#
var objctx1 = new myDomainContext();
var count = (from c in objctx1.registrations where c.userid == userid_txtbx.Text select c).Count();
MessageBox.Show(count.ToString());


Or the same thing in dot syntax...
C#
var objctx1 = new myDomainContext();
int count = objctx1.registrations.Where(i => i.userid == userid_txtbx.Text).Count();
MessageBox.Show(count.ToString())


Your query1 bit is also fine. You could write a method which gives a true/false based on whether the userid is already taken like this:
C#
bool IsUserIdTaken(string userid)
{
    var objctx1 = new myDomainContext();
    bool hasReg = objctx1.registrations.Where(i => i.userid == userid_txtbx.Text).Any();
    bool hasMem = objctx1.members.Where(i => i.userid == userid_txtbx.Text).Any();
    bool hasPst = objctx1.pastmembers.Where(i => i.userid == userid_txtbx.Text).Any();
    return hasReg || hasMem || hasPst;
}


Which you can then use easily with something like:
C#
if(IsUserIdTaken(userid))
{
    // The userid already exists (it is taken)
    // Add whatever code you like here
}
else
{
    // The userid does not yet exist (it is available)
    // Add whatever code you like here
}


Hope that helps.
 
Share this answer
 
Comments
Vikram_ 17-Sep-12 1:28am    
I tried both ways but don't know why, although I have a user id in my registration table which I again try to enter in the registration page intentionally but in the function returning count it gives the count equal to zero and in the function returning Boolean value it gives false. I even tried to update the .edmx file mapping domainservice.cs, clean solution,rebuilt etc. even tried creating new solution with same thing but still I get 0(zero) or false which means I have no duplicate value in the database but there is a value in the registration table with same id which I try to enter and so the details get saved with duplicate values in the database.
Adam David Hill 17-Sep-12 4:10am    
That's very strange. It shouldn't be the code above that's the problem, at least. Can you try testing it with a hard-coded string in both the app and SQL Management Studio? Try a SQL query like "SELECT * FROM registration WHERE userid = 'writeYourIdHere' ", and then also hard-code the string in the C# code above, replacing the userid_txtbx.Text part with "writeYourIdHere". Does one work and not the other? If neither work then try examining the userid for hidden characters.
Vikram_ 17-Sep-12 7:28am    
In SQL Management Studio I get those records with provided userid. But in Visual Studio even after hard-coded userid it shows there is no duplicate record found (i.e it returns false in your method provided IsUserIdTaken()) and adds that record in database resulting a duplicate userid in the database.
Adam David Hill 17-Sep-12 7:40am    
Okay, just to make 100% sure - are you connected to the same database instance you think you are? Can you select anything at all from this database? If so can you manipulate some test values directly in the database and see the changes in VS?
Vikram_ 17-Sep-12 7:56am    
I am having another page where I dropped a datagrid and in page constructor after the InitializeComponent();

as

var objctx = new myDomainContext();
EntityQuery<registration> query = objctx.GetRegistrationsQuery();

LoadOperation<registration> loadOp = this.objctx.Load(query);
mydatagrid.ItemsSource = loadOp.Entities;

and that works perfectly fine, showing all records in my database (specifically the entire registration table which is having all those duplicate values)

Hope that is enough to be sure that same database instance is connnected. I also did the modification in the database directly and checked the changes in VS its all as expected reflecting all those changes.
Ok I am giving the step-by-step process I performed for a demo silverlight navigation application which I created where I face similar issue.



1) Created a silverlight navigation application using VS 2010.

Name: Membership

http://i48.tinypic.com/21ep0d2.png[^]


2) In SQL Server 2008 Managment Studio I created a database named as "mydb"

Now in the same database I add three tables : registrations, members, pastmembers

Here is the SQL script for each one



-SQL Script for registration is as follows:
SQL
USE [mydb]
GO

/****** Object:  Table [dbo].[registrations] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[registrations](
	[userid] [nvarchar](50) NOT NULL,
	[fullname] [nvarchar](50) NOT NULL,
	[contact] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_registration] PRIMARY KEY CLUSTERED 
(
	[userid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO




-SQL Script for members is as follows:
SQL
USE [mydb]
GO

/****** Object:  Table [dbo].[members] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[members](
	[userid] [nvarchar](50) NOT NULL,
	[fullname] [nvarchar](50) NOT NULL,
	[contact] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_members] PRIMARY KEY CLUSTERED 
(
	[userid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO




-SQL Script for pastmembers is as follows:
SQL
USE [mydb]
GO

/****** Object:  Table [dbo].[pastmembers]  ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[pastmembers](
	[userid] [nvarchar](50) NOT NULL,
	[fullname] [nvarchar](50) NOT NULL,
	[contact] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_pastmembers] PRIMARY KEY CLUSTERED 
(
	[userid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO




3) Now I will just add a row in members table intentionally using same SQL Server Management Studio so that I can demonstrate the issue with only one page that I create in application.

Ok so the new row in members table is as:

userid fullname contact
abc@abc.com Mr. ABC 9876543210



4) Coming back to VS 2010. Now I add the ADO.NET Entity Data Model. Name: mydbModel.edmx

http://i49.tinypic.com/nohgn7.png[^]



Select all entities and enable editing.
http://i45.tinypic.com/n6zuc9.png[^]



Select Generate from database option from Entity Data Model Wizard
and click Next in the wizard.

I then selected the database from new connection and gave test connection for confirming the connectivity and proceed to Next step in wizard.

http://i45.tinypic.com/21cdsu1.png[^]

I selected all tables and clicked on Finish and gave build process to my application to work fine for next step.

5) Next I add DomainService. Name: MembershipDomainService.cs

http://i45.tinypic.com/11uyp0j.png[^]

6) Next I remove existing contents and add new controls to Home page available in Views folder.

So here is the xaml code of all those controls

HTML
<grid x:name="LayoutRoot" xmlns:x="#unknown">        
        <canvas name="canvas1">
            <textbox canvas.left="297" canvas.top="100" height="23" name="userid_txtbx" width="120" text="" />
            <textbox canvas.left="297" canvas.top="143" height="23" name="fullname_txtbx" width="120" />
            <textbox canvas.left="297" canvas.top="186" height="23" name="contact_txtbx" width="120" />
            <textblock canvas.left="215" canvas.top="104" height="23" name="textBlock1" text="User ID" />
            <textblock canvas.left="215" canvas.top="147" height="23" name="textBlock2" text="Full Name" />
            <textblock canvas.left="215" canvas.top="190" height="23" name="textBlock3" text="Contact" />
            <Button Canvas.Left="280" Canvas.Top="268" Content="Submit" Height="23" Name="submit_btn" Width="75" Click="submit_btn_Click" />
        </canvas>
    </grid>



Now giving a build process to application and adding code in the Home.xaml.cs file

adding namespace initially:
C#
using Membership.Web;



adding a function:
C#
private bool IsUserIdTaken(string userid)
        {
            var objctx1 = new MembershipDomainContext();
            bool hasReg = objctx1.registrations.Where(i => i.userid == userid_txtbx.Text).Any();
            bool hasMem = objctx1.members.Where(i => i.userid == userid_txtbx.Text).Any();
            bool hasPst = objctx1.pastmembers.Where(i => i.userid == userid_txtbx.Text).Any();
            if (hasReg == true)
            {
                return hasReg;
            }
            else if (hasMem == true)
            {
                return hasMem;
            }
            else
            {
                return hasPst;
            }
        }



Finally adding the submit button handler:
C#
private void submit_btn_Click(object sender, RoutedEventArgs e)
        {
            var objctx1 = new MembershipDomainContext();
            var count = (from c in objctx1.members where c.userid == userid_txtbx.Text select c).Count();
            // To see how many rows there (It shows zero always even we have record with userid as 'abc@abc.com' )
            MessageBox.Show(count.ToString());

            if (IsUserIdTaken(userid_txtbx.Text.Trim()) == false)
            {
                registration r = new registration();
                r.userid = userid_txtbx.Text.Trim();
                r.fullname = fullname_txtbx.Text.Trim();
                r.contact = contact_txtbx.Text.Trim();

                var objctx = new MembershipDomainContext();

                objctx.registrations.Add(r);
                try
                {
                    objctx.SubmitChanges();
                    MessageBox.Show("Records successfully added!");
                }
                catch (Exception ex)
                {
                    //MessageBox.Show(ex.ToString());
                    MessageBox.Show("Unexpected error occured! Please try again.");
                }

            }
        }


Now Run application and try entering same userid i.e "abc@abc.com" and other details and click submit. Record is saved even duplicate value available in members table.
 
Share this answer
 
v3
Comments
Adam David Hill 20-Sep-12 11:42am    
Hi Vikram, not that it will likely make a difference, but there's a small slip in that function which was my fault. Use this tweaked version to use the parameter rather than directly referencing the text box...

bool IsUserIdTaken(string userid)
{
var objctx1 = new myDomainContext();
bool hasReg = objctx1.registrations.Where(i => i.userid == userid).Any();
bool hasMem = objctx1.members.Where(i => i.userid == userid).Any();
bool hasPst = objctx1.pastmembers.Where(i => i.userid == userid).Any();
return hasReg || hasMem || hasPst;
}
Adam David Hill 20-Sep-12 11:51am    
Also you're inconsistently using .Trim() in one place where you reference the textbox's text and not the other. For safety, read the text into a string variable at the beginning of the method, trim it there if desired, and subsequently only refer to the variable in that method.
Vikram_ 28-Sep-12 1:53am    
Even with this code the same thing happens just try to use this piece of code in the above step-by-step described application. You may find same issue (0) zero records found and later data gets added with duplicate id.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900