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

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.

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()

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.

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?

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.

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?

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.

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

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.