I need help with my SQL Statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

The Mad Ape - 21 Jan 2008 22:08 GMT

Hi I am using sqlce on my desktop and trying to write a query that
sums two fields. I am an Access guy and built this query there. When I
tried to use it in my application it crashes

Dim sqlStands As New SqlCeCommand("SELECT si_stand, Sum(si_size) AS
TotSz, Sum(si_samplelength) AS TotLen FROM(tblStandInfo) GROUP BY
si_stand ORDER BY si_stand", MyConn)

Error reads as follows:

SQLCe Exception was unhandled

There was an error parsing the query. [ Token line number = 1,Token
line offset = 89,Token in error = ) ]

I do not see the error. Please help.

Thanks

The Mad Ape
www.tatumba.com

Reply to this Message

Jin - 21 Jan 2008 22:58 GMT

> Hi I am using sqlce on my desktop and trying to write a query that
> sums two fields. I am an Access guy and built this query there. When I
[quoted text clipped - 16 lines]
>
> The Mad Apewww.tatumba.com

I think removing the parenthesis from the source table name should do
the trick. You can use the [] to enclose the name, but not ().

- Jin

Reply to this Message

The Mad Ape - 21 Jan 2008 23:59 GMT

> > Hi I am using sqlce on my desktop and trying to write a query that
> > sums two fields. I am an Access guy and built this query there. When I
[quoted text clipped - 21 lines]
>
> - Jin

Hi Jin

I removed the parenthesis and am now getting a new error:

I did what you said and now I am getting a new error:

The specified argument value for the function is not valid. [ Argument
# = 1,Name of function(if known) = Sum ]

Any ideas?

Thanks

The Mad Ape
www.tatumba.com

Reply to this Message

The Mad Ape - 22 Jan 2008 00:33 GMT

> > Hi I am using sqlce on my desktop and trying to write a query that
> > sums two fields. I am an Access guy and built this query there. When I
[quoted text clipped - 21 lines]
>
> - Jin

Hi again Jin

I think I may have discovered the problem. The fields that need summed
are character, not numeric, even though all values in the table are
numeric in nature.

I tried: Dim sqlStands As New SqlCeCommand("SELECT si_stand,
Sum(CDbl([si_size])) AS TotSz, Sum(CDbl([si_samplelength])) AS TotLen
FROM tblStandInfo GROUP BY si_stand ORDER BY si_stand", MyConn)

Although it works in Access it does not work in MS SQL. I will have to
look at the table creation and redo it unless you know of a way to
converting string to double numeric on the fly within MS SQL.

The error I am getting currently is:

The function is not recognized by SQL Server Compact Edition. [ Name
of function = CDbl,Data type (if known) = ]

The Mad Ape
www.tatumba.com

Reply to this Message

Jin - 22 Jan 2008 03:08 GMT

> > > Hi I am using sqlce on my desktop and trying to write a query that
> > > sums two fields. I am an Access guy and built this query there. When I
[quoted text clipped - 42 lines]
>
> The Mad Apewww.tatumba.com

You could use the CONVERT function to convert any of the character
types (i.e. nvarchar) to numerics.
For example:

SELECT SUM(CONVERT(FLOAT, FIELD_NAME)) AS ALIAS_NAME FROM
SOURCE_TABLE;

or you can force an implicit conversion as in

SELECT SUM(1 * FIELD_NAME) AS ALIAS_NAME FROM SOURCE_TABLE;

See the documentation that should have been installed with the SQL
server.

- Jin

Reply to this Message

The Mad Ape - 22 Jan 2008 12:55 GMT

> > > > Hi I am using sqlce on my desktop and trying to write a query that
> > > > sums two fields. I am an Access guy and built this query there. When I
[quoted text clipped - 58 lines]
>
> - Jin

The convert method worked fine but the later through a data conversion
error. Thanks for the help.

The Mad Ape
www.tatumba.com

Reply to this Message

Jin - 22 Jan 2008 21:14 GMT

> > > > > Hi I am using sqlce on my desktop and trying to write a query that
> > > > > sums two fields. I am an Access guy and built this query there. When I
[quoted text clipped - 63 lines]
>
> The Mad Apewww.tatumba.com

If it threw a data conversion error, then you most likely had a null
value in the text field.
I'm sure you can figure that one out by yourself.

- Jin

Reply to this Message