Click here to Skip to main content
14,830,313 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Friends,
I hava a Table named "EmpRegister" and
Coluumns
UId-PrimaryKey
ProfileID
UserName
Password
Mobile
Email

I need to insert an incremented value in ProfileId column(which is NOT a PrimaryKey) in a table by selecting Max(UId)+1 which IS a PrimaryKey from that same table and use cmd.Parameters.AddWithValue(.....,.....) along with other text values fetched from .aspx file.
I want to Concatenate "PM" with Max(UId)+1, for example PermanentMember and the desired value will be PM1, PM2, PM3....soon and so forth, where 1 2 3...is UID

I DONT want to use Stored Procedure in SQL Server 2008.

What I have tried:

What I did was.

C#
protected void btnlogin_Click1(object sender, EventArgs e)
{
{
SqlConnection cn = new SqlConnection(@"Data Source=Admin-PC\SQLEXPRESS;AttachDbFilename=C:\Users\.......mdf;Integrated Security=True;User Instance=True");


String query = "Insert into EmpRegister(ProfileId,UserName,Password,Mobile,Email) SELECT MAX(UId)+1 as @ProfileID,@UserName,@Password,@Mobile,@Email)";
SqlCommand cmd = new SqlCommand(query, cn);


//Pass Values to parameters

cmd.Parameters.AddWithValue("@UserName", txtuname.Text);
cmd.Parameters.AddWithValue("@Password", txtupass.Text);
cmd.Parameters.AddWithValue("@Mobile", mobPhone.Text);
cmd.Parameters.AddWithValue("@Email", emailID.Text);

try
{
cn.Open();
cmd.ExecuteNonQuery();
//Console.WriteLine("Records Inserted Successfully");
}
catch (Exception)
{
//Console.WriteLine("Error Generated. Details: " + e.ToString());
}
finally
{
cn.Close();
}
}
}


Please help me where to insert this MAX(UId)+1 value in ProfileId column.
How can I put a value in ProfileID.
Do I need a hidden textbox or any value assigned to ProfileID

cmd.Parameters.AddWithValue("@....", .........);

I tested the code without MAX(UId)+1 and removing SELECT MAX(UId)+1 as @ProfileID,@UserN...... from String query, works well.

Thanks for you time.
Ravi.
Posted
Updated 6-Mar-21 6:48am
v2
Comments
PIEBALDconsult 6-Mar-21 12:20pm
   
Tried using a SEQUENCE? Ah, not in 2008, but since 2012.
ravitv 7-Mar-21 4:23am
   
Nope. sorry I am still using 2008

Quote:
I need to insert an incremented value in ProfileId column(which is NOT a PrimaryKey) in a table by selecting Max(UId)+1 which IS a PrimaryKey from that same table

Basically, don't.
That's dangerous - it can lead to some really nasty data integrity problems, and can take ages to sort out once it's spotted.
The problem is that reading the max value, adding one to it, and saving it back isn't an "atomic" operation - and because SQL Server is intrinsically multiuser there is absolutely nothing which can guarantee that two users don't try this at the same time, and end up with the same number. And when that happens, your data will almost certainly become unsafe.

If you need incrementing values, then use an IDENTITY column and let SQL handle it - it does it very well - and if you need the value for other tables then use the @@IDENTITY[^] value to tell you what it was. If you absolutely need to specify it yourself, use a UNIQUEIDENTITY column instead, and supply a Guid value from your C# code instead of an incrementing integer.
   
Comments
ravitv 7-Mar-21 4:20am
   
Great advice Griff, Thanks.
Is there a sample code or any reference, please?
OriginalGriff 7-Mar-21 4:49am
   
Sample code for what?
Quote:
I need to insert an incremented value in ProfileId column(which is NOT a PrimaryKey) in a table by selecting Max(UId)+1

this is a 'Bad Good Idea', it is secure as long as there is 1 user, it works as long as only 1 user is doing the operation at same time, it fail when more than 1 user fo it at same time.

As far as I understand, you want to insert a new record with a field having same value as the pkey will have in that record, while do you need a second field that duplicate the pkey ?

I see 2 methods:
1) make the second field auto-incremented too (without the unique constraint).
2) insert a new record without the duplicate field, get the pkey and update the record for that field.
   
Comments
ravitv 7-Mar-21 4:22am
   
Great advice like Griff suggested, Thanks you Patrice.
Please give any sample code or reference. Thanks.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900