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 = 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

Nulls when summing a group in an expression

Hi guys,

I know this has been discussed before, but not in a way that solves this particular problem. I have some data in a table that is grouped by rows, but when i sum the fields in the group i am getting an error because one of the grouped rows contains a null.

Now when i have a one to one relationship between a dataset field and a table cell, checking for a null is easy. But how do you do it when you have several fields that are being summed for that table cell?

In this particular case, i have a work around - there are only two data rows per row group (paid revenue, and unpaid revenue), so i can check them individually for nulls by using First(Fields!Revenue.Value) and Last(Fields!Revenue.Value). This is the expression i used:

=IIf(

Not IsNothing( Fields!Revenue.Value) and isnumeric(Fields!Revenue.Value),

FormatCurrency(

IIf(

Not IsNothing( First(Fields!Revenue.Value)),

First(Fields!Revenue.Value),

0

)

+

IIf(

Not IsNothing( Last(Fields!Revenue.Value)),

Last(Fields!Revenue.Value),

0

),

0,

true,

false,

true

),

"-"

)

The first expression in the outer IIf is worthless, because using Fields!Revenue.Value only checks the first row in the group, and in my case it is always the second row that contains the null.

What is the best way to account for the null values?* It seems i can't access the .Value field like an array (i.e. Fields!Revenue.Value(1)). Should i be passing Fields!Revenue.Value through to a piece of custom code to sum it?

Thanks!

sluggy

*The data i am working with is sourced from a cube with an mdx query. The query uses a COALESCEEMPTY(<measure>, 0) function, but it seems the data provider being used is changing the fields that were coalesced back into nulls. Which is ok - at least i am getting the rows, prior to using the coalesce the rows were being dropped. I'd rather have nulls than have the row missing from the dataset.

Not sure if I understand your question completely, but you can use IIF inside of an aggregate function if you want to skip null values. For example, =Sum(IIF(IsNothing(Fields!Revenue.Value), 0, Fields!Revenue.Value)).

|||

Hi

can you try this out and check whether it is working or not.

=IIf(

Not IsNothing( Fields!Revenue.Value) and isnumeric(Fields!Revenue.Value),

FormatCurrency(

IIf(

Not IsNothing( Cint(First(Fields!Revenue.Value))),

Cint(First(Fields!Revenue.Value)),

0

)

+

IIf(

Not IsNothing( Cint(Last(Fields!Revenue.Value))),

Cint(Last(Fields!Revenue.Value)),

0

),

0,

true,

false,

true

),

"-"

)

Thanks,

Srinivas Reddy.

|||

Srinivas, Fang,

thanks for your answers, but neither work. In this case casting the fields has no effect, as they are already being returned as ints, and you can't typecast a field that is already null.

I have noticed something further, and consequently some of my original information was incorrect. What i have noticed is that nulls are NOT being returned - zeros are. So if i code this expression:

=First(Fields!Revenue.Value) + Last(Fields!Revenue.Value)

it gets evaluated no problem - i get the revenue from the first row plus zero from the second. But if i do any sort of aggregation type function on it, like Sum or Avg etc, then i get an error.Can anyone explain what is happening here, or suggest how i can determine exactly what error is occurring?

Thanks,

sluggy

|||

If you are designing and previewing the report in the report designer, you can check the error message in the task bar.

What's the data type of the Revenue field? One possible reason could be you are summing mixed types. Aggregate functions require all the values passed in are of the same type.

|||

Thanks Fang, i will check into that.

sluggy

Null result returned even though IS NOT NULL specified.

Hi guys,

I've got a query on a particular table returning an odd result:

SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStation

This query returns the rows I'd expect plus a null row. This doesn't happen in databases at other sites, or in other tables at this site. The following query behaves as I'd expect returning only non-null AccountNumbers.

SELECT DISTINCT AccountNumber
FROM Suppliers
WHERE AccountNumber Is Not Null
ORDER BY AccountNumber

I can't reproduce these results on another site on a table of the same structure, or on another table at this site.

Any suggestions as to what might be going on?

Pertinent info:
--
select @.@.Version

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
dbcc checkdb
Abridged result:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'POS'.
--
SELECT * INTO #Inv FROM Invoice

SELECT DISTINCT WorkStation
FROM #Inv
WHERE WorkStation Is Not Null
ORDER BY WorkStation

Does not reproduce this problem (and so is a probable fix) but the questions remains, what causes this?

TIA,
Karl.are you sure it's null, and not perhaps an empty string instead? try this --SELECT sum(case when WorkStation is null
then 1 end) as nulls
, sum(case when WorkStation = ''
then 1 end) as empties
, sum(case when WorkStation > ''
then 1 end) as somethings
, count(*) as total_rows
FROM Invoice and let's see what kind of totals you get|||Hi Rudy,

Thanks for your reply

are you sure it's null, and not perhaps an empty string instead? try this --SELECT sum(case when WorkStation is null
then 1 end) as nulls
, sum(case when WorkStation = ''
then 1 end) as empties
, sum(case when WorkStation > ''
then 1 end) as somethings
, count(*) as total_rows
FROM Invoice and let's see what kind of totals you get

Returns:
nulls empties somethings total_rows
---- ---- ---- ----
25212 NULL 2660565 2685777

(1 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.

It would seem that the nulls are slipping into somewhere they shouldn't be for you too :)|||It would seem that the nulls are slipping into somewhere they shouldn't be for you too :)no, what makes you say that?

25212 + 0 + 2660565 = 2685777

so everything is accounted for

you said earlier that this query returns a NULL row --SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStationmay i ask how do you know it does this? where are you running the query, and how do you detect the NULL?|||no, what makes you say that?

An incorrect assumption on my part. Moving right along :)

you said earlier that this query returns a NULL row --SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStationmay i ask how do you know it does this? where are you running the query, and how do you detect the NULL?

This error was reported by the client who uses this database (the app they use uses ADO) but I've been using Query Analyzer to see it.

My method of null detection and confirmation is twofold:
- Occular examination (it's the first record and there's only ~20 rows)
- SELECT WorkStation
FROM Invoice
WHERE WorkStation = 'NULL' returns no rows.

-Karl.|||of course WHERE WorkStation = 'NULL' returns 0 rows

the correct syntax is WHERE WorkStation IS NULL|||Thanks for your time Rudy,

of course WHERE WorkStation = 'NULL' returns 0 rows

the correct syntax is WHERE WorkStation IS NULL

My intention for that second query is to show the 'NULL' result from the first query was in fact a null, and not a string containing the phrase 'NULL' as the two appear identical in the result pane.|||I know this has been somewhat covered, but just for gits and shiggles, what happens if you do this?

SELECT DISTINCT WorkStation
FROM Invoice
WHERE IsNull(WorkStation, '') <> ''
ORDER BY WorkStation|||Thanks for your reply Teddy,

That query returns the results I hoped that I'd get from the original one. It returns a list of workstations and contains no blank, or null, records.

I think I'm just going to:
-select the data into a temp table
-drop the original table
-select the data the original table
as I believe this will cease the behaviour, but it does mean we never find out why it occurred...|||If the query I posted returns your intended results, I would highly suggest you make sure you don't have empty strings in your workstation column. What that clause does is first convert null values to an empty string, and then filter by anything that doesn't have an empty string. I would be curious if you had some fields with just spaces in them as well, as there is an environmental setting that would automagically trim those spaces to ''.

Example:

-- Create a scratch table
CREATE TABLE #NullVsEmpty (test_column char(1))

-- Dump two rows into scratch table. One is empty, one is null
INSERT INTO #NullVsEmpty VALUES ('')
INSERT INTO #NullVsEmpty VALUES (NULL)

-- Display whats in scratch table for reference
select * from #NullVsEmpty

-- Count where test_column is not null
SELECT COUNT(*) FROM #NullVsEmpty WHERE test_column IS NOT NULL

-- Count where test_column is not EQUAL TO an empty string.
-- Note this returns 0 because Null cannot be directly compared to a scalar value
SELECT COUNT(*) FROM #NullVsEmpty WHERE test_column <> ''

-- Convert test_column to a finite value and then do comparison
SELECT COUNT(*) FROM #NullVsEmpty WHERE ISNULL(test_column, '') <> ''

DROP TABLE #NullVsEmpty

Null Left Join

Hi guys,
I need to get data from a join between two tables, but I just want the data that exists in first table and not in second one. It's like:

SELECT t1.*
FROM t1
LEFT JOIN t2 ON t1.col1 = t2.col1
WHERE t2.col1 IS NULL

I'm using the merge join transformation, but I can't find no options to put the WHERE code in the join. How can I do that? Anyone can help me?

Cheers!Just use the Left Outer Join option under Join Type. That will output all rows from the left input, with any matching rows from the right input. The where clause is not necessary.|||

jwelch wrote:

Just use the Left Outer Join option under Join Type. That will output all rows from the left input, with any matching rows from the right input. The where clause is not necessary.

And then add a conditional split to kick out the rows that are not null from the second source (the right side of the merge join) so as to get a resultset of rows from the left that don't exist in the right.

John's approach is just part of the answer if you want to show only records that don't exist in the right side.|||Phil is correct. Too much caffeine today, or not enough, I don't know which |||

Phil Brammer wrote:

And then add a conditional split to kick out the rows that are not null from the second source (the right side of the merge join) so as to get a resultset of rows from the left that don't exist in the right.

John's approach is just part of the answer if you want to show only records that don't exist in the right side.

I can't believe I can't see it. Man, you're good. Thanks a lot!