Why do you have SQL embedded in your VB code? As a DBA that is a no no. You can get SUM in T-SQL, but you can also SUM in SSRS and Crystal Reports. As for your new I have never seen that. I am not a very experienced VB developer so it may be something I have not seen there. I would do it in a stored proc.
I got thtis to work, but I can't figure out how to insert my 2 joins, and get those values all in 1 result set
Dim pResults = _
From cohc In context.Order_History_Cart
Where cohc.PartNumber = "24-FLP44"
Group By cohc = New With
} Into group
Select New With
.totalQty = group.Sum(function(q) q.Qty),
.totalCost = group.Sum(function(c) c.Qty * c.Cost),
.TotalPrice = group.Sum(function(p) p.Qty * p.Price)<br>
Been there done that, but in WCF.
In design time you're apparently not using the app.config you think you are.
There's a description and kind of solution to one of the ways this problem can occur here[^].
1/ How can you add transaction functionality to your stored procedure without locking anything ( I know there is different isolation level, but I'm not sure which one to use )
2/ I need to use transaction just for the sake of rolling back the changes if there was an exception in the middle of the procedure. so I really don't care about locking tables. which isolation level should I use.
How can you add transaction functionality to your stored procedure without
locking anything ( I know there is different isolation level, but I'm not sure
which one to use )
Locking is done when writing. The isolation levels are for specifying what you want to read.
I need to use transaction just for the sake of rolling back the changes if there
was an exception in the middle of the procedure. so I really don't care about
locking tables. which isolation level should I use.
Sounds like the procedure consists of multiple steps that can fail individually, and those updates might lock multiple tables while processing.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
READ UNCOMMITTED is the isolation level you are looking for I think. This will allow dirty reads, but almost eliminate all locking. Writing will lock the affected tables, but if others READ UNCOMMITTED they should be fine. If you have to lock the table in order the rollback the process then you really have no choice but to lock it.
It's a Navigator fly out tab, in which if your working on a product in the editor, you can activate the tab, and the tab will populate with lets say 4 products before and 4 products after the selected product in the database table. This is so you don't have to go back to the index to load the next or previous product.
Since SQL Linq doesn't support the custom SQL Server Function, I need to think or create an alternative way to do this.
I guess I can create a List(Of with a blank column, and then go back and fill in the row numbers
get the row number of the item and ask for -3 and +3 of the row.
Just looking for some suggestions.
Here's the old TSQL
WITH CTE AS
, ROW_NUMBER() OVER(ORDER BY PartNumber DESC) AS RowId
FROM PRODUCTINFO p
), CTE1 AS (
SELECT RowId FROM CTE WHERE PartNumber = @PartNumber
SELECT * FROM CTE WHERE RowId BETWEEN (SELECT RowId-4 FROM CTE1) AND (SELECT RowId+3 FROM CTE1) ORDER BY PartNumber
This is what I was building in Linq Pad. It's pretty weak now, but I'm getting an idea of what I can do. But before I go off in some strange direction that will lead nowhere or be super slow, I thought I would just try and get a general consensus on this.
I tried to mark it as code but it gets chopped off, end of the day for me, going home now.
Dim p_partNumber as string = "06-SM4"
Dim context as new DBcontext
Dim ordersAsList = _
From pi in context.ProductInfo
Dim orders = _
From pi in ordersAsList
I didn't know you can do that.
I know how slow it can get when the record count get high. That's why I didn't want to do the list.
I'll give that a try for now, and then redesign the whole thing later. I knew that using special DB function would take a tool in the future when I implemented it.
Works like a charm. Took awhile to figure out how to implement it.
Did you write that? it's pretty elegant and wild in how it works.
It would of taken me months to figure that out! But that should be the most complex TSQL remaining in my program.
Oh, this conversion I'm making is mentally draining on me every day, but I'm 1/2 way there now.
The reward will pay off for me when done. Thanks again for taking the time to write that, that was beyond my current knowledge level.
I have this report for sales rep margin in Account Mate for DOS, that uses FoxPro for database table file, .dbx
The item tables or DB files does not have a column for sales rep and order dates, so I can't go to the table directly to get my data.
So without knowing or even where to start to write this in pure SQL, I wrote 2 functions
1. Get Invoices that match the sales rep and start and stop dates, and store it in a List(of invoices) 2. loop a function that targets items by invoice number, and get the cost, price, qty, etc.
When you have 500 invoices, it takes forever to run, slowing down fixing my other problems, which may be fixed now.
Do you think it's possible to merge these statements into a single statement. Plus if so, a nudge in the right direction. I hate this old Fox Pro stuff, it was before my time.
FROM ARINV01H.dbf h
h.FSALESPN = @FSALESPN
h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate
FROM ARINV01.dbf v
v.FSALESPN = @FSALESPN
v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate
, SUM(FCOST * FSHIPQTY)
, SUM(FPRICE * FSHIPQTY)
, (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC
FROM ARTRS01H.dbf h
h.FINVNO = @FINVNO
FROM ARTRS01.dbf v
v.FINVNO = @FINVNO
GROUP BY FITEMNO "
The AccountMate for DOS works. The sales rep for them in Dallas sort of dicked them around with the price to upgrade, and raised the price 15K after he paid a visit, so they decided to just stick it to them.
So I made an overlay program for them that performs most of the daily functions for the front office. Hey its income for me, they pay fast.