we are having great trouble in getting our database to update when we try to add another user (register). The code from the book and website doesnt seem to work at all, so we are trying the insert into method. This is from the AddUser.asp file - the code in blue is the original code, and that in red is our code:

  rsUsers.Filter = "PersonID = '" & Session("PersonID") & "'"
  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
      insertSQL = "insert into Person(emailaddress, [password], familyname, givenname, StreetAddress1, StreetAddress2, City, State, PostalCode, Country) " & _ values (" & Request.Form("email") & ", " & Request.Form("password") & ", " & Request.Form("FamilyName") & ", " & Request.Form("GivenName") & ", " & Request.Form("Address1") & ", " & Request.Form("Address2") & ", " & Request.Form("City") & ", " & Request.Form("State") & ", " & Request.Form("PostalCode") & ", " & Request.Form("Country") & ",1,#" & Now & "#);"
      objConn.execute insertSQL
' Else
' Email address and password matched with DB records -
' In this case we'll allow this to update user's personal details
    End If
  End If
                                                  ' write personal details to record
  'rsUsers("EMailAddress") = Request.Form("email")
  'rsUsers("Password") = Request.Form("password")
  'rsUsers("FamilyName") = Request.Form("FamilyName")
  'rsUsers……………..

We would be incredibly grateful if anyone out-there can give us any advice AT ALL on where to go from here, as our deadline is rapidly approaching!

Hi there,

Can you define "great trouble"? Do you get an error message?

Before you execute insertSQL, write it to the browser window. E.g.:

insertSQL = "bla bla bla"
Response.Write("Sql Statement is " & insertSQL)
Response.End
objConn.execute insertSQL

If you put in that code, what is the output? What happens when you copy and paste the output in the query builder of your database?

Cheers,

Imar

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

we have added that section of code and when the submit registration button is clicked we now get:

Sql Statement is insert into Person(emailaddress, [password], familyname, givenname, StreetAddress1, StreetAddress2, City, State, PostalCode, Country, active, lastlogin) values ('[email protected]','123','Man','Super','6 Krypton Street','','Kypton City','Cornwall','KR1 5TT','Krypton',1,#3/9/2004 7:50:13 AM#);

suggesting perhaps that we are getting the information from the right places, but that it isnt going to the database?

From what I can see, your SQL statement looks fine.

Did you copy and paste it in Access? That's usually the best way to test it out.

What connectionstring are you using? How do you create your connection (objConn)? And what error do you get?

Imar


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

we have this line in the clssfd.asp file:

objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("\goinggoinggone\db\Classified_2000 .mdb") & ";"

You'll have to help me help you. How about you answer the other questions as well?

Did you copy and paste it in Access?
How do you create your connection?
And what error do you get?

It's going to take ages if you only answer 25% of my questions each time. ;)

Imar



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

We are no longer getting a error message of any type, the broswer simply does not display a web page (the normal 'this page can not be displayed'). As for access we are use access 2000, but use 97 to test the code without any problems.we thought that what we posted in our previous message was how we created our connection - the AddUser.asp page refers to clssfd.asp, and that is the code we have for connecting to the DB. if this is not the connection you are talking about please define what you mean by connections, as we are new to asp and have been thrown in at the deep ends,

Many thanks!

What you posted in your previous message is how you open the connection; not how you create the connection object. You probably have something like
Set objConn = Server.CreateObject("ADODB.Connection") in your code, which is how it's supposed to be. Are you sure server.mappath("\goinggoinggone\db\Classified_2000 .mdb") results in a valid path to the database? That is, does the current root of the site have a goinggoinggone folder?

If you get the "the page cannot be displayed" error, you may need to turn off friendly error messages. In Internet Explorer, open the Advanced tab of the Options dialog, and uncheck "Show friendly HTTP error messages". For your Web site in IIS, open the Properties dialog, switch to the Home Directory tab, and click the Configuration button. On the Debugging tab, make sure "Send detailed ASP error messages to the client" is checked.
Then run the page, and see if you get any error information.

What I meant with testing in access: copy the SQL statement you posted here and paste it in a new Access Query. For this to work, you may need to switch to SQL View of the Query Designer. What happens when you run the code there? Is the record inserted correctly?



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

Set objConn = Server.CreateObject("ADODB.Connection")

We are using the connection object above.

We are happy that the mappath to the database is correct as when this is modified by removing goinggoinggone we are getting the same result, below.

Sql Statement is insert into Person(emailaddress, [password], familyname, givenname, StreetAddress1, StreetAddress2, City, State, PostalCode, Country, active, lastlogin) values ('[email protected]','123','Man','Super','6 Krypton Street','','Kypton City','Cornwall','KR1 5TT','Krypton',1,#3/9/2004 7:50:13 AM#);

We have ran the sql statement in access and got it working by creating a new form within access, so we are fairly happy that it is work correctly.

The error message we are getting with Show friendly HTTP error messages disabled is shown below.

ADODB.Recordset error '800a0cb3'

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

/goinggoinggone/AddUser.asp, line 41

Line 41 is in red!


<%
  Dim rsUsers
  Set rsUsers = Server.CreateObject("ADODB.Recordset")
  rsUsers.Open "Person", objConn
  If Session("PersonID") <> "" Then ' currently logged-on user
    rsUsers.Filter = "PersonID = '" & Session("PersonID") & "'"
  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
      insertSQL = "insert into Person(emailaddress, [password], familyname, givenname, StreetAddress1, StreetAddress2, City, State, PostalCode, Country, active, lastlogin) " & "values ('" & Request.Form("email") & "','" & Request.Form("password") & "','" & Request.Form("FamilyName") & "','" & Request.Form("GivenName") & "','" & Request.Form("Address1") & "','" & Request.Form("Address2") & "','" & Request.Form("City") & "','" & Request.Form("State") & "','" & Request.Form("PostalCode") & "','" & Request.Form("Country") & "',1,#" & Now & "#);"
'Response.Write("Sql Statement is " & insertSQL)
'Response.End
objConn.execute insertSQL

' Else
' Email address and password matched with DB records -
' In this case we'll allow this to update user's personal details
    End If
  End If
                                                  ' write personal details to record
  'rsUsers("EMailAddress") = Request.Form("email")
  'rsUsers("Password") = Request.Form("password")
  'rsUsers("FamilyName") = Request.Form("FamilyName")
  'rsUsers("GivenName") = Request.Form("GivenName")
  '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

  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 ' declare that current user is validated
  Response.Redirect "MenuForRegisteredUsers.asp"
%>

Thanks again,

It seems that the insert works fine now, doesn't it?

Using a Form in Access may not give you the same results as putting in the SQL statement directly in the Query Designer. For example, you could be forgetting quotes around a field's value which can result in an error. This may not be a problem in the Access database. So, effectively you are comparing apples with pears. Even if your Access form works, you're not really checking whether the INSERT statement from the ASP page works.

Also, there are subtle differences in the way data and columns are handled. For example, Password is a reserved keyword. Access will let you create a column by that name, and even let you insert data in that column. However, when you use ADO with a column named password, you run into problems.

Do you get this error when the insert completes? If you have a new user, you execute the INSERT statement. The recordset rsUsers will be empty in that case. However, the error message you get seems to indicate that you are updating the recordset, but your code doesn't show any updates anymore. Is this all the code? How about you remove all the commented and other irrelevant stuff, bringing the page back to the bare minimum.

Imar


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