Using Dynamic Text to pull specific info from Access DB

[Dsrt]

Setup: I have an .asp page with a form that submits data to an Access database. When the submit button is clicked, the data is entered into the db and the user is automatically sent to a "Thank you" page.

On the "Thank you" page, I have dynamic text set up to autopull the primary key autonumber and display it (in essance, I will use this number as the trouble ticket number).

Problem: I can get it to display the autonumber with no problems, but it shows the first number in that field. I need it to show the last number, or the one that was just created when the user clicked submit.

I've created a recordset that uses just the ID field and then I've created a dynamic text in the server behaviors linking it to the ID field in the recordset (the string for this is <%=(Recordset1.Fields.Item("ID").Value)%>).

Anyone have any ideas on how to get it to pull the correct number?

Thanks in advance for any time put into this question.

 

[CTekMedia]

I don't know the correct syntax for asp (nasty stuff but this is what it looks like in ColdFusion. The SQL should be the same:

<cfquery name="Recordset_SwitchNum" datasource="Switch_Assign">

SELECT MAX(ID)
FROM tb_25KV

</cfquery>

<CFSET ID2=Recordset_SwitchNum.Expr1000>

"ID" is the field name. "Switch_Assign" is the table name.

The last bit (CFSET) creates a variable from the query output - which is the last ID created.

This method is quick and dirty - it won't work in a busy environment, i.e., it is possible another user could create an entry right at the same time and for that record's info to be pulled.

 

[pixl8r]

What is the SQL query you are using to populate your recordset?

 

[CTekMedia]

Correction to above: the table name is "tb_25KV". The datasource is "Switch_Assign".

Sorry

So the SQL pulls the last (MAX) number from the field "ID". The <CFSET> tag makes the variable ID2 equal to the result of the above query. You then query the datbase again, pulling up the data WHERE ID = ID2 and then display that as your "trouble ticket" or display of what was originally entered in the form.

 

[Cheech]

Select Max(id) from ... will work in asp as well

[Peace][Pipe]

 

[traingamer]

Also see: thread181-1010703

traingamer

 

[Dsrt]

Thanks for the advice and sorry it took so long to respond back...I've been slammed at work over the past several days.

I figured out the solution, I just needed to filter the data correctly.

Once again, thanks for the help.