Dilema adding incremental numbers to my table

Question

5.00/5 (1 vote)

See more:

Hello All

I have a table that I'm trying to add an auto-increment like set of numbers to a specific column.

I'm using C# VS2010 and using oledb to make the connection to the table. I'm able to connect to the table but I'm not able to figure out the SQL code needed to make this work.

I've got an except of the table that I'm working with and it looks like this

Description ShortDescription ID
Heathrow LHR
Halifax YHZ
Los Angeles LAX

I realize that I believe that I have to use an update statement. I've tried everything. I even created a table that has numbers of 50-150 and I've tried to merge the two but I couldn't figure that out.

How do I get my table to look like this??

Description ShortDescription ID
Heathrow LHR 50
Halifax YHZ 51
Los Angeles LAX 52
53
54

I'm trying to get the ID to count up to 150 even if there's nothing in Description or ShortDescription.

How do I add the numbers to increment starting from 50 and stoping at 150 in a blank column and to increase even if there's nothing in the other columns? Is there a way using an SQL Update Statement or would it better to use dataset merge or something else? I'm not able to change the table structure to add autoNumber datatype.

Thanks in advance.

Posted 4-Nov-11 17:32pm

mzrax

Add a Solution

Comments

Mehdi Gholam 5-Nov-11 0:03am

Why do you want to do this?

mzrax 6-Nov-11 22:20pm

Because the table that I'm working with does not have a incrementing column where I can control the numbers that have to go in there. The numbers that I want to put in there will change because of other variables that the table is connected with.

I'm pretty sure it can be done one of two ways either I do an insert first with the numbers counting and then I do an update to the table with the other information that has to go in there or I insert everything and then somehow I do the incrementing with some kind of update statement.

What do you think?? Think it's possible??

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)

krishna_goluguri · Answer 1 · 2011-11-04T19:45:00

1.If you want the Auto Incriment select ID in your Table From (you are using Oledb i think its Access) From Access and Select Auto Increment to Yes from Column Properties Identity seed (starting Number) Increment(which number to Incriment)
2.If you wanted to add null values also Simply Leave ID (not put it as Primary Key)
3.If you want PK and Wanted to increment and Wanted to Have null rows then create another Field in Database with the Name of ID2 and Put First ID as PK and Increment
and ID1 to only incriment

I think this will help if you want any help comment

Amir Mahfoozi · Answer 2 · 2011-11-04T20:24:00

As I understood you need something like this (I named your table airports and assumed that you have exactly 150 rows in your table):

SQL

select b.Description, b.ShortDescription, 
	ROW_NUMBER() OVER(order by shortdescription desc) +49 AS 'ID'
	from airports b

If you need modifications please express more details for me to provide another solution ;)

Dilema adding incremental numbers to my table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0