|
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
February 2nd, 2004, 07:27 AM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Select record after inserting it
Im kind of following the beginning ASP 3 examples for inserting the records and it requires this at the top of the page
Ive then put all the form data into a string which i use in this adodb.command:
DIM objInsert
SET objInsert = Server.CreateObject("ADODB.Command")
objInsert.ActiveConnection = MM_connCourses_STRING
objInsert.CommandText = sqlStr & ") " & valuesStr & ");"
objInsert.CommandType = adCmdText
objInsert.Execute
This works fine. Im now trying to get this record's ID and then create a string with a url and this id appended to the end of it use the response.redirect(strUrl) to go to this the page that this data is fore, however i am getting an error that says either BOF or EOF is true. So its not selecting the record. however, when i check the database the record has been inserted.
Can somebody please help.
Andy
|
February 2nd, 2004, 07:52 AM
|
|
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
How does the code that retrieves the ID again look like?
After you have inserted a record, the record is not automatically available in the client. You'll need to execute another SELECT statement to retrieve the record into a recordset
Cheers,
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
|
February 2nd, 2004, 10:32 AM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I created a select statement that was something like
"SELECT ID FROM Courses WHERE Title = '" & request.form("Title") & "' , Levels = '" & request.form("Levels") & "';"
level is a reserved word but levels is not so its ok. I created the recordset using Dreamweaver and would look something like this:
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.ActiveConnection = MM_connCourses_STRING
objRS.Source = sqlStr
objRS.CursorType = 0
objRS.CursorLocation = 2
objRS.LockType = 1
objRS.Open()
|
February 2nd, 2004, 10:36 AM
|
|
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
You're doing a SELECT so you'll need to use AND and OR to separate columns in the WHERE clause:
SELECT Bla bla bla WHERE Column1 = 'Something' AND Column2 = 'SomethingElse'
Cheers,
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
|
February 2nd, 2004, 10:38 AM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
what im going to do is change the ID field from an autonumber to a plain old integer which does not allow duplicates. Ill then just SELECT MAX(ID) FROM Courses and use this number for the ID field in my insert statement and for the redirect URL. This was suggested to me by a colleague.
Does anybody have any other suggestions?
|
February 2nd, 2004, 10:56 AM
|
|
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
Using Max is a bit tricky in a multi-user environment.
Here's why:
1. You insert a record
2. Someone else inserts a record
3. You try to retrieve the Max value
4. Someone else tries to get the Max value.
In a busy environment, this scenario is not unlikely. Both of you will end up with the same Max value (that is, the ID from the second insert).
IMO, it's better to you requery the Recordset for the values you just inserted and then retrieve the new ID.
Alternatively, you can use a GUID instead of an AutoNumber. GUIDs can be generated at the client as well, so you can create a new ID which you can pass to the database with the INSERT statement.
Cheers,
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
|
February 2nd, 2004, 12:45 PM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
As there are only 2 people that are responsible for inserting the data I can't be bothered especially as I'm probably going to be out of a job in a few months. If they keep me on and its a problem then ill reconsider.
Also what are GUID's?
|
February 2nd, 2004, 01:30 PM
|
|
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
Now, *that* is a good reason to write sloppy code..... Not ;)
GUIDs are Globally Unique Identifiers. In SQL Server, the datatype for a column can be set to a uniqueidentifier. You'll see them everywhere on the Windows platform. (COM / COM+ + uses them a lot as well).
They look like this: EA18E707-08C4-416E-8370-4382AG8C11F2
For more info: http://whatis.techtarget.com/definit...213990,00.html
Cheers,
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
|
February 2nd, 2004, 06:21 PM
|
Friend of Wrox
|
|
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
You can do this with ado
rst.AddNew
'omit the emp_id autonumber field
rst!fname = strFirstName
rst!lname = strLastName
rst.Update
strsomestring = rst!emp_id
then you can reference the newly created autonumber value and you do not have to double guess the Max() value.
Does that work?
Sal
|
February 3rd, 2004, 05:50 AM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
ah ha! iyv jist spoyyed tha mistayc yo pontid owt im ny secund psot :) I can't believe i did that! LOL! Cheers for the info on GUIDs.
|
|
|