While insert Record it will display message recordalready exists Visual studio 2005 with SQL Server

Question

While insert same Record it will display message recordalready exists Visual studio 2005 with SQL Server

textbox1 = Projectcode

textbox2 = Projectname
textbox3 = ProjectDesc
textbox4 = Cost

my code:

protected void Button1_Click(object sender, EventArgs e)
{
con.Open();

str = "insert into Project values('"+TextBox1.Text+"','" + TextBox2.Text + "','" + TextBox3.Text + "'," + TextBox4.Text + ")";

cmd = new SqlCommand(str, con);
cmd.ExecuteNonQuery();

Response.Write("Record inserted Successfully");
con.Close();
}

Aarti · Answer

Hi Narasiman, protected void Button1_Click(object sender, EventArgs e) { SqlCommand spcmd; SqlDataReader dr; string qry = ""; string strMsg = ""; int rowsaffected = 0; try { dbConn.Open(); str = "insert into Project values('"+TextBox1.Text+"','" + TextBox2.Text + "','" + TextBox3.Text + "'," + TextBox4.Text + ")"; spcmd = new SqlCommand(qry, dbConn); dr = spcmd.ExecuteReader(); rowsaffected = dr.RecordsAffected; if (rowsaffected > 0) { strmsg=Response.Write("record already exists!"); } else strmsg= Response.Write("Record inserted Successfully"); } catch (Exception ex) { LogWriter.writelog(System.Configuration.ConfigurationManager.ConnectionStrings["errorpath"].ConnectionString.ToString(), "Error=> " + ex.Message.ToString() + System.Environment.NewLine + "Method=> " + (new System.Diagnostics.StackFrame()).GetMethod().Name); } finally { dbConn.Close(); GC.Collect(); } return strMsg; } }

Satyapriya Nayak · Answer

Hi narasiman, Try this... Table create table Project (Projectid int primary key identity, Projectcode varchar(50), Projectname varchar(50), ProjectDesc varchar(MAX), Cost int) Untitled Page BackColor="#FF99FF" Font-Bold="True" Width="329px" onclick="Button1_Click" /> Width="197px" ForeColor="#FF3300"> ControlToValidate="txtProjectcode" ErrorMessage="*Name Required"> Width="197px" ForeColor="#FF3300"> ControlToValidate="txtProjectname" ErrorMessage="*Projectname Required"> Width="197px" ForeColor="#FF3300"> ControlToValidate="txtProjectDesc" ErrorMessage="*ProjectDesc Required"> Width="197px" ForeColor="#FF3300"> ControlToValidate="txtCost" ErrorMessage="*Cost Required"> ForeColor="Black"> using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; namespace WebApplication2 { public partial class _Default : System.Web.UI.Page { string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; SqlCommand com; string str = null; public void Projectcode() { SqlConnection con = new SqlConnection(connStr); con.Open(); str = "select count(*)from Project where Projectcode='" + txtProjectcode.Text + "'"; com = new SqlCommand(str, con); int count = Convert.ToInt32(com.ExecuteScalar()); con.Close(); if (count > 0) { lblMessage.Text = "Sorry! you can't take this Projectcode"; } else { lblMessage.Text = "You can take this Projectcode"; con.Open(); str = "insert into Project (Projectcode,Projectname,ProjectDesc,Cost) values('" + txtProjectcode.Text + "','" + txtProjectname.Text + "','" + txtProjectDesc.Text + "'," + txtCost.Text + ")"; com = new SqlCommand(str, con); com.ExecuteNonQuery(); con.Close(); } } protected void Button1_Click(object sender, EventArgs e) { Projectcode(); } } } Thanks

Pravin Ghadge · Answer

Hi, protected void Button1_Click(object sender, EventArgs e) { con.Open(); str = "Select * from Project where Pk_ColumnName='"+TextBox1.Text+"')"; cmd = new SqlCommand(str, con); SqlDataReader dr=cmd.ExecuteReader(); if(dr.HasRows) { Response.Write("Record already exist"); } else { str = "insert into Project values('"+TextBox1.Text+"','" + TextBox2.Text + "','" + TextBox3.Text + "'," + TextBox4.Text + ")"; cmd = new SqlCommand(str, con); cmd.ExecuteNonQuery(); Response.Write("Record inserted Successfully"); con.Close(); } }