calling from a sql Table

[boux123]

Hi,

I am not too sure if i am in the wrong forum. Basically, I have created a form that gets user information and is stored into a sql database. However prior to storing into the sql table, I want to capture the next available field from the sql table and display it to the user. For example, I submit a form to the sql table. No, there is an identiy field in the form(item #) that gets auto generated and is auto incremented when a nex form is added. No i want to capture the next available item# from the sql table. Please advise as to how this can be achieved.

Thx

 

[monksnake]

Assuming you have a viable connection to your database, you can do it like this:


oRS = Recordset Name

oRS.Source = "select (max(ITEM#FIELD) + 1) nextId from tablename";
oRS.Open();
   var nextId = oRS.Fields('nextId').value;
oRS.Close();

<.

 

[onpnt]

Look up using the MAX() function in transact-sql. You can also use ORDER BY and descending orders.

With this and a record set (ADO) .Execute on the statement you build you will have the fields available to your application for viewing on load

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]