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