Click here to Skip to main content
11,495,331 members (65,856 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Silverlight C#4.0 .NET4
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 15-Sep-12 1:24am
Vikram_622
Edited 15-Sep-12 1:30am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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:

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...
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:
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:
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.
  Permalink  
Comments
Vikram_ at 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.
AdamDavidHill at 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_ at 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.
AdamDavidHill at 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_ at 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 query = objctx.GetRegistrationsQuery();

LoadOperation 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.
AdamDavidHill at 17-Sep-12 8:47am
   
Well, as frustrating as this is, at least that is a good sign!

I guess the next step is try a hardcoded userid comparison again but in that new page (in case there's something on your other page which is interfering). Hardcode a single id in your C# code for now so you know 100% it's not because it can't read the text out of a control or something. Something like:

var query1 = objctx.registrations.ToList();
var query2 = objctx.registrations.Where(i => i.userid == 'myUserId');
var query3 = objctx.registrations.Where(i => i.userid == 'myUserId').Count();

Stick a breakpoint on the next line and each version of "query" to see if there are any results / what they are.
Vikram_ at 17-Sep-12 9:21am
   
I have added the code in the page but since its in View folder which is not under myproject.web node but under other myproject node(having folders like Assets,Views) so may be breakpoint doesn't work there the way breakpoint in javascript in asp.net

what I did is added a messagebox displaying value and its shows the count 0(zero) for query3 which you provided.

I added your code just below where I bind my datagrid in constructor as:


InitializeComponent();

var objctx = new myDomainContext();
EntityQuery query = objctx.GetRegistrationsQuery();
LoadOperation loadOp = this.objctx.Load(query);
mydatagrid.ItemsSource = loadOp.Entities;

// code provided by you begins below

var query1 = objctx.registrations.ToList();


foreach (registration reg in query1)
{
MessageBox.Show(reg.userid); // Is never called.
}

var query2 = objctx.registrations.Where(i => i.userid == "abc@abc.com");
var query3 = objctx.registrations.Where(i => i.userid == "abc@abc.com").Count();
MessageBox.Show(query3.ToString()); // This shows 0(zero)
Vikram_ at 17-Sep-12 9:42am
   
One thing I don't understand why the datagrid is able to show all records where as these queries unable to find a single row
I even tried adding the following code after the above code:

MessageBox.Show(objctx.registrations.ToList().Count.ToString());

this shows me 0(zero) which is really very strange!
AdamDavidHill at 17-Sep-12 10:08am
   
I can't see it'll make much of a difference but you could try executing that code in the Loaded event rather than in the constructor.

I was beginning to suspect it was a mismatch in character sets or something like that, but this line doesn't rely on the where clause:

MessageBox.Show(objctx.registrations.ToList().Count.ToString());

Unfortunately I think I'd pretty much need to see the rest of the project to help any further. If you can't debug your View, then can you not put the code to examine the data somewhere you can? If that's View as in MVC or MVVM then there probably shouldn't be database code in there in the first place.

If I think of anything else I'll let you know.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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:
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:
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:
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

<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:
using Membership.Web;


adding a function:
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:
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.
  Permalink  
v3
Comments
AdamDavidHill at 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;
}
AdamDavidHill at 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_ at 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)

  Print Answers RSS
0 Dnyaneshwar@Pune 692
1 CHill60 318
2 Sascha Lefèvre 275
3 Sergey Alexandrovich Kryukov 255
4 Nakul Chaudhari 236
0 Sergey Alexandrovich Kryukov 10,307
1 OriginalGriff 8,821
2 Sascha Lefèvre 3,889
3 Maciej Los 3,412
4 Richard Deeming 2,560


Advertise | Privacy | Mobile
Web04 | 2.8.150520.1 | Last Updated 18 Sep 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100