check if record exists in Databse and prevent entry [Resolved]

var existingUserCount = db.customers.Count(x =>x.Firstname == Firstname);
if (existingUserCount == 0) 
{
    // Do your insert
try
 {
 Customer newCustomer = new customer();

 newCustomer .Firstname = Firstname;
 newCustomer.Telephone = Telephone;
 newCustomer.Location = Location;
db.Customers.InsertOnSubmit(newCustomer);
 db.SubmitChanges();


 return newCustomer;
 }
 catch (Exception)
 {

 throw;
 }
}
else
{
// here is the code to get to know the Username is already exist  in the database
return null;
}

if the CreateUser return value is null then you should get to know as ALREADY exists

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

Mark as Answer if you got solution from the above reply; otherwise post it back

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

SQL Server script to check for record existence

If EXISTS ( SELECT UserName FROM tableName WHERE UserName = @PassedUserName )
BEGIN 
   SELECT 'Record EXIST'
END
ELSE
BEGIN
  INSERT INTO TableName ...
END

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

write stored procedure and then call this in program to insert user details to database

CREATE PROCEDURE USP_Insert (@UserName VARCHAR(40))
AS 
BEGIN
If NOT EXISTS ( SELECT UserName FROM tableName WHERE UserName = @UserName ) 
BEGIN 
  INSERT INTO TableName VALUES(@UserName) 
END 
ELSE 
BEGIN 
 SELECT 'Record EXIST' 
END  
END 

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

On the Begin part is where i can av the rest of code to insert my data?

Mark as answer if satisfied

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

No,have not understood this code well ..Kindly clarify further.Thanks

Mark as answer if satisfied

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

can you post the table structure of Users ?

I have given you the stored procedure USP_Insert .. This is for inserting new user details to database. if already username exists in the table then it will give u message "Record EXIST ".
You should call this procedure in your asp code by passing required parameters such as username and so on...

http://stackoverflow.com/questions/17903092/check-if-record-in-a-table-exist-in-a-database-through-executenonquery

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

Refer this link for detailed description
http://www.aspdotnet-suresh.com/2010/10/how-to-get-output-parameters-return-by.html
for stored procedure sp_userinformation
http://www.aspdotnet-suresh.com/2010/10/introduction-here-i-will-explain-how-to.html
Let me know further updates

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

Okey then.lemme work on the links yu av given me,al giv you the results of that

Mark as answer if satisfied

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

am using linq to sql,is thea a way i can implement it to work on Linq to sql?

Mark as answer if satisfied

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

var usersCnt = (from u in Users
               where u.username  == "chandu"
               select u).count()

if ( usersCnt != 1)
{
 //Insert code
}

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

Refer http://social.msdn.microsoft.com/Forums/en-US/66a0eee0-cccd-44ce-9917-6a388d923a6a/how-do-i-do-an-if-exists-style-query-using-linq?forum=linqprojectgeneral

bool doesUserExist = myContext.Users.Any(u=>u.Username == "bob" && u.Password=="mypassword");

if ( !doesUserExist)
{
 //Insert
}

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

Another approach using Lambda expression

var existingUserCount = publishContext.Users.Count(a => a.username == "chandu");

if (existingUserCount == 0) 

{

    // Do your insert

}

Refer
http://stackoverflow.com/questions/725708/how-to-insert-only-new-records-using-linq-to-sql


//Simple expression
int result = (from t in db.Table
where t.username == 'chandu'
select p).count();

if(result > 0)
{
record exists
}
else
{
//do Insert
}
reference: http://dotnet-programming-solutions.blogspot.in/2013/01/linq-how-to-check-if-record-exists-in.html
http://social.msdn.microsoft.com/Forums/en-US/b1a0eb5b-d5d3-41af-829f-bbbac47b7383/linq-to-sql-insert-if-not-already-in-database
http://stackoverflow.com/questions/6300972/linq-query-problem-need-to-check-if-exists

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

What about if yu dont know the username to be entered in the part of the this code"var existingUserCount = publishContext.Users.Count(a => a.username == "chandu")?
"I can use the part to replace txtboxusername.text?

Mark as answer if satisfied

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

string username = String.IsNullOrEmpty(txtboxusername.text) ? "" : txtboxusername ;

var existingUserCount = publishContext.Users.Count(a => a.username == username)

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

if there is no value in txtbox username you should display sone validation message. to create a user username is must...

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

am getting this error that Error 2 Error 2 Operator '==' cannot be applied to operands of type 'int' and 'string'

This is the line of my code to check
var existingUserCount = db.customers.Count(a => a.CustomerId == username);
if (existingUserCount>1)
{
return null;

}

Mark as answer if satisfied

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

int username = int.Parse(txtboxusername.text)

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

Hav used this but still it cannot check but submits records,av placed the code in my method "Createuser".it still submits ...

Mark as answer if satisfied

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

Can you post the createuser method code... ?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

Customer userExists = db.customers.FirstOrDefault(x =>x.Firstname == Firstname);
if (userExists != null)
{
return null;
}
else
{
try
{
Customer newCustomer = new customer();

newCustomer .Firstname = Firstname;
newCustomer.Telephone = Telephone;
newCustomer.Location = Location;
db.Customers.InsertOnSubmit(newCustomer);
db.SubmitChanges();


return newCustomer;
}
catch (Exception)
{

throw;
}

This is my method to create user

Mark as answer if satisfied

Jopito, if this helps please login to Mark As Answer. | Alert Moderator

hi..
If not Exists (select loginname from master.dbo.syslogins
where name = @loginName and dbname = 'PUBS')
Begin
Select @SqlStatement = QUOTENAME('CREATE LOGIN [' + @loginName + ']
FROM WINDOWS WITH DEFAULT_DATABASE= [PUBS], DEFAULT_LANGUAGE=[us_english]')

EXEC sp_executesql @SqlStatement
End

SAMIR
Sr. Software Engineer

Jopito, if this helps please login to Mark As Answer. | Alert Moderator