Unique Id for the different Items

ankit gaur

8y1.7k.NET

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

21 Replies

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

Ruchi Sharavat8y ago
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
+2
ankit gaur8y ago
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.
0
Ruchi Sharavat8y ago
Can you debug and check what is value you set in "paraNo" within your save function for the below line :

string ParaNo = "" + ("P" + ParameterId);
+1
ankit gaur8y ago
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
+1
ankit gaur8y ago
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.
+1
Jenith Thakkar8y ago
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
+1
ankit gaur8y ago
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 + "'))");
+1
Jenith Thakkar8y ago
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
+1
ankit gaur8y ago
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 + "'))");
+1
rajendra singh8y ago
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
+1
ankit gaur8y ago
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 + "'))");
+1
rajendra singh8y ago
Code seems right could you please post full source code with db script so i can check easily.

Thanks
Rajendra Singh
+1
ankit gaur8y ago
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 + "'))");
+1
rajendra singh8y ago
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
+1
ankit gaur8y ago
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.....
+1
Jenith Thakkar8y ago
Hi Ankit Please try this

SELECT ROW_NUMBER() OVER (PARTITION BY Category ORDER BY ParaId DESC) AS rn
FROM tblMatParameterM
+1
rajendra singh8y ago
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
+1
ankit gaur8y ago
Hi Pratik
the code u have send . i write the same.
plz see the source code that i have attached.
thanku
+1
Pratik Shirse8y ago
you may use MAX function

select isnull(max(PrimaryID),0)+1 from tableName

set Datatype as varchar in database
+1
ankit gaur8y ago
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
+1
rajendra singh8y ago
Hi Ankit,

Could you please tell where you were getting issues while doing this.

Thanks
+1