ASP and MS Access problem

[John]

When I enter data into my access db through the gui, my SQL Query
recognizes the data it is looking for and returns the result. ie I
enter "Dog" through the Access GUI and when my SQL query asks for
"Dog" it returns all the records with "Dog" in it.

The problem is if I enter "Dog" through my ASP web interface to
exactly the same database, I can see "Dog" in the table through the
gui, however when I run the SQL query, it only returns records with
"Dog" that were entered directly in through the Access GUI.

rs("BreakfastIncl") = BreakfastInclInput
rs("ConfCenter") = ConfCenterInput
rs("ConfCenterDescription") = ConfCentDescInput
rs("Distances") = DistancesInput
rs("File Name") = fileName
rs("File Size") = fileSize
rs("File Data").AppendChunk fileData
rs("Content Type") = contentType

rs("Subject")=nameInput
rs.Update

rs.Close
Set rs = Nothing

SQL Query = 'strSQL = "SELECT * FROM BIBS WHERE Suburb ='" &
Request.QueryString("link") & "' ORDER BY ID DESC"'


I am doing an rs.update - I can see the web entered "Dog" through the
gui - but the SQL query or the following line just does not see it.

If UCase(Trim(CStr(oRset("Suburb"))))=UCase(Trim(CStr(Request.QueryString("link"))))
then .....

I am very perplexed. Thanks
John

 

[Phillip Windell]

Don't use Recordsets to update data. You need to update or add data directly
to the tables using an SQL "Update" or "Insert" command (depending on which
you are doing).

 

[John]

Thanks Phillip - I switched to using INSERT and that worked - do you
know why that occurs with Recordsets?

Don't use Recordsets to update data. You need to update or add data directly
to the tables using an SQL "Update" or "Insert" command (depending on which
you are doing).


--

Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

When I enter data into my access db through the gui, my SQL Query
recognizes the data it is looking for and returns the result. ie I
enter "Dog" through the Access GUI and when my SQL query asks for
"Dog" it returns all the records with "Dog" in it.

The problem is if I enter "Dog" through my ASP web interface to
exactly the same database, I can see "Dog" in the table through the
gui, however when I run the SQL query, it only returns records with
"Dog" that were entered directly in through the Access GUI.

rs("BreakfastIncl") = BreakfastInclInput
rs("ConfCenter") = ConfCenterInput
rs("ConfCenterDescription") = ConfCentDescInput
rs("Distances") = DistancesInput
rs("File Name") = fileName
rs("File Size") = fileSize
rs("File Data").AppendChunk fileData
rs("Content Type") = contentType

rs("Subject")=nameInput
rs.Update

rs.Close
Set rs = Nothing

SQL Query = 'strSQL = "SELECT * FROM BIBS WHERE Suburb ='" &
Request.QueryString("link") & "' ORDER BY ID DESC"'


I am doing an rs.update - I can see the web entered "Dog" through the
gui - but the SQL query or the following line just does not see it.

If UCase(Trim(CStr(oRset("Suburb"))))=UCase(Trim(CStr(Request.QueryString("link
"))))
then .....

I am very perplexed. Thanks
John

 

[Bob Barrows]

When I enter data into my access db through the gui, my SQL Query
recognizes the data it is looking for and returns the result. ie I
enter "Dog" through the Access GUI and when my SQL query asks for
"Dog" it returns all the records with "Dog" in it.

The problem is if I enter "Dog" through my ASP web interface to
exactly the same database, I can see "Dog" in the table through the
gui, however when I run the SQL query, it only returns records with
"Dog" that were entered directly in through the Access GUI.

rs("BreakfastIncl") = BreakfastInclInput
rs("ConfCenter") = ConfCenterInput
rs("ConfCenterDescription") = ConfCentDescInput
rs("Distances") = DistancesInput
rs("File Name") = fileName
rs("File Size") = fileSize
rs("File Data").AppendChunk fileData
rs("Content Type") = contentType

rs("Subject")=nameInput
rs.Update

rs.Close
Set rs = Nothing

SQL Query = 'strSQL = "SELECT * FROM BIBS WHERE Suburb ='" &
Request.QueryString("link") & "' ORDER BY ID DESC"'


I am doing an rs.update - I can see the web entered "Dog" through the
gui - but the SQL query or the following line just does not see it.

If
UCase(Trim(CStr(oRset("Suburb"))))=UCase(Trim(CStr(Request.QueryString("link
"))))
then .....

I am very perplexed. Thanks
John

The Jet cursor engine uses a delayed-write technology to improve
performance. It caches updates to the recordset and executes the cache all
at once. While this does have the benefit of improving performance, it can
lead to the confusion you are experiencing.

As Philip suggested, my preferred methodology for doing data maintenance is
to use SQL DML (data modification language) calls: UPDATE, INSERT and
DELETE. Since the cursor engine is not involved, the modifications occur
when expected.

The delay-write period can be adjusted, and even eliminated, by using the
proper parameters in your connection string, but even still, I prefer to use
DML instead of cursor updates due to the improved performance of using set
operations instead of cursor operations.

HTH,
Bob Barrows