Javscript-How to increment a number in an sql table without using autoincrement

Question

0.00/5 (No votes)

See more:

For example: cust_code=000001,this should be the starting cust_code in my customer table and should increment accordingly like 000002,000003..etc.. on inserting new fields into this table that means by table should look like,
cust_code cust_name
000001 abc
000002 dca
.
.
.and it goes on.

C#

function pad(number, length)
{
   alert(number);
    var str = '' + number;
    while (str.length < length)
     {
        str = '0' + str;
    }

      return '000'+str;

}

C#

var s="000001";

var number=parseInt(s.substring(3,6),10)+1;
         var value=pad(number,3);

		      transaction.executeSql('INSERT INTO customer(cust_code,cust_name)VALUES(?,?)',[value,$('#name').val()],populate_success,transaction_error );

I have tried this code but everytime I insert, the cust_code will be same as 000002,because the initial value I have given is 000001.
Can anybody help me to solve this...

Posted 23-May-14 20:54pm

p@y@l

Add a Solution

4 solutions

Solution 1

Don't. Don't even think about it.

Stop for a moment and think about what you are trying to do: This is a website, which implies multiple simultaneous updates. Over the net, which is slow, and non-deterministic. via a webserver (or more likely a web server farm, which means you probably won't get the same physical server each time the user communicates with it). Which will talk to a different computer running SQL (or quite likely, one of a number of such servers in an SQL server farm, so again, you probably won't get the same computer twice in a row).

And you are going to have the browser say: "Give me the highest number in use", increment it, and then save it back to the database as "it's" number?

You do realize what a recipe for disaster and unpredictable bugs that is, don't you?

Auto increment fields are there for a reason: use them. Lest SQL sort out teh problems and return to you the number it just assigned.

Posted 23-May-14 23:13pm

OriginalGriff

Comments

p@y@l 24-May-14 6:44am

but I am in a need to create my own values with 6 digits,something like I mentioned above..HOW WILL I DO THAT?

CHill60 27-May-14 12:08pm

Not by shouting (all capitals). 6 digits with a C in front of it is only the way you display the number - so just format it for display but store as auto-incremented int.

p@y@l 27-May-14 23:37pm

sorry for that I didn't mean to shout!By mistake I wrote in capitals and didn't change it!

Solution 2

First of all your s is local and being initiated every time you try to update. Declare it as global and initialize it just once and continue updating. And for padding follow the link[^]

Posted 24-May-14 0:36am

Mohibur Rashid

Solution 4

You can use this query to generate the ID and use in insertion through sql only without creating function

SQL

insert into customer (cust_id,cust_name)
select cast(replicate('0',6-LEN(id)) as varchar)+ID as cust_id, 'Bhavana' as cust_name from (
select  cast(isnull(max(cust_ID),0)+1 as varchar) as id from Customer
--select '10' as id --//to check for 2 digit
)x

Posted 26-May-14 23:30pm

Bhavana P

Comments

p@y@l 14-Jun-14 1:19am

is the select cast to be used inside the insert ??

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)

p@y@l · Accepted Answer · 2014-05-26T01:19:00

C#

db1.transaction(function(transaction)
    {
     transaction.executeSql('SELECT * FROM customer where cust_code=(select max(cust_code) from customer)  ', [], function(transaction, result) {
      var len = result.rows.length;
      if (result.rows.length != 0 )
      {
            for (var i = 0; i < result.rows.length; i++)
            {
            var row = result.rows.item(i);
                custcode=row.cust_code;
             }
              console.log("custcod",custcode);
               var number=parseInt(custcode.substring(5,6),10)+1;
                console.log(number);
               value=zeroFill(number,3);
               console.log(value);
        }
        else
        {
               var number=parseInt(s.substring(5,6),10)+1;
                console.log(number);
               value=zeroFill(number,3);
        }

C#

function zeroFill( number, width )
{
   console.log("NUMBER:",number);
    var str = '' + number;
    while (str.length < width)
     {
        str = '0' + str;
    }

    return 'C0000'+str;

}

This code works fine till the value is C000010 after that,C000010 is repeated,increment is not done how can I increment it ??