I need help with my SQL Statement

On Jan 21, 5:08 pm, The Mad Ape 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

On Jan 21, 6:58 pm, Jin wrote:
> On Jan 21, 5:08 pm, The Mad Ape 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 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

On Jan 21, 6:58 pm, Jin wrote:
> On Jan 21, 5:08 pm, The Mad Ape 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 Ape
www.tatumba.com

On Jan 21, 7:33 pm, The Mad Ape wrote:
> On Jan 21, 6:58 pm, Jin wrote:
>
>
>
> > On Jan 21, 5:08 pm, The Mad Ape 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

On Jan 21, 11:08 pm, Jin wrote:
> On Jan 21, 7:33 pm, The Mad Ape wrote:
>
>
>
> > On Jan 21, 6:58 pm, Jin wrote:
>
> > > On Jan 21, 5:08 pm, The Mad Ape 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


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

The Mad Ape
www.tatumba.com

On Jan 22, 7:55 am, The Mad Ape wrote:
> On Jan 21, 11:08 pm, Jin wrote:
>
>
>
> > On Jan 21, 7:33 pm, The Mad Ape wrote:
>
> > > On Jan 21, 6:58 pm, Jin wrote:
>
> > > > On Jan 21, 5:08 pm, The Mad Ape 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
>
> The convert method worked fine but the later through a data conversion
> error. Thanks for the help.
>
> 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