Error 80040e21 - Received when setting a new record's values

[Jason Kauffman]

I am opening a connection to my Table via the recordset object. I have
poured over it, but i can't seem to find the issue.

I consistently receive the following error:
----------------------------------------------------------------------------
--------------
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done.
/addEmployee.asp, line 49
------------------------------------------------------------
And the code:

The code is simple enough. I gather form data from the form that calls this
page. I am opening a recordset and adding a new record. I am then
populating the fields in the new record with the form data. Updating the
table and closing. The only thing I haven't accounted for here is the field
objRS("empID") which is an int(11) auto_increment field in the table. But I
believe it will default to the next number, hence auto_increment(I'm open
for clarification on this)?!?

<%
Option Explicit
Dim strConnect
%>
<!-- #include file="DataStore.asp" -->
<!-- #include file="adovbs.inc" -->
<%
Dim strFname, strLname, strMain, strAlt, strFax, strTitle, strEmail,
strWebsite
Dim objRS

strFname = Request.Form("fname")
strLname = Request.Form("lname")
strMain = Request.Form("mainNum")
strAlt = Request.Form("altNum")
strFax = Request.Form("faxNum")
strTitle = Request.Form("title")
strEmail = Request.Form("email")
strWebsite = Request.Form("website")

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "tblemployee", strConnect, adOpenForwardOnly, adLockOptimistic,
adCmdTable

objRS.AddNew

objRS("empFirst") = strFname '<----------this is line 49 where the error
is being thrown
objRS("empLast") = strLname
objRS("mainPhone") = strMain
objRS("altPhone") = strAlt
objRS("fax") = strFax
objRS("title") = strTitle
objRS("email") = strEmail
objRS("website") = strWebsite
objRS.Update

objRS.Close
Set objRS = Nothing

Response.Write ("Sucess")
%>
----------------------------------------------------------------------------
------
And the connection string:

<%
strConnect="DRIVER={MySQL ODBC 3.51
Driver};Server=localhost;Database=dbname;UID=usern ame;pwd=password;"
%>
----------------------------------------------------------------------------
-------

It seems like this sequence should work. Any help anyone can offer would be
greatly appreciated.

Thanks,

Jason

[Ray at]

"Jason Kauffman" <jkauffman22@attbi.com> wrote in message
news:2fltb.1909$196.140505@news.uswest.net...

> I am opening a connection to my Table via the recordset object. I have
> poured over it, but i can't seem to find the issue.
>
> I consistently receive the following error:
> --------------------------------------------------------------------------

--

> --------------
> Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
> Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.

> And the connection string:
>
> <%
> strConnect="DRIVER={MySQL ODBC 3.51
> Driver};Server=localhost;Database=dbname;UID=usern ame;pwd=password;"
> %>

Switch to an OLEDB connection, and you should get a better error than that
one.
strConnect="Provider=MySQLProv;Data Source=dbname;User
Id=username;Password=password;"

> ------------------------------------------------------------
> And the code:
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objRS.Open "tblemployee", strConnect, adOpenForwardOnly, adLockOptimistic,
> adCmdTable
>
> objRS.AddNew
>
> objRS("empFirst") = strFname '<----------this is line 49 where the

error
<snip>

> objRS.Close
> Set objRS = Nothing
>

Instead of creating a recordset and doing things this way, just execute an
INSERT.
strFname = Replace(strFname, "'", "''")
sSQL = "INSERT INTO [tblemployee] ([empFirst]) VALUES ('" & strFname & "')"
Set oADO = Server.CreateObject("ADODB.Connection")
oADO.Open strConnection
oADO.Execute sSQL
oADO.Close : Set oADO = Nothing

Ray at home

[Bob Barrows]

Jason Kauffman wrote:

> I am opening a connection to my Table via the recordset object.

What database?

> I have poured over it, but i can't seem to find the issue.
>
> I consistently receive the following error:
> --------------------------------------------------------------------------

--

> --------------
> Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
> Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available. No work was done.
> /addEmployee.asp, line 49
> ------------------------------------------------------------
>
> strFname = Request.Form("fname")
> strLname = Request.Form("lname")
> strMain = Request.Form("mainNum")
> strAlt = Request.Form("altNum")
> strFax = Request.Form("faxNum")
> strTitle = Request.Form("title")
> strEmail = Request.Form("email")
> strWebsite = Request.Form("website")
>

In addition to Ray's good advice, you should verify that these variables
contain values before attempting to write them into your database,
especially if any of your columns do not allow Nulls or zero-length strings.

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Jason Kauffman]

Ray,

Thanks for your help. The INSERT statement worked much better than using
addNew with a Recordset. Is the Replace() function replacing all single
quotes with double-single quotes, so that the INSERT statement won't throw
an error? At any rate, I appreciate the help.

Jason
"Jason Kauffman" <jkauffman22@attbi.com> wrote in message
news:2fltb.1909$196.140505@news.uswest.net...

> I am opening a connection to my Table via the recordset object. I have
> poured over it, but i can't seem to find the issue.
>
> I consistently receive the following error:
> --------------------------------------------------------------------------

--

> --------------
> Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
> Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.
> /addEmployee.asp, line 49
> ------------------------------------------------------------
> And the code:
>
> The code is simple enough. I gather form data from the form that calls

this

> page. I am opening a recordset and adding a new record. I am then
> populating the fields in the new record with the form data. Updating the
> table and closing. The only thing I haven't accounted for here is the

field

> objRS("empID") which is an int(11) auto_increment field in the table. But

I

> believe it will default to the next number, hence auto_increment(I'm open
> for clarification on this)?!?
>
> <%
> Option Explicit
> Dim strConnect
> %>
> <!-- #include file="DataStore.asp" -->
> <!-- #include file="adovbs.inc" -->
> <%
> Dim strFname, strLname, strMain, strAlt, strFax, strTitle, strEmail,
> strWebsite
> Dim objRS
>
> strFname = Request.Form("fname")
> strLname = Request.Form("lname")
> strMain = Request.Form("mainNum")
> strAlt = Request.Form("altNum")
> strFax = Request.Form("faxNum")
> strTitle = Request.Form("title")
> strEmail = Request.Form("email")
> strWebsite = Request.Form("website")
>
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objRS.Open "tblemployee", strConnect, adOpenForwardOnly, adLockOptimistic,
> adCmdTable
>
> objRS.AddNew
>
> objRS("empFirst") = strFname '<----------this is line 49 where the

error

> is being thrown
> objRS("empLast") = strLname
> objRS("mainPhone") = strMain
> objRS("altPhone") = strAlt
> objRS("fax") = strFax
> objRS("title") = strTitle
> objRS("email") = strEmail
> objRS("website") = strWebsite
> objRS.Update
>
> objRS.Close
> Set objRS = Nothing
>
> Response.Write ("Sucess")
> %>
> --------------------------------------------------------------------------

--

> ------
> And the connection string:
>
> <%
> strConnect="DRIVER={MySQL ODBC 3.51
> Driver};Server=localhost;Database=dbname;UID=usern ame;pwd=password;"
> %>
> --------------------------------------------------------------------------

--

> -------
>
> It seems like this sequence should work. Any help anyone can offer would

be

> greatly appreciated.
>
> Thanks,
>
> Jason
>
>

[Ray at]

Yes, the replace is so that you don't wind up with something like:

INSERT INTO [Restaurants] ([ResterauntName]) VALUES ('Joe's Chicken Shack')

That ' in Joe's would mess things up. The way to insert a ' is by doubling
it in your sql string.

Ray at home

"Jason Kauffman" <jkauffman22@attbi.com> wrote in message
news:nettb.49$7Q.19731@news.uswest.net...

> Ray,
>
> Thanks for your help. The INSERT statement worked much better than using
> addNew with a Recordset. Is the Replace() function replacing all single
> quotes with double-single quotes, so that the INSERT statement won't throw
> an error? At any rate, I appreciate the help.
>
> Jason