<pre lang="VB">Dim strSql As String = "SELECT DISTINCT Orders.OrderId, Orders.ClientId, OrderDate, DueDate, SubTotal, SalesTax, CityTax, LaborTax, Discount, Delivery, OrderTotal,
OrderDetails.ItemId, OrderDetails.Category, OrderDetails.Description, OrderDetails.Qnty, OrderDetails.Unit, OrderDetails.RetailPrice, OrderDetails.Total, (Select MIN (OrderPymts.PrevBal)) As PrevBal, (Select MAX(OrderPymts.PaymentDate)) As PaymentDate,
(Select MIN (OrderPymts.Payment)) As Payment, (Select MIN (OrderPymts.Balance)) As Balance, OrderPymts.Terms, (Select MAX(OrderPymts.PaymentId)) As PaymentId, Clients.Name,
Clients.Address + ' ' + Clients.City + ', ' + Clients.State + '. ' + Clients.Zip AS ClientsAddress,
'Phone : ' + Clients.Phone + ' Fax : ' + Clients.Fax + ' CellPhone : ' + Clients.CellPhone + ' Email : ' + Clients.Email As ContactInfo, Clients.Since
FROM Orders, Clients
INNER JOIN OrderDetails ON OrderDetails.OrderId = Orders.OrderId
INNER JOIN Clients ON Orders.ClientId = Clients.ClientId
INNER JOIN OrderPymts ON OrderPymts.OrderId = Orders.OrderId
WHERE (Orders.OrderId = @OrderId)"</pre>
I'm having trouble with this statement. This statement is used to call the records in the invoice. Odd since I have 2 records, One with multiple items and only 1 payment and it displays fine. The other record with multiple detail items and multiple payments displays the items twice on the invoice report and shows the first payment record instead of the latest payment record. There are 3 tables, Orders, OrderDetails, and OrderPymts. So I'm trying to query from all three tables based on the orderId.
Any Help would be greatly appreciated.
We can't see the structure of your tables, nor the data they contain.
But what you've described is the expected result of joining multiple records - if you have three lines and two payments for one order, and join them on the order ID, you will get six records in the output.
So, I'm trying to import Semicolon separated files into SQL Server.
The files are in codepage 1252 and are "Text Qualified" with a broken bar ¦
In the preview everything looks fine, but in the imported Tables all text fields looks like this: ¦text¦
This wouldn't be a big problem if it wasn't for the fact that some text fields contain semicolons.
Then some columns reads: ¦beginning of text , and the next one: end of text¦, and the last one might look like this: ¦¦;¦¦
If I'm saving the import as a dtsx package and open it in visual studio, the problem remains the same.
Is this a known bug or am I doing something wrong?
Any solutions that doesn't require me to create my own CSV-import?
What gets at me is that this seem to work as expected in the preview tab.
That would have me tearing my hair out! At which stage I would probably try a reinstall
However, weird as this may sound (is anything truly weird when it comes to Microsoft??) have you tried not setting TextQualified on the individual fields? (i.e. just on the General page). I'm probably just grasping at straws tbh
I just found out that opening the files in Notepad++ as codepage 1252 and saving them as UTF8, and then change the import from 1252 to UTF8, fixes the problem.
I'm putting this down as a bug from MS. (That probably won't ever be fixed)
The question is, what is the best way to fix it?
For a one off I will just open and save all the files and import them as UTF8.
But this will be a weekly import later on, preferably run as an SSIS package.
This might or might not help ... check what happens using Excel or Access and their X-referencing/inter-app-transferring abilities on specific data table (as .txt; I'm assuming you can morph tables by exporting them with whatever tool you're currently sleuthing the inputs and outputs).
In Excel or Access, generally speaking, parsing data using what Microsoft terms a "delimiter" and those various options of which you speak, can often run afoul of it's own preaching (doc code won't run as-printed) ... in which case deploying UDF using the copious functionality of VBA most times does the trick. A favorite workaround of mine is to use Split and InStr, along with Find and Search although logic there becomes draconian quickly.
It sounds like you're in the testing stages anyway.