Multiple-step OLE DB operation generated errors

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!!
LVL 2
johnson00Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mis_understoodCommented:
> 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
mis_understoodCommented:
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
mcdown75Commented:
What exactly is on line 183?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

johnson00Author Commented:
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
nurbekCommented:
try

rs.open sSQL, adoCon, 3, 3
0
nurbekCommented:
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
johnson00Author Commented:
line 183:

rs.Fields("ADDSKL") = Request.Form("OtherSkills")
0
mcdown75Commented:
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
johnson00Author Commented:
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
mcdown75Commented:
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
johnson00Author Commented:
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
mcdown75Commented:
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
nurbekCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnson00Author Commented:
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
nurbekCommented:
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
nurbekCommented:
a bit late post :)
0
mcdown75Commented:
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
johnson00Author Commented:
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
nurbekCommented:
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
mcdown75Commented:
Thanks guys.  I have something to talk to my web guy about..... <evil grin>
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.