| Author |
 Topic  |
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
 Posted - 06 January 2004 : 11:44:47
|
I'm trying to run this query in Access 2000:
INSERT INTO tblBasket ( CardTypeID ) VALUES ( 5 );
SELECT @@Identity;
but I'm fairly sure there can only be one 'statement' per query? |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
CarKnee
Junior Member
USA
297 Posts |
Posted - 06 January 2004 : 12:07:06
|
Try something like this:
Set insertCmd = Server.CreateObject("ADODB.Command")
insertCmd.ActiveConnection = strConnString
insertCmd.CommandText = strSQL
insertCmd.Execute
' Get the new ID number
Set adoRS = insertCmd.ActiveConnection.Execute("select @@identity")
intID = adoRS(0)
Set adoRS = Nothing
insertCmd.ActiveConnection.Close
Set insertCmd = Nothing
|
 |
 |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 06 January 2004 : 13:26:55
|
| as long as you do not close your connection, you can issue the two commands seperately, it will still return the correct identity. |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 06 January 2004 : 14:36:58
|
Thanks guys, that confirms what I had suspected, but unfortunately I'm still having trouble getting back the correct value for the @@Identity. Oddly, it seems to return a zero value.
tblBasket has a Primary Key - BasketID, and it is an 'autonumber' field. Do I need to do anything else to configure the table or its contents? I had read that in Access, it still might might not be properly configured as an Identity field?
I'm probably trying to push the limits of Access a little too far as, essentially, I am trying to use Access Queries in the same way that I would have used SQL Stored Procedures. There don't seem to be many resources online for hand-writing Access Queries, so I guess I'll have to get hold of a good book. |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 06 January 2004 : 16:09:49
|
are you using the Jet drivers or the older ODBC drivers. If you aren't using jet it won't work.
you could always requery the table to find the id, for instance if you wanted the id of the reply you just posted, issue a query for max(reply_id) where member_id is you.
so for your basket, when you do the insert store an extra column which allows you to retreive the id |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 06 January 2004 : 16:48:31
|
Oh yes, I'm using the Jet drivers. Yeah, I suppose an additional query to find the maximum value of BasketID might work, but I'm concerned about the possibility of another basketID having been added in the meantime and messing things up.
Know of any good books which might particularly focus on writing MS Access Queries? Not keen on buying an Access 'Doorstep' only to find 10 pages in 1000 giving instructions on just using the query wizards. |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 06 January 2004 : 18:20:00
|
The best book I know is a Sybex edition: Access 2000 Developer's Handbook, by Paul Litwin. There is a 2002 version as well. In fact there are two volumes for each Access version since Access 2000, but you probably need just Volume 1.
I've used SELECT @@IDENTITY with Access 2000 and never had a problem. I usually add an alias at the end, so that I can refer to the retrived field by name:
SELECT @@Identity As MyID
As I wrote, never had a problem using it. What Access version are you using? Have the latest MDACs?
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 07 January 2004 : 04:36:06
|
| My version is Access 2000 (9.0.6926 SP3) and I assume I have the latest version of MDAC as all the Windows 2000 updates are in place. I seem to recall the installation of MDAC version 2.8? |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 07 January 2004 : 12:00:19
|
| Don't worry, I've used the re-querying max(primary key) method for now. The project is somewhat time-sensitive so I've not got long to play. |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 07 January 2004 : 14:20:46
|
| It's not a matter of worry, it's just that I get puzzled when something that should work does not work. I know SELECT @@IDENTITY works, so if you want I can take a look at the code. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 07 January 2004 : 15:06:21
|
Ah OK thanks.
I have a query (qryNewBasket) that adds data into a field in a table (tblBasket) to create a new row, as follows:
INSERT INTO tblBasket ( CardTypeID ) VALUES ( 5 );
I also have another query (qryBasketIdentity) which simply returns the @@Identity:
SELECT @@Identity AS BasketID;
My ASP code is essentially as follows:
Dim objDB, objRS, strSQL
Const adCmdStoredProc = 4
Const adExecuteNoRecords = 128
Set objDB = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.RecordSet")
objDB.Open strConnect 'strConnect defined elsewhere
strSQL = "qryNewBasket"
objDB.Execute(strSQL, ,adCmdStoredProc + adExecuteNoRecords )
strSQL = "qryBasketIdentity"
set objRS = objDB.Execute(strSQL, ,adCmdStoredProc)
if not objRS.EOF then
response.write objRS(0)
end if
objRS.Close
Set objRS = Nothing
objDB.Close
Set objDB = Nothing
The best I've been able to achieve is to get 0 (zero) displayed. The table has in the region of 15 rows, So I was expecting a higher number.
|
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
Edited by - D3mon on 07 January 2004 15:09:01 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 07 January 2004 : 19:34:31
|
Try this
Dim objDB, objRS, strSQL, objCmd
Const adCmdStoredProc = 4
Const adOpenKeySet = 1
Const adExecuteNoRecords = 128
Const adLockOptimistic = 3
Set objDB = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.RecordSet")
Set objRS = Server.CreateObject("ADODB.Command")
objDB.Open strConnect 'strConnect defined elsewhere
Set objCmd.ActiveConnection = objDB
objCmd.CommandType = adCmdStoredProc
objCmd.Text = "qryNewBasket"
objCmd.Execute
strSql = "qryBasketIdentity"
objRS.Open strSQL,objDB, adOpenKeySet, adLockOptimistic, adCmdStoredProc
if not objRS.EOF then
response.write objRS(0)
end if
objRS.Close
Set objRS = Nothing
objDB.Close
Set objCmd = Nothing
Set objDB = Nothing
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Steve D.
Average Member
USA
640 Posts |
|
| |
Topic |
|
Topic Locked
