•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the ASP.NET section within the Web Development category of DaniWeb, a massive community of 403,407 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,654 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our ASP.NET advertiser: Lunarpages ASP Web Hosting
Views: 371 | Replies: 3
 |
•
•
Join Date: Feb 2008
Posts: 9
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
I am new to SQL and I am making an asp.net page in which is a small airline service for a project for scool. For one of my tables I have:
AccountID, CustomerName, FrequentFlierMiles, FrequentFliernum for my column names. The acount AccountID column can not have duplicated values and FrequentFliernum can not have duplicated values and the only thing I could think from stopping that is through identity specification, the problem is you can only set one of the columns to have identity specification.
The AccountID and FrequentFliernum I would like to automatically increment by 1.
Right now my sql statement is
sql = "Insert Into Air_Customers VALUES(','" & txtname.Text & "','0','0')"
I would like that final '0' to automatically be incremented in the database and not have to manually put it in.
Anyhelp is appreciated. If this does not make sense I will try re-writing it.
AccountID, CustomerName, FrequentFlierMiles, FrequentFliernum for my column names. The acount AccountID column can not have duplicated values and FrequentFliernum can not have duplicated values and the only thing I could think from stopping that is through identity specification, the problem is you can only set one of the columns to have identity specification.
The AccountID and FrequentFliernum I would like to automatically increment by 1.
Right now my sql statement is
sql = "Insert Into Air_Customers VALUES(','" & txtname.Text & "','0','0')"
I would like that final '0' to automatically be incremented in the database and not have to manually put it in.
Anyhelp is appreciated. If this does not make sense I will try re-writing it.
•
•
Join Date: Sep 2007
Posts: 1,057
Reputation:
Rep Power: 3
Solved Threads: 61
You cannot have two, however you can get around it three ways:
1. Make a separate table and relate the two. Each will have a unique identity column.
2. Do an update statement after inserting the values into the database, but this requires two hits to the database, which you should try to avoid.
3. Solution: Do a subquery that selects the highest/last entry for frequireflyernum, then increment it by one.
Example:
Now what this will do is subquery the same database for the highest frequentfliernum and then add 1 to it. Now if it doesn't return anything, it will return the value "0", which when you add 1 to it, it becomes "1".
Learn to use parameters. they are very easy. The code I gave you uses parameters. It helps prevent sql injection, and poor formatting.
1. Make a separate table and relate the two. Each will have a unique identity column.
2. Do an update statement after inserting the values into the database, but this requires two hits to the database, which you should try to avoid.
3. Solution: Do a subquery that selects the highest/last entry for frequireflyernum, then increment it by one.
Example:
sql = "Insert Into Air_Customers (CustomerName, FrequentFlierMiles, FrequentFliernum) VALUES (@CustomerName, 0, COALESCE((SELECT TOP 1 FrequentFlierNum FROM Air_Customers ORDER BY FrequentFlierNum DESC), 0)+1)" commandname.Parameters.AddWithValue( "@CustomerName", Trim(txtname.Text) )
Learn to use parameters. they are very easy. The code I gave you uses parameters. It helps prevent sql injection, and poor formatting.
•
•
Join Date: Feb 2008
Posts: 9
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Thank you for your help!
|
•
•
•
•
•
•
•
•
DaniWeb ASP.NET Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
•
•
•
•
ajax asp database decimal seperator thousand seperator when using sql server deleting records from ms sql table where columns have duplicate values developer development fraud hack hacker identity identity theft investigation management studio 2005 microsoft msdn news office paper security software sql sql cache dependency with polling-based invalidation survey vista
- Autonumber in a SQL Server table (MS SQL)
Other Threads in the ASP.NET Forum
- Previous Thread: Multiple DDLs populating different fields in a form
- Next Thread: Drop Down List, Text Box to DataGrid..
