December 30, 2011 at 2:58 am
Hi
Hope someone can help with this one. I've created a web-based form with a number of parameters (50 or so), named in the HTML code. The user enters data into various text input fields in the form. The form's submit action passes the form contents field-by-field into variables in some ASP (I'm using VB.NET) code in an .aspx file. The code does various things including data validity checks (date and time types, valid ranges, etc.), some user input to bit type conversion ('Yes' to 1, for example) etc. etc. This all works fine.
It all falls down when I'm trying to connect to my SQL Server database and INSERT INTO the tables I've set up there. I've put the tables in 1NF (i.e. no duplicate values, primary keys). The connection is fine and I'm able to insert data. However, if I try to insert a string longer than 128 characters I get an error. The stack trace is below:
[COMException (0x80040e14): The identifier that starts with 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nullam augue augue, congue eget cursus quis, hendrerit porttitor nulla.' is too long. Maximum length is 128.]
Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack, Boolean IgnoreReturn) +792
Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn) +1219610
ASP.send_aspx.__Render__control1(HtmlTextWriter __w, Control parameterContainer) in C:\inetpub\wwwroot\send.aspx:447
System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +109
System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +8
System.Web.UI.Page.Render(HtmlTextWriter writer) +29
System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +27
System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +100
System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +25
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3060
I'm using ADO for the connection on a .NET Framework 4.5, IIS-enabled Windows 7 machine with the binding set to localhost:80. Connection string is (with server & password redacted):
connection = Server.CreateObject("ADODB.Connection")
connection.ConnectionString = "Data Source=XXXX\SQLEXPRESS;Initial Catalog=RFCINFO;User Id='form_login';Password='XXXX';PROVIDER=SQLNCLI10"
connection.Open
(Yes, I know the credentials are plaintext - I'll fix that 🙂 )
The datatype for the affected column is NVARCHAR(MAX) in SQL Server, System.String in VB.NET. It's not max string length in VB that's the issue as this is 2^31 - 1 for VB. And I'm using SQL Server 2008 which means row-overflow should be supported for variable length columns so the 8092 byte row restriction shouldn't apply.
I'm racking my brains about this and can only find one workaround, which is to write the query string to a text file then execute a batch file (containing a SQLCMD string with -i <text file> parameter) to remotely insert the data. Then parse an output log file for errors. However this is an extremely messy solution and error handling will be a nightmare.
Does anyone have any better ideas or am I missing something fundamental?
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
December 30, 2011 at 3:24 am
By looking into code & your description I can confirm that it’s not SQL Server limitation. You didn’t get ‘SqlException’ that indicate database errors. The Error says ‘COMException’ so I would suggest you to cross check asp.net (or vb.net) components & its definitions.
December 30, 2011 at 3:33 am
I've just run the query directly against the DB, the same message appears:
Msg 103, Level 15, State 4, Line 1
The identifier that starts with 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nullam augue augue, congue eget cursus quis, hendrerit porttitor nulla.' is too long. Maximum length is 128.
So definitely a SQL problem. Is it because I'm using an SP (method$Loader)? SP definition is:
CREATE PROCEDURE method$Loader (
@SWN nvarchar(10), @PREREQS nvarchar(max), @IMPPLAN nvarchar(max)
)
AS
INSERT INTO dbo.METHOD
VALUES(@SWN, @PREREQS, @IMPPLAN)
GO
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
December 30, 2011 at 3:39 am
Can you please post the table structure (DDL) for 'dbo.METHOD'?
December 30, 2011 at 3:48 am
Got it! Many thanks to Dan Lloyd - I'm using double quotes in the VALUES in SQL rather than single quotes. So SQL was taking the values as column names and... there's a 128 char limit on column names. Works fine with singles. Thanks!
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply