How to auto increment?

[lkaravi]

Hi Paladine,

Am new to ASP.NET. I followed all the steps which you had given for Login Page and Registration Page. Everything is working properly. Thank you so much.

Paladin, I need one help from you, as per you steps I created one table called "tblUser". In this table U_id is auto number(In this same table I added additional two fields FirstName, LastName for Registration Page purpose). In your stored procedure sp_RegisterNewUser, you have used four fields username, password, Firstname and Lastname, so these four fields only inserting in the table. The first column U_id is inserting null value only. Can you advice me, how to auto increment U_id field while inserting every record.

My table structure:

Table Name: tblUser

U_id Integer (9)
U_Name VARCHAR(50)
U_Password VARCHAR(50)
FirstName VARCHAR(50)
LastName VARCHAR(50)

Advance Thanks Paladine.

[Paladine]

Sure thing. I am a little unclear as to what you are having an issue with, so let me say this (in hopes that it will help).

Ok, if you have the U_Id set to Autonumber then you have no need to increment it. Saying that, this means you do not pass a value to that column in an insert statement/query. In fact it is not required in any queries because Access handles it.

If you mean how do you increment it when if you set it to number. Well, I would create a temp table or even an array to store the last value used for the U_Id field and increment it by one prior to or part of an insert statement. If this is what you want more information on, then let me know and I can guide you through it.

Good luck...and happy coding

[lkaravi]

Hi Paladine,

Thanks for your reply. Actually that U_id field should be incremented in front-end not in backend. Assume our table is empty, so while inserting we have to check whether our table contains record or not. If it is empty, we have to insert value for U_id. This value should be user defined, ie numeric or alpha numeric value.
Example:

Numeric value of U_id
1000

[or]

Alpha Numeric value of U_id
CAN00001

If the table already contains records, we have fetch the last record and auto increment that field.
Example:

Numeric value of U_id
1000
1001
1003
1004
1005

[or]

Alpha Numeric value of U_id
CAN00001
CAN00002
CAN00003
CAN00004
CAN00005

(In my table U_id is Integer, so pls first advice me how to increment the numeric value. Then if u can spend your valuable time for me, let me know how to auto-increment alpha numeric value)

Thanks Paladine

[Paladine]

Ok, no problem, but in your original question you had stated autonumber, so I assume that was what you were wanting to increment.

So lets clear this up, from what you have said you are not going to auto increment, you just simply want to increment the U_Id field. But what concerns me is this statement : "This value should be user defined, ie numeric or alpha numeric value"

You are wanting to user to choose? I hope not, because the U_Id is the Primary Key (Unique Identifier) for the table.

Saying that I am not the Database police, but be warned any relationships with this table will not be maintain referential integrity.

So what I would do is this. You could create a table in the same databae, that would hold the table name, the last value used (for the U_Id column - the primary key column), and the amount to increment by (maybe you want to increment by 2's or 4's instead of by 1 ).

Then you create a function (called GetPrimaryKey) and have this function query the database to get the last value used, and amount to increment by columns (whatever you choose to call them), based on a parameter query which you pass the name of the table you want these values from. (Note: this whole scernario will accommodate multiple tables in the database to work on this premise). Then pass those values (or at least the last value used column value anyway) as part of your Insert statement.

So something like this:

pseudocode:

Function GetPrimaryKey (strTableName as String) as Long
	 ' Declarations
	  ...
	  ...
	 ' Sets
	 ....
	 ....
	' Logic
	 GetPrimaryKey = .... [i]"Select LastNumberUsed FROM tblPrimaryKeys WHERE tableName = tblUser"[/i]
	
	  Return GetPrimaryKey

End Function

**note: Don't forget to update the tblPrimaryKeys Table with the newest "lastNumberUsed" value. **

I will let you try to work on that logic.

Basically the Logic Flows like this for the whole thing:

Before you goto insert data into a table (tblUser in our case), you call the function GetPrimaryKey, it goes to the tblPrimaryKeys with the name of the table you are inserting data into, retrieves the value of the LastNumberUsed, increments it by the amount you specify or the amount stored in the GetPrimaryKeys Table (whatever route you want to go - hard code or store in db?) and updates the GetPrimaryKeys table to reflect the new value of the LastNumberUsed, Your call to the GetPrimaryKey function returns the next available number for the U_Id column (in our case), and you include this as part of your insert statement. And this would only be on an insert... NOT ON AN UPDATE!

Hope this helps.

[kedar_challa]

Hi Ravi,

This solution looks very strange to me. If you requirement is just to increment the U_Id value on insert of new record, u can do that in the database level rather than using c# code.

How can you insert the alphanumeric value in to the U_id (numeric) field ? Anyway i can give both solution.

If u use the numeric datatype
in SQL Server:

• Open the table for designing.
• Select the U-Id field.Goto the properties of the field.
• Select Identity is Yes. Give Identity Increment as 1.

in other Databases:

• Modify the table and Declare U_Id as Identity.

If you use alpha numeric

• first get the last record from the table using the sql query.

select u_id from users where u_id in (select TOP 1 u_id
from users order by u_id desc)

• This returns the last record. now u can generate next sequential userid with C# code.

string userid = //Get from DB using the Query.
userid= userid.Substring(3, userid.length);
int intval = Int32.Parse(userid);
intval += 1;
userid = "CAN" + inval.ToString();

Hope this can help you.

Thanks,
Kedar

[Paladine]

Agreed, this method works, but from what I read, this doesn't meet his request.... but I think you solution is a preferred route.

Hi Ravi,

This solution looks very strange to me. If you requirement is just to increment the U_Id value on insert of new record, u can do that in the database level rather than using c# code.

How can you insert the alphanumeric value in to the U_id (numeric) field ? Anyway i can give both solution.

Hope this can help you.

Thanks,
Kedar

[kedar_challa]

Agreed, this method works, but from what I read, this doesn't meet his request.... but I think you solution is a preferred route.

Hi Paladine,

Could you please let me know his special requirement.

Thanks,
Kedar

[Paladine]

Like I said your reocmmendation is correct, but when he stated things like :

"This value should be user defined, ie numeric or alpha numeric value"

and

"Actually that U_id field should be incremented in front-end not in backend"

And your recommendation of:

If u use the numeric datatype
in SQL Server:

• Open the table for designing.
• Select the U-Id field.Goto the properties of the field.
• Select Identity is Yes. Give Identity Increment as 1.

in other Databases:

• Modify the table and Declare U_Id as Identity.

This is how the table is already designed and built in the tutorials, so that was already a given which he didn't want.

Ikaravi also clearly states this : "Assume our table is empty, so while inserting we have to check whether our table contains record or not. If it is empty, we have to insert value for U_id. This value should be user defined, ie numeric or alpha numeric value."

So using Identity is not the method he wants to go with from these points.

Your second suggestion is clearly acceptable, but he also stated that "the value should be user defined..." Defined when? What is the restriction on this? So your solution does provide a method to accomplish this.

So yes, he could just do a Select statement on the table with U_Id set as a numeric only value, but I was providing a method for multiple tables set up this way as well as maintainability considerations. Not the only way to do this numeric field incrementation, but it works.

Hope this helps

Hi Paladine,

Could you please let me know his special requirement.

Thanks,
Kedar

[kedar_challa]

So, he is generating the table dynamically on fly. Right

Thanks,
Kedar

[Paladine]

Didn't say that.

[kedar_challa]

Didn't say that.

Ok, i got it.
First we have to check for the records in the database.

Select count(*) from users;

If the number of records is zero. Then ask the user to enter the initial value.
Check whether it is alphanumeric or numeric using RegEx class.

Regex regex = new Regex("^[a-zA-Z]");

string str = "CAN00001";

regex.IsMatch(str);

and follow the method i described to increment the alphanumeric value.