asked on

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!!

mis_understood

> 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?

mis_understood

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

mcdown75

What exactly is on line 183?

johnson00

ASKER

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.

nurbek

try

rs.open sSQL, adoCon, 3, 3

nurbek

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

johnson00

ASKER

line 183:

rs.Fields("ADDSKL") = Request.Form("OtherSkills")

mcdown75

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.

johnson00

ASKER

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?

mcdown75

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.

johnson00

ASKER

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.

mcdown75

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.

ASKER CERTIFIED SOLUTION

nurbek

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnson00

ASKER

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!

nurbek

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 :)) )

nurbek

a bit late post :)

mcdown75

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....

johnson00

ASKER

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!

nurbek

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.

mcdown75

Thanks guys. I have something to talk to my web guy about..... <evil grin>