Data type mismatch in criteria expression. 1

[jmurrayhead]

This is my SQL:

Set rs = Conn.Execute("UPDATE Users SET Webmaster = '" & request.form("RWebmaster") & "' AND Teachers = '" & request.form("RTeachers") & "' AND Staff = '" & request.form("RStaff") & "' AND Parents = '" & request.form("RParents") & "' AND Students = '" & request.form("RStudents") & "' WHERE UserName = '" & request.form("Users") & "'")

Information is sent to this via a form, where all form fields listed above That have an R, (eg RStaff) are radio buttons. There is a True and False button for each name.

I get this error:
Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

/updacc.asp, line 13

Line 13 is the above SQL statement.

Any ideas? Thanks

 

[pgferro]

you do not need ' ' around TRUE / FALSE

--
PG

 

[jmurrayhead]

Okay, here's the new sql:

Set rs = Conn.Execute("UPDATE Users SET Webmaster = " & request.form("RWebmaster") & " AND Teachers = " & request.form("RTeachers") & " AND Staff = " & request.form("RStaff") & " AND Parents = " & request.form("RParents") & " AND Students = " & request.form("RStudents") & " WHERE UserName = '" & request.form("Users") & "'")

The error message has gone away, but the database isn't updating. I verified that the column names are as shown above and the form field names are as well. I do have the actual database fields set as Data Type Yes/No Format True/False. Would that have anything to do with it?

Thanks for your help

 

[pgferro]

Do the radio buttons in the form look like this ? :

<input type="radio" name="RWebmaster" value="TRUE">
<input type="radio" name="RWebmaster" value="FALSE">

 

[jmurrayhead]

Yes

 

[pgferro]

If you are using access, set value for true = -1 false= 0
so

<input type="radio" name="RWebmaster" value="-1">
<input type="radio" name="RWebmaster" value="0">

 

[jmurrayhead]

Yes, I'm using Access, but that didn't work. Here is my form page code, maybe that will help determine the problem:

<form method="POST" action="updacc.asp">
<table border="0" width="100%">
<tr valign="top">
<td valign="top">
<b>Select a User:</b><br></td><td valign="top"><b>Access Settings</b></td></tr>
<tr valign="top">
<td valign="top">
<% Set rs = Conn.Execute("SELECT UserName, x_firstname, x_lastname FROM Users ORDER BY x_lastname, x_firstname ASC")
if rs.eof then
Response.write "No records returned."
else
Response.write ("<Select name='Users'>")
do until rs.eof
response.write ("<option value='" & rs("UserName") & "'>" & rs("x_lastname") & "," & rs("x_firstname") & "</option>")
rs.movenext
loop
response.write ("</select>")
end if


%></td>
<td valign="top"><table border="0">
<tr valign="top"><td valign="top"></td><td valign="top"><b>True</b></td><td valign="top">
  <b>False</b></td>
</tr>
<tr valign="top"><td valign="top">Students</td><td valign="top">
    <input type="radio" value="-1" name="RStudents"></td><td valign="top"><input type="radio" value="0" checked name="RStudents"></td>
</tr>
<tr valign="top"><td valign="top">Parents</td><td valign="top">
  <input type="radio" value="-1" name="RParents"></td><td valign="top"><input type="radio" value="0" checked name="RParents"></td>
</tr>
<tr valign="top"><td valign="top">Staff</td><td valign="top">
  <input type="radio" value="-1" name="RStaff"></td><td valign="top"><input type="radio" value="0" checked name="RStaff"></td>
</tr>
<tr valign="top"><td valign="top">Teachers</td><td valign="top">
  <input type="radio" value="-1" name="RTeachers"></td><td valign="top"><input type="radio" value="0" checked name="RTeachers"></td>
</tr>
<%if not Session("Webmaster") = "True" then%><input type="hidden" value="0" name="RWebmaster" checked>
<%else%>
<tr valign="top"><td valign="top">Webmaster</td><td valign="top">
<input type="radio" value="-1" name="RWebmaster"></td><td valign="top"><input type="radio" value="0" checked name="RWebmaster"></td>
</tr>
<%end if%>
</td></tr></table><p>
<input type="submit" value="Update Access"><input type="reset" onClick="window.location.replace('[URL unfurl="true"]http://mypage.murrayherber.com/users.asp');">[/URL]

</form>

Thanks for your help

 

[pgferro]

Code looks fine to me, try to display the sql statement and see if you are missing something ...

SQLUpdt="UPDATE Users SET Webmaster = " & request.form("RWebmaster") & " AND Teachers = " & request.form("RTeachers") & " AND Staff = " & request.form("RStaff") & " AND Parents = " & request.form("RParents") & " AND Students = " & request.form("RStudents") & " WHERE UserName = '" & request.form("Users") & "'"
Response.Write SQLUpdt

 

[jmurrayhead]

Prints out fine, I don't understand what's wrong.
I set Parents and Students to true and this is what it gave me:

UPDATE Users SET Webmaster = 0 AND Teachers = 0 AND Staff = 0 AND Parents = -1 AND Students = -1 WHERE UserName = '27133'

 

[pgferro]

Arghhh, what a shame ! Hope nobody else is following this thread ...

SQLUpdt="UPDATE Users SET Webmaster = " & request.form("RWebmaster") & " , Teachers = " & request.form("RTeachers") & " , Staff = " & request.form("RStaff") & " , Parents = " & request.form("RParents") & " , Students = " & request.form("RStudents") & " WHERE UserName = '" & request.form("Users") & "'"

 

[jmurrayhead]

LOL...well that did the trick! Thanks much for your help. I thought we were supposed to be able to use AND? Or is that just for certain cases?

 

[pgferro]

and in sql is used to concatenate clauses

Glad it helped..