Crosstab Query in VB2008 Express RRS feed

  • Question

  • I am using my horseracing database with records with the following fields:

    Date,Meet,Time,Runner,SP,Stake,Profit,Bank, ID

    The following code works perfectly in VB6 but I just cannot get VB2008 to run it. Who cqn tell me what is wrong? If I type it into trhe query builder itr just says 'cannot parse'.

    TRANSFORM SUM(SP) AS Odds SELECT RUNNER FROM + FName + WHERE SP>0 GROUP BY RUNNER ORDER BY RUNNER PIVOT'Month '&DatePart('m',Date) IN ('Month 1','Month 2','Month 3','Month 4','Month 5','Month 6','Month 7','Month 8','Month 9','month 10','Month 11','Month 12');

    FName is of course the name of the table.

    Please help. I am getting desperate.

    Tuesday, December 7, 2010 5:24 PM


  • Well, since this is unrelated to training and certification, I believe that you would get better answers if you asked the question in a relevant software development forum, for instance, the VB Express forum at MSDN:


    About your query: I am astonished that it would "work perfectly" in VB6, since the query is not syntactically correct in the way that you have posted it. Maybe some quotes got dropped when you pasted it into the forum. Anyway, neither VB6 nor VB.NET can execute SQL Queries by themselves. Both languages need to submit the queries to a database engine for processing. I am under the impression that you used the JET Engine in VB6, while you are trying to use SQL Server from VB.NET. This will not work, because SQL Server does not have the Transform operator, and the syntax for PIVOT is also different. Even if you are using the JET Engine in .Net (which will only work in 32 bits), there is another problem in the way that you are building the query: apparently, you are concatenating text by means of the "+" operator. But for this to work, the literal parts of the query should be between double quotes, and the variables should be outside of the quotes. You have written everything without quotes. Also, you cannot submit this to the query builder; this tool requires the completed sentence, after all the concatenations have been performed. You cannot expect it to retrieve the values of your variables to fill-in the query, since those values will only exist at runtime.

    As you can see, there is plenty of trouble with the way you are doing things. I recommend that you move the question into a development forum, such as the one I pointed above, to get better answers and explanations about this subject.

    • Marked as answer by KWINWIN Wednesday, December 15, 2010 8:59 PM
    Wednesday, December 8, 2010 10:44 AM