Help for Autoincrement

Question

1.00/5 (1 vote)

See more:

hello friends,

I make one registration fourm i want increment my userid like F01 WHEN i select female and want M01 WHEN i select male so what is the code for it
help me.

Posted 23-Jul-12 19:30pm

shinebudy

Updated 23-Jul-12 19:36pm

Prasad_Kulkarni

v2

Add a Solution

Comments

_Amy 24-Jul-12 1:37am

What have you tried so far?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Santhosh Kumar Jayaraman · Answer 1 · 2012-07-23T20:27:00

Solution 1

try this..

My asp Code:
Here i have dropdown for Gender, whenever gender name changes, i populate incrementedId in Label:

XML

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:DropDownList runat="server" Id="ddlGender" AutoPostBack="true"
        onselectedindexchanged="ddlGender_SelectedIndexChanged" >
        <asp:ListItem Text="Male" Value="M" />
        <asp:ListItem Text="Female" Value="F" />
    </asp:DropDownList>
    <asp:Label runat="server" ID="IncId" />
</asp:Content>

My aspx.cs code behind.

protected void ddlGender_SelectedIndexChanged(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=localhost\\sqlexpress;Initial Catalog=Santhosh;Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand("Select Isnull(MAX(SUBSTRING(Userid,2,LEN(UseriD)-1),0)+1 from tbl_user where USERId like '" + ddlGender.SelectedValue + "%'", con);
con.Open();
int incrementedId = (cmd.ExecuteScalar()!=null ||!(cmd.ExecuteScalar() is DBNull))?Convert.ToInt32(cmd.ExecuteScalar()):1;
string Id = ddlGender.SelectedValue+incrementedId.ToString();
IncId.Text = Id;

}

My sample table and query to get max id

SQL

create table tbl_user(
UserId varchar(10)
)

insert into tbl_User values ('M01')
insert into tbl_User values ('M02')
insert into tbl_User values ('F123')


select * from tbl_User

Select  MAX(SUBSTRING(Userid,2,LEN(UseriD)-1))+1 from tbl_user where USERId like  'F%'

Posted 23-Jul-12 20:27pm

Santhosh Kumar Jayaraman

Updated 24-Jul-12 1:51am

v3

Comments

shinebudy 24-Jul-12 3:28am

okey i will try...........

Santhosh Kumar Jayaraman 24-Jul-12 3:29am

let me know if it didnt work for you

shinebudy 24-Jul-12 3:40am

i want stored procedure how can i write?

shinebudy 24-Jul-12 3:51am

i want stored procedure plz

Santhosh Kumar Jayaraman 24-Jul-12 4:29am

why do you want stored proc? You can use the query

Select MAX(SUBSTRING(Userid,2,LEN(UseriD)-1))+1 from tbl_user where USERId like 'F%'

shinebudy 24-Jul-12 4:44am

budy i make class so i wnt sp

Santhosh Kumar Jayaraman 24-Jul-12 4:45am

you want the whole thing in sp and not in asp.net?

shinebudy 24-Jul-12 5:29am

ALTER PROCEDURE C256622_gujaratijeev.GetRegId

@Gender varchar(15)
--other parameters here
AS
BEGIN
SET NOCOUNT ON;
declare @RegId int

Select @RegId=MAX(RegId) from Registration
where Gender=@Gender

set @RegId=ISNULL(@RegId,0)+1

insert into Registration(RegId,Gender) values(@RegId,@Gender)

END
i write this sp and here is my dataset
public DataSet increment()
{
string No = "GetRegId";
SqlParameter[] par = new SqlParameter[2];
par[0] = new SqlParameter("RegId", RegId);
par[1] = new SqlParameter("@Gender", Gender);
DataSet ds = new DataSet();
ds = objDBAccess.GetDataSet(No, par);
return ds;
}
what is the code for aspx page

Santhosh Kumar Jayaraman 24-Jul-12 5:35am

What code u need in aspx. You have done insert also in your stored proc. WHat you want to display in aspx? Moreover your stored proc don't return any values, so how will you fill dataset?

shinebudy 24-Jul-12 5:41am

i want selected indexchange code which increment my regid

Santhosh Kumar Jayaraman 24-Jul-12 5:43am

Thats what i already gave you right? I dont understand why do you want in stored proc when it is not at all required.

shinebudy 24-Jul-12 5:48am

okey wait i try without sp

shinebudy 24-Jul-12 5:55am

erro at the place of
int incrementedId = (cmd.ExecuteScalar()!=null ||!(cmd.ExecuteScalar() is DBNull))?Convert.ToInt32(cmd.ExecuteScalar()):1;

erro:"Argument data type int is invalid for argument 1 of substring function."

Santhosh Kumar Jayaraman 24-Jul-12 6:02am

What are the values present in ur table for userid

shinebudy 24-Jul-12 6:03am

where is i'm wrong?

shinebudy 24-Jul-12 6:05am

RegId is int and autoincrement
value 1,2,3..

Santhosh Kumar Jayaraman 24-Jul-12 6:13am

You said you wanted it as M01,F01 like that..but now you are saying the column is int. Give us proper requirement. I gave how it works with my sample values in table. But it seems you didnt look into that

shinebudy 24-Jul-12 6:15am

yes i never say it varchar its int

is there any solution?

Santhosh Kumar Jayaraman 24-Jul-12 6:17am

Query:
Select MAX(UseriD)+1 from tbl_user where Gender like 'F%'

shinebudy 24-Jul-12 6:17am

i want my regid autoincrement like f01,f02 selection of female
and at time of male i want m01,m02.....

shinebudy 24-Jul-12 6:27am

budy how can i solve the error....................

Santhosh Kumar Jayaraman 24-Jul-12 6:32am

I have posted new solution.check that

shinebudy 24-Jul-12 6:42am

budy error solve but still store 1,2,3 not m01,m02 or f01,f02............

Santhosh Kumar Jayaraman · Answer 2 · 2012-07-24T00:19:00

Solution 3

Final solution:
Try this..
My asp Code:
Here i have dropdown for Gender, whenever gender name changes, i populate incrementedId in Label:

ASP.NET

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:DropDownList runat="server" Id="ddlGender" AutoPostBack="true"
        onselectedindexchanged="ddlGender_SelectedIndexChanged" >
        <asp:ListItem Text="Male" Value="M" />
        <asp:ListItem Text="Female" Value="F" />
    </asp:DropDownList>
    <asp:Label runat="server" ID="IncId" />
</asp:Content>

My aspx.cs code behind.

C#

protected void ddlGender_SelectedIndexChanged(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection("Data Source=localhost\\sqlexpress;Initial Catalog=Santhosh;Integrated Security=SSPI");
    SqlCommand cmd = new SqlCommand("Select Isnull(MAX(UseriD),0)+1 from tbl_user where Gender like '" + ddlGender.SelectedValue + "%'", con);
    con.Open();
    int incrementedId = (cmd.ExecuteScalar()!=null ||!(cmd.ExecuteScalar() is DBNull))?Convert.ToInt32(cmd.ExecuteScalar()):1;
    IncId.Text = ddlGender.SelectedValue+incrementedId.ToString();
}

Posted 24-Jul-12 0:19am

Santhosh Kumar Jayaraman

Updated 24-Jul-12 1:51am

v3

Comments

shinebudy 24-Jul-12 6:38am

budy error solve but strore int value not store like m01 or f01 what can i do?

Santhosh Kumar Jayaraman 24-Jul-12 6:41am

int incrementedId = (cmd.ExecuteScalar()!=null ||!(cmd.ExecuteScalar() is DBNull))?Convert.ToInt32(cmd.ExecuteScalar()):1;

This will have values in integer only.. Just am displaying M01 in label and not saving it any variable. So while inserting into table, use incrementedId variable which is an int

shinebudy 24-Jul-12 6:45am

it is possible to store m01,m02..............?

Santhosh Kumar Jayaraman 24-Jul-12 6:47am

Now again you are going back to old one...If you want to save it as M01,M02 then you have to change ur column datatype.. If you want to save with M01, like that check solution 1 else check solution 3

shinebudy 24-Jul-12 6:48am

so i change my datatype of regid right?

i cahnge it i take now varchar okey.........

Santhosh Kumar Jayaraman 24-Jul-12 6:51am

Then use solution 1

shinebudy 24-Jul-12 6:55am

error:Object cannot be cast from DBNull to other types.

Santhosh Kumar Jayaraman 24-Jul-12 7:52am

I have updated both solutions in the query part. check now

shinebudy 24-Jul-12 8:22am

which one i'm using?

Santhosh Kumar Jayaraman 24-Jul-12 8:23am

Use solution1 If you want to save M01,M02..

I just changed the query.
SqlCommand cmd = new SqlCommand("Select Isnull(MAX(SUBSTRING(Userid,2,LEN(UseriD)-1),0)+1 from tbl_user where USERId like '" + ddlGender.SelectedValue + "%'", con);

shinebudy 24-Jul-12 8:44am

keep the regid varchar or not? for this solution

Santhosh Kumar Jayaraman 24-Jul-12 8:48am

Solution 1- will save as MO1,M02 and the column should be varchar.
Solution 3- will save as 1,2,3 and column should be int

shinebudy 24-Jul-12 9:16am

sir it still store only number in my db not store alphanumeric nummber

Santhosh Kumar Jayaraman 24-Jul-12 9:18am

Which one u passed Id or incrementedId.??

You should pass Id.IncrementedId is int and ignore that when saving it to database.
int incrementedId = (cmd.ExecuteScalar()!=null ||!(cmd.ExecuteScalar() is DBNull))?Convert.ToInt32(cmd.ExecuteScalar()):1;
string Id = ddlGender.SelectedValue+incrementedId.ToString();

shinebudy 24-Jul-12 9:22am

i pass increment id

Santhosh Kumar Jayaraman 24-Jul-12 9:22am

Pass Id please.

Also mark the answer as aceepted if you are able to save

shinebudy 24-Jul-12 9:25am

wait

shinebudy 24-Jul-12 9:28am

not yet save :( but you try very hard for me
thank u so much.................

Santhosh Kumar Jayaraman 24-Jul-12 9:29am

not able to save??Whats the error?

shinebudy 24-Jul-12 9:30am

i still face the problem

Santhosh Kumar Jayaraman 24-Jul-12 9:32am

Please paste the error.Otherwise i cant know what error

shinebudy 24-Jul-12 9:34am

i make class for insert in reg
so i in my code behind my parameter is

objclass.gender=ddlgender.selectedvalue;
objclass.mobileno=txtmobile.text;

and i donit put regid like objuserid.userid ao guide me the i aplly objof userid in my page?

Santhosh Kumar Jayaraman 24-Jul-12 9:35am

Create a property inside userid class for userid

shinebudy 24-Jul-12 9:37am

no error return but still save number

shinebudy 24-Jul-12 9:38am

okey i make this
objclass.userid=what?

Santhosh Kumar Jayaraman 24-Jul-12 9:39am

set user id as the Id which is of type string in my code.

objclass.userid= Id

shinebudy 24-Jul-12 9:41am

Objclass.Password = txtpass.text;
Objclass.MemberType = ddlmemeber.selectedvalue;
Objclass.FirstName = txtfirstname.text;
Objclass.LastName = txtlastname.text;
objclass.userid=?

Santhosh Kumar Jayaraman 24-Jul-12 9:42am

set objclass.userId inside dropdown selection changed.
protected void ddlGender_SelectedIndexChanged(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=localhost\\sqlexpress;Initial Catalog=Santhosh;Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand("Select Isnull(MAX(UseriD),0)+1 from tbl_user where Gender like '" + ddlGender.SelectedValue + "%'", con);
con.Open();
int incrementedId = (cmd.ExecuteScalar()!=null ||!(cmd.ExecuteScalar() is DBNull))?Convert.ToInt32(cmd.ExecuteScalar()):1;
objclass.userid = ddlGender.SelectedValue+incrementedId.ToString();
}

shinebudy 24-Jul-12 9:44am

but id created in ddlselected index so on submitbutton coding its not take this
objclass.userid=id not seen

shinebudy 24-Jul-12 9:46am

okey wait........

Santhosh Kumar Jayaraman 24-Jul-12 9:48am

OK then just move all the code inside selected index changed to button click.

Like this.
protected void btnSubmit_CLick(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=localhost\\sqlexpress;Initial Catalog=Santhosh;Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand("Select Isnull(MAX(SUBSTRING(Userid,2,LEN(UseriD)-1),0)+1 from tbl_user where USERId like '" + ddlGender.SelectedValue + "%'", con);
con.Open();
int incrementedId = (cmd.ExecuteScalar()!=null ||!(cmd.ExecuteScalar() is DBNull))?Convert.ToInt32(cmd.ExecuteScalar()):1;
string Id = ddlGender.SelectedValue+incrementedId.ToString();
IncId.Text = Id;

}

shinebudy 24-Jul-12 9:50am

i take this but facing int to string conver eeor
than i make it below
objclass.userid = convert.toint(ddlGender.SelectedValue+incrementedId.ToString());
but it reruns input string was not in formate errorr...............

shinebudy 24-Jul-12 9:51am

okey wait i will be back...............

Santhosh Kumar Jayaraman 24-Jul-12 9:51am

why you are converting to int??

Instead of this.
objclass.userid = convert.toint(ddlGender.SelectedValue+incrementedId.ToString());

write
objclass.userid = ddlGender.SelectedValue+incrementedId.ToString();

shinebudy 25-Jul-12 1:08am

sory for the late reply b'coz of my net connectivety