NVarchar and SelectedValue and InsertCommand

Hi guys,
I've got a problem inserting data into my db.
I've created a NVARCHAR column and I'm using SelectedValue Parameters.
I only have a problem in the INSERT mode.
The UPDATE and DELETE are working fine.
All the fields can be updated or deleted, but I can't insert new data inside my db.
I've changed one column to NVARCHAR : "reference"
I use NVARCHAR because I have some Arabic Fields (unicode) into my db.
But I've copy-pasted everything about the SqlDataSource.
10x a lot anyway !

ASP.NET using MS Visual Studio 2005 :

...

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

OldValuesParameterFormatString="original_{0}"
OnDeleted="SqlDataSource2_Deleted"
OnUpdated="SqlDataSource2_Updated"
OnInserted="SqlDataSource2_Inserted"
SelectCommand=
"SELECT [reference], [ddf], [description], [quantity], [pru], [supname], [catname]
FROM [Products]
WHERE ([reference] = @.reference)"
InsertCommand="INSERT INTO [Products]
([reference], [ddf], [description], [quantity], [pru], [supname], [catname])
VALUES (@.reference, @.ddf, @.description, @.quantity, @.pru, @.supname, @.catname)"
UpdateCommand="UPDATE [Products]
SET [ddf] = @.ddf,
[description] = @.description,
[quantity] = @.quantity,
[pru] = @.pru,
[supname] = @.supname,
[catname] = @.catname
WHERE [reference] = @.original_reference"
DeleteCommand="DELETE FROM [Products] WHERE [reference] = @.original_reference">

<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="reference" PropertyName="SelectedValue" Type="String" />
</SelectParameters>

<InsertParameters>
<asp:Parameter Name="reference"
Type="String" />
<asp:Parameter Name="ddf"
Type="DateTime" />
<asp:Parameter Name="description"
Type="String" />
<asp:Parameter Name="quantity"
Type="String" />
<asp:Parameter Name="pru"
Type="Decimal" />
<asp:Parameter Name="supname"
Type="String" />
<asp:Parameter Name="catname"
Type="String" />
</InsertParameters>

<UpdateParameters>
<asp:Parameter Name="ddf"
Type="DateTime" />
<asp:Parameter Name="description"
Type="String" />
<asp:Parameter Name="quantity"
Type="String" />
<asp:Parameter Name="pru"
Type="Decimal" />
<asp:Parameter Name="supname"
Type="String" />
<asp:Parameter Name="catname"
Type="String" />
<asp:Parameter Name="original_reference"
Type="String" />
</UpdateParameters>

<DeleteParameters>
<asp:Parameter Name="original_reference"
Type="String" />
</DeleteParameters>

</asp:SqlDataSource>

SQL SERVER 2005 : Create Database File

USE master
GO

IF EXISTS(SELECT * FROM sysdatabases
WHERE name='Products')
DROP DATABASE Products
GO

CREATE DATABASE Products
ON ( NAME=Product,
FILENAME = 'C:\WebApp\App_Data\Products.mdf',
SIZE=10 )
GO

USE Products
GO

CREATE TABLE Categories (
catname VARCHAR(25) NOT NULL,
PRIMARY KEY (catname) )
GO

CREATE TABLE Suppliers (
supname VARCHAR(25) NOT NULL,
tel VARCHAR(50) ,
cell VARCHAR(50) ,
fax VARCHAR(50) ,
pob VARCHAR(25) ,
address VARCHAR(300) ,
nearby VARCHAR(100) ,
website VARCHAR(100) ,
email VARCHAR(100) ,
skypephone VARCHAR(100) ,
PRIMARY KEY (supname) )
GO

CREATE TABLE Products (
reference NVARCHAR(25) NOT NULL,
ddf DATETIME NOT NULL,
description VARCHAR(50) NOT NULL,
quantity VARCHAR(10) NOT NULL,
pru MONEY NOT NULL,
supname VARCHAR(25) NOT NULL,
catname VARCHAR(25) NOT NULL,
pv MONEY NOT NULL,
PRIMARY KEY(reference),
FOREIGN KEY(catname) REFERENCES Categories(catname),
FOREIGN KEY(supname) REFERENCES Suppliers(supname) )
GO

what type of exception give the .net runtime, ensure that your size of reference parameter is 25 chars

|||

Hi,

From your description, it is a database operation issue. Based on the code you provided, we found that it was the following sql statement that handles with your inserting operation.

InsertCommand="INSERT INTO [Products]
([reference], [ddf], [description], [quantity], [pru], [supname], [catname])
VALUES (@.reference, @.ddf, @.description, @.quantity, @.pru, @.supname, @.catname)"

And in your database creation file, you are creating your [Products] table in the following way:

CREATE TABLE Products (
reference NVARCHAR(25) NOT NULL,
ddf DATETIME NOT NULL,
description VARCHAR(50) NOT NULL,
quantity VARCHAR(10) NOT NULL,
pru MONEY NOT NULL,
supname VARCHAR(25) NOT NULL,
catname VARCHAR(25) NOT NULL,
pv MONEY NOT NULL,
PRIMARY KEY(reference),
FOREIGN KEY(catname) REFERENCES Categories(catname),
FOREIGN KEY(supname) REFERENCES Suppliers(supname) )
GO

We can see that pv is a NOT NULL field, but in your insert statement, you haven't inserted the pv field, so the inserting operation couldn't work.

For this kinds of issue, it's better to run your sql statement in some tools like SQLServer management studio to check if the statement can work. After that, you can use it in your .NET application.

Thanks.

|||

10x a lot guys ! :D
I forgot inserting the "pv" field.
I though the problem was from the NVarchar.
Sorry to bother u but i'm still a beginner ! :D

nvarchar and output to flat file.

Hello,
Im outputting a sql table to a text file. One of the columns in this table
is an nvarchar(1200) but the result in the flat file is truncated to 254
chars....how do ensure that it outputs all chars up to 1200?
thxJohn,
which approach / utility are you using to outputting the sql table to a text
file?
AMB
"John Smith" wrote:

> Hello,
> Im outputting a sql table to a text file. One of the columns in this table
> is an nvarchar(1200) but the result in the flat file is truncated to 254
> chars....how do ensure that it outputs all chars up to 1200?
> thx
>
>|||I think I gave him a suitable answer on a separate post.
Barrysql

NVarchar And NChar

Hi ,
What is the different I use NChar and Char as my database field datatype
? In what circumstance I should use NChar or NVarchar ? Kindly advised.
Travis Tan
The datatypes nvarchar, nchar, ntext are UNICODE data types.
From SQL Server 2005 BOL
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6befd9de-9218-4a2c-826e-54f957ab6b4a.htm):
Storing data in multiple languages within one database is difficult to
manage when you use only character data and code pages. It is also difficult
to find one code page for the database that can store all the required
language-specific characters. Additionally, it is difficult to guarantee the
correct translation of special characters when being read or updated by
different clients running various code pages. Databases that support
international clients should always use Unicode data types instead of
non-Unicode data types.
For example, consider a database of customers in North America that must
handle three major languages:
Spanish names and addresses for Mexico
French names and addresses for Quebec
English names and addresses for the rest of Canada and the United States
When you use only character columns and code pages, you must take care to
make sure the database is installed with a code page that will handle the
characters of all three languages. You must also take care to guarantee the
correct translation of characters from one of the languages when read by
clients running a code page for another language.
With the growth of the Internet, it is even more important to support many
client computers that are running different locales. Selecting a code page
for character data types that will support all the characters required by a
worldwide audience would be difficult.
The easiest way to manage character data in international databases is to
always use the Unicode nchar, nvarchar, and ntext data types, instead of
their non-Unicode equivalents, char, varchar, and text......
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:B5DBA6D6-7559-40CF-A13F-D2E7ADE98789@.microsoft.com...
> Hi ,
> What is the different I use NChar and Char as my database field datatype
> ? In what circumstance I should use NChar or NVarchar ? Kindly advised.
> --
> Travis Tan

NVarchar And NChar

Hi ,
What is the different I use NChar and Char as my database field datatype
? In what circumstance I should use NChar or NVarchar ? Kindly advised.
--
Travis TanThe datatypes nvarchar, nchar, ntext are UNICODE data types.
From SQL Server 2005 BOL
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6befd9de-9218-4a2c-826e-54f957ab6b4a.htm):
Storing data in multiple languages within one database is difficult to
manage when you use only character data and code pages. It is also difficult
to find one code page for the database that can store all the required
language-specific characters. Additionally, it is difficult to guarantee the
correct translation of special characters when being read or updated by
different clients running various code pages. Databases that support
international clients should always use Unicode data types instead of
non-Unicode data types.
For example, consider a database of customers in North America that must
handle three major languages:
Spanish names and addresses for Mexico
French names and addresses for Quebec
English names and addresses for the rest of Canada and the United States
When you use only character columns and code pages, you must take care to
make sure the database is installed with a code page that will handle the
characters of all three languages. You must also take care to guarantee the
correct translation of characters from one of the languages when read by
clients running a code page for another language.
With the growth of the Internet, it is even more important to support many
client computers that are running different locales. Selecting a code page
for character data types that will support all the characters required by a
worldwide audience would be difficult.
The easiest way to manage character data in international databases is to
always use the Unicode nchar, nvarchar, and ntext data types, instead of
their non-Unicode equivalents, char, varchar, and text......
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:B5DBA6D6-7559-40CF-A13F-D2E7ADE98789@.microsoft.com...
> Hi ,
> What is the different I use NChar and Char as my database field datatype
> ? In what circumstance I should use NChar or NVarchar ? Kindly advised.
> --
> Travis Tan

NVarchar And NChar

Hi ,
What is the different I use NChar and Char as my database field datatype
? In what circumstance I should use NChar or NVarchar ? Kindly advised.
Travis TanThe datatypes nvarchar, nchar, ntext are UNICODE data types.
From SQL Server 2005 BOL
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6befd9de-9218-4a2c-826e-54f
957ab6b4a.htm):
Storing data in multiple languages within one database is difficult to
manage when you use only character data and code pages. It is also difficult
to find one code page for the database that can store all the required
language-specific characters. Additionally, it is difficult to guarantee the
correct translation of special characters when being read or updated by
different clients running various code pages. Databases that support
international clients should always use Unicode data types instead of
non-Unicode data types.
For example, consider a database of customers in North America that must
handle three major languages:
Spanish names and addresses for Mexico
French names and addresses for Quebec
English names and addresses for the rest of Canada and the United States
When you use only character columns and code pages, you must take care to
make sure the database is installed with a code page that will handle the
characters of all three languages. You must also take care to guarantee the
correct translation of characters from one of the languages when read by
clients running a code page for another language.
With the growth of the Internet, it is even more important to support many
client computers that are running different locales. Selecting a code page
for character data types that will support all the characters required by a
worldwide audience would be difficult.
The easiest way to manage character data in international databases is to
always use the Unicode nchar, nvarchar, and ntext data types, instead of
their non-Unicode equivalents, char, varchar, and text......
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:B5DBA6D6-7559-40CF-A13F-D2E7ADE98789@.microsoft.com...
> Hi ,
> What is the different I use NChar and Char as my database field datatype
> ? In what circumstance I should use NChar or NVarchar ? Kindly advised.
> --
> Travis Tan

nvarchar and different collation comparison

Hi,
I have read some articles on Unicode characters, I understand that they use
as a "big international code page" that host thousands of characters. The
thing I don't understand is why I get an error when I compare two nvarchar
column having a different collation... as this:
I know that COLLATE in the comparison would correct the issue, but I want to
find a way to avoid hard coding COLLATE keywords and avoid to change both
collations of the fields. I though nvarchar was to resolve this. Someone can
explain why it doesn't do that?
Or please leave me a personnal mail or web link... Thanks
CREATE TABLE a
(
LatinAS nvarchar(50) COLLATE Latin1_General_CI_AS,
LatinAI nvarchar(50) COLLATE Latin1_General_CI_AI
)
GO
INSERT INTO a (LatinAS, LatinAI) VALUES ('Eric', 'Eric')
SELECT * FROM a WHERE LatinAS = LatinAI
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
david.parenteau@.compuware.com
TIANVARCHAR allows you to use Unicode characters. There are still differences
in character sets when you move from collation to collation. So, your
options here are pretty limited.
"David Parenteau" <DavidParenteau@.discussions.microsoft.com> wrote in
message news:5031A14F-4D36-47B8-A07F-3ABB5BF019D2@.microsoft.com...
> Hi,
> I have read some articles on Unicode characters, I understand that they
> use
> as a "big international code page" that host thousands of characters. The
> thing I don't understand is why I get an error when I compare two nvarchar
> column having a different collation... as this:
> I know that COLLATE in the comparison would correct the issue, but I want
> to
> find a way to avoid hard coding COLLATE keywords and avoid to change both
> collations of the fields. I though nvarchar was to resolve this. Someone
> can
> explain why it doesn't do that?
> Or please leave me a personnal mail or web link... Thanks
>
> CREATE TABLE a
> (
> LatinAS nvarchar(50) COLLATE Latin1_General_CI_AS,
> LatinAI nvarchar(50) COLLATE Latin1_General_CI_AI
> )
> GO
> INSERT INTO a (LatinAS, LatinAI) VALUES ('Eric', 'Eric')
> SELECT * FROM a WHERE LatinAS = LatinAI
>
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
>
> david.parenteau@.compuware.com
> TIA|||David
CREATE TABLE b
(
LatinAS nvarchar(50) COLLATE Latin1_General_CI_AS,
LatinAI nvarchar(50) COLLATE Latin1_General_CI_AI
)
GO
INSERT INTO b (LatinAS, LatinAI) VALUES ('Eric', 'Eric')
SELECT * FROM b WHERE LatinAS COLLATE Latin1_General_CI_AI = LatinAI
"David Parenteau" <DavidParenteau@.discussions.microsoft.com> wrote in
message news:5031A14F-4D36-47B8-A07F-3ABB5BF019D2@.microsoft.com...
> Hi,
> I have read some articles on Unicode characters, I understand that they
> use
> as a "big international code page" that host thousands of characters. The
> thing I don't understand is why I get an error when I compare two nvarchar
> column having a different collation... as this:
> I know that COLLATE in the comparison would correct the issue, but I want
> to
> find a way to avoid hard coding COLLATE keywords and avoid to change both
> collations of the fields. I though nvarchar was to resolve this. Someone
> can
> explain why it doesn't do that?
> Or please leave me a personnal mail or web link... Thanks
>
> CREATE TABLE a
> (
> LatinAS nvarchar(50) COLLATE Latin1_General_CI_AS,
> LatinAI nvarchar(50) COLLATE Latin1_General_CI_AI
> )
> GO
> INSERT INTO a (LatinAS, LatinAI) VALUES ('Eric', 'Eric')
> SELECT * FROM a WHERE LatinAS = LatinAI
>
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
>
> david.parenteau@.compuware.com
> TIA|||If nvarchar keep the same characters bytes for every characters in a
collation A, does the same character in collation B will be the same bytes
values?
Collation A
Characters A correspond to 265 for example.
In Collation B, does the character A will have the same bits and be 265 or
it could be something else?
Does the sort order and other things (except code page) in collations differ
also for nvarchar columns?
David
"Aaron Bertrand [SQL Server MVP]" wrote:

> NVARCHAR allows you to use Unicode characters. There are still difference
s
> in character sets when you move from collation to collation. So, your
> options here are pretty limited.
>
>
> "David Parenteau" <DavidParenteau@.discussions.microsoft.com> wrote in
> message news:5031A14F-4D36-47B8-A07F-3ABB5BF019D2@.microsoft.com...
>
>

nvarchar = utf-16?

Hi Guys,
Is nvarchar the same as UTF-16? If yes, isn't it true UTF-8 is more popular?
TIATechnically, it's not exactly UTF-16 but you are not far:
http://msdn.microsoft.com/library/d...ataencoding.asp
http://support.microsoft.com/defaul...kb;en-us;322112
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Raymond Du" <rdrd@.yahoo.com> wrote in message
news:OyOrxRRiGHA.3996@.TK2MSFTNGP03.phx.gbl...
> Hi Guys,
> Is nvarchar the same as UTF-16? If yes, isn't it true UTF-8 is more
> popular?
> TIA
>sql