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

Posted by Jopito under ASP.NET on 11/22/2013 | Points: 10 | Views : 12795 | Status : [Member] | Replies : 22
Hi pals,av a form which inserts data i n a database.Have not worked with how to check if record exists and prevent entry.my datakey value is the username .i thea a simple way to solve this?Thanks

Mark as answer if satisfied


Responses

Posted by: Bandi on: 11/25/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

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

Posted by: Bandi on: 12/3/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
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

Posted by: Bandi on: 11/22/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Bandi on: 11/22/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Jopito on: 11/22/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Jopito on: 11/22/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bandi on: 11/22/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Bandi on: 11/22/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Jopito on: 11/22/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Jopito on: 11/22/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bandi on: 11/22/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Bandi on: 11/22/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Bandi on: 11/22/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Jopito on: 11/23/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bandi on: 11/23/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Bandi on: 11/23/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Jopito on: 11/23/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bandi on: 11/23/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Jopito on: 11/23/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bandi on: 11/24/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Jopito on: 11/25/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Samirbhogayta on: 12/4/2013 [Member] Starter | Points: 25

Up
0
Down
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

Login to post response