Return a 0 instead of null

I need to verify if I have partial sales of certain items. I request data
from the server and I am getting NULL.
select sum(matrixamount) matrix
from sales
where invid =@.MerchID
and assettype = 2
If there are NO transactions in Sales for invid = @.MerchID I'd love to
return a 0.
I have tried :
CREATE FUNCTION dbo.GetMatrixTotal
( @.iid int )
RETURNS int AS
BEGIN
declare @.ret int
select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
end ) from sales
where invid = @.iid and assettype = 2
return @.ret
END
Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
But how do I flip it to 0 so there is a return back to a data container in
.NET
TIA
__StephenTry
select ISNULL(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID
and assettype = 2
Mike A.
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:OvYQa73aFHA.1148@.tk2msftngp13.phx.gbl...
>I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> .NET
> TIA
> __Stephen
>
>|||Try,
select isnull(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID and assettype = 2
AMB
"Stephen Russell" wrote:

> I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> ..NET
> TIA
> __Stephen
>
>

Return a 0 instead of null

I need to verify if I have partial sales of certain items. I request data
from the server and I am getting NULL.
select sum(matrixamount) matrix
from sales
where invid =@.MerchID
and assettype = 2
If there are NO transactions in Sales for invid = @.MerchID I'd love to
return a 0.
I have tried :
CREATE FUNCTION dbo.GetMatrixTotal
( @.iid int )
RETURNS int AS
BEGIN
declare @.ret int
select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
end ) from sales
where invid = @.iid and assettype = 2
return @.ret
END
Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
But how do I flip it to 0 so there is a return back to a data container in
.NET
TIA
__StephenTry
select ISNULL(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID
and assettype = 2
Mike A.
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:OvYQa73aFHA.1148@.tk2msftngp13.phx.gbl...
>I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> .NET
> TIA
> __Stephen
>
>|||Try,
select isnull(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID and assettype = 2
AMB
"Stephen Russell" wrote:
> I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> ..NET
> TIA
> __Stephen
>
>

Return a 0 instead of null

I need to verify if I have partial sales of certain items. I request data
from the server and I am getting NULL.
select sum(matrixamount) matrix
from sales
where invid =@.MerchID
and assettype = 2
If there are NO transactions in Sales for invid = @.MerchID I'd love to
return a 0.
I have tried :
CREATE FUNCTION dbo.GetMatrixTotal
( @.iid int )
RETURNS int AS
BEGIN
declare @.ret int
select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
end ) from sales
where invid = @.iid and assettype = 2
return @.ret
END
Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
But how do I flip it to 0 so there is a return back to a data container in
..NET
TIA
__Stephen
Try
select ISNULL(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID
and assettype = 2
Mike A.
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:OvYQa73aFHA.1148@.tk2msftngp13.phx.gbl...
>I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> .NET
> TIA
> __Stephen
>
>
|||Try,
select isnull(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID and assettype = 2
AMB
"Stephen Russell" wrote:

> I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> ..NET
> TIA
> __Stephen
>
>

Retuning rows as a single string

Is there any way to do this?
Say I have SELECT MyCol FROM MyTable
MyCol is a nvarchar field, but instead of coming back like this in a table
ItemA
ItemB
ItemC
I want it to come back as a stingle string like this
ItemA, ItemB, ItemC
is this possible in T-SQL? thanks!http://www.aspfaq.com/show.asp?id=2529

> Is there any way to do this?
> Say I have SELECT MyCol FROM MyTable
> MyCol is a nvarchar field, but instead of coming back like this in a table
> ItemA
> ItemB
> ItemC
> I want it to come back as a stingle string like this
> ItemA, ItemB, ItemC|||Try this:
Declare @.Results varchar(2000)
SELECT @.Results =
COALESCE(@.Results + ', ', '') + CAST(MyCol AS
varchar(3))
FROM
MyTable
Select @.Results
HTH
Barry|||Sorry that should read:
Declare @.Results nvarchar(2000)
SELECT @.Results =
COALESCE(@.Results + ', ', '') + MyCol
FROM
MyTable
Select @.Results|||didn't know about coalesce, thanks!
"Barry" <barry.oconnor@.manx.net> wrote in message
news:1145990261.449558.38970@.y43g2000cwc.googlegroups.com...
> Sorry that should read:
> Declare @.Results nvarchar(2000)
>
> SELECT @.Results =
> COALESCE(@.Results + ', ', '') + MyCol
> FROM
> MyTable
>
> Select @.Results
>

Retrieving the autoincremented primary key value

I am using several TextBox Controls, instead of a FormView, for inserting data into a Sql Database. The primary key (ID) is an integer which is automatically incremented by one at each insertion.
At each insertion I need to retrieve the ID value of the newly inserted record.
I have followed a suggestion from a help sample with this code:

Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Data
Partial Class Insert
Inherits System.Web.UI.Page

Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LinkButton1.Click

SqlDataSource1.Insert()
End Sub

Sub On_Inserting(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)
Dim InsertedKey As SqlParameter
InsertedKey = New SqlParameter("@.PK_GuestList", SqlDbType.Int)
InsertedKey.Direction = ParameterDirection.Output
e.Command.Parameters.Add(InsertedKey)
End Sub

Sub On_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)
Dim command As DbCommand
command = e.Command
Label1.Text = command.Parameters("@.PK_GuestList").Value.ToString()
End Sub

End Class

No output appears on the Label1.
If in the last code row I replace "@.PK_GuestList" with the name of any TextBox used for inputting data, its content is correctly shown in Label1.

Where is the problem?

You need to use Select Scope_Identity() in your SQL to get the ID that was just created. Without seeing your SQL, it's impossible to tell if you are doing this correctly.|||

Thanks Mike.

This is my Sql:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"DeleteCommand="DELETE FROM [GuestList] WHERE [ID] = @.ID"InsertCommand="INSERT INTO [GuestList] ( [IDD], [UserName],, [Title], [Message], [Reason],

[Accento], [Views], [Posts], [Quando], [LastPost]) VALUES ( @.IDD, @.UserName, @.Email, @.Title,

@.Message, @.Reason, @.Accento, @.Views, @.Posts, @.Quando, @.LastPost)"

OnInserting="On_Inserting"OnInserted="On_Inserted"SelectCommand="SELECT * FROM [GuestList]"UpdateCommand="UPDATE [GuestList] SET [IDD] = @.IDD, [UserName] = @.UserName, = @.Email, [Title] = @.Title, [Message] = @.Message, [Reason] = @.Reason, [Accento] = @.Accento, [Views] = @.Views, [Posts] = @.Posts, [Quando] = @.Quando, [LastPost] = @.LastPost WHERE [ID] = @.ID">

<DeleteParameters><asp:ParameterName="ID"Type="Int32"/></DeleteParameters><UpdateParameters><asp:ParameterName="IDD"Type="Int32"/><asp:ParameterName="UserName"Type="String"/><asp:ParameterName="Email"Type="String"/><asp:ParameterName="Title"Type="String"/><asp:ParameterName="Message"Type="String"/><asp:ParameterName="Reason"Type="String"/><asp:ParameterName="Accento"Type="String"/><asp:ParameterName="Views"Type="String"/><asp:ParameterName="Posts"Type="String"/><asp:ParameterName="Quando"Type="DateTime"/><asp:ParameterName="LastPost"Type="String"/><asp:ParameterName="ID"Type="Int32"/></UpdateParameters><InsertParameters><asp:ParameterName="ID"Type="Int32"/><asp:formparametername="IDD"formfield="IDD"/><asp:formparametername="UserName"formfield="UserName"/><asp:formparametername="Email"formfield="Email"/><asp:formparametername="Title"formfield="Title"/><asp:formparametername="Message"formfield="Message"/><asp:formparametername="Reason"formfield="Reason"/><asp:formparametername="Accento"formfield="Accento"/><asp:formparametername="Views"formfield="Views"/><asp:formparametername="Posts"formfield="Posts"/><asp:formparametername="Quando"formfield="Quando"/><asp:formparametername="LastPost"formfield="LastPost"/>

</InsertParameters>|||May be I missunderstood. I have written no SQL code.|||

nodar10:

May be I missunderstood. I have written no SQL code.

Yes you have, on both counts. The InsertCommand, SelectCommand etc are SQL code. Have a look at this to see if you can work out how to get the value of Scope_Identity() in a stored procedure rather than with the SqlDataSource:http://www.eggheadcafe.com/forumpost.aspx?topicid=1&forumpostid=68123

|||

Hi Mike!

You were right with the suggestion for SCOPE_IDENTITY.

I have solved the problem by simply adding ;SELECT @.PK_GuestList = SCOPE_IDENTITY() at the end of the InsertCommand SQL statement , which becomes:

InsertCommand

="INSERT INTO [GuestList] ( [IDD], [UserName],, [Title], [Message], [Reason],

[Accento], [Views], [Posts], [Quando], [LastPost]) VALUES ( @.IDD, @.UserName, @.Email, @.Title,

@.Message, @.Reason, @.Accento, @.Views, @.Posts, @.Quando, @.LastPost);

SELECT @.PK_GuestList= SCOPE_IDENTITY()"

PK_GuestList is an auxiliary variable present in both .aspx and .aspx.vb files, and can be assume any name, but not ID.

Thank you very mauch for your help.

Federico

|||

nodar10:

Hi Mike!

You were right with the suggestion for SCOPE_IDENTITY.

....

Thank you very mauch for your help.

Federico

Then I don't understand why you unmarked my post as answer.

|||

Hi Mike

Sorry for doing some action inadvertely. I just tried to set the flag that my question has been properly answered.

Federico