update statement troubles

[csdowney]

here is my trouble - i have 3 pages, the first runs a simple querey and populates a drop down list, the user picks one of the returns and upon submission is sent to the second page - there the user can modify certin fields, once they click on submitting changes the third page takes the modified data, strips out single quotes and updates the table and displays the information that was supposedly written to the table. here is the code on the third page

Dim Comments

Comments = Request.Form("NewComments")

Dim cnnSimple
Dim rstSimple
Dim strDBPath

Comments = Replace(Comments, "'", "")

strDBPath = Server.MapPath("ProjectsReport.mdb")

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

cnnSimple.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

Set rstSimple = cnnSimple.Execute("UPDATE tblProjects SET Comments = '" & Comments & "' WHERE Project = '" & ProjectName & "'")


Now here is the trouble, the values are passed ok, displayed ok, querey appears to work however the table is not updated - any suggestions.

[albertw]

here is my trouble - i have 3 pages, the first runs a simple querey and populates a drop down list, the user picks one of the returns and upon submission is sent to the second page - there the user can modify certin fields, once they click on submitting changes the third page takes the modified data, strips out single quotes and updates the table and displays the information that was supposedly written to the table. here is the code on the third page

Dim Comments

Comments = Request.Form("NewComments")

Dim cnnSimple
Dim rstSimple
Dim strDBPath

Comments = Replace(Comments, "'", "")

strDBPath = Server.MapPath("ProjectsReport.mdb")

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

cnnSimple.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

Set rstSimple = cnnSimple.Execute("UPDATE tblProjects SET Comments = '" & Comments & "' WHERE Project = '" & ProjectName & "'")


Now here is the trouble, the values are passed ok, displayed ok, querey appears to work however the table is not updated - any suggestions.

hi

can't you use

With datReport.Recordset
.Edit
'make changes code
.Update
End With

datReport.Refresh

where datReport is the dataconnection to ProjectReport.mdb
???

[scripto]

here is my trouble - i have 3 pages, the first runs a simple querey and populates a drop down list, the user picks one of the returns and upon submission is sent to the second page - there the user can modify certin fields, once they click on submitting changes the third page takes the modified data, strips out single quotes and updates the table and displays the information that was supposedly written to the table. here is the code on the third page

Dim Comments

Comments = Request.Form("NewComments")

Dim cnnSimple
Dim rstSimple
Dim strDBPath

Comments = Replace(Comments, "'", "")

strDBPath = Server.MapPath("ProjectsReport.mdb")

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

cnnSimple.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

Set rstSimple = cnnSimple.Execute("UPDATE tblProjects SET Comments = '" & Comments & "' WHERE Project = '" & ProjectName & "'")


Now here is the trouble, the values are passed ok, displayed ok, querey appears to work however the table is not updated - any suggestions.

As per your code, ProjectName seems to be blank??
and why are you using a recordset? this is an update statment - try just executing the command

[willakawill]

As per your code, ProjectName seems to be blank??
and why are you using a recordset? this is an update statment - try just executing the command

Also you don't need to check the database to see if the update worked. Put a Long variable after your SQL statement and check it for the number of rows affected by your code:

cnnSimple.Execute("UPDATE tblProjects SET Comments = '" & Comments & "' WHERE Project = '" & ProjectName & "'", lngRowsAffected)
 
If lngRowsAffected = 0 Then
   MsgBox "Oh Dear! Something amiss with my SQL programming again"
End If
 

:)