Updating Recordset

[yoeddy25]

I have a combo box which selects the first entry with its status set to false. When I select this value I want to automatically update its status to true, so it isn't selected again. I've tried the following before closing the recordset.
rstemp.edit
rstemp("status")=true
rstemp.update

Any ideas anyone?? Here's my original code

<%DSN= "dsn=regno;uid=;pwd="
SQL="SELECT regno FROM testreg1 WHERE type ='Free to air (test)' AND status = false"

set conntemp=server.createobject("adodb.connection")
conntemp.open DSN
set rstemp=conntemp.execute(SQL)
if rstemp.eof then
response.write "no data for<br>"
response.write SQL
conntemp.close
set conntemp=nothing
response.end
end if%>

<select name="RegNo" size="1">

<% rstemp.movefirst %>

<option> <%=RStemp(0)%> </option>
</select>

<% rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing %>

[monte96]

Are you doing this database processing on a page refresh? The value is changing on the client side so you would have to have an event onchange on the client side that refreshes the page and passes an id of the record to be updated back to the page.

You will have to re-display the page in order to do this since you can't touch the database on the client-side.

[artsapimp]

I haven't tried it but I don't know why this wouldn't work when loaded.


Dim cnn
Dim rst
Dim ConnectString

Set cnn = Server.CreateObject("ADODB.Connection")
Set rst = Server.CreateObject("ADODB.Recordset")

ConnectString = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = c:\inetpub\wwwroot\db\whatever.mdb"

cnn.Open ConnectString

rst.Open "SELECT * FROM table WHERE status = 'FALSE'"

rst.Update
rst("status") = "True"
rst.Update
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing


This code should open the database, grab the first record with the status of 'False' and change it to 'True'.

[monte96]

Well, the best solution would probably be to stick an onchange event on the client side for the SELECT tag and refresh the page with the ID of the record to be changed (stored it in the value of the OPTION tags). So you would need to modify your original SQL query to include an ID for the record (Or if regno is the ID, change your option tag to this:


<SELECT id=onchange="window.location.href=pagename?regno=this.options[this.selectedIndex].value">
<%Do until rsTemp.EOF%>
<OPTION value=<%=rsTemp.Fields(0)%>><%=rsTemp.Fields(0)%></OPTION>
<%rsTemp.MoveFirst%>
<%Loop%>
</SELECT>


Then add code to the beginning of the page that looks for len(Request.Querystring("regno")) > 0 and only updates if the page has been refreshed.