Once again my futile knowledge of ASP is failing me again.

What i am trying to re write i am sure is most peoples "walk in the park".

Below is the original code for updating an SQL table

set conn = server.CreateObject ("ADODB.Connection")
    Conn.connectionstring = strConn
    Conn.Open
if Request.form("action")="Save" then
     username=Request.Form("username")
     sql="UPDATE memberlist SET password='" & Request.Form("password") & "',"
     sql=sql & "organisation='" & Request.Form("organisation") & "',"
     sql=sql & "asstype='" & Request.Form("asstype") & "',"
     sql=sql & "fullname='" & Request.Form("fullname") & "'WHERE memberlist.username='" & username &"'"
    
conn.Execute sql , Recordsaffected

So far so good!

What i am trying to do is re-write to an access DB which i have re-written below.

if Request.form("action")="Save" then
     set rs=Server.CreateObject("ADODB.Recordset")
     username=Request.Form("username")
     rs.open "UPDATE members SET password='" & Request.Form("password") & "',"_
     & "organisation='" & Request.Form("organisation") & "',"_
     & "permission='" & Request.Form("permission") & "',"_
     & "fullname='" & Request.Form("fullname") & "' WHERE members.username='" & username &"'", data_source

(data_source) is taken from an includes DB connection.

Feel free to smile as i'm sure any developer would.

Oddly enough my latest attempt has not worked.

Any suggestions or plain Sarcasm would be appreciated!

Cheers


aspless

<s>It looks like you're not opening the recordset with the connection. E.g.:

rs.Open MyConnection, MySqlStatement

should work.</s>Sorry, talking crap. You do have the connection and my example showed you the wrong order of parameters.

<s>If that doesn't work, m</s>May I suggest you post the error message and a problem description? Analyzing code to see what the problem *could* be while you already know isn't very useful.... ;)

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Imar,

Thanks for your input.

Firstly i have had quite a problem trying to create an error, essentially the page displays a blank page and i am unsure where to correctly place a response.write other than strSQL (see below) which gives the correct query and username.

Essentially i have an edit page which displays a record set.

The code posted is where the form action = save from the edit page.

What i want to achieve is where the username matches the DB record set it updates the following fields.

Request.Form("username")
Request.Form("password")
Request.Form("organisation")
Request.Form("permission")
Request.Form("fullname")

I have had another look at alternative ways to update the record set but still failing ..

The latest attempt is below.

if Request.form("action")="Save" then

Dim adoCon 'Holds the Database Connection Object
Dim rsUpdateEntry 'Holds the recordset for the record to be updated
Dim strSQL 'Holds the SQL query to query the database
Dim lngRecordNo 'Holds the record number to be updated

'Read in the record number to be updated
lngRecordNo = Request.Form("username")

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("blahblah.mdb")

'Create an ADO recordset object
Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT members.* FROM members WHERE username=" & lngRecordNo

'Open the recordset with the SQL query
rsUpdateEntry.Open strSQL, adoCon

'Update the record in the recordset
rsUpdateEntry.Fields("username") = Request.Form("username")
rsUpdateEntry.Fields("password") = Request.Form("password")
rsUpdateEntry.Fields("organisation") = Request.Form("organisation")
rsUpdateEntry.Fields("permission") = Request.Form("permission")
rsUpdateEntry.Fields("fullname") = Request.Form("fullname")


rsUpdateEntry.Update

end if

'Reset server objects
rsUpdateEntry.Close
Set rsUpdateEntry = Nothing
Set adoCon = Nothing

From my still minimal understanding of asp this looks like it should work but no joy.

Any assistance would be appreciated.


Cheers


aspless

Yeah, I agree. AT first glance it seems OK.

Try this to get more detail information about errors:

http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=264

Also, try updating the connection string. The one you have is a bit outdated. Take a look here:

http://www.connectionstrings.com/?carrier=access

and then use one of the OleDb connection.

Without an error message, there isn't much I can do. Maybe it's a security problem? http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=263

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Thanks Imar,

I had a good look through your docs and both permission, show error messages and IIS setting are correct.

The "show error messages" option has been off for a long while.. I've broken too much code to have that set lol.

What i find even stranger is the delete record set works fine.

if Request.Form("action")="Delete" then
      set rs=Server.CreateObject("ADODB.Recordset")
      username=Request.Form("username")
 rs.open "DELETE FROM members WHERE members.username='" & username&"'" , data_source '(data_source = include ADODB.Connection)
 if err <> 0 then
 Response.Write("You do not have permission to delete a record from this database!")
 else
 Response.Write("CIS Member " & username & " was deleted.")
 end if

So as you can imagine this is a tad confusing.

Normally a few un healthy hours of google and attempts normally fixes such issues if your not a post grad computer science boff but this is still eluding me.

I have done a response.write check which does confirm all form field are being transferred to the submit page.

I am also confused by the complete lack of errors.

Thanks


aspless

What is the data type of username? In the delete query, you're enclosing it in single quotes, but for the SELECT statement you aren't.

Are you sure that lngRecordNo contains a valid value?

What happens when you write out the query? E.g.

strSQL = "SELECT members.* FROM members WHERE username=" & lngRecordNo
Response.Write(strSQL)
Response.End

What do you get? When you copy and paste that in Access, do you get a record?

Also, make sure you don't have any On Error Resume Next code....

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Thanks once again,

I assure you your help is appreciated!

The query comes back with the record eg

SELECT members.* FROM members WHERE username=aspless

The access table data type for username and the other fields is text for ref.

I do have one other field call IUD which is autonumber but that should not affect the code " he hopes".

Also all On Error Resume lines are rem'd out.

Could you possibly expand on your quote

Also i thought i't might be worth adding the original input code less redirects ect.

'Save entered username and password
    Username = Request.Form("txtUsername")
    Password = Request.Form("txtPassword")
    Fullname = Request.Form("txtFullname")
    Organisation = Request.Form("txtOrganisation")
    rdpermission = Request.Form("rdperm")

    'Build connection
    set rs = server.CreateObject ("ADODB.Recordset")
    'Open all records
    rs.Open "SELECT * FROM members", data_source, 3, 3

    'Add a record
    rs.AddNew
    'Put username and password in record
    rs("username")=Username
    rs("password")=Password
    rs("fullname")=Fullname
    rs("organisation")=Organisation
    rs("permission")=rdpermission
    'Save record
    rs.Update

    set conn=nothing

Thanks

aspless

Maybe i missed it but to me it looks like you are useig very limited connection/Open strings to your database. I have code in one of my pages very similar to yours and it is working fine so if you are sure your IIS server setting are correct Maybe try changing you connect strings here is a sample of what I am doing and is working great.
<%
constr= "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Inetpub\wwwroot\DataStore\DB.MDB;User Id=Admin;" & _
    "Password="
Dim objConn, objRS
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

objconn.Open constr

objRS.Open "Table", objConn, adFowardOnly,adlockoptimistic, adCmdTable
'Then To do my update i do something like this

With Objrs
   .AddNew
   .Fields("UserName") = Request.Form("UserName")
   .Fields("password") = Request.Form("Password")
   .Update
End With

This code is updating a Access Recordset for me without any issues. So try the connection strings and maybe check the IIS settings again
and makesure that your anon access account is setup to an account that has the writes to change the databasre set it to an Admin account for testing if you need to



Hope I am offering help and not messing you up any more.

Jim

Regarding what I said about the Access query: Microsoft Access as part of Office has a query designer that allows you to enter or design queries. It's the best way to test out queries and see if they work. It catches many errors for you.

Anyway, look at this:

SELECT members.* FROM members WHERE username=aspless

In your DELETE statement the username was enclosed in quotes. You need to do the same here. Only numeric data types can be added in the WHERE clause like this. Other types, like strings need quotes:

SELECT members.* FROM members WHERE username = 'aspless'

strSQL = "SELECT members.* FROM members WHERE username = '" & lngRecordNo & "'"

Hope this helps,

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Thanks Imar, JAdkins

You both helped towards the end solution which i found last night.

Imar,

Using the enclosed quotes worked much better.

strSQL = "SELECT username,password,organisation,permission,fullname FROM members WHERE username='" & lngRecordNo & "'"

JAdkins,

Your connection string thought helped towards my mistake, after re-checking i changed my rsUpdateEntry line below.

rsUpdateEntry.Open strSQL, adoCon,3,3

I have not had much chance to investigate what relevance the "3,3" is to the record set so if either of you have any good docs that would be superb.

Thanks again


aspless