Wrox Programmer Forums
|
ASP Pro Code Clinic As of Oct 5, 2005, this forum is now locked. No posts have been deleted. Please use "Classic ASP Professional" at: http://p2p.wrox.com/forum.asp?FORUM_ID=56 for discussions similar to the old ASP Pro Code Clinic or one of the other many remaining ASP and ASP.NET forums here.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP Pro Code Clinic section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 29th, 2004, 03:38 PM
Registered User
 
Join Date: Sep 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default ADO Command Update Problem

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
 
Old September 30th, 2004, 04:02 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Asynchronous Command Execution in ADO.NET 2.0 suresh_ala Visual Studio 2005 1 February 8th, 2007 10:31 AM
ADO Command Update hcweb Classic ASP Basics 2 January 14th, 2004 06:55 PM
Add and Update Problem with ADO bpadmana Pro VB Databases 1 August 21st, 2003 03:36 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.