Updating Records

[avaffa]

I would like to update a table using info users enter from a previous form. I keep receiving the error message: "ADODB.Recordset error '800a0cb3' Object or provider is not capable of performing requested operation."

Below is my code. Please help, thanks!

<%
Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.open &quot;DSN=Access&quot;
q = &quot;SELECT * FROM speakers WHERE S_ID = '&quot; & S_ID & &quot;'&quot;

Set oRS = Server.CreateObject(&quot;ADODB.Recordset&quot
oRS.Open &quot;Speakers&quot;, &quot;Access&quot;

oRS.Fields(&quot;Email&quot = Request.Form(&quot;email&quot

oRS.Update
oRS.Close
Set oRS = Nothing
conn.Close
Set conn = Nothing
%>

 

[FesterSXS]

try this:

oRS.Open "Speakers", "Access", adOpenStatic, adLockOptimistic

make sure the ADO Constants file is declared at the top of the page:

<!-- METADATA TYPE=&quot;typelib&quot; FILE=&quot;C:\Program Files\Common Files\System\ado\msado15.dll&quot; -->

Tony

http://www.SpiraxSarco.com/cad

 

[avaffa]

I'm not receiving the error message anymore but the change isn't reflected in the table.

 

[avaffa]

If I use SQL will I be able to update specific fields or will I have to update the entire record?

 

[onpnt]

kill the ADO usage and do it as straight SQL. you'll get better performance and it will be far easier to debug your applications

___________________________________________________________________

_{The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page}

 

[onpnt]

multi submit

yes, you already have everything you need in the ID

eg:
sql = &quot;UPDATE speakers SET Email = '&quot; & Request.Form(&quot;email&quot & &quot;' WHERE S_ID = '&quot; & S_ID & &quot;'&quot;

___________________________________________________________________

_{The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page}

 

[avaffa]

I tried using SQL and the changes are still not being made in the table. Here's the code:

<%
Set oConn = Server.CreateObject(&quot;ADODB.Connection&quot
oConn.open &quot;Access&quot;

SQLstmt = &quot;UPDATE speakers SET Email = '&quot; & Request.Form(&quot;email&quot & &quot;' WHERE S_ID = '&quot; & S_ID & &quot;'&quot;

oConn.execute(SQLstmt)
oConn.close
Set oConn = Nothing
%>

 

[SQL2KDBA69]

Where are you pulling in the S_ID that your using as a pointer in the ='&quot;%S_ID%&quot;' because if that is blank your table will not be updated. i dont see a request.form(&quot;S_ID&quot any where.

 

[avaffa]

On the previous form, I inserted a hidden field:

<input type=&quot;hidden&quot; name=&quot;S_ID&quot; value=&quot;<%=rs(&quot;S_ID&quot%>&quot;>

On the current form, above the Connection statement, the code is

<%S_ID = Request.Form(&quot;S_ID&quot%>

 

[avaffa]

I know for sure that the S_ID is being passed from the previous form to the current form b/c when I insert &quot;Response.Write(S_ID)&quot; after the connection statement on the current form, the S_ID is displayed on the form to which the current form is being submitted.

 

[FesterSXS]

try converting the S_ID form value to an Int...

<%S_ID = CInt(Request.Form(&quot;S_ID&quot;))%>

Tony

http://www.SpiraxSarco.com/cad

 

[avaffa]

S_ID is comprised of all letters. Is it possible to change only one field using SQL or do I need to use Recordsets? Thanks!

 

[FesterSXS]

yes - onpnt's SQL example above only updates the Email field but it does this for each record where the contents in the S_ID field matches your S_ID variable

Tony

http://www.SpiraxSarco.com/cad

 

[avaffa]

I'm using onpnt's example and for some reason it isn't working. I'm not receiving any error messages but the data is not being changed.

 

[FesterSXS]

Is your DSN connection working OK? - are you using it successfully elsewhere? I always use DSNless connections - just what I'm used to.



Tony

http://www.SpiraxSarco.com/cad

 

[avaffa]

As far as I know it is working properly - information is being retrieved and displayed from the database. How would I change my code so that I could you DSNless connection?

 

[FesterSXS]

humour me for a second if you would - try this line without the brackets:

oConn.execute SQLstmt

I always get an error if I try to use the Execute command with brackets.

Tony

http://www.SpiraxSarco.com/cad

 

[avaffa]

It didn't work.

 

[cookiespin]

Have you tried response.write SQLstmt to make sure the the variables are being included as you expect?

 

[SQL2KDBA69]

I dont know what on the form page that pull out the S_ID from your database but this is what the update script should look like and i will work.

<%
S_ID=Request.Form(&quot;S_ID&quot
Email=Request.Form(&quot;email&quot


Set oConn = Server.CreateObject(&quot;ADODB.Connection&quot
oConn.open &quot;Access&quot;

SQL = &quot;UPDATE speakers SET Email = '&quot; & Email & &quot;' WHERE S_ID = '&quot; & S_ID & &quot;'&quot;

Set RecordSet=oConn.execute(SQL)
oConn.close
Set oConn = Nothing
%>