Hi All,

I've been climbing the walls to figure out why I can't get this Access Yes/No field to update with an ADO Command object. I have to assume the YesNo datatype in Access is a adBoolean data type, but when I run the following script I get an error:

Provider error '80020005'

Type mismatch.

/updateuser.asp, line 50

Line 50 is "var parm9 = cmd.CreateParameter("Active", 11, 10, "");"

Here is the entire page, less the connect string and some include files:



<%
var cmd = Server.CreateObject("ADODB.Command");
cmd.ActiveConnection = cn;
cmd.CommandText = "UPDATE [Employee] SET [EmpNumber]=?, [EmpType]=?, [Password]=?, [FirstName]=?, [LastName]=?, [EmailAddress]=?, [BadgeNumber]=?, [Notes]=?, [Active]=? WHERE [EmpID]=?"
cmd.CommandType = 1; //adCmdText
cmd.Prepared = true;

var parm1 = cmd.CreateParameter("EmpNumber", 200, 1, 50, "");
cmd.Parameters.Append(parm1);
parm1.Value = Request("EmpNumber").Item;

var parm2 = cmd.CreateParameter("EmpType", 200, 1, 2, "");
cmd.Parameters.Append(parm2);
parm2.Value = Request("EmpType").Item;

var parm3 = cmd.CreateParameter("Password", 200, 1, 50, "");
cmd.Parameters.Append(parm3);
parm3.Value = Request("Password").Item;

var parm4 = cmd.CreateParameter("FirstName", 200, 1, 50, "");
cmd.Parameters.Append(parm4);
parm4.Value = Request("FirstName").Item;

var parm5 = cmd.CreateParameter("LastName", 200, 1, 100, "");
cmd.Parameters.Append(parm5);
parm5.Value = Request("LastName").Item;

var parm6 = cmd.CreateParameter("EmailAddress", 200, 1, 100,"");
cmd.Parameters.Append(parm6);
parm6.Value = Request("EmailAddress").Item;

var parm7 = cmd.CreateParameter("BadgeNumber", 200, 1, 50,"");
cmd.Parameters.Append(parm7);
parm7.Value = Request("BadgeNumber").Item;

var parm8 = cmd.CreateParameter("Notes", 201, 1, 500,"");
cmd.Parameters.Append(parm8);
parm8.Value = Request("EmpNotes").Item;

var parm9 = cmd.CreateParameter("Active", 11, 10, "");
cmd.Parameters.Append(parm9);
parm9.Value = Request("Active").Item;

var parm10 = cmd.CreateParameter("EmpID", 200, 1, 100, "");
cmd.Parameters.Append(parm10);
parm10.Value = Request("EmpID").Item;

Response.Write(cmd.CommandText);

var theUser = Server.CreateObject("ADODB.Recordset");
WriteRS(theUser);
theUser = cmd.Execute();
%>
<form action="updateuser.asp" method="post" name="updateuser">
<input type="hidden" name="EmpID" value="<%= Request("EmpID").Item %>">
<table>
<tr>
    <td>Employee Number:</td>
    <td><input type="text" name="EmpNumber" value="<%= Request("EmpNumber").Item %>"></td>
</tr>
<tr>
    <td>Password:</td>
    <td><input type="password" name="Password" value="<%= Request("Password").Item %>"></td>
</tr>
<tr>
    <td>Role:</td>
    <td><%
var cmd = Server.CreateObject("ADODB.Command");
cmd.ActiveConnection = cn;
cmd.CommandText = "SELECT * FROM EmployeeType ORDER BY 2 ASC;"
cmd.CommandType = 1; //adCmdText
cmd.Prepared = true;
var rs = Server.CreateObject("ADODB.Recordset");
ReadRS(rs);
rs = cmd.Execute();
Response.Write("<select name=\"emptype\">\n");
Response.Write("<option value=\"\"></option>\n");
while(!rs.EOF){
    if(rs("EmpTypeID").Value == Request("EmpType").Item){
        Response.Write("<option value=\"" + rs("EmpTypeID") + "\" selected>" + rs("EmpTypeName") + "\n");
    } else {
        Response.Write("<option value=\"" + rs("EmpTypeID") + "\">" + rs("EmpTypeName") + "\n");
    }
    rs.MoveNext();
}
rs.Close();
Response.Write("</select>");
%></td>
</tr>
<tr>
    <td>First Name:</td>
    <td><input type="text" name="FirstName" value="<%= Request("FirstName").Item %>"></td>
</tr>
<tr>
    <td>Last Name:</td>
    <td><input type="text" name="LastName" value="<%= Request("LastName").Item %>"></td>
</tr>
<tr>
    <td>E-mail Address:</td>
    <td><input type="text" name="EmailAddress" value="<%= Request("EmailAddress").Item %>"></td>
</tr>
<tr>
    <td>Badge Number:</td>
    <td><input type="text" name="BadgeNumber" value="<%= Request("BadgeNumber").Item %>"></td>
</tr>
<tr>
    <td>Active Account:</td>
    <td><input type="checkbox" name="Active" value="<%= Request("Active").Item %>"
    <%
    if(Request("Active").Item == true) Response.Write(" checked")
    %>
    ></td>
</tr>
</table>
<p>Notes:<br><textarea name="EmpNotes" rows="5" cols="30"><%= Request("EmpNotes").Item %></textarea></p>
<p><input type="submit" value="Update"> <input type="reset" value="Reset"></p>

</form>

<%
function ReadRS(rs) {
    rs.CursorType = adOpenStatic;
    rs.LockType = adLockReadOnly;
    rs.ActiveConnection = cn;
}

function WriteRS(rs) {
    rs.CursorType = adOpenKeyset;
    rs.LockType = adLockOptimistic;
    rs.ActiveConnection = cn;
}
%>


Thanks in advance for any advice you can give me.

- MunicpalMistakes

The arguments are not right here cmd.CreateParameter("Active", 11, 10, "");

Going throyugh therm one-by-one we have:
Name => "Active" // OK
Type => 11 // = adBoolean, OK
Direction => 10 // not valid, it should be in the range 0-4, you want 1 (adParamInput)
Size => "" // size is not a string - you can leave this out for Boolean

So, in summmary, you need:
var parm9 = cmd.CreateParameter("Active", 11, 1);
cmd.Parameters.Append(parm9);
parm9.Value = Request("Active").Item;

hth
Phil