|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
December 9th, 2003, 11:49 PM
|
|||
|
|||
|
Stored Proc/Subquery or something else?
Yep, I'm still using Access 97
 I have an application that writes a record with a unique, incremented ID to a database, immediately after which I need to write three records to a related table using ID as the foreign key. So I need to run a query that grabs the highest ID in one table and a text input string, and INSERTs them both into another table. This is essentially going to be a stored procedure (so far as you can do them in Access). Anyway, here's the SQL which as far as I can see is legal: PHP Code:
As you can see I'm trying to use a subselect to get the max ID value from one table, while the other value is just passed in normally. Right now it's a hardcoded string but will eventually be a parameter passed from the application. Should work, yes? Access has no issues with me creating and saving the query. However when I try to run it, it crashes Access! Same error each time (Memory could not be read blah blah blah). Any thoughts on how else I could write this query? At this rate I am going to have to pass the MAX(ID) back to the application, insert it there and then run several separate queries against the database for the INSERT. Don't really want to do that - seems like a lot of overhead. I'm using ASP.NET by the way...
|
|
#2
December 10th, 2003, 12:13 PM
|
||||
|
||||
|
Code:
insert
into tblprofitsummary
( id
, lineitem
)
select max(id)
, '<COGS>'
from tbldocuments
where customername = 'mycustomer'
rudy http://r937.com/
|
|
#3
December 10th, 2003, 12:25 PM
|
|||
|
|||
|
Bingo. Funnily enough I got exactly the same answer from Dave Maxwell on Sitepoint at about the same time
 Don't you hate the way self-evident answers are really hard to see until you see them? Yep, I know all about the limitations of Max(ID). I'm not comfortable with using it, however I can't think of any other way to do it unfortunately. The ID field is set to "increment" rather than "random" while cross-referencing with the customer name should help ensure it's pulling out the correct record. On to the next problem...
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > Stored Proc/Subquery or something else? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
