inserting a new row in MS SQL 2005

Check the row is not exist before inserting a new row

Page 1 of 1

1 Replies - 1794 Views - Last Post: 19 November 2008 - 08:43 PM Rate Topic: -----

#1 sraymond101  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 6
  • Joined: 20-July 08

inserting a new row in MS SQL 2005

Posted 19 November 2008 - 08:34 PM

I am creating a page where customers can sign up, and I am capturing the information on a ms sql express database. Before adding a row for a customer I would like to check first and last Name to see if this customer already exist. If I ever get two customers with identical first and last name, it would be a problem, but for now I am assuming no two customers with the same first and last name.


if (txtEmail.Text.Trim() != "" || txtHomePhone.Text.Trim() != "" || txtCellPhone.Text.Trim() != "")
{
checkExistance();
}

elese
{

lblRequiredContactError.Text = "Either phone number or email address is required.";
}
private Void checkExistance()
{

string _connectionString = ReadWebConfig.GetConnectionString();
string sql;
DataSet ds;
DataTable dt
DataRow dr;

using (SqlConnection conn = new SqlConnection(_connectionString))
{

using (SqlCommand cmd = new SqlCommand())
{

sql = " select FirstName, LastName from Customer where FirstName = txtFirstName.Text and LastName = txtLastName.Text";

}

I want to say if row count greater than zero display message you are already register else call a method to insert the row.

Is This A Good Question/Topic? 0
  • +

Replies To: inserting a new row in MS SQL 2005

#2 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Re: inserting a new row in MS SQL 2005

Posted 19 November 2008 - 08:43 PM

you would want your sql to be this...

sql = "SELECT COUNT(*) FROM Customer WHERE FirstName = '" + txtFirstName.Text + "' AND LastName = '" + txtLastName.Text + "'";



This is also opening you up to sql injection.

though this would be easier using a stored procedure..

ALTER PROCEDURE [dbo].[tsp_InsertCustomer]
	@FirstName varchar(100)
	, @LastName varchar(255)
AS DECLARE @Count int
SET @Count = (SELECT COUNT(*) 
		       FROM Customer WHERE FirstName = @FirstName AND LastName = @LastName)

IF @Count = 0
	-- Do your insert


and for the code, you would run "ExecuteNonQuery" which would return the number of rows that were altered. If it's 0, then the customer already existed. If it's 1, then it inserted a new customer.

This post has been edited by eclipsed4utoo: 19 November 2008 - 08:45 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1