Upsizing Access to SQL - Can't add new records

Hello,

I have some asp pages that used to update an access database.

I have uploaded the access database to sql server and changed lots of things in my code and databases including the connection string, date delimiters (# to '), database fields that sql doesn't like, and created new sql primary keys that auto increment.

It seemed like i had most everything ironed out, when I realized that the sql server would allow me to update and query records, but not to add brand new records.

What might be the problem?

I've damaged several pieces of furniture and I'm afraid my head might be next!

Thanks!

How are you trying to add records?

Check over the upsized table definitions. One thing that sometimes gets lost in upsizing is primary key information. Missing primary keys can make recordsets be non-updateable.

Thanks!

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:







<!--#include file="connection.asp"-->

<%

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 hope we can figure this one out!

Okay. Someone way able to help me and provide the solution. It was the way I opened my record set. I should have used adOpenKeyset instead of adForwardOnly.


Thanks to Imar Spaanjaars on the www.wrox.com Beginning ASP 3.0 book forum!