Dataset returns in wrong order

Dataset returns in wrong order

Postby zszipola » Wed 07 May 2008 14:06

Hello,

I have been using ByteFx with ASP.NET for a couple of years now. No problems apart from only working with MySql up to version 4.1. So it was high time to upgrade.
My web application (ASP.NET 2.0 VS 2008) works seamlessly with MyDirect. However, my winform is less impressive. I have long queries with 20 - 40 columns returning in my datatable. I have noticed that CoreLab MyDirect returns these in a way that some of the fields get swapped (i.e. column 19 with column 28). I have tested the same queries with Navicat and it is fine and it worked just as well with ByteFx.
However, the same query returns the columns in a different order with MyDirect. I don't use JOIN only WHERE and GROUP BY. This difference obviously causes type mismatches in .Net.

I am puzzled.

Please advise!

Best,

Zoltan
zszipola
 
Posts: 2
Joined: Wed 07 May 2008 13:56
Location: UK

Postby zszipola » Wed 07 May 2008 14:14

The query is (never mind the tables etc.):

SELECT department.DepartmentId, InvoiceDate, customers.Name, customers.ContactPerson, customers.Address1, customers.Address2, customers.Town, customers.PC_ZIP, countries.Country, customers.DelAddress1, customers.DelAddress2, customers.DelTown, customers.DelPC_ZIP, delcountries.CountryId , invoice.DueDate, paymentmethod.PaymentMethod, bank.Description, invoice.PaidDate, invoice.TransactionId, invoice.DespatchedDate, invoice.TrackingNumber, invoice.DeliveredDate, CONCAT(DeliveryCompany,' - ',DeliveryType) AS DC, customers.ContactEmail, customers.CustomerId, invoice.Online, invoice.Void, countries.CountryId, countries.EU, customers.VAT

FROM

department, invoice, customers, bank, countries, delcountries, paymentmethod, deliverycompany

WHERE

(department.DepartmentId = invoice.DepartmentId) AND (invoice.InvoiceId = " + inv.ToString() + " ) AND (customers.CustomerId = invoice.CustomerId) AND (invoice.BankId = bank.BankId) AND (customers.CountryId = countries.CountryId) AND (customers.DelCountryId = delcountries.CountryId) AND (IFNULL(deliverycompany.DeliveryCompanyId = invoice.DeliveryCompanyId, 1 = 1)) AND (paymentmethod.PaymentMethodId = invoice.PaymentMethodId) GROUP BY (invoice.InvoiceId)

...and it always returns the columns in the order as it shown between SELECT and FROM. (Why wouldn't it?)
Well, with MyDirect it swaps the last two fields (countries.EU and customers.VAT ) for some unknown reason. And it does the same with longer queries in an unpredictable way.

ps.: inv.ToString() is the invoice number
zszipola
 
Posts: 2
Joined: Wed 07 May 2008 13:56
Location: UK

Postby Alexey.mdr » Thu 08 May 2008 13:23

Hello Zoltan,

Please send me (alexeyman*crlab*com) a small test project to reproduce the problem.
Include the definition of your own database objects.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24


Return to dotConnect for MySQL