Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > BOOK: Beginning ASP 3.0
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning ASP 3.0 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 5th, 2004, 07:59 PM
Authorized User
Points: 49, Level: 1
Points: 49, Level: 1 Points: 49, Level: 1 Points: 49, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Chicago, IL, USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Updated from Access to SQL - Some problems

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.
  #2 (permalink)  
Old March 6th, 2004, 04:56 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
  #3 (permalink)  
Old March 9th, 2004, 03:10 PM
Authorized User
Points: 49, Level: 1
Points: 49, Level: 1 Points: 49, Level: 1 Points: 49, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Chicago, IL, USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!
  #4 (permalink)  
Old March 9th, 2004, 05:02 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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:
Code:
  Dim NewUserID
  Dim rsUsers
  Set rsUsers = Server.CreateObject("ADODB.Recordset")
  rsUsers.Open "Users", objConn, adOpenKeyset, adLockOptimistic, adCmdTable

  rsUsers.AddNew
  rsUsers("EMailAddress") = "Email Address"
  ...
  rsUsers.Update

  NewUserID = rsUsers("ID")  
  Response.Write("NewUserID is " & NewUserID)
  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.
  #5 (permalink)  
Old March 9th, 2004, 05:29 PM
Authorized User
Points: 49, Level: 1
Points: 49, Level: 1 Points: 49, Level: 1 Points: 49, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Chicago, IL, USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!

  #6 (permalink)  
Old March 9th, 2004, 05:57 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
  #7 (permalink)  
Old March 10th, 2004, 06:28 PM
Authorized User
Points: 49, Level: 1
Points: 49, Level: 1 Points: 49, Level: 1 Points: 49, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Chicago, IL, USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You're the greatest!
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
java-access problems ruzdi JSP Basics 3 January 24th, 2008 06:51 AM
Access 2003 Wizard Problems Steve2909 Access 7 April 12th, 2006 07:14 AM
Sql server access problems mbje ADO.NET 0 October 29th, 2004 03:18 AM
SQL Access/ASP.NET data access issue saeta57 SQL Server ASP 1 July 4th, 2004 04:29 PM
SQL Access/ASP.NET data access issue saeta57 Classic ASP Databases 1 July 4th, 2004 03:32 PM



All times are GMT -4. The time now is 05:17 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.