[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Multiple-step OLE DB operation generated errors

Posted on 2004-11-24
20
Medium Priority
?
2,102 Views
Last Modified: 2013-11-25
I'm using a page to connect to a database and add data to the tables.  Some of the fields on my form, and not always the same ones, produce the following error upon submission:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/spfldclinicscripts/InternalCombined.asp, line 183

Here's my connection string:

set adoCon = server.createobject("adodb.connection")
set rs = server.createobject("adodb.recordset")
sSQL =  "SELECT * FROM Internal"

adoCon.open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=C:\spfldclinicscripts\scdata\db\InternalApplicants.mdb"
rs.open sSQL, adoCon, 1, 3, 1

I use the same connection string with a different database and don't receive this error.  All fields in the database are set to text fields, and text is what is being collected on the form and is attempting to write to the database.  

Any suggestions are greatly appreciated!!
0
Comment
Question by:johnson00
  • 6
  • 6
  • 6
  • +1
20 Comments
 

Expert Comment

by:mis_understood
ID: 12666205
> adoCon.open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=C:\spfldclinicscripts\scdata\db\InternalApplicants.mdb"

My first thought: Do you think the path of the database (C:\spfldclinicscripts\scdata\db\InternalApplicants.mdb) is different on this server?
0
 

Expert Comment

by:mis_understood
ID: 12666313
Are you trying to update a row which has been newly inserted into a table, either through an OLEDB rowset or an ActiveX Data Objects (ADO) recordset?

This error is not caused due to the connection strings but where you update/insert records in the database. there could be the problem. Pasting your code might help in  resolving the issue.

Read more here.. http://support.microsoft.com/default.aspx?scid=kb;en-us;294160
0
 
LVL 2

Expert Comment

by:mcdown75
ID: 12666332
What exactly is on line 183?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 2

Author Comment

by:johnson00
ID: 12666371
rs.addnew


'Add a new record to the recordset
rs.Fields("LNAME") = Request.Form("LastName")
rs.Fields("CURDATE") = Request.Form("Date")
rs.Fields("FNAME") = Request.Form("FirstName")
rs.Fields("MINT") = Request.Form("MiddleName")
rs.Fields("APPPOS") = Request.Form("Position")
rs.Fields("EMPDTE") = Request.Form("SCDate")

Lots of other fields done just like this....

rs.Update

'Reset server objects
rs.Close
Set rs = Nothing
Set adoCon = Nothing

I thought it may have something to do with the cursor state or other options I selected in my connection string.  Those items determine how the database will function.
0
 
LVL 13

Expert Comment

by:nurbek
ID: 12666412
try

rs.open sSQL, adoCon, 3, 3
0
 
LVL 13

Expert Comment

by:nurbek
ID: 12666450
also
make sure that the textfields size is enough for the string coming from the form

textfield size should be bigger than the length of the string coming from the form field
0
 
LVL 2

Author Comment

by:johnson00
ID: 12666699
line 183:

rs.Fields("ADDSKL") = Request.Form("OtherSkills")
0
 
LVL 2

Expert Comment

by:mcdown75
ID: 12666761
Viola, you are using ASP, yes?  Don't use recordsets.  ASP and recordsets don't mix well.   Do you insert, update, and deletes through SQL statements.  Use the rs.open mySQL, MyConn as nurbek suggest.  But you can't open and run through a recordset like you do in VB from the ASP page.  Don't feel bad, I fought it for two days the first time until someone clued me in.
0
 
LVL 2

Author Comment

by:johnson00
ID: 12666824
Yes, ASP.  I just need to write this data to the database.  I don't need to run through it or query or select any of it.  I just need it to get in there. So how do you use SQL statements in ASP pages without using recordsets?  Do you have an example?
0
 
LVL 2

Expert Comment

by:mcdown75
ID: 12666981
No no.  You use your recordset.  Do something like this...

Dim oConn, oRS, oFld
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")
oConn.ConnectionString = "DSN=myDB;UID=sa"
OConn.Open
oRS.Open "Insert into myTable (field1 = " & val1 & ", field2=" & val2 & ")", oConn
oRS.Close
Set oRs = Nothing
oConn.Close
Set oConn = Nothing

Now you are updating with your sql instead of trying to manipulate a recordset.
0
 
LVL 2

Author Comment

by:johnson00
ID: 12667079
Thank you for the suggestion, but I'm working with a form here that has over 150 fields to be written to a database, and personally, I think that method of using the sql is far more complicated and laborious than it has to be when you can use the ASP code instead.  I will keep that in mind for future applications that don't require working with so much data.
0
 
LVL 2

Expert Comment

by:mcdown75
ID: 12667200
Are you using ASP.NET?  That's the only way your going to get around doing it this way.  If you run the compile ASP.NET program within the web browser you can use recordsets, but if it is straight ASP it's not going to work.
0
 
LVL 13

Accepted Solution

by:
nurbek earned 2000 total points
ID: 12667309
mcdown
you can use Recordset in classic asp

also johnson's idea is true dealing with more fields
(because it shows the error line, if occured)

also as i wrote ,
make sure that textfield size is bigger than the length of the string coming from the form field
(do you understan what i mean here?)

ConnStr =" DRIVER=Microsoft Access Driver (*.mdb);DBQ=C:\spfldclinicscripts\scdata\db\InternalApplicants.mdb"

Set DataConn= Server.CreateObject("ADODB.Connection")      
DataConn.Open ConnStr
Set RS = Server.CreateObject("ADODB.RecordSet")
               SQL = "SELECT * FROM myTable"
      RS.Open SQL, DataConn, 3, 3
      RS.AddNew
      RS("field1")= Request.Form("field1")
                'other fileds come here
      RS.Update
RS.Close
Set RS = Nothing      
DataConn.Close
Set DataConn = Nothing


0
 
LVL 2

Author Comment

by:johnson00
ID: 12667367
Nurbek -

You're correct.  It was the field size in the database that wasn't set to handle a string as long as I was trying to send.  McDown, it will work this way and is much more efficient, and doesn't have to be done in ASP.NET.

Thank you for all the help and suggestions!
0
 
LVL 13

Expert Comment

by:nurbek
ID: 12667392
example
RS("field1")= Request.Form("field1")

when you define field1 in the database
as text(50)

and length of Request.Form("field1") is bigger than the size of RS("field1")

(now, i think you understand what i mean :)) )

0
 
LVL 13

Expert Comment

by:nurbek
ID: 12667398
a bit late post :)
0
 
LVL 2

Expert Comment

by:mcdown75
ID: 12667410
I'm intrigued now.  What is your server running?  I was told that in IIS 5 you could not access the recordsets directly as you do in VB.  Most of the servers I access I have to live with the settings they give me.  Can you actually manipulate the RS itself with all of the normal parameters in straight ASP?  Very Interesting.  I'm going to have to have a talk with someone....
0
 
LVL 2

Author Comment

by:johnson00
ID: 12667454
Our server is running IIS 5, and I've always worked with my recordsets in this manner.  It will also work to do it the way you suggested, but it's much more efficient and easier to decode when you use the method I used here.  Good luck!
0
 
LVL 13

Expert Comment

by:nurbek
ID: 12667501
i am also working on IIS 5,
but i am not sure that you can use with full proporties (rs.find, rs.requery etc... as you can do VB)

but you can do many things like
add, delete, insert, recordcount etc.
0
 
LVL 2

Expert Comment

by:mcdown75
ID: 12667652
Thanks guys.  I have something to talk to my web guy about..... <evil grin>
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question