Click here to Skip to main content
14,838,044 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
Hi,
     I have a  table named Itemdetails. There is a column named ItemCode it is initialised as varchar(50). So I entered both int and string values to the column. Now I need to use auto increment operation for the integer values in the column. So I need to get  max(ItemCode)+1 value. For that I need to seperate string and int values.
Thanks
Posted
Comments
Aarti Meswania 31-Mar-14 9:10am
   
can you give format of Itemcode ?
like
item_001 or item001...

1 solution

You can't.
A VARCHAR column does not contain integers - it only contains strings, so all the "Integer values" you inserted were converted to strings by SQL Server when you issued the INSERT command.

It might be possible to write some SQL to convert them back to integer, increment them, and UPDATE the column, but it wouldn't be that simple as SQL doesn't have a "TryParse" method which allows you to check if a column would convert - you would have to rely on SQL exceptions and catch those. Nasty stuff.

Instead, I would add a new INT column, allow NULL values on your VARCHAR column, and knock up some C# / VB code to look at each row in turn. If it is numeric, convert it to a number, write the number to the new numeric column, and NULL the text. Tehn modify your existing software to work with the two columns. It'll be a lot, lot quicker and easier in the long run.
   
Comments
My Doubt 31-Mar-14 7:13am
   
I need to get the max id of the column. It says that "Conversion failed when converting the varchar value 'abcd' to data type int".
But I need to find the max id of the column for using auto increment.
Please help me out.
OriginalGriff 31-Mar-14 7:38am
   
That is exactly the problem: there is no "try and convert this" method: so SQL will throw an exception.
You really, really, need to change the design.
And unless you *really* know what you are doing, use an Identity field instead of working it out yourself - it's very risky when you try to use this in a multiuser system!
My Doubt 31-Mar-14 7:45am
   
Hi OriginalGriff,
I can't able to change the design because that table is already used by others. I got a new requirement that Item Code should be having 2 forms either auto increment id or by user entry. The user can enter any type of data like abc,abc123. But the auto incremented value will in integers like 345,346,347 etc.
OriginalGriff 31-Mar-14 8:33am
   
Then you need to go to whoever made the requirement and explain the situation.
Someone has made a poor design decision, and the best way is to rectify it, or change the requirement to something that fits with the existing design.
My Doubt 1-Apr-14 0:27am
   
Hi OriginalGriff,
The main situation is that I have an itemcode which can be entered by users or a number generated by the system. In my design there is a text box named Item code along with a button. On the text box user can directly enter the item code (it will be a string or an integer) and on clicking the button the item code is automatically generated( should be an integeger and cannot be duplicated).
Thanks
OriginalGriff 1-Apr-14 4:40am
   
All the more reason to have two separate columns.
What happens if the user types it wrong?
The "actual" item code should be unique: an integer Identity is ok, but I prefer Guids.
YOu can add a "user entered" string version (such as a part code) as well, but don't try to mix-n-match them!
My Doubt 1-Apr-14 4:46am
   
Currently I am using with Max(ItemID)+1 to the ItemCode. Here there will have a conflict on duplicate. If a duplication occurs I will show a message and user can enter other code.
Any way Thanks OriginalGriff for spending your valuable time with me.

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