When update duplicate rows inserted

ALTER PROCEDURE [dbo].[sp_update_farmer_registration_web]
@originname varchar(100),
@farmerctscode varchar(100),
@farmerseason varchar(100),
@dateofjoinseco varchar(100),
@governmentcode varchar(100),
@firstname varchar(100),
@lastname varchar(100),
@surname varchar(100),
@gender varchar(100),
@dateofbirth varchar(100),
@birthlocation varchar(100),
@fathername varchar(100),
@mothername varchar(100),
@village varchar(100),
@currentyearcooperative varchar(100),
@phonenumber varchar(100),
@observation varchar(100),
@nooffarms varchar(100),
@productionestimation varchar(100),
@otherdetails varchar(max),
@previouscropsdetails varchar(max),
@firstlevelapprovalstatus varchar(100),
@firstlevelapprovedby varchar(100),
@secondlevelapprovalstatus varchar(100),
@secondlevelapprovedby varchar(100),
@firstlevelcomments varchar(max),
@secondlevelcomments varchar(max),
@updatedby varchar(100),
@mastertype varchar(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlQuery VARCHAR(MAX)
IF @mastertype = ''
BEGIN
SET @sqlQuery = 'UPDATE dbo.tbl_farmerregistration_' + @originname + ' SET farmerseason = ''' + @farmerseason + ''',
dateofjoinseco = ''' + @dateofjoinseco + ''', governmentcode =''' + @governmentcode + ''', firstname = ''' + @firstname + ''',
lastname = ''' + @lastname + ''', surname = ''' + @surname + ''', gender = ''' + @gender + ''', dateofbirth = ''' + @dateofbirth + ''',
birthlocation = ''' + @birthlocation + ''', fathername = ''' + @fathername + ''', mothername = ''' + @mothername + ''',
village = ''' + @village + ''', currentyearcooperative = ''' + @currentyearcooperative + ''',
phonenumber = ''' + @phonenumber + ''', observation = ''' + @observation +''', nooffarms = ''' + @nooffarms + ''', produtionestimation = ''' + @productionestimation + ''',
otherdetails = ''' + @otherdetails + ''',previouscropsdetails = ''' + @previouscropsdetails + ''',
updatedby = ''' + @updatedby + ''', updateddate = GETDATE()
WHERE farmerctscode = ''' + @farmerctscode + ''''
END

yes duplicate farmerctscode.

i am updating the record only but when i updating records is updated for that particular and another record is inserting in that table

asp code for updating as follows

public string DAL_Update_FarmerRegistration_ByWeb( string farmerctscode, string farmerseason, string dateofjoinseco,
string governmentcode, string firstname, string lastname, string surname, string gender, string dateofbirth, string birthlocation, string fathername,
string mothername,string mastertype)
{
rtnStr = string.Empty;
try
{
sqlConnection = new SqlConnection(connStr);
openConnection();
sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "sp_update_farmer_registration_web";
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.AddWithValue("@originname", originname);
sqlCommand.Parameters.AddWithValue("@farmerctscode", farmerctscode);
sqlCommand.Parameters.AddWithValue("@farmerseason", farmerseason);
sqlCommand.Parameters.AddWithValue("@dateofjoinseco", dateofjoinseco);
sqlCommand.Parameters.AddWithValue("@governmentcode", governmentcode);
sqlCommand.Parameters.AddWithValue("@firstname", firstname);
sqlCommand.Parameters.AddWithValue("@lastname", lastname);
sqlCommand.Parameters.AddWithValue("@surname", surname.);
sqlCommand.Parameters.AddWithValue("@gender", gender);
sqlCommand.Parameters.AddWithValue("@dateofbirth", dateofbirth);
sqlCommand.Parameters.AddWithValue("@birthlocation", birthlocation);
sqlCommand.Parameters.AddWithValue("@fathername", fathername);
sqlCommand.Parameters.AddWithValue("@mothername", mothername);

sqlCommand.Parameters.AddWithValue("@mastertype", mastertype);
bool s = Convert.ToBoolean(sqlCommand.ExecuteNonQuery());
if (s)
{
rtnStr = "true";
}
else
{
rtnStr = "false";
}

i am also checking with bool for update the record in table record only need to be updated.

in that SP update query only written.

how to fix this issue. in my above aspx code any mistake in bool part code.

how to fix that error.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bryian Tan8y ago
Not clear where the code is checking if need to be updated. By the way, The ExecuteNonQuery return number of row affected. "s" will be true if number of rows affected > 0.

bool s = Convert.ToBoolean(sqlCommand.ExecuteNonQuery());

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx
8y ago
yes duplicate farmerctscode.

i am updating the record only but when i updating records is updated for that particular and another record is inserting in that table

asp code for updating as follows

public string DAL_Update_FarmerRegistration_ByWeb( string farmerctscode, string farmerseason, string dateofjoinseco,
string governmentcode, string firstname, string lastname, string surname, string gender, string dateofbirth, string birthlocation, string fathername,
string mothername,string mastertype)
{
rtnStr = string.Empty;
try
{
sqlConnection = new SqlConnection(connStr);
openConnection();
sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "sp_update_farmer_registration_web";
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.AddWithValue("@originname", originname);
sqlCommand.Parameters.AddWithValue("@farmerctscode", farmerctscode);
sqlCommand.Parameters.AddWithValue("@farmerseason", farmerseason);
sqlCommand.Parameters.AddWithValue("@dateofjoinseco", dateofjoinseco);
sqlCommand.Parameters.AddWithValue("@governmentcode", governmentcode);
sqlCommand.Parameters.AddWithValue("@firstname", firstname);
sqlCommand.Parameters.AddWithValue("@lastname", lastname);
sqlCommand.Parameters.AddWithValue("@surname", surname.);
sqlCommand.Parameters.AddWithValue("@gender", gender);
sqlCommand.Parameters.AddWithValue("@dateofbirth", dateofbirth);
sqlCommand.Parameters.AddWithValue("@birthlocation", birthlocation);
sqlCommand.Parameters.AddWithValue("@fathername", fathername);
sqlCommand.Parameters.AddWithValue("@mothername", mothername);
sqlCommand.Parameters.AddWithValue("@mastertype", mastertype);
bool s = Convert.ToBoolean(sqlCommand.ExecuteNonQuery());
if (s)
{
rtnStr = "true";
}
else
{
rtnStr = "false";
}

i am also checking with bool for update the record in table record only need to be updated.

in that SP update query only written.

how to fix this issue. in my above aspx code any mistake in bool part code.

how to fix that error.
Bryian Tan8y ago
Based, on the query, I don't see any Insert statement. Is it possible there are duplicates farmerctscode?