Solved

Multiple-step OLE DB operation generated errors: works in development

Posted on 2006-10-24
21
327 Views
Last Modified: 2008-02-01
I am getting an error after moving from development to a production server:
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.

/newticketsave.asp, line 35

The line is:
    rs("TK_DETAIL") = Request.Form("TK_DETAIL")

I can run the page local using the same data and pages and it works.  On the production server, it errors out. (development is XP Pro SP2 IIS5, production is Server2003 IIS6)  Checked spelling, form, etc.  Field allows for nulls but the data it's passing is not null.

Thanks,

Traigo
0
Comment
Question by:traigo
  • 11
  • 5
  • 3
  • +2
21 Comments
 
LVL 12

Expert Comment

by:sandip132
ID: 17801336
Try using this structure of connectionstring.

connStr= "driver={MySQL ODBC 3.51 Driver};server=localhost;uid=userid;pwd=password;database=database;Option=16387;"
0
 
LVL 12

Expert Comment

by:sandip132
ID: 17801349
Usually this error rises in a case if there is a mismatch between the
datatype in a database and the value that application tries to insert. For
example, if your field is numeric in a database and you try to insert a
string, you could get this error.  TRy checking your query and passed values to database.

REgards,
Sandip.
0
 
LVL 6

Expert Comment

by:ksbhat
ID: 17801408
See if these links help
http://support.microsoft.com/kb/269495

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 1

Expert Comment

by:jethropassing
ID: 17802520
I have even got this error if the error actually exists on another line.  So instead of just checking your field "TK_DETAIL" make sure you check the other field in the table or tables you are updating.  Look at date field especially.  What I do is delete part of my code at a time until it starts working to find what is really generating that error.

Good Luck
0
 

Author Comment

by:traigo
ID: 17804954
ksbhat, link doesn't work

sandip132, I'm not using MySQL.  I am using MSSQL 2005.  I found some information about mismatched datatypes but my filed is set to nvarchar(max) and I'm trying to save text to the field.  It's only about 15 characters.

jethropassing, I'll look.  But it's interesting that the exact same page works in SQL Express 2005 on my local IIS5 but when I move to SQL 2005 on IIS6 it doesn't.  Just copy and paste the pages.  They are identical.
0
 

Author Comment

by:traigo
ID: 17805116
I commented out that one line and I was able to save records.  
0
 

Author Comment

by:traigo
ID: 17805134
Not sure if it helps but it's the only nvarchar(max) and the only field in the record larger than 254
0
 
LVL 1

Expert Comment

by:jethropassing
ID: 17805285
You might have tried this but, can you update this field with any other content.  Such as try:

rs("TK_DETAIL") = "Testing this field"

And see if you can get this field to take any update info.  If this works,then check to see what the asp page is getting from Request.Form("TK_DETAIL") Such as:

Response.Write Request.Form("TK_DETAIL")

There might be a hyphen or a on reqonizable charactor in this Request.Form("TK_DETAIL") string.


0
 

Author Comment

by:traigo
ID: 17805394
jethropassing, I get the same error by just putting some text in there rather than trying to pull from the form
0
 

Author Comment

by:traigo
ID: 17805549
I'm going through the rest of my pages now and I'm finding that I'm having the problem with the same field on multiple pages so I think it's something to do with SQL2005.
0
 
LVL 1

Expert Comment

by:jethropassing
ID: 17805648
Can you change the data type to varchar my preferance.  Check out:


http://www.firebirdsql.org/manual/migration-mssql-data-types.html
0
 

Author Comment

by:traigo
ID: 17805710
I'm upgrading SQL right now to the newest version to see if that helps.  I was running SP1 local and pre-sp1 production.  I didn't realize they had released an SP1 yet and forgot how long ago I installed it on my server.
0
 

Author Comment

by:traigo
ID: 17806508
now I'm on v9.0.2047 and I don't get the error anymore, but it stops at that line still and I get a blank page.  I used response.write "made it here" before and after the line.  On the page I get the first but not the second.  Debugger doesn't come up on the server either.

I changed to varchar(max) and had the same result
I changed to nvarchar(100) and it worked.
0
 
LVL 1

Expert Comment

by:jethropassing
ID: 17807582
I have never used varchar(max)

I would use varchar(250)
0
 

Author Comment

by:traigo
ID: 17807620
The field has the potential for storing large amounts of text.  It's supposed to be similar to a blob or memo data type.
0
 
LVL 1

Accepted Solution

by:
jethropassing earned 500 total points
ID: 17808104
Can you use text()
0
 

Author Comment

by:traigo
ID: 17808125
I've tried looking up the datatypes and can't really find a difference between text/ntext and varchar(max)/nvarchar(max).  I remember working with memo and blob fields.  They had problems with storing and retrieving carriage returns.  When saving and displaying, I had to do a replace to convert them to the correct type.  Is this true with text()?
0
 
LVL 12

Expert Comment

by:sandip132
ID: 17808561
Please store your sql query in a string variable, response the output to the page.

string YourQuery="Insert into............"

check in query analyser Or Paste your sqlquery here.

OR

Check whether you are inserting same values in any Primary key column.

Regards,
Sandip.
0
 

Author Comment

by:traigo
ID: 17812627
text, or rather ntext, works for all my forms now.  The only thing I had to change was where I concantenated fields.  I was using SQL + to do so.  Now I am doing it in VB on the page.

Thanks for the help!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17814336
If you can you should use varchar(max) instead of Text.  You will find that the Text data type is very limiting (as you are starting to find out).  The reason varchar(max) is causing you grief I suspect is because your database is set to 8.0 (SQL Server 2000) which does not support varchar(max)
0
 

Author Comment

by:traigo
ID: 17814482
How might I change it to not be?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Autosum input type=text when checkbox is true 28 102
Writing comments on <p></P> or paragraph 2 19
innerHTML 7 35
Syntax for query to update table 2 29
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

830 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