problem reading datavalues to an arraylist

I have an SQL command below that works fine

"SELECT sum(Preference) FROM [projectDB].[dbo].[Vote] group by preference;

SqlDataReader

datareader = cmdPres.ExecuteReader();
ArrayList myArr =newArrayList();

when i read the data in the values i get are
10,14,10,17

I want to loop through the reader, then add the values to an arrayList. Then i want to check of the value in the arrayList is greater than 15 but it dosent work. I get an error message when i add
sum(Prefernece) to the arraylist, it will not allow me.

while (dreader.Read())
{
myArr.Add(datareader["sum(Preference)"].ToString());

if(myArr.? > 15)
{
Response.Write("Its greater than 15");
}
}

sorry,

think i posted this into the wrong section...its not really an sql problem.

but if anyone has any ideas they will be appriciated

|||

ArrayList uses index to get the specific position's value. Because you use reader() to get the data, you have to add extra steps to do. Here is the clue

int position = 0;

while (dreader.Read())
{

myArr.Add(datareader["sum(Preference)"].ToString());

if((int)myArr[position] > 15)

{
Response.Write("Its greater than 15");
}

position++;

}

Hope it helps

sql

Problem passing columns as parameter!

Dear friens,

I need to pass a column as a parameter in my query. I did this:

ALTER PROCEDURE [dbo].[GD_SP_GET_UsersByDIR_COD]

@.Direccao nvarchar(11),

@.prmFieldName nvarchar(25),

@.prmFieldValue nvarchar(25)

AS

BEGIN

IF @.prmFieldValue='*' OR @.prmFieldValue=''

BEGIN

SELECT TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

ORDER BY Nome

END

ELSE

BEGIN

DECLARE @.SQL varchar(7000)

SET @.Direccao='CGD-DAS'

SET @.prmFieldName='UserID'

SET @.prmFieldValue='C095122'

SET @.SQL = 'SELECT

TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

AND '+ @.prmFieldName +' =' + @.prmFieldValue + ' '

EXEC @.SQL

END

END

THE COMAND EXECUTE SUCESSFULLY IN QUERY ANALISER, BUT IN ASP.NET 2.0 CLIENT RETURNS THE FOLLOWING ERROR:

Server Error in '/WS_GestaoDesktop' Application.

---

The name 'SELECT
TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,
dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,
dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,
dbo.HARDWARE.NS_ID
FROM dbo.ModeloPC INNER JOIN
dbo.SERVICO INNER JOIN
dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN
dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.H' is not a valid identifier.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The name 'SELECT
TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,
dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,
dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,
dbo.HARDWARE.NS_ID
FROM dbo.ModeloPC INNER JOIN
dbo.SERVICO INNER JOIN
dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN
dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.H' is not a valid identifier.

Source Error:

Line 3269: }

Line 3270: dsHardware.dtGD_SP_GET_UsersByDIR_CODDataTable dataTable = new dsHardware.dtGD_SP_GET_UsersByDIR_CODDataTable();

Line 3271: this.Adapter.Fill(dataTable);

Line 3272: return dataTable;

Line 3273: }

The problem is based here:

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

if you want to pass a static value use

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = ''' + @.Direccao + ''')

if you want to pass a identifier use

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = ' + @.Direccao + ')

HTh, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Dear Friend,I changed as you told, but the error still there! :-(

CREATE PROCEDURE [dbo].[GD_SP_GET_UsersByDIR_COD]

@.Direccao nvarchar(11),

@.prmFieldName nvarchar(25),

@.prmFieldValue nvarchar(25)

AS

BEGIN

IF @.prmFieldValue='*' OR @.prmFieldValue=''

BEGIN

SELECT TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

ORDER BY Nome

END

ELSE

BEGIN

DECLARE @.SQL varchar(7000)

SET @.Direccao='CGD-DAS'

SET @.prmFieldName='UserID'

SET @.prmFieldValue='C095122'

SET @.SQL = 'SELECT

TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName, dbo.HARDWARE.New_Computername,

dbo.ModeloPC.MOD_ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome,

dbo.HARDWARE.Migrada, dbo.HARDWARE.MigraViaChange, dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD ='+ @.Direccao +')

AND '+ @.prmFieldName +' =' + @.prmFieldValue + ' '

EXEC @.SQL

END

END

ERROR:

Msg 203, Level 16, State 2, Procedure GD_SP_GET_UsersByDIR_COD, Line 49

The name 'SELECT

TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.H' is not a valid identifier.

|||

change the following things..

CREATE PROCEDURE [dbo].[GD_SP_GET_UsersByDIR_COD]

@.Direccao nvarchar(11),

@.prmFieldName nvarchar(25),

@.prmFieldValue nvarchar(25)

AS

BEGIN

IF @.prmFieldValue='*' OR @.prmFieldValue=''

BEGIN

SELECT TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

ORDER BY Nome

END

ELSE

BEGIN

DECLARE @.SQL varchar(7000)

SET @.Direccao='CGD-DAS'

SET @.prmFieldName='UserID'

SET @.prmFieldValue='C095122'

SET @.SQL = 'SELECT

TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName, dbo.HARDWARE.New_Computername,

dbo.ModeloPC.MOD_ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome,

dbo.HARDWARE.Migrada, dbo.HARDWARE.MigraViaChange, dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD ='''+ @.Direccao +''')

AND '+ @.prmFieldName +' =''' + @.prmFieldValue + ''' '

EXEC @.SQL

END

END

|||

Manid,

I changed as you told, but the error still there! :-(

Regards.

|||I guess you are either trying to change another procedure than you are executing or you will have to provide the whole snippet of code via mail or something to make it reproducable.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Dear Jens,

I created a more simple query, based in the same problem:

CREATE PROCEDURE TEMP

AS

DECLARE @.SQL varchar(8000)

DECLARE @.prmFieldName varchar(20)

DECLARE @.prmFieldValue varchar(20)

SET @.prmFieldName='UserID'

SET @.prmFieldValue='C095122'

SET @.SQL = 'SELECT New_Computername

FROM HARDWARE

WHERE '+ @.prmFieldName +' =''' + @.prmFieldValue + ''' '

EXEC @.SQL

The logical is the some of other queries, but in this one I can't executed it, because returns the following error:

Msg 2812, Level 16, State 62, Procedure TEMP, Line 15

Could not find stored procedure 'SELECT New_Computername

FROM HARDWARE

WHERE UserID ='C095122' '.

(1 row(s) affected)

If you can put this query with column parameter workink good, my problem probably is resolved for the other queries.

Thanks!

|||Sorry and blame on me for not seeing this, you will have to wriite the exec as follows:

EXEC(@.SQL)

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Dear friends,

I created a new simple query to try to resolve the problem of passing a column as parameter, using sp_executesql, but still return error. :-(

ALTER PROCEDURE TEMP

@.prmFieldName nvarchar(25),

@.prmFieldValue nvarchar(25)

AS

DECLARE @.SQL varchar(8000)

SET @.prmFieldName='UserID'

SET @.prmFieldValue='C095122'

SET @.SQL = N'SELECT New_Computername

FROM HARDWARE

WHERE '+ @.prmFieldName +' =''' + @.prmFieldValue + ''' '

EXECUTE sp_executesql @.SQL;

ERROR:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

Procedure expects parameter '@.statement' of type 'ntext/nchar/nvarchar'.

|||

Dear Friends,

I found the solution for my problem.

I must use EXECTUTE sp_executesql @.SQL in spite of EXEC @.SQL, and I must use nvarchar(4000) in spite of varchar(8000).

The final Result:

ALTER PROCEDURE [dbo].[GD_SP_GET_UsersByDIR_COD]

@.Direccao nvarchar(11),

@.prmFieldName nvarchar(30),

@.prmFieldValue nvarchar(25)

AS

BEGIN

IF @.prmFieldValue='*' OR @.prmFieldValue=''

BEGIN

SELECT TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

ORDER BY Nome

END

ELSE

BEGIN

DECLARE @.SQL nvarchar(4000)

SET @.SQL = N'SELECT

TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD ='''+ @.Direccao +''')

AND '+ @.prmFieldName +' =''' + @.prmFieldValue + ''' '

EXECUTE sp_executesql @.SQL

END

END

THANKS FOR ALL YOUR IMPORTANT SUPPORT!!!

Problem on query operation

Hello, I have my table Produits :

CREATE TABLE [dbo].[Produit] (
[Produit_ID] [int]IDENTITY (1, 1)NOT NULL ,
[Reference] [nvarchar] (50) COLLATE French_CI_ASNULL ,
[Designation] [nvarchar] (50) COLLATE French_CI_ASNULL ,
[Quantite] [int]NULL ,
[PrixU] [sql_variant]NULL ,
[MontantHT] [sql_variant]NULL ,
[TVA] [sql_variant]NULL ,
[Facture_ID] [int]NOT NULL
)
GO
here is stored procedure

 CREATE PROC spBaseTVA_Bis( @.Facture_IDint )AS SELECTSUM(MontantHT)AS montantHT, TVAFROM ProduitGROUP BY TVA, Facture_IDHAVING ( Facture_ID = @.Facture_ID)GOMy stored procedure  fill data in a datagrid with to column  TauxTVA and TVA  like this :
    TauxTVA      TVA
 
But I would like to add a theard column as (the value of the fisrt colum) *  ( the value of the second colum )
    
I would like to desplay data like this  :
    
     TauxTVA      TVA    Prod
       X           Y     X*Y
      
How can I modify my stored procedure to perform this ?
    
Regards
 

Hello,

This one will work with your table definition.

SELECTSUM(convert(int,MontantHT))AS montantHT,convert(int,TVA)as TVA,SUM(convert(int,MontantHT))*convert(int,TVA)as newColFROM

Produit

WHERE

( Facture_ID= @.Facture_ID)

GROUP

BY TVA, Facture_ID

However, I don't think the data types you defined here are approprite. In stead of sql_variant, you can redefine them to something in your case, such as int, float... for your calculations later on.

|||

Thank you !

This work very well;

But it possible to perform this :

TauxTVA TVA Prod
X Y X*Y

Z T Z*T

And in another column X +Z AND X*Y + Z*T

|||

You can use the previous result as a derived table and run a sum on top of it like this:

SELECT

SUM(a.montantHT)as Sum_montantHT,SUM(a. newCol)as new_SumFROM(SELECTSUM(convert(int,MontantHT))AS montantHT,convert(int,TVA)as TVA,SUM(convert(int,MontantHT))*convert(int,TVA)as newColFROM

Produit

WHERE

(

Facture_ID= 1)

GROUP

BY

TVA, Facture_ID)AS a

Problem on creating stored procedure

The code is as below:

--Drop procedures if they exsit
if exists (SELECT * FROM master.dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_PagedItems]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_PagedItems]
GO

if exists (SELECT * FROM master.dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_PagedItemsByTime]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_PagedItemsByTime]
GO

if exists (SELECT * FROM master.dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_selectedEventMessage]') AND OBJECTPROPERTY(id,N'IsProcedure')=1)
drop procedure [dbo].[sp_selectedEventMessage]
GO

--Definitions of procedures
USE LanDeskDB
GO

CREATE PROCEDURE sp_PagedItems
(
@.QueryVARCHAR(1000),
@.Pageint,
@.RecsPerPageint,
@.startDateVARCHAR(100),
@.endDateVARCHAR(100),
@.allTimeint,
@.flagint
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(2000)
DECLARE @.Order VARCHAR(200)
DECLARE @.TotalBIGINT

CREATE TABLE #TempTable
(
TempTableID BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1),
EventLogID BIGINT ,
EventDateTime datetime,
MachineID INT ,
TypeID INT ,
SessionID INT ,
SourceID INT ,
MessageID BIGINT ,
UserID INT,
CategoryNumber INT,
EventID INT
)
IF (@.flag = 1)
BEGIN
SET @.Order = 'ORDER BY EventDateTime'
END
IF (@.flag = 2)
BEGIN
SET @.Order = 'ORDER BY SessionID,EventDateTime'
END
IF (@.flag = 3)
BEGIN
SET @.Order = 'ORDER BY TypeID,EventDateTime'
END
IF (@.flag = 4)
BEGIN
SET @.Order = 'ORDER BY CategoryNumber,EventDateTime'
END
IF(@.allTime <> 1)
BEGIN
IF(LEN(@.Query)>1)
BEGIN
SET @.Query = @.Query+'AND EventDateTime>='''+@.startDate+''' AND EventDateTime <= '''+@.endDate+''''
END
ELSE
BEGIN
SET @.Query = 'WHERE EventDateTime>='''+@.startDate+''' AND EventDateTime <= '''+@.endDate+''''
END
END
SET @.SQL=
'INSERT INTO #TempTable (EventLogID,EventDateTime,MachineID,TypeID,SessionID,SourceID,MessageID,UserID,CategoryNumber,EventID)'+
'SELECT EventlogID,EventDateTime,MachineID,TypeID,SessionID,SourceID,MessageID,UserID,CategoryNumber,EventID FROM EventLog '+
@.Query+' '+@.Order
EXEC (@.SQL)

CREATE TABLE #TempTableTwo
(
TempTableTwoID BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1),
TempTableID BIGINT,
EventLogID BIGINT ,
EventDateTime datetime,
MachineID INT ,
TypeID INT ,
SessionID INT ,
SourceID INT ,
MessageID BIGINT ,
UserID INT,
CategoryNumber INT,
EventID INT,
)

DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
SELECT @.LastRec = @.Page * @.RecsPerPage+1

INSERT #TempTableTwo

SELECT * FROM #TempTable T
WHERE T.TempTableID >@.FirstRec AND T.TempTableID < @.LastRec

SELECT TempT.EventLogID AS EventLogID,TempT.EventDateTime AS EventDateTime,
Ma.MachineName AS MachineName,Se.SessionName AS SessionName,Ty.TypeName AS TypeName,
TempT.CategoryNumber AS CategoryNumber,Us.UserName AS UserName,So.SourceName AS SourceName,
TempT.EventID AS EventID
FROM #TempTableTwo TempT,Machines Ma,Types Ty,Sessions Se,Sources So,Users Us
WHERE TempT.MachineID = Ma.MachineID AND TempT.TypeID = Ty.TypeID AND TempT.SessionID = Se.SessionID
AND TempT.SourceID = So.SourceID AND TempT.UserID = Us.UserID

SELECT COUNT(*) FROM #TempTable

DROP TABLE #TempTable
DROP TABLE #TempTableTwo
SET NOCOUNT OFF
END
GO

CREATE PROCEDURE sp_PagedItemsByTime
(
@.QueryVARCHAR(1000),
@.Page int,
@.RecsPerPage int,
@.startDateVARCHAR(100),
@.endDateVARCHAR(100),
@.allTime int
)
AS
BEGIN
EXEC sp_PagedItems @.Query,@.Page,@.RecsPerPage,@.startDate,@.endDate,@.allTime,1
END
GO

CREATE PROCEDURE sp_selectedEventMessage
(
@.EventLogID int
)
AS

BEGIN

SELECT Ma.MachineName,Ev.EventDateTime,Se.SessionName,Ty.TypeName,So.SourceName,Me.MessageDescription
FROM EventLog Ev,Sessions Se,Types Ty,Sources So,Messages Me,Machines Ma
WHERE Ev.EventLogID = @.EventLogID AND Ev.SessionID = Se.SessionID AND Ma.MachineID = Ev.MachineID
AND Ev.TypeID = Ty.TypeID AND Ev.SourceID = So.SourceID AND Ev.MessageID = Me.MessageID

END

GO

I got the error messge as
Server: Msg 2714, Level 16, State 5, Procedure sp_PagedItems, Line 107
There is already an object named 'sp_PagedItems' in the database.
Server: Msg 2714, Level 16, State 5, Procedure sp_PagedItemsByTime, Line 13
There is already an object named 'sp_PagedItemsByTime' in the database.
Server: Msg 2714, Level 16, State 5, Procedure sp_selectedEventMessage, Line 12
There is already an object named 'sp_selectedEventMessage' in the database.

But I already delete those procedures before I create them. Could anyone give some suggestion?Change this master.dbo.sysobjects to LanDeskDB.dbo.sysobjects in your 3 EXISTS statements in the top of your code and it will work fine for you.

Terri|||Thank you very much. It works now.