SQL and autonumber
I have a form which first creates a record in one database. While it's doing that it makes the autonumber into a variable (intSaleID). That intSaleID is then referenced with each product sold.
The problem is this doesn't work with SQL Server. When using Access this works fine. Now that SQL server is being used it doesn't allow me to obtain the autonumber field (SaleID) as a variable.
Does anyone know how to do this or what a common resolution to this problem would be?
Thanks for any help.
Peter J. Boettcher posted this at 13:55 — 25th February 2002.
They have: 812 posts
Joined: Feb 2000
Convert your query to a stored procedure and then use @@identity to get the new record id.
For an example check out http://www.aspfaq.com/show.asp?id=2174
PJ | Are we there yet?
pjboettcher.com
artsapimp posted this at 18:13 — 26th February 2002.
They have: 330 posts
Joined: Apr 2000
This works great. Thank you.
Now I am having another problem. I know it has a simple resolution but I don't know what that is. How do I link to a table in a different database? Both databases are on the same SQL server.
Thanks for your help.
Free Math Test
Fun Math Games
Peter J. Boettcher posted this at 22:12 — 26th February 2002.
They have: 812 posts
Joined: Feb 2000
For selecting data you can create a view, for example:
CREATE VIEW ViewName
AS
SELECT a.ColumnName1, a.ColumnName2, b.ColumnName1, b.ColumnName2
FROM database1.owner.table1 a, database2.owner.table2 b
WHERE a.ColumnName1 = b.ColumnName1
PJ | Are we there yet?
pjboettcher.com
artsapimp posted this at 22:26 — 26th February 2002.
They have: 330 posts
Joined: Apr 2000
Ok, that makes sense.
Thank you for your help.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.