• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 610
  • Last Modified:

adOpenKeyset not returning pKey value after new record is added

Hi,
I'm having a problem with adOpenKeyset not returning the primary key value for a row after data is entered. I have the same code working on many other tables & pages, but for some reason, it's not working here.

There is a trigger for insert on this table that creates a new entry in another table, and I'm thinking that may be the problem is.

Here's the code - FYI, stripIt(n) is a function to trap any malicious input, and companyID is the primary key on this table.

      Set rs=Server.createObject("ADODB.Recordset")
      rs.Open "tblCompany", conn, adOpenKeyset, adLockOptimistic, adCmdTable
      rs.addNew
            for each fld in rs.Fields
IF fld.Name<>"companyID" AND len(request.form(fld.Name))>0 THEN fld.Value=stripIt(request.form(fld.Name))
            Next
      rs.Update
      companyID=rs("companyID")
      rs.close

I can't seem to find any info on this anywhere, so any help is greatly appreciated.
0
m_soltis
Asked:
m_soltis
  • 6
  • 4
1 Solution
 
fritz_the_blankCommented:
One thing right away, please change:

 rs.Open "tblCompany", conn, adOpenKeyset, adLockOptimistic, adCmdTable

to:

 rs.Open "SELECT * FROM tblCompany WHERE 1=0", conn, adOpenKeyset, adLockOptimistic, adCmdTable

as that will return only an empty recordset.

Now what type of database are you using here?


FtB
0
 
m_soltisAuthor Commented:
The DB is SQL 2000.

An empty recordset isn't a problem as I'm not trying to return any values. Again, I use this code without any problems all of the time, it's just not working on this table with an insert trigger.

Thanks
0
 
fritz_the_blankCommented:
No, I am not expressing myself clearly then.

If you do this:

 rs.Open "tblCompany", conn, adOpenKeyset, adLockOptimistic, adCmdTable

You are returning each and every record in the table, and that is something that you do not want to do--especially if you have lots of records in the table. This:

rs.Open "SELECT * FROM tblCompany WHERE 1=0", conn, adOpenKeyset, adLockOptimistic, adCmdTable

however, returns an empty recordset which is exactly what you want!

FtB

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
fritz_the_blankCommented:
As far as the other issue goes, does this trigger cause something to happen on another table or do you have an insert trigger pointed at this table?

FtB
0
 
m_soltisAuthor Commented:
Ahhhh, I gotcha. I'll make the change.

What do you think about the empty ID?
0
 
m_soltisAuthor Commented:
It's pointed at another table & inserts a new record on that table as well.
0
 
fritz_the_blankCommented:
So, once the insert is done on this table, it fires a trigger to do something to a different table then? I am wondering if something happens to remove the connection or recordset's "focus" from the record you just added...

If you remove the trigger, it works fine?

FtB
0
 
fritz_the_blankCommented:
You may have to change what you are doing and grab it with a select scope_identity:

http://www.sqlteam.com/item.asp?ItemID=319

FtB
0
 
m_soltisAuthor Commented:
I figured it revolved around an @@IDENTITY issue - I was just hoping for a solution that was a single straight shot from this page.

Thanks for your help.
0
 
fritz_the_blankCommented:
Glad to have helped, but if you want to be more accurate, use the scope_identity. That way you will get the actual value created by your transaction rather than the latest entry to the table.

FtB
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now