SQL Query string issue

MumbleB

Hi Guys, I am having troyble with a SQL query. When I run the same query in SQL Server management studio it works fine. When I try and pass it through C# string I get an error. I know it has to do with the sum(HoursWorked) portion of the query. What I want to do is the following. This is from SQL Server management studio:

SELECT TSPRojectCodeID, EmployeeID, sum(HoursWorked) AS "Total Hours"
FROM tblTimesheetDetails
WHERE TimeSheetDate >= '2010/07/01' AND TimeSheetDate <= '2010/07/30'
GROUP BY TSProjectCodeID, EmployeeID;

When I try and pass the same query in C# i get and error that reads as follows:"The Select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect." The code used is as follows:

    public string processSQL()
    {
        string sql = null;
        string inSql = null;
        string firstPart = null;
        string lastPart = null;
        int selectStart = 0;
        int fromStart = 0;
        string\[\] fields = null;
        string\[\] sep = { "," };
        int i = 0;
        TextObject MyText;
        string startdate = fromDate.Value.ToString("yyyy/MM/dd");
        string todate = toDate.Value.ToString("yyyy/MM/dd");
        //inSql = @"Select EmployeeID,TSProjectCodeID,TaskCode,TimeSheetDate, from tblTimesheetDetails WHERE TimeSheetDate >= '" + startdate + "' AND TimeSheetDate <= '" + todate + "' AND TSProjectCodeID = '" + cmbprojects.Text.ToString() + "'";//ORDER BY TSProjectCode";
        inSql = @" Select DISTINCT TSProjectCodeID, EmployeeID, SUM(HoursWorked) AS 'Total\_Hours', FROM tblTimesheetDetails WHERE TimeSheetDate >= '" + startdate + "' AND TimeSheetDate <= '" + todate + "' AND TSProjectCodeID = '" + cmbprojects.Text.ToString() + "'ORDER BY TSProjectCodeID, EmployeeID, HoursWorked";
        //inSql = textBox1.Text;
        inSql = inSql.ToUpper();

        selectStart = inSql.IndexOf("SELECT");
        fromStart = inSql.IndexOf("FROM");
        selectStart = selectStart + 15;
        firstPart = inSql.Substring(selectStart, (fromStart - selectStart));
        lastPart = inSql.Substring(fromStart, inSql.Length - fromStart);

        fields = firstPart.Split(',');
        firstPart = "";
        for (i = 0; i <= fields.Length - 1; i++)
        {
            if (i > 0)
            {
                firstPart = firstPar

Lost User

Might not be whats' bugging you, but there's a comma before the "from" keyword;

TimeSheetDate, from tblTimesheetDetails

I are Troll :suss:

PIEBALDconsult

And please use a parameterized statement.

MumbleB

Thanks for the replies but whether the "," (Comma) is there or not I still get the same result. What I am actualy doing is supplying data to a Dynamic DataTable for Crystal reporting at runtime. The only way I can get the Crystal report to work. In order for me to sum the HoursWorked and get it output, I need to be able to assign it to an Alias. However, the code assigns each selected Column to an Alias setup in the Dynamic DataTable in Crystal report so in fact it is a double Alias issue. If you know what I mean. I can post some more code etc if you require. I never knew that Crystal reporting or the Report Viewer tools were such a nightmare to work with!! What the code is doing results in the following:

"SELECT TSProjectCode AS COLUMN1, EmployeeID AS Column2, SUM(HOURSWORKED) AS 'TOTAL_HOURS AS COLUMN3 FROM TblTimesheetDetails WHERE.........

The bold part is where the problem is!! As you can see there is a double assign to an Alias and I can't run the SUM without assigning it to an Alias!! :confused: :confused: :(( :(( :((

Excellence is doing ordinary things extraordinarily well.

Lost User

Kwagga wrote:

The bold part is where the problem is!! As you can see there is a double assign to an Alias and I can't run the SUM without assigning it to an Alias!!

Wouldn't it get "COLUMN3" as a columnname of you omit it in the declaration?

inSql = @" Select DISTINCT TSProjectCodeID, EmployeeID, SUM(HoursWorked) FROM tblTimesheetDetails WHERE TimeSheetDate >= '" + startdate + "' AND TimeSheetDate <= '" + todate + "' AND TSProjectCodeID = '" + cmbprojects.Text.ToString() + "'ORDER BY TSProjectCodeID, EmployeeID, HoursWorked";

..or would you like to name it "Total_Hours", while the other columns remain mapped to a numbered alias?

I are Troll :suss:

MumbleB

Hi Eddy. I managed to get it to work OK. However, now I ams tuck on a another bit of an issue. My tblTimsheetDetails has data as follows:

TimesheetDetailID EmployeeID TimesheetID TSProjectCodeID TaskCode TimeSheetDate HoursWorked
100 F3309134 14 MT101 Test Execution (INT) 2010/07/09 4.00
56 F3309134 14 MT101 Test Support 2010/07/26 2.00
62 F3309134 14 MT101 Project Management 2010/07/27 3.00
66 F3309134 14 MT101 Meeting Project 2010/07/27 0.50
67 F3309134 14 MT101 Meeting Project 2010/07/28 0.50
69 F3309134 14 MT101 Project Management 2010/07/28 2.00
70 F3309134 14 MT101 Meeting Project 2010/07/29 1.00
61 F3309134 13 MT900/910 CR 2010/07/26 0.50

When I run the code I am expecting it to group all the MT101 stuff together and give me as single row with a total sum depending on the date range I select as inout. However, it lists thwm all with the individual timea. this is my first attempt at Crystal Reporting and I am hoping to publish this once I get it all to work 100%. Ay help or suggestions would be great and it is appreciated mate.

Excellence is doing ordinary things extraordinarily well.

MumbleB

using parameterised statements is a bit dodgy when it comes to crystal reports at run time. I am getting input from DateTimePickers and a pre-loaded comboBox with values from the Database, so SQWL injection is kinda limited or not possible. :laugh:

Excellence is doing ordinary things extraordinarily well.

PIEBALDconsult

You can (and should) use parameterized queries with Crystal.

PIEBALDconsult

Kwagga wrote:

AS 'TOTAL_HOURS AS COLUMN3

That looks wrong.

Lost User

Kwagga wrote:

When I run the code I am expecting it to group all the MT101 stuff together and give me as single row with a total sum depending on the date range I select as inout. However, it lists thwm all with the individual timea.

Can you post the generated Sql? Might be something in the GROUP BY clause.

Kwagga wrote:

this is my first attempt at Crystal Reporting and I am hoping to publish this once I get it all to work 100%.

I'm afraid I can't help you there; I don't have any experience in creating Crystal Reports. Been using MS-Access for far too long :)

I are Troll :suss:

MumbleB

I managed to resolve the Issues without parameterized queries mate. Because I am creating the dataset at runtime and then populating it to a database at runtime and dynamically assigning them to columns I had to go the route I went. For some reason it is working perfect now. I understand that parameterized queries workes better and they are safer but I am tunning against an MS Access DB which is not that friendly with stored procedures as far as I have read.

Excellence is doing ordinary things extraordinarily well.

PIEBALDconsult

Kwagga wrote:

Access DB which is not that friendly with stored procedures

No one said anything about stored procedures. :confused: Aren't you using parameters within Crystal?