Solved

Text Insert Thru VB.NET Stored Proc Causes Severe Error

Posted on 2003-11-11
12
397 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
12 Comments
 

Expert Comment

by:Orbitalgr
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:sneidig
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:sneidig
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
PAQed, with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now