Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Data type mismatch in criteria expression. 1

Status
Not open for further replies.

jmurrayhead

Programmer
Feb 13, 2004
47
US
This is my SQL:

Code:
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
 
Okay, here's the new sql:

Code:
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
 
Do the radio buttons in the form look like this ? :
Code:
<input type="radio" name="RWebmaster" value="TRUE">
<input type="radio" name="RWebmaster" value="FALSE">

 
If you are using access, set value for true = -1 false= 0
so
Code:
<input type="radio" name="RWebmaster" value="-1">
<input type="radio" name="RWebmaster" value="0">

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

Code:
<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
 
Code looks fine to me, try to display the sql statement and see if you are missing something ...
Code:
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

 
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'
 
Arghhh, what a shame ! Hope nobody else is following this thread ... :)

Code:
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") & "'"

 
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?
 
and in sql is used to concatenate clauses

Glad it helped..

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top