Hello,

Ive updated my database from access to sql and for some reason the register.asp & adduser.asp do not put people into the menuforregisteredusers.asp page rather they give the new user the login.asp page.

I'm having a hard time understanding why.

to test it i've response.written the PersonID field instead of random redirecting the adduser page to the menuforregisteredusers page so that this value shows.

It shows blank which tells me that the autogenerated value from the sql database was not written to the session variable.

However, when i look at the database, I see the new record with its new PersonID field. And if i hit the refresh button, the Person ID session value is shown.

Any help would be greatly appreciated.

I think you'll need to post some code, and provide some background about the problem.

With just the filenames, it's pretty hard to guess what goes wrong where ;)

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

OK, I think I’ve figured some things out and can describe my problem better.

Like I said before, I moved from an access database to a sql server database.

What seems to be happening is that when I used the access database I could write values to the database and then attach them to local session variables in one step.

For some reason SQL server does not want to allow me to write a new record to the database, retrieve the automatically generated UserID, and attach it to the session variables in one step.

I can kinda make the page work by adding the new record in one step and then going back to the database and filtering the record set again, thus retrieving the new record with the new User ID value.

The problem with that is that I don’t have any real unique value to filter except the auto-generated user ID by the SQL database.

Why is it that access will allow me to use its new autogenerated UserID and SQL won’t? Is there some kinda of setting I’ve got wrong. I really hope so, because I think making a second trip to the SQL database is the wrong way to go.

If there is no way to do this, is there anyway to filter my second record set by the last User ID entered in the database before it reaches EOF? Even so, that won’t really guarantee that the database is pulling the same record, if by some chance someone else is entering a record at the same time.

Here’s my code. It’s a slight variation on the books add user page:




<%
  Dim rsUsers
  Set rsUsers = Server.CreateObject("ADODB.Recordset")
  rsUsers.Open "Users", objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable

  If Session("userID") <> "" Then ' currently logged-on user
    rsUsers.Filter = "UserID = '" & Session("userID") & "'"
  Else ' New session
    rsUsers.Filter = "EMailAddress = '" & Request.Form("email") & "'" & _
                     "AND Password = '" & Request.Form("password") & "'"
    If rsUsers.EOF Then ' User not found
      rsUsers.AddNew ' ...so add a new record
' Else
' Email address and password matched with DB records -
' In this case we'll allow this to update user's useral details
    End If
  End If
                                                  ' write useral details to record
  rsUsers("EMailAddress") = Request.Form("email")
  rsUsers("Password") = Request.Form("password")
  rsUsers("FName") = Request.Form("FName")
  rsUsers("LName") = Request.Form("LName")
  rsUsers("Fax") = Request.Form("Fax")
  rsUsers("Organization") = Request.Form("Organization")
  rsUsers("Phone") = Request.Form("Phone")
  rsUsers("StreetAddress1") = Request.Form("Address1")
  rsUsers("StreetAddress2") = Request.Form("Address2")
  rsUsers("City") = Request.Form("City")
  rsUsers("State") = Request.Form("State")
  rsUsers("PostalCode") = Request.Form("PostalCode")
  rsUsers("Country") = Request.Form("Country")
  rsUsers("Active") = True
  rsUsers("LastLogin") = Now
  rsUsers.Update ' update the database


    rsUsers.Filter = "EMailAddress = '" & Request.Form("email") & "'" & _ ' pull out the updated record again
                     "AND Password = '" & Request.Form("password") & "'"




  Dim strName, strValue ' create session variables
  For each strField in rsUsers.Fields
    strName = strField.Name
    strValue = strField.value
    Session(strName) = strValue
  Next
  Session("blnValidUser") = True



  Session("UserID") = rsUsers("UserID")

              ' declare that current user is validated

  Response.Redirect "MenuForRegisteredUsers.asp"

%>



Thanks for your help! I really hope you guys can help!

Right, I think you're running into a few problems at the same time.

For example, did you define the constants you are using? Did you include the file adovbs.inc or add a reference to it somewhere? If not, check out this article: http://www.adopenstatic.com/faq/800a0bb9step2.asp If I were you, I'd go with method 1, just as Ken suggests.

Once you do that, you'll probably see some other errors appear. One error will indicate that using AddNew in combination with a ForwardOnly recordset is not going to work. If I am not mistaken you'll need an OpenKeyset recordset instead. This will work:   At the end of the code block, you can simply retrieve the new ID of the user, by getting it from the ID property in the newly added record in the recordset.

Does this help?

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

You're a genious!

adOpenKeyset worked perfectly. I'm glad it involved the connection settings and not simply the way SQL works.

I also found that rsUsers.requery worked, but the way you suggested is the correct way.

Thanks!

Well, there are other ways as well..... (me, always nitpicking)

You can execute an INSERT statement, followed by an SELECT @@IDENTITY or SELECT SCOPE_IDENTITY( ) and then execute that against your connection. It'll work faster than using the AddNew method.

Something along these lines should work:

Dim SQL
SQL = "SET NOCOUNT ON" & vbCrLf & "INSERT INTO Users (FirstName, LastName, ...) VALUES('Bla', 'Bla die Bla', ...)" & vbCrLf
SQL = SQL & "SELECT SCOPE_IDENTITY( )" & vbCrLf & "SET NOCOUNT OFF"

Dim rsUsers
Set rsUsers = objConn.Execute(SQL)
Response.Write("ID is " & rsUsers(0))

This looks a bit odd when you use it directly in ASP code by constructing the SQL statement with ASP. So, in this context, it may not be worth the trouble, and you may be better of using AddNew.

However, in combination with a Stored Procedure using OUTPUT parameters, this is a clean and super fast way of dealing with these kind of inserts.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

You're the greatest!