Solved

Text Insert Thru VB.NET Stored Proc Causes Severe Error

Posted on 2003-11-11
12
400 Views
Last Modified: 2011-09-20
I have an ASP.NET application powered by VB.NET that I'm using to insert a large string of text into a SQL Server 2000 table. The column the text is being inserted into is datatype 'text'. There is a textbox on the ASP.NET page. When my user enters text (copy for an html email) into the textbox and hits the submit button, the contents of the textbox are inserted via a stored procedure into the the appropriate table in a SQL Server 2000 database.

I'm working with two SQL Servers: SQL Server A and SQL Server B. SQL Server A is physically located ~20 feet from the machine hosting the ASP.NET application. SQL Server B is located ~2000 miles away from the machine hosting the ASP.NET application.

When my connection string points to SQL Server A, the stored procedure flawlessly inserts the large block of text into the database. When my connection string points to SQL Server B, the stored procedure 'fails' when I add the same block of text and I catch the following error "A severe error occurred on the current command. The results, if any, should be discarded." and sometimes that error is accompanied by this error "General network error. Check your network documentation."

The only thing I change is the connection string. The database structures, including table columns and datatypes and stored procs, are exactly the same on both Server A and Server B.

Also, if I cut the large bit of text in half or just try to upload one line of text, the ASP.NET application will successfully insert the text into Server B. So the problem may have something to do with the size of the text block (I estimate the text is about 7k-15k worth of data).

Also, if I execute the stored procedure via Query Analyzer using the exact same parameters that I pass thru using the ASP.NET application, the sproc executes and the data is inserted.

Any ideas what I'm missing here? The errors aren't too informative and my google and yahoo searches aren't yielding much info. I can post the sprocs or any of the VB.NET code, though I don't think that's the problem since the code works if I enter a smaller block of text and works on one server and not the other. I'm choosing to award 500 points purely because my question is so long even though for some people the answer might be obvious (I hope).

Thanks,
Scott




0
Comment
Question by:sneidig
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 

Expert Comment

by:Orbitalgr
ID: 9730828
I had experience a similar problem and the solution was just to encode the html tags. It would be easy to check whether you experience the same problem (though i doubt u do) or not, by posting a large text with no html tags.
If it works then just encode the textbox.text when you post it.
Good Luck
Orbital.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9731610
Hi,

The ADO Connexion Object has a CommandTimeOut property
Setting it to 0 turns it to infinity, and you can set it to any value in secs
eg 90 stands for one minute and a half

I don't know ADO.NET very well, but maybe you'll find this property
for the SQLConnection Object if you use System.Data.SQLClient

Just my two cents ...

Hilaire
0
 

Author Comment

by:sneidig
ID: 9732678
Thanks for your replies Orbital and Hilaire!

Orbital - Earlier we attempted to upload 100 characters at a time, no html tags, to the textbox to insert them into the database. We found that entering around 4000 characters causes the insert to fail and generate the errors I mentioned above. Less than 4000 characters is OK but more than that and we get errors. Also, we can include HTML tags in the text and it will work as long as the text is under 4000 characters.

Hilaire - The SQLCommand object does indeed have a CommandTimeOut property. I set the timeout value for the command object I'm using to 0 and tried again but no luck. The problem doesn't seem to be a timeout issue because the page reloads immediatly after I hit the submit button whether the insert succeeds or fails. The timeout problems I've run into in the past have always taken a long time before the page reloads. With this it's immediate.

Thanks you both for your replies. Let me know if you think of anything else.

Scott
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:sneidig
ID: 9732758
Hilaire - I just read your post again and realized you were talking about the connection object and not the command object.

With ADO.NET you create a SQLConnection object and pass a connection string to it. Then you create a SQLCommand object and pass the newly created SQLConnection object to it.

The SQLConnection object does have a readonly connectiontimeout property, but CommandTimeOut is a property of the SQLCommand object.

Basically the same thing, I just wanted to clarify because my comment above probably didn't make complete sense.

Thanks Again,
Scott
0
 

Expert Comment

by:Orbitalgr
ID: 9733092
Is it possible that your datafield length (on the second server) to be set to a number around 4000?
Why don't you try to run a simple alter query againts your table such as:

ALTER TABLE  TableName
ALTER COLUMN ColumnName
varchar(7200)


0
 

Author Comment

by:sneidig
ID: 9733144
That's a good idea. I'm not sure that's the solution though. Note that if I execute the same stored procedure directly thru Query Analyzer, the stored procedure executes successfully. The exact same stored proc with the exact same data called by the ASP.NET application fails.

So if there was some issue with the datatype, wouldn't the stored proc executed thru Query Analyzer fail as well?

I noticed you suggested the datatype be a large varchar (varchar(7200)). I have the datatype set to text. Maybe there's something I don't know about the text datatype and I should be using a large varchar instead.

I'll expirement with these ideas and let you know if anything works.

Thanks for replying,
Scott

0
 

Author Comment

by:sneidig
ID: 9733397
I just finished trying changing the datatype from text to varchar(7200) and then I tried it as ntext. Both changes yeilded the same result: error - A severe error occurred on the current command. The results, if any, should be discarded.

Thanks for trying though,
Scott
0
 

Author Comment

by:sneidig
ID: 9751214
Allright we were able to solve this problem, or at least find a suitable workaround. Remember how I wrote that I could execute the stored procedure in Query Analyzer? Well the trick was not to add parameters to the command object, but instead set the value of the command objects 'commandtext' property to a sql statement that executes the stored procedure ( exec yourProc 'param1', 'param2', 3, 'param4' ). For some reason setting the command type to 'storedprocedure' and adding parameters to the command object was causing the unusual error I documented above.

Thanks again for you help. I guess I don't get to hand out any points this time, but thank you for your efforts. This is my first post on experts-exchange so if should have handed out points and didn't please let me know. Be honest ;o)

Thanks,
Scott
0
 

Author Comment

by:sneidig
ID: 11506860
I would have "closed" this question on 11/14/03 on the day of my last post, but I don't see any functionality in my Admin page or on this page that allows me to do so. No one answered my question, I ended solving the problem but I can't award points to myself. So I just left it open because I don't see any other options. Am I missing something? Maybe when no answers are provided you just wait three weeks and have admins close the topic.

I have deleted questions in the past if I solved the problem before anyone posted a response. But after the first response is posted it seem our only other choice to get some closure is to award points. TheLearnedOne, please let me know what you think.
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 11533207
PAQed, with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

726 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