Update Multi Records in DB

[farnorth]

I am getting this error

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/asp/masterlist.asp, line 53

line 53 code

<% If Request("update") <> "" Then %>
<%

set updatemulti = Server.CreateObject("ADODB.Command")
updatemulti.ActiveConnection = MM_cottonwooddrugs_STRING
updatemulti.CommandText = "UPDATE prescription_refill SET printed ='-1' WHERE printed ='0' "
updatemulti.CommandType = 1
updatemulti.CommandTimeout = 0
updatemulti.Prepared = true
updatemulti.Execute() This is line 53
%>
<% End If %>

The field this command was built for is Printed which is a yes/no True/False. Is the error because of the printed=-1 and 0? Is my command update using the wrong data for a yes/no field?

Thanks
Bob

 

[SQLDenis]

try this
updatemulti.CommandText = "UPDATE prescription_refill SET printed =-1 WHERE printed =0 "


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[farnorth]

Thank You!! That did it. Now the next problem, how do I limit the update to only the records browsed and not any new ones that may have come in since the report was run. For example the user brings up the report to print while a client fills out a new request that gets saved to the database but has not shown up on the report. I tested it and when I click the update print stats button all records in the database get updated and not just the ones browsed on the report page. Any Ideas?

Thanks Again for your help.

Bob

 

[SQLDenis]

You will need to return a timestamp or max(id)
so that you will update everything WHERE printed =0 AND timestamp <= Timestamp returned

or

WHERE printed =0 AND id < MAX(id) Returned


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[farnorth]

Hmmm Doesn't like the syntax.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'printed ='False' AND timestamp <= Timestamp returned'.

/asp/masterlist.asp, line 52

Am I missing " or ' somewhere?

Thanks
Bob

 

[SQLDenis]

timestamp would be a field in your db that holds the time, I called it timestamp for the purpose of explaining the concept

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/