ParameterId is not inserting in a incremental order in sql

Hi Ankit,

 

your query returned 13 record while you need only at a time to set the value of "ParameterId" variable. you will have to use top(1) to select only one record and have to add where condition if needed. From the result of your query there are multiple result values for "ParameterIdNew" but you need only one.

 

I think you need to modify your query to get the single value for parameterid as you are using this query only to set parameterid variable.

This gives me the perfect record that i want but it creates a new column in sql table ParameterIdNew but i have column ParameterId where i want to insert these Id.

Plz See.

i think i have the problem in the c# code. 

Hi ankit,

 

I would recommed to you to execute your query first in database to check the result return by this:

 

Select *,ParameterId = 'P' + convert(varchar(10),row_number() over (partition by ItemId order by ParaId desc)) from tblMatParameter

 

You will have to see if this is returning the same that you want to get or not? Please show result of your query. 

thanku @Ruchi Sharavat

But its not incerting the ParameterId in incremental order. it insert the same ParameterId

Plz Check

 

private string AutoIncrementID()

{

SqlConnection con = new SqlConnection();

con.Open();

SqlCommand cmd = new SqlCommand("Select *,ParameterId = 'P' + convert(varchar(10),row_number() over (partition by ItemId order by ParaId)) from tblMatParameter", con);

SqlDataReader dr = cmd.ExecuteReader();

string ParameterId = null;

if (dr.Read())

{

ParameterId = dr[0].ToString();

con.Close();

}

return ParameterId;

}

protected void Save(object sender, EventArgs e)

{

foreach (GridViewRow gvr in Gridview1.Rows)

{

//Connection

SqlConnection con2 = new SqlConnection();

con2.Open();

TextBox Parameter = gvr.FindControl("txtPara") as TextBox;

string txtPara = Parameter.Text;

string ParameterId = AutoIncrementID();

//int idLimit = 2;

string ParaNo = "" + ("P" + ParameterId);

SqlCommand cmd = new SqlCommand("INSERT INTO [ElixirERP].[ElixirAdmin].[tblMatParameter](ParameterId,Category,SubCategory,Brand,Item,Parameter,ItemId)" +

"VALUES('" + ParaNo + "',@ddlCategory,@ddlSubCategory,@ddlBrand,@ddlItem,'" + txtPara + "',(Select ItemId From [ElixirERP].[ElixirAdmin].[tblMatItem] where ItemId = '" + ddlItem.SelectedValue + "'))",con2);

cmd.ExecuteNonQuery();

con.Close();

}

}

In your query you don't have any condition that which record you want to choose. It might be picking top 1 always because it will result all records of your table tblMatParameter.

 Or

 

If you really need all records than try to use order by desc with order by ParaId