Help Please

[trafmore]

Can some tell me what i am doing wrong

SELECT SUM(CAST(Admin As numeric (18,2)))+ SUM(CAST(PaidOT As numeric(18,2))) + SUM(CAST(UnpaidOT As numeric(18,2))) AS Total, IBMNum
FROM dbo.DSPActivitySheet
Where (dt BETWEEN @dt AND @dt2 )And( IBMNum=@IBMNum)
GROUP BY IBMNum

I get " error Converting data type varchar to numeric"

[azimmer]

Can some tell me what i am doing wrong

SELECT SUM(CAST(Admin As numeric (18,2)))+ SUM(CAST(PaidOT As numeric(18,2))) + SUM(CAST(UnpaidOT As numeric(18,2))) AS Total, IBMNum
FROM dbo.DSPActivitySheet
Where (dt BETWEEN @dt AND @dt2 )And( IBMNum=@IBMNum)
GROUP BY IBMNum

I get " error Converting data type varchar to numeric"

It can be at a number of places. Can you please post your table structure and variable declarations?

[trafmore]

It can be at a number of places. Can you please post your table structure and variable declarations?

here is the table



CREATE TABLE dbo.DSPActivitySheet(
Id int PRIMARY KEY CLUSTERED,
dt SMALLDATETIME,
IBMNum real,
Hours_On_Duty Varchar(50),
UnpaidOT Varchar(50),
PaidOT Varchar(50),
Admin Varchar(50),
Court Varchar(50),
INSTR Varchar(50),
STDNT Varchar(50),
COMM_SERV Varchar(50),
VICT_SERV Varchar(50),
Remarks Varchar(50)
);
GO

[ChrisAtWokingham]

Hi,

I have an SQL table which uses a primary key that is an automatically generated 32 bit integer, assigned by SQL when a new record is added to the table. None of the other fields can be guaranteed to be unique. When I add a new record, I need to know what primary key was allocated so that I can refer to the record uniquely, but there seems to be no neat way of finding it out.

I can think of two messy ways to do it as follows:

1) Temporarily put a large random number in one of the fields and search for it.
2) Add a timestamp and search for the last modification in the table.

Any better ideas?

Many thanks, Chris.

[azimmer]

Hi,

I have an SQL table which uses a primary key that is an automatically generated 32 bit integer, assigned by SQL when a new record is added to the table. None of the other fields can be guaranteed to be unique. When I add a new record, I need to know what primary key was allocated so that I can refer to the record uniquely, but there seems to be no neat way of finding it out.

I can think of two messy ways to do it as follows:

1) Temporarily put a large random number in one of the fields and search for it.
2) Add a timestamp and search for the last modification in the table.

Any better ideas?

Many thanks, Chris.

There are several ways. The simplest is that the key increments by one at each insert, so the largest one is the last. Another one is that you can catch insert with an insert trigger.

[azimmer]

here is the table



CREATE TABLE dbo.DSPActivitySheet(
Id int PRIMARY KEY CLUSTERED,
dt SMALLDATETIME,
IBMNum real,
Hours_On_Duty Varchar(50),
UnpaidOT Varchar(50),
PaidOT Varchar(50),
Admin Varchar(50),
Court Varchar(50),
INSTR Varchar(50),
STDNT Varchar(50),
COMM_SERV Varchar(50),
VICT_SERV Varchar(50),
Remarks Varchar(50)
);
GO

A few declarations are still missing. I set up the following and it works fine for me.

declare @dt as datetime, @dt2 as datetime
declare @IBMNum as real
 

Thus I suppose some of your variable declarations are not OK.