Re: I need help with my SQL Statement

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Jan 21, 7:33 pm, The Mad Ape <chief...@xxxxxxxxx> wrote:
On Jan 21, 6:58 pm, Jin <jinsooch...@xxxxxxxxx> wrote:



On Jan 21, 5:08 pm, The Mad Ape <chief...@xxxxxxxxx> wrote:

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 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

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 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
.

Relevant Pages

  • Re: copyFromRecordset - vs - getRows
    ... SQL Server, Access, other? ... efficient to have the db convert the string to numeric - that way, ... recordset field will be numeric, and Excel should treat the corresponding ... > retrieved data from strings to numerics (really - just trimming the ...
    (microsoft.public.excel.programming)
  • Re: I need help with my SQL Statement
    ... You could use the CONVERT function to convert any of the character ... types to numerics. ... See the documentation that should have been installed with the SQL ... The convert method worked fine but the later through a data conversion ...
    (microsoft.public.sqlserver.ce)
  • Re: IS*ALPHA*()
    ... This doesn't help me identify that is isn't all alpha either. ... Numerics also varies. ... Links for SQL Server Books Online: ...
    (comp.databases.ms-sqlserver)
  • Re: SQL Problem
    ... As far as I know, that's the required syntax for SQL and text fields - for numerics, you don't need the single quotes if they're going into numeric fields. ...
    (microsoft.public.access.formscoding)
  • Re: data provider
    ... Yes it gets intalled with one of the SQL Compact msi files, but I'm not sure which one, and I don't remember what the name of the DLL is since that's not it. ... When I talk about a sdf file in my mind its a SQL Server Compact edition database. ... I have used SSCE2.0 in the past and all databases were sdf files.I think I am confusing everyone because I was told that my customer needs to have a instance of sql server installed to be able to let my foxpro app access the SSCE database and since express is free and can open sdf files I was thinking this is perfect, but it sounds that I was misinformed. ...
    (microsoft.public.sqlserver.ce)