I am trying to change a record in an Access database.  Earlier I used 

EMPID (a number) without problem.  For various reasons, I decided to use 

initials as the field to find the record (a string variable).



I use a simple HTML form for the user to find and display the record.  It 

seems to work fine and displays the correct record.  Then clicking 

the "Submit" button triggers an ASP program to change the record.  But 

using the string, it changes the wrong record, usually the first record.  

The code snippet in "ChangePersonnelRecord.asp" is as follows:



'The form that displays the record includes a read only textbox - EmpID

'that has the intials.

strIDForChangedRecord = Request.Form("EmpID")

 

 'set the recordset object and open the personnel database as a table

 Set objRS=Server.CreateObject("ADODB.Recordset")

 objRS.Open "Personnel", strLocalCounselConnect, adOpenStatic, 

adLockPessimistic, adCmdTable

 

 strSQL = "SELECT * FROM Personnel WHERE Initials = '" & 

strIDForChangedRecord & "'"

'NOTE - THE INITIALS DISPLAYED BELOW ARE NOT THE ONES THAT I AM TRYING TO 

'SELECT IN THE ABOVE SQL COMMAND.  BUT IF I WRITE strIDForChangedRecord

'I GET THE CORRECT INITIALS

Response.Write objRS("Initials")

'These insert the data in the Personnel database as changed 

'form fields BUT THEY ARE INSERTING IT IN THE WRONG RECORD

 objRS("LastName")=Request.Form("LastName")

 objRS("FirstName")=Request.Form("FirstName")

 objRS("Address")=Request.Form("Address")

 objRS("City")=Request.Form("City")

objRS("State")=Request.Form("State")

objRS("ZipCode")=Request.Form("ZipCode")

objRS("Extension")=Request.Form("Extension")

objRS("HomeEmail")=Request.Form("HomeEmail")

objRS("HomePhone")=Request.Form("HomePhone")

objRS("Birthdate")=Request.Form("Birthdate")

objRS("HomeState")=Request.Form("HomeState")

objRS("Spouse")=Request.Form("Spouse")

objRS("Interests")=Request.Form("Interests")

objRS("Children")=Request.Form("Children")

ChangedNotes=Request.Form("Notes")

strIDForChangedRecord=Request.Form("EmpID")



 objRS.Update

 objRS.Close

 Set objRS=Nothing

 

 %>



Any help is appreciated.



Steve Proctor

''' This command would open the whole table here as a recordset

''' objRS.Open "Personnel", strLocalCounselConnect, _

                     ''' adOpenStatic, adLockPessimistic, adCmdTable



''' Your SQL string isn't doing anything here

 strSQL = "SELECT * FROM Personnel WHERE Initials = '" &

 strIDForChangedRecord & "'"



''' Try this open command instead -- note adCmdTxt at end

objRS.Open strSQL, strLocalCounselConnect, _

                    adOpenStatic, adLockPessimistoc, adCmdTxt





Does this help?









----- Original Message -----

From: "Stephen Proctor" <steveproctor@c...>

To: "Access ASP" <access_asp@p...>

Sent: Saturday, October 13, 2001 6:21 PM

Subject: [access_asp] Using String to Find, Display and Change Record





> I am trying to change a record in an Access database.  Earlier I used

> EMPID (a number) without problem.  For various reasons, I decided to use

> initials as the field to find the record (a string variable).

>

> I use a simple HTML form for the user to find and display the record.  It

> seems to work fine and displays the correct record.  Then clicking

> the "Submit" button triggers an ASP program to change the record.  But

> using the string, it changes the wrong record, usually the first record.

> The code snippet in "ChangePersonnelRecord.asp" is as follows:

>

> 'The form that displays the record includes a read only textbox - EmpID

> 'that has the intials.

> strIDForChangedRecord = Request.Form("EmpID")

>

>  'set the recordset object and open the personnel database as a table

>  Set objRS=Server.CreateObject("ADODB.Recordset")

>  objRS.Open "Personnel", strLocalCounselConnect, adOpenStatic,

> adLockPessimistic, adCmdTable

>

>  strSQL = "SELECT * FROM Personnel WHERE Initials = '" &

> strIDForChangedRecord & "'"

> 'NOTE - THE INITIALS DISPLAYED BELOW ARE NOT THE ONES THAT I AM TRYING TO

> 'SELECT IN THE ABOVE SQL COMMAND.  BUT IF I WRITE strIDForChangedRecord

> 'I GET THE CORRECT INITIALS

> Response.Write objRS("Initials")

> 'These insert the data in the Personnel database as changed

> 'form fields BUT THEY ARE INSERTING IT IN THE WRONG RECORD

>  objRS("LastName")=Request.Form("LastName")

>  objRS("FirstName")=Request.Form("FirstName")

>  objRS("Address")=Request.Form("Address")

>  objRS("City")=Request.Form("City")

> objRS("State")=Request.Form("State")

> objRS("ZipCode")=Request.Form("ZipCode")

> objRS("Extension")=Request.Form("Extension")

> objRS("HomeEmail")=Request.Form("HomeEmail")

> objRS("HomePhone")=Request.Form("HomePhone")

> objRS("Birthdate")=Request.Form("Birthdate")

> objRS("HomeState")=Request.Form("HomeState")

> objRS("Spouse")=Request.Form("Spouse")

> objRS("Interests")=Request.Form("Interests")

> objRS("Children")=Request.Form("Children")

> ChangedNotes=Request.Form("Notes")

> strIDForChangedRecord=Request.Form("EmpID")

>

>  objRS.Update

>  objRS.Close

>  Set objRS=Nothing

>

>  %>

>

> Any help is appreciated.

>

> Steve Proctor

>

Steve,



wouldn't want to write the whole thing over for you, but I will give you 

some pointers in the right direction. In your code, you are opening a 

recordset but you are not sending the SQL command of what you want 

updated. Try this instead:



Set cn= server.createobject("ADODB.connection")

cn.open "DSN=xxxxx"



strSQL= "UPDATE  Personnel SET Lastname=" & request.form("LastName") & ", "

strSQL= strSQL & "Firstname=" & request.form("Firstname") & ", " etc......

WHERE empID = " & strIDforChangedRecord



cn.execute strSQL



you see how this is done. Recordsets are best used for "select" 

statements. What you are wanting to do is update a record which needs to 

be done with a direct connection with the database. A recordset is good to 

use for reports and the like, but not good for modifying. You cannot 

update records by opening a recordset and then setting the values of the 

fields to the values from the forms. When conversing with the database, 

you must use SQL statements to perform updates, deletes, or insertions. 

Hope this helps you out.



Mike. 









> I am trying to change a record in an Access database.  Earlier I used 

> EMPID (a number) without problem.  For various reasons, I decided to use 

> initials as the field to find the record (a string variable).

> 

> I use a simple HTML form for the user to find and display the record.  

It 

> seems to work fine and displays the correct record.  Then clicking 

> the "Submit" button triggers an ASP program to change the record.  But 

> using the string, it changes the wrong record, usually the first 

record.  

> The code snippet in "ChangePersonnelRecord.asp" is as follows:

> 

> 'The form that displays the record includes a read only textbox - EmpID

> 'that has the intials.

> strIDForChangedRecord = Request.Form("EmpID")

>  

>  'set the recordset object and open the personnel database as a table

>  Set objRS=Server.CreateObject("ADODB.Recordset")

>  objRS.Open "Personnel", strLocalCounselConnect, adOpenStatic, 

> adLockPessimistic, adCmdTable

>  

>  strSQL = "SELECT * FROM Personnel WHERE Initials = '" & 

> strIDForChangedRecord & "'"

> 'NOTE - THE INITIALS DISPLAYED BELOW ARE NOT THE ONES THAT I AM TRYING 

TO 

> 'SELECT IN THE ABOVE SQL COMMAND.  BUT IF I WRITE strIDForChangedRecord

> 'I GET THE CORRECT INITIALS

> Response.Write objRS("Initials")

> 'These insert the data in the Personnel database as changed 

> 'form fields BUT THEY ARE INSERTING IT IN THE WRONG RECORD

>  objRS("LastName")=Request.Form("LastName")

>  objRS("FirstName")=Request.Form("FirstName")

>  objRS("Address")=Request.Form("Address")

>  objRS("City")=Request.Form("City")

> objRS("State")=Request.Form("State")

> objRS("ZipCode")=Request.Form("ZipCode")

> objRS("Extension")=Request.Form("Extension")

> objRS("HomeEmail")=Request.Form("HomeEmail")

> objRS("HomePhone")=Request.Form("HomePhone")

> objRS("Birthdate")=Request.Form("Birthdate")

> objRS("HomeState")=Request.Form("HomeState")

> objRS("Spouse")=Request.Form("Spouse")

> objRS("Interests")=Request.Form("Interests")

> objRS("Children")=Request.Form("Children")

> ChangedNotes=Request.Form("Notes")

> strIDForChangedRecord=Request.Form("EmpID")

> 

>  objRS.Update

>  objRS.Close

>  Set objRS=Nothing

>  

>  %>

> 

> Any help is appreciated.

> 

> Steve Proctor