Need help looping db

[nsanto17]

I using a SQL Database and i am creating a form to add records to the database. I need it to loop though the Loan_ID field to get the next available number and have that number entered into the Loan_ID field in the form. This way when someone creates a new Loan they get the next avaiable loan number.

I don't know where to start.

Thanks in advance.

Nick

 

[vacunita]

Can you be a little more specific of your DB? Is it MYSQL? MSSQL? PostgreSQL? And how your tables are set up? Is the Loan_ID key set as Autoincrement?

MYSQL for instance has something called [blue]LAST_INSERT_ID()[/blue]
This will retrieve the ID of the last inserted row.

If its autoincrement you can query for the largest number for that ID and then ADD 1 to it. something like: SELECT Loan_ID from mytable ORDER BY Loan_ID DESC LIMIT 1. this will return the numerically largest Loan_ID.








----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[nsanto17]

Oh i see...

I have set up MSDE/MSSQL.

That Loan_ID field is set up as the Primary Key and with a datatype of varchar.

Thanks

Nick

 

[vacunita]

MSSQL. Hmm, I'm not too familiar with it, but i believe if the column is an identity column, you need to use SCOPE_IDENTITY.

Perhaps you can get more help posting this question in the MSSQL forum here: forum183



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[GUJUm0deL]

If the Loan_ID is set as a primary key with an identity seed then you don't need to do this. Every new loan request will create a new ID in a v order.

If for what every reason you still need to loop over the table to get the next Loan_ID, run a SQL query after the new loan has been added successfully.

Select MAX(Loan_ID) as LID
from tableName

Then use the LID alias name for the next record.

http://www.techonthenet.com/sql/max.php

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[johnwm]

GUJU - how do you add an identity seed to a VARCHAR field?

Nick - I would follow vacunita's advice. Use SCOPE_IDENTITY which will avoid the problems caused when you get two simultaneous requests for a new ID

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[GUJUm0deL]

johnwm,

how do you add an identity seed to a VARCHAR field?

Sorry, didn't see his second post where he mentioned the Loan_ID was a varchar field.

In that case, Nick should follow vacunita's advice.



_____________________________
Just Imagine.

http://www.imajinarts.com