how to update a table where ID is 414

[laura2k]

hi there,

i have a ms access data base called db.mdb and within that i have a table called tblUsers. this table has some data. eac h row has a column called "Active". this is a checkbox that can be set to yes or no

can anyone plz tell me how i can connect to the database and set the "Active" to "no" where the ID is "414"

thanx

[dotme]

Are you using VB/ASP? or .NET Framework?

[streetshirts]

You can try using something like this (you will need to modify this with you DSN and field name for ID):

first set up a system DSN for your database db1

<%
Dim up,strSQLup
Set up = Server.CreateObject("ADODB.Connection")
up.ConnectionString = "DSN=YourDSN"
up.Open
Dim RSupdate
Set RSupdate = Server.CreateObject("ADODB.Recordset")

strSQLup = "UPDATE tblUsers SET Active='no' WHERE ID='414';"

RSupdate.Open strSQLup, up
Set RSupdate = Nothing
up.Close
Set up = Nothing
%>

[laura2k]

i tried the following but i get an error saying Data type mismatch in criteria expression. Any ideas?

<%
Dim objConn, conn, sql

conn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("/database/db.mdb")

set objConn=server.createobject("ADODB.Connection")
objConn.open conn

sql="UPDATE tblUsers SET Active='No' WHERE ID='414'; "

objConn.execute sql

objConn.close
set objConn=Nothing

response.Write "done"
%>

[mattglet]

<%
Dim objConn, conn, sql

conn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("/database/db.mdb")

set objConn=server.createobject("ADODB.Connection")
objConn.open conn

sql="UPDATE tblUsers SET Active='No' WHERE ID= 414; "

objConn.execute sql

objConn.close
set objConn=Nothing

response.Write "done"
%>

Note the quotes gone from 414. You are inserting a number, not a string.

-Matt

[laura2k]

now i get an error saying:

No value given for one or more required parameters.

[Spooky]

sql="UPDATE tblUsers SET Active=0 WHERE ID=414"

[laura2k]

i am still getting the error saying: No value given for one or more required parameters.

[macrost]

is active 'no' or 0 as in a true/false field? if 'no' then

sql="UPDATE tblUsers SET Active='no' WHERE ID=414"

[laura2k]

still getting the same error. Active i am assuming is Yes/No. It is a checkbox where you tick for yes and untick for no.

[Easy_Coder]

Is the ID an integer datatype? If so have you tried passing it in as such?

dim sql

sql = ("UPDATE tblUsers SET Active='no' WHERE ID=" & CInt(414))

[txbakers]

Don't use those silly Access checkbox fields. Make it a text field with one character and either put a Y or N.

Those chekbox fields are so unstandard.

All the queries above are valid.

update table set active="n" where id=414

[macrost]

If you still want to use the checkboxes then try to have this:

active='false'