INSERT into statement truncating data to first three characters

[S N]

I was using an INSERT into statement to insert records into an access
database which was working alright.
However, ever since I am using the same SQL statement to insert records into
SQL server database (nothing has changed except the connection string and
schema information), the INSERT into statement is inserting data of only
first three characters into one particular field , instead of 5 or 7
characters as is being inserted in the form in asp page. The SQL server
database field is character type (nvarchar) .
I have checked the form values being submitted from the asp page and the
values are getting submitted alright, still the database is getting only the
first three characters from the field. When I use the same asp page to insert
into the access database, it again inserts all the data without any
truncation.
Please advise on how to insert all the characters in the SQL server database
field.

[Erland Sommarskog]

S N (S...@discussions.microsoft.com) writes:
> I was using an INSERT into statement to insert records into an access
> database which was working alright. However, ever since I am using the
> same SQL statement to insert records into SQL server database (nothing
> has changed except the connection string and schema information), the
> INSERT into statement is inserting data of only first three characters
> into one particular field , instead of 5 or 7 characters as is being
> inserted in the form in asp page. The SQL server database field is
> character type (nvarchar) .

nvarchar(what?)

> I have checked the form values being submitted from the asp page and the
> values are getting submitted alright, still the database is getting only
> the first three characters from the field. When I use the same asp page
> to insert into the access database, it again inserts all the data
> without any truncation.

Well, for starters verify that the column is not created as nvarchar(3).

Assuming that it is not such a simple hickup, use Profiler to catch the
statements emitted by ASP page.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[S N]

"Erland Sommarskog" wrote:

> S N (S...@discussions.microsoft.com) writes:
> > I was using an INSERT into statement to insert records into an access
> > database which was working alright. However, ever since I am using the
> > same SQL statement to insert records into SQL server database (nothing
> > has changed except the connection string and schema information), the
> > INSERT into statement is inserting data of only first three characters
> > into one particular field , instead of 5 or 7 characters as is being
> > inserted in the form in asp page. The SQL server database field is
> > character type (nvarchar) .
>
> nvarchar(what?)

nvarchar(50)

>
> > I have checked the form values being submitted from the asp page and the
> > values are getting submitted alright, still the database is getting only
> > the first three characters from the field. When I use the same asp page
> > to insert into the access database, it again inserts all the data
> > without any truncation.
>
> Well, for starters verify that the column is not created as nvarchar(3).
>
> Assuming that it is not such a simple hickup, use Profiler to catch the
> statements emitted by ASP page.

I have tried to print the variables submitted by the form using
response.write statement, and have found that the values submitted by the
form are alright and full length of characters 5 or 7 etc are getting read by
the asp page. It also gets submitted properly and full length gets stored
when the same page inserts it in an access database. However, it does not
enter full length of field value when done in SQL server database only the
first three characters get stored in the SQL server database.
It surprises me but that is how it is. Kindly advise.

>
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

> .
>

[S N]

I would also like to indicate that I am using SQL Server Express, and hence I
dont have the profiler to check the insert statements.

I have tried to print the variables submitted by the form using
response.write statement, and have found that the values submitted by the
form are alright and full length of characters 5 or 7 etc are getting read
by
the asp page. It also gets submitted properly and full length gets stored
when the same page inserts it in an access database. However, it does not
enter full length of field value when done in SQL server database only the
first three characters get stored in the SQL server database.
It surprises me but that is how it is. Kindly advise.

[S N]

I downloaded the fee Anjlab SQL Profiler and found that the textdata exec
sp_executesql N'INSERT INTO
is inserting only three characters only and it is showing @P3 char(3) at the
location of field data in the profiler.
How to correct it to ensure all the characters are inserted, more than 3
chars in length.

[Erland Sommarskog]

S N (S...@discussions.microsoft.com) writes:
> I would also like to indicate that I am using SQL Server Express, and
> hence I dont have the profiler to check the insert statements.

Developer Edition is 50 USD. The license would not allow you to
use Profiler on a production system, but this is obviously development.

And, you can run server-side traces on Express. While you don't need
Profiler to set it up, using Profiler to set up the conditions and
then scripting the trace is far easier than doing all by hand. Not
the least the first time.

> I have tried to print the variables submitted by the form using
> response.write statement, and have found that the values submitted by
> the form are alright and full length of characters 5 or 7 etc are
> getting read by the asp page. It also gets submitted properly and full
> length gets stored when the same page inserts it in an access database.
> However, it does not enter full length of field value when done in SQL
> server database only the first three characters get stored in the SQL
> server database.

Well, I have zero idea of what your code does. Maybe you could post it?

[S N]

I downloaded the fee Anjlab SQL Profiler and found that the textdata exec
sp_executesql N'INSERT INTO
is inserting only three characters only and it is showing @P3 char(3) at the

location of field data for field 'sClass' as indicated in the code below when
seen in the profiler. However, it inserts the complete data without any
truncation when the same SQL is used to insert data in MS Access.
How to correct it to ensure all the characters are inserted, without any
truncation in SQL server.

MM_editCmd.CommandText = "INSERT INTO tblData (nOrg, dDate, sClass, nHAG1,
nHAG2, nHAG3,nHAG4,nHAG5,nHAG6,nHAG7) VALUES (?, ?, ?, ?, ?, ?, ?,?,?,?)"
MM_editCmd.Prepared = true
MM_editCmd.CommandType=1

rstClassMaster.MoveFirst
Do until rstClass.EOF
With Request

nIDVal=rstClassificationMaster.Fields.Item("aID").Value
ardata=array(server.HTMLEncode(.Form("nOrg")), _
server.HTMLEncode(.Form("dDate")), _
server.HTMLEncode(.Form("sClass" &(nIDVal))), _
server.HTMLEncode(.Form("nHAG1"&(nIDVal))), _
server.HTMLEncode(.Form("nHAG2"&(nIDVal))),_
server.HTMLEncode(.Form("nHAG3"&(nIDVal))),_
server.HTMLEncode(.Form("nHAG4"&(nIDVal))),_
server.HTMLEncode(.Form("nHAG5"&(nIDVal))),_
server.HTMLEncode(.Form("nHAG6"&(nIDVal))),_
server.HTMLEncode(.Form("nHAG7")))

MM_editCmd.Execute ,arData,adExecuteNoRecords '128

End With
rstClassMaster.MoveNext

Loop

"Erland Sommarskog" wrote:

> .
>