Re: FN-FORUM ASP & Access Query

date posted 1st September 2001 13:40

Hi Jonathan

As no one else has answered, and I'm in work I thought I'd take a stab at
helping you out!

Now my understanding of the problem is that you want people to add a pub and
then go straight to adding a review for the pub. The problem you seem to
have is retriveing the Primary ID of the pub once it's been entered into the
database. Now this is actually quiet easy. What you have to do is use a
different type of cursor, instead of using a forward-only cursor (the
default), you need to use a Keyset cursor. What this will do is allow you to
return data from a recordset, after you've written to it.

I assume that you're using a recordset to update your database, so your code
will look something like this:

Set updateRS = server.createObject("ADODB.Recordset")
updateRS.Open "tableName", "DSN", , ,adCmdTable
updateRS.AddNew
updateRS.Fields("pubName") = pubName
updateRS.Fields("pubAddress") = pubAddress
updateRS.Update
updateRS.Close
Set updateRS = nothing

Now what we want to do is return the primary id to us, so we'll slightly
modify the code above. This is assuming that you're including the
ADOVBS.inc file with the page

Set updateRS = server.createObject("ADODB.Recordset")
updateRS.Open "tableName", "DSN", adOpenKeySet, adLockPessimistic,adCmdTable
updateRS.AddNew
updateRS.Fields("pubName") = pubName
updateRS.Fields("pubAddress") = pubAddress
updateRS.Update
pubID = updateRS.Fields("PrimaryID")
updateRS.Close
Set updateRS = nothing

What happens here is:

1.Information is written to the recordset
2.The recordset is updated, putting the information into the database. But
this time it also fetches out any new information (in this case the Primary
ID field).
3.We assign the new information to a variable

This means that we now have the Primary ID for the pub we've just created.
We can then use this to generate an appropriate review page for that pub.

HTH, and if you need any help with it just give me a yell

Norman



> When the pub isn't listed user can add them. However they aren't taken to
> the actual review form
> afterwards. Is there anyway of after submitting knowing the Primary Id of
> the pub they've entered
> and redirecting them to a review form which will know which pub to add it
> too?
>
> Thank everyone. Feel free to test it and see... make sure the pub name is
> obvious.