error message when creating a datatable

[Guest]

Hi Folk

Im trying to create a datatable but i keep encountering the following error message

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dl
Additional information: System error

does anyone know what im doin wrong
The problem seems to go away when i remove the following portion of code from the select statement
Sum(c.Cost) AS Tot_Cara_Charge
I cant however see what is so wrong with this!!!
I have used this in another datatable and it works fine!

'create the Caravan_Booking datatabl
daClient = New SqlDataAdapter("SELECT a.BookingNo, a.Caravan_Inv_No, b.Caravan_Model,
b.Length, c.Cost, Sum(c.Cost) AS Tot_Cara_Charge FROM Caravan_Booking a,
Caravan_Inv b, Caravan_Details c WHERE a.Caravan_Inv_No = b.Caravan_Inv_No AND
b.Caravan_Model = c.Caravan_Model", Cn

daClient.FillSchema(dsFullBooking, SchemaType.Mapped, "dtCaraBooking"

Can anyone plz offer any assistance

 

[Rajesh Patel]

well, you haven't put group by clause. it should be

group by a.BookingNo, a.Caravan_Inv_No, b.Caravan_Model, b.Length, c.Cost

Hope this helps

Rajesh Patel

 

[Guest]

You could add the group by clause, but if you want the grand total to show instead of all the matching rows totalled, you need to append it to the results returned by running a separate query

ie

Make a stored proc
create procedure dbo.blabl

a

DECLARE @SumCharges in

SELECT @SumCharges = Sum(c.Cost)
FROM Caravan_Booking a,
Caravan_Inv b, Caravan_Details c
WHERE a.Caravan_Inv_No = b.Caravan_Inv_No AND
b.Caravan_Model = c.Caravan_Mode


SELECT @SumCharges AS Tot_Cara_Charge, a.BookingNo, a.Caravan_Inv_No, b.Caravan_Model,
b.Length, c.Cos
FROM Caravan_Booking a,
Caravan_Inv b, Caravan_Details c
WHERE a.Caravan_Inv_No = b.Caravan_Inv_No AND
b.Caravan_Model = c.Caravan_Model