Unique Id for the different Items

Question

I have a database table . i want to save parameterid for the different Items like. Item Parameterid Parameter Home Threatre P1 Para1 Home Threatre P2 Para2 Home Threatre P3 Para3 Osram 10W P1 Para1a Osram 10W P2 Para2b Osram 10W P3 Para3c Osram 10W P4 Para4d

Ruchi Sharavat · Answer

So, problem is in getting latest parameterid from DB within AutoIncrement() function. You must debug that to check what is the exact query created and what is the result returned in datareader and what value is set to be returned parameter. Query posted by Rajender should work fine in your case as your query is returing multiple records while you just need the maximum value

Ruchi Sharavat · Answer

Can you debug and check what is value you set in "paraNo" within your save function for the below line : string ParaNo = "" + ("P" + ParameterId);

ankit gaur · Answer

Plz help me to save to dynamic ParameterId for the different items. see the code i attached. like :- Item ParameterId ABC P1 ABC P2 XYZ P1 XYZ P2

ankit gaur · Answer

Hi jenith Select query is working fine for me its increase the parameterid but i want to save these increment parameterid in ParameterId column of the table tblMatParamter by the insert query. Can u plz see the attached RAR file. i upload the code and database table.

Jenith Thakkar · Answer

Hi Have you tried without first convert like this 'P' + convert(varchar(10),row_number() over (partition by ItemId order by ParaId)) Instead of convert(varchar(100), 'P' + convert(varchar(10),row_number() over (partition by ItemId order by ParaId))) https://stackoverflow.com/questions/46079808/select-column-with-row-number-concatenated-in-sql-server

ankit gaur · Answer

Hi . Its return the same parameterid not in increasing order.. see rar file private string AutoIncrementID() { SqlConnection con = new SqlConnection(); myconn.Open(); SqlCommand cmd = new SqlCommand("Select *, convert(varchar(100), 'P' + CAST(ROW_NUMBER() over (partition by ItemId order by ParaId) AS VARCHAR)) " + " as ParameterId from tblMatParameter", myconn); SqlDataReader dr = cmd.ExecuteReader(); string ParameterId = null; if (dr.Read()) { ParameterId = dr[0].ToString(); myconn.Close(); } return ParameterId; } protected void Save(object sender, EventArgs e) { foreach (GridViewRow gvr in Gridview1.Rows) { //Connection 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 + "'))");

Jenith Thakkar · Answer

Hi Ankit Please replace convert(varchar(10),row_number() over (partition by ItemId order by ParaId)) to CAST(ROW_NUMBER() over (partition by ItemId order by ParaId) AS VARCHAR) and try again

ankit gaur · Answer

Hello Rajendra I m using this code but its not returing the parameterid in increasing order.. private string AutoIncrementID() { SqlConnection con = new SqlConnection(); myconn.Open(); SqlCommand cmd = new SqlCommand("Select *, convert(varchar(100), 'P' + convert(varchar(10),row_number() over (partition by ItemId order by ParaId))) " + " as ParameterId from tblMatParameter", myconn); SqlDataReader dr = cmd.ExecuteReader(); string ParameterId = null; if (dr.Read()) { ParameterId = dr[0].ToString(); myconn.Close(); } return ParameterId; } protected void Save(object sender, EventArgs e) { foreach (GridViewRow gvr in Gridview1.Rows) { //Connection 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 + "'))");

rajendra singh · Answer

Earlier you had made function for creating parameter you have to follow same thing but only you need to change query inside the function. Please see below Function Code private string AutolncrementlD() { Sq1Connection con = new Sq1Connection(); myconn.Open(); Sq1Command cmd = new Sq1Command( "select substring(IsNULL(MAX(parameterid),'P0'),2,LEN((IsNULL(MAX(parameterid),'P0'))))+1 FROM tbl_parameter where itemid=" + ddlItem.SelectedValue , myconn); Sq1DataReader dr = cmd.ExecuteReader(); string Parald = null; if (dr.Read()) { Parald = dr[0].ToString(); myconn.Close(); } return Parald; } and update the parameter Parameterid with function value. Hope this will resolve your issue. Thanks Rajendra Singh

ankit gaur · Answer

Select *, convert(varchar(100), 'P' + convert(varchar(10),row_number() over (partition by ItemId order by ParaId))) as ParameterId from tblMatParameter i m using this query .. this query generate the paramterid in increasing order item wise. but i m not understand how to insert these parameterid with insert query. In My insert query multiple fields. SqlCommand cmd = new SqlCommand("INSERT INTO [ElixirERP].[ElixirAdmin].[tblMatParameter](ParameterId,Category,SubCategory,Brand,Item,Parameter,ItemId)" + "VALUES( ' ' ,@ddlCategory,@ddlSubCategory,@ddlBrand,@ddlItem,'" + txtPara + "',(Select ItemId From [ElixirERP].[ElixirAdmin].[tblMatItem] where ItemId = '" + ddlItem.SelectedValue + "'))");

rajendra singh · Answer

Code seems right could you please post full source code with db script so i can check easily. Thanks Rajendra Singh

ankit gaur · Answer

Hello Rajendra, Not Solve, Plz see the code . if i m geeting wrong. plz help private string AutoIncrementID() { SqlConnection con = new SqlConnection(); myconn.Open(); SqlCommand cmd = new SqlCommand("select substring(IsNULL(MAX(ParameterId),'P0'),2,LEN((IsNULL(MAX(ParameterId),'P0'))))+1 as rn " + " FROM tblMatParameter where Item = '" + ddlItem.SelectedValue + "'", myconn); SqlDataReader dr = cmd.ExecuteReader(); string rn = null; if (dr.Read()) { rn = dr[0].ToString(); } myconn.Close(); return rn; } protected void Save(object sender, EventArgs e) { foreach (GridViewRow gvr in Gridview1.Rows) { //Connection TextBox Parameter = gvr.FindControl("txtPara") as TextBox; string txtPara = Parameter.Text; string rn = AutoIncrementID(); //int idLimit = 2; string ParaNo = "" + ("P" + rn); 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 + "'))");

rajendra singh · Answer

Hi Ankit, I had reviewed you code as you told you need parameter id according to itemid so you have to find max parameter for particular item instead of max in whole table i had write a script please refer below: create table tbl_parameter ( paraid int identity(1,1) primary key, itemid int , parameterid varchar(10) ) insert into tbl_parameter values(1, 'P1' ) insert into tbl_parameter values(1, 'P2' ) insert into tbl_parameter values(1, 'P3' ) insert into tbl_parameter values(2, 'P1' ) insert into tbl_parameter values(2, 'P2' ) insert into tbl_parameter values(2, 'P3' ) insert into tbl_parameter values(3, 'P1' ) insert into tbl_parameter values(3, 'P2' ) select substring(IsNULL(MAX(parameterid), 'P0' ),2,LEN((IsNULL(MAX(parameterid), 'P0' ))))+1 FROM tbl_parameter where itemid=3 Please execute this in sql and in last query you have to replace your itemid through c# code. Let me know if other help needed. Thanks Rajendra Singh

ankit gaur · Answer

Hi Jenith and Rajendra Thanku for u for replies but my prblm has not been solved. i want to increse the ParameterId based on Item. Like - Item1 - ParameterId- P1,P2,P3,P4..... Item2- ParameterId- P1,P2,P3,P4.....

Jenith Thakkar · Answer

Hi Ankit Please try this SELECT ROW_NUMBER() OVER (PARTITION BY Category ORDER BY ParaId DESC) AS rn FROM tblMatParameterM

rajendra singh · Answer

Hi again, select Top 1 ISNULL(parameterid,0) FROM tbl_parameter WHERE ITEMID=1 ORDER BY parameterid DESC you will have to use like above query so from this you will get max parameter id of particular item like p1,p2 than you will have to make plus one after p. and if no row were return you have to start with p1. I hope this will help you. Thanks

ankit gaur · Answer

Hi Pratik the code u have send . i write the same. plz see the source code that i have attached. thanku

Pratik Shirse · Answer

you may use MAX function select isnull(max(PrimaryID),0)+1 from tableName set Datatype as varchar in database

ankit gaur · Answer

I m getting the paramterid in sequence . like P1,P2,P3,P4,P5,P6....and so on but i do not want like this. i add the source code plz see

rajendra singh · Answer

Hi Ankit, Could you please tell where you were getting issues while doing this. Thanks

ankit gaur · Answer

Hi Ruchi On the Save fucntion its return the same Paraid like P115,P115.. Not Increasing the ParaId when i save multiple record on click.

Unique Id for the different Items

Answers (21)