Hello

I have a simple query to count in a sales graph and i group by seller

but Strata Bo ask me all primary key in my view and my query is not disctint or grouping.

Can you tell me , how you start your query with count(*) and some group by on Stratraframe
to learn and reproduce this.

thanks
Olivier,

==============================================
Asp.net C# - Strataframe - telerik
==============================================

Hello Edhy

What do you think about this ! i want to build a dynamic query !
and do you think it's secure ?

declare @cName varchar(30);
declare @cPrice varchar(12);
declare @cCondition varchar(max);
declare @MyQuery varchar(max)

set @cName = 'm.dupont';
set @cPrice = '120000';

IF @cName <>''
     set @cCondition = 'name like '+@cName+'%';

IF @cPrice <>''
     set @cCondition = 'price>='+@cPrice;


SET @MyQuery = 'select * from product where ' + @cCondition
EXEC (@MyQuery)

Do you see the way what i need ?

thanks
Olivier

==============================================
Asp.net C# - Strataframe - telerik
==============================================

Hi Olivier,
You have to change the way you are thinking from VFP to a SQL DBA so you can build your queries better in SQL.

I would build your query this way, for testing purpose set the value of the ProductName and ProductPrice parameters then comment them for production.  What makes this query generic in a way that could accept a Product Name or a Product Price is the values of the parameters which are expected as '%' or zero, and in this case since you are using the "LIKE" attribute, then we will test for the string '%' to test for empty, or you can create whatever condition you want to test as empty for the ProductName.

-- spGetProductByPriceOrName
declare @ProductName varchar(30)
declare @ProductPrice DECIMAL

set @ProductName = 'm.dupont'
set @ProductPrice = 120000

SELECT
    p.*
FROM
    Product p
WHERE
    (p.Name LIKE @ProductName OR @ProductName = '%') AND
    (p.Price >= @ProductPrice OR @ProductPrice = 0)

In your VB program you would have a method or function like this:

        Public Sub MyName(ProductName As String, ProductPrice As Decimal)
            Using cmd As New SqlClient.SqlCommand()
                '-- Create the params
                cmd.CommandTimeout = 0
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "dbo.spGetProductByPriceOrName"

                cmd.Parameters.AddWithValue("@ProductName", ProductName.Trim + "%").SqlDbType = SqlDbType.VarChar
                cmd.Parameters.AddWithValue("@ProductPrice", ProductPrice).SqlDbType = SqlDbType.Int

                Me.ExcecuteScalar(cmd)
            End Using 'cmd
        End Sub

Edhy Rijo