Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
VB is awful
- Thread starter SBTBILL
- Start date
- Thread starter
- #3
Been using VB.Net for about a month after reading 3 books on it. On my 4th now. It is nice for putting simple webpages up and terrible for database analysis.
Worse part is all the VB people tell me much better it is then the other stuff. Guess if your used to cold grule warm grule is better. Trouble is it's still grule.
Worse part is all the VB people tell me much better it is then the other stuff. Guess if your used to cold grule warm grule is better. Trouble is it's still grule.
Mike Lewis
Programmer
Why do you need a replacement? I understand the reasons that you are disgusted with Microsoft. But VFP is still the same VFP that it's always been.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro tips, advice, training, consultancy
Custom software for your business
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro tips, advice, training, consultancy
Custom software for your business
You might try .Net (you might choose VB, C# or any one of the many .Net languages). VB is not awful if you can be objective (but I chose C# and F#). If you can be objective enough, in a short time you would see that .Net is simply better than VFP in all areas including data processing.
From a subjective POV, I have been using foxbase,foxpro,VFP maybe for over 25+ years and is/was one of its lovers. I appreciate VFP and still earn cash using it. However, I have a family and development is my primary earning source, I am not tied to VFP till death (and actually she is leaving me not that I am leaving her). Anyway, I have been working with C# too for some time (quite a time it is for a learning period but I needed to bite it and I did) and now thinking the advances in areas within .Net and its supporting sources as a whole, today, if you would be programming primarily for windows clients then .Net is the way to go.
My .02 cents.
Cetin Basoz
MS Foxpro MVP, MCP
From a subjective POV, I have been using foxbase,foxpro,VFP maybe for over 25+ years and is/was one of its lovers. I appreciate VFP and still earn cash using it. However, I have a family and development is my primary earning source, I am not tied to VFP till death (and actually she is leaving me not that I am leaving her). Anyway, I have been working with C# too for some time (quite a time it is for a learning period but I needed to bite it and I did) and now thinking the advances in areas within .Net and its supporting sources as a whole, today, if you would be programming primarily for windows clients then .Net is the way to go.
My .02 cents.
Cetin Basoz
MS Foxpro MVP, MCP
And BTW if you would like a more detailed explanation of my ideas + some 'teaching' of .Net (particularly in data area like Linq) you might want to check my blog on:
and first entry is:
(although I couldn't find time to add articles for a long time, soon thinking about Silverlight, WPF, WCF ... titles)
Cetin Basoz
MS Foxpro MVP, MCP
and first entry is:
(although I couldn't find time to add articles for a long time, soon thinking about Silverlight, WPF, WCF ... titles)
Cetin Basoz
MS Foxpro MVP, MCP
The first thing you need to ask yourself is if you want to replace one niche product with another. If the answer is no, then you need to go with Java, .NET, Python, or PHP. If the answer is it's ok, then add xBase++ to your list.
.NET is not terrible for database analysis. You just need to do it a different way. Keep in mind that you're working with SQL Server. So you are saying the SQL Server is terrible for database analysis. And since pretty much all the big name databases (Oracle, DB2, Informix, MySQL, etc) all work pretty much the same way, none are good for database analysis. People have been doing successful database analysis for years without VFP.
Craig Berntson
MCSD, Visual FoxPro MVP,
.NET is not terrible for database analysis. You just need to do it a different way. Keep in mind that you're working with SQL Server. So you are saying the SQL Server is terrible for database analysis. And since pretty much all the big name databases (Oracle, DB2, Informix, MySQL, etc) all work pretty much the same way, none are good for database analysis. People have been doing successful database analysis for years without VFP.
Craig Berntson
MCSD, Visual FoxPro MVP,
- Thread starter
- #8
I've been using VFP with SQL for years. I've always found SQL limited but by using it for the first step and then doing the rest in VFP I can do things that I just can't see getting done in SQL.
I merge date from multiple source. I break data down in multiple tables. I like the idea of letting end users fill in a grid and then using that to create a criteria line. Try that in a stored procedure. Yes I can get VB to this. But it is so much slower.
I merge date from multiple source. I break data down in multiple tables. I like the idea of letting end users fill in a grid and then using that to create a criteria line. Try that in a stored procedure. Yes I can get VB to this. But it is so much slower.
SBTBILL:
Once you've learned the power of SQL Server and how to really use it, you'll see that does the job, does it well, and does it fast.
friend01:
VB 6.0 sucks. VB.Net is just fine. Basically, there is not difference in capabilities between VB.NET and C#.
Craig Berntson
MCSD, Visual FoxPro MVP,
Once you've learned the power of SQL Server and how to really use it, you'll see that does the job, does it well, and does it fast.
friend01:
VB 6.0 sucks. VB.Net is just fine. Basically, there is not difference in capabilities between VB.NET and C#.
Craig Berntson
MCSD, Visual FoxPro MVP,
STBILL,
You are saying that you have been working with SQL server for years but still talking as if you don't know SQL server.
Letting users fill a grid to create a criteria line is famous in VB world and is typical of SQL server/Access query wizards. However you should first understand SQL server is not designed for the UI and end user interface. Saying VB is much slower, maybe it is only slow because of your code. Can you show us your code?
Cetin Basoz
MS Foxpro MVP, MCP
You are saying that you have been working with SQL server for years but still talking as if you don't know SQL server.
Letting users fill a grid to create a criteria line is famous in VB world and is typical of SQL server/Access query wizards. However you should first understand SQL server is not designed for the UI and end user interface. Saying VB is much slower, maybe it is only slow because of your code. Can you show us your code?
Cetin Basoz
MS Foxpro MVP, MCP
Mike Lewis
Programmer
SBTBill,
You still haven't said why you want to abandon VFP.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro tips, advice, training, consultancy
Custom software for your business
You still haven't said why you want to abandon VFP.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro tips, advice, training, consultancy
Custom software for your business
- Thread starter
- #13
- Thread starter
- #14
Source code some are asking for
Imports System.Data.OleDb
Imports System.Data.SqlTypes
Imports System.Data.SqlDbType
Imports system.Data.SqlClient
Imports system.Data
Imports System.ComponentModel
Imports System.Collections
Partial Class _Default
Inherits System.Web.UI.Page
Dim mdofeb As String = CType(Session.Item("mbdate"), String)
Dim mdofee As String = CType(Session.Item("medate"), String)
Dim mcountry As String = CType(Session.Item("mcountry"), String)
Dim mbrand As String = CType(Session.Item("mbrand"), String)
Dim mproduct As String = CType(Session.Item("mproduct"), String)
Dim mrpttype As String = CType(Session.Item("mrpttype"), String)
'
Dim x2, _
mqty, _
mshipped, _
mback, _
mreturned, _
mordered, _
mdisc_per As Integer
Dim mucost, _
mextcost, _
mroyalty, _
muprice, _
mextprice, _
mdiscount, _
mrcost, _
mprofit, _
mqty_back_ordered, _
TDATA As Decimal
Dim _mextprice As Decimal = 0
Dim mitem As String
Dim mcriteria, _
grabline As String
Dim itemlist As New DataTable
Dim invitems As New DataSet
Public GRAB As New SqlConnection("data source = sqlprod; user id = FOXPRO; password = $SBT@123")
Dim grab1 As New SqlCommand
'****************************************
'* initialize the table for the results *
'****************************************
Public rs1 As New DataTable
Dim G2 As New DataSet
Dim dr As DataRow
Dim item As DataColumn
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Me.lblbdate.Text = mdofeb
Me.lbledate.Text = mdofee
Me.lblbrand.Text = mbrand
Me.lblcountry.Text = mcountry
Me.lblsku.Text = mproduct
Me.lblreport_type.Text = mrpttype
'
Call initvar()
mitem = ""
'Call getitems()
Me.getitems()
Call create_report()
'
mcriteria = " ecommerce.dbo.orders.order_date >= "
mcriteria = mcriteria + " convert(datetime, '" + mdofeb + " 00:00:00') "
mcriteria = mcriteria + " and "
mcriteria = mcriteria + " ecommerce.dbo.orders.order_date <= "
mcriteria = mcriteria + " convert(datetime, '" + mdofee + " 23:59:59') "
If mcountry <> "" Then
mcriteria = mcriteria + " and "
mcriteria = mcriteria + " ecommerce.dbo.orders.ship_country = '" + mcountry + "' "
End If
If mproduct <> "" Then
mcriteria = mcriteria + " and "
mcriteria = mcriteria + " ecommerce.dbo.order_line_items.item_number like '%" + mproduct + "%' "
End If
mcriteria = mcriteria + " and "
Select Case mrpttype
Case "RMA'"
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 3 "
Case "Back Order"
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 4 "
Case Else
mcriteria = mcriteria + " (ecommerce.dbo.orders.ordertypeid = 1 or "
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 8 or "
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 11 or "
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 12 or "
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 13 or "
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 15) "
End Select
mcriteria = mcriteria + " and "
mcriteria = mcriteria + " ((ecommerce.dbo.order_status.shipping_status_id >= 3 and "
mcriteria = mcriteria + " ecommerce.dbo.order_status.shipping_status_id <= 7) or "
mcriteria = mcriteria + " (ecommerce.dbo.order_status.shipping_status_id = 9 or "
mcriteria = mcriteria + " ecommerce.dbo.order_status.shipping_status_id = 11)) "
'
grabline = "Select ecommerce.dbo.order_line_items.item_number, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.quantity) as tquantity, "
grabline = grabline + " ecommerce.dbo.order_line_items.cost as price, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.cost_subtotal) as extprice, "
grabline = grabline + " ecommerce.dbo.order_line_items.pv_value, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.pv_subtotal) as total_pv, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.qty_shipped) as shipped, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.qty_bo) as back_ordered, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.qty_received) as returned, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.RoyaltyValue) as total_royalty, "
grabline = grabline + " ecommerce.dbo.orders.ordertypeid, "
grabline = grabline + " ecommerce.dbo.orders.discount_percent "
grabline = grabline + " from ecommerce.dbo.order_line_items "
grabline = grabline + " left outer join "
grabline = grabline + " ecommerce.dbo.orders "
grabline = grabline + " on "
grabline = grabline + " ecommerce.dbo.orders.order_id = "
grabline = grabline + " ecommerce.dbo.order_line_items.order_id "
grabline = grabline + " left outer join "
grabline = grabline + " ecommerce.dbo.order_status "
grabline = grabline + " on "
grabline = grabline + " ecommerce.dbo.orders.order_id = "
grabline = grabline + " ecommerce.dbo.order_status.order_id "
grabline = grabline + " where " + mcriteria
grabline = grabline + " group by item_number, cost, pv_value, ordertypeid, discount_percent"
grabline = grabline + " order by ecommerce.dbo.order_line_items.item_number "
'
GRAB.Open()
Dim grab2 As New SqlCommand(grabline, GRAB)
'
Dim G1 As New SqlDataAdapter(grab2)
'
G1.Fill(G2, "THEREPORT")
GRAB.Close()
'
mitem = G2.Tables("thereport").Rows(1).Item("Item_number").ToString
mdisc_per = G2.Tables("thereport").Rows(1).Item("discount_percent")
Dim mtunits As Integer = 0
Dim x3 = G2.Tables("thereport").Rows.Count
For x2 = 0 To x3 - 1
If mitem <> G2.Tables("thereport").Rows(x2).Item("Item_number").ToString Then
Me.storerow()
mitem = G2.Tables("thereport").Rows(x2).Item("Item_number").ToString
mdisc_per = G2.Tables("thereport").Rows(x2).Item("discount_percent")
Me.get_muscost(mucost)
End If
mordered = mordered + G2.Tables("thereport").Rows(x2).Item("tquantity")
mshipped = mshipped + G2.Tables("thereport").Rows(x2).Item("shipped")
Try
mqty_back_ordered = mqty_back_ordered + G2.Tables("thereports").Rows(x2).Item("back_ordered")
Catch
End Try
Try
mreturned = mreturned + G2.Tables("thereports").Rows(x2).Item("returned")
Catch
End Try
Try
mroyalty = mroyalty + G2.Tables("thereport").Rows(x2).Item("royaltyvalue")
Catch
End Try
Try
muprice = muprice + G2.Tables("thereport").Rows(x2).Item("price")
Catch
End Try
Try
mextprice = mextprice + G2.Tables("thereport").Rows(x2).Item("extprice")
Catch
End Try
Call storerow()
Call initvar()
mtunits = mtunits + 1
LBLTotal_Units.Text = mtunits
Next
Me.lbltotal_pv.Text = Me._mextprice
GRDSales.DataSource = rs1
GRDSales.DataBind()
'
GRDSales.Visible = True
Me.txtmsg.Text = grabline
End Sub
Public Sub create_report()
With rs1.Columns
.Add("Item", Type.GetType("System.String"))
.Add("Qty_Ordered", Type.GetType("System.Int16"))
.Add("Qty_Shipped", Type.GetType("System.Int16"))
.Add("Qty_Back_Ordered", Type.GetType("System.Int16"))
.Add("Qty_Returned", Type.GetType("System.Int16"))
.Add("Unit_Cost", Type.GetType("System.Decimal"))
.Add("Extended_Cost", Type.GetType("System.Decimal"))
.Add("Royalty", Type.GetType("System.Decimal"))
.Add("Unit_Price", Type.GetType("System.Decimal"))
.Add("Extended_Price", Type.GetType("System.Decimal"))
.Add("Est_Discount", Type.GetType("System.Decimal"))
.Add("Discount_percent", Type.GetType("System.Decimal"))
.Add("Return_Cost", Type.GetType("System.Decimal"))
.Add("Est_Profit", Type.GetType("System.Decimal"))
End With
rs1.PrimaryKey = New DataColumn() {rs1.Columns("item")}
End Sub
Public Sub initvar()
mqty = 0
mdisc_per = 0
mshipped = 0
mback = 0
mreturned = 0
mucost = 0.0
mextcost = 0.0
mroyalty = 0.0
muprice = 0.0
mextprice = 0.0
mdiscount = 0.0
mrcost = 0.0
mprofit = 0.0
mordered = 0.0
mqty_back_ordered = 0.0
End Sub
Private Sub storerow()
Try
Dim mtdiscount As Decimal
mtdiscount = Math.Round(mextprice * mdisc_per, 2)
dr = rs1.NewRow
dr("item") = mitem
dr("qty_ordered") = mordered
dr("qty_shipped") = mshipped
dr("qty_back_ordered") = mqty_back_ordered
dr("qty_returned") = mreturned
dr("Royalty") = mroyalty
dr("Unit_price") = muprice
dr("extended_price") = mextprice
dr("unit_cost") = mucost
dr("extended_cost") = Math.Round(mucost * mordered, 2)
dr("est_discount") = mtdiscount
dr("discount_percent") = mdisc_per
dr("unit_cost") = mucost
dr("extended_cost") = mextcost
dr("est_profit") = Math.Round(mextprice - (mextcost + mroyalty + mtdiscount), 2)
_mextprice = _mextprice + mextprice
rs1.Rows.Add(dr)
rs1.AcceptChanges()
Catch
End Try
End Sub
Private Sub getitems()
itemlist.Columns.Add(New DataColumn("Item", Type.GetType("System.String")))
itemlist.Columns.Add(New DataColumn("Description", Type.GetType("System.String")))
itemlist.Columns.Add(New DataColumn("Cost", Type.GetType("System.String")))
'
grabline = "Select item, "
grabline = grabline + " itmdesc, "
grabline = grabline + " stdcost "
grabline = grabline + " from s:\pro60\data01\icitem01 "
'
Dim g3B As New Odbc.OdbcCommand
Dim Gab As New Odbc.OdbcDataAdapter
Dim sbtcon As New Odbc.OdbcConnection
'
sbtcon.ConnectionString = "Dsn=Visual FoxPro Tables;source=s:\PRO60\data01;sourcetype=DBF;exclusive=No;backgroundfetch=Yes;collate=Machine"
sbtcon.Open()
g3B.CommandText = grabline
g3B.Connection = sbtcon
Gab.SelectCommand = g3B
Gab.Fill(invitems, "TheItems")
Dim x3b = invitems.Tables("TheItems").Rows.Count
Dim xi As Integer
For xi = 1 To x3b - 1
dr = itemlist.NewRow
dr("item") = invitems.Tables("TheItems").Rows(xi).Item("Item").ToString
dr("description") = invitems.Tables("TheItems").Rows(xi).Item("Itmdesc").ToString
dr("cost") = invitems.Tables("TheItems").Rows(xi).Item("stdcost").ToString
Next
sbtcon.Close()
End Sub
Private Sub get_muscost(ByVal muscost)
'Dim s As String = "primaryKeyValue"
Try
Dim foundRow As DataRow = invitems.Tables("TheItems").Rows.Find(mitem)
If foundRow IsNot Nothing Then
muscost = foundRow.Item("cost")
Else
muscost = 0.01
End If
Catch
muscost = -100
End Try
End Sub
End Class
Imports System.Data.OleDb
Imports System.Data.SqlTypes
Imports System.Data.SqlDbType
Imports system.Data.SqlClient
Imports system.Data
Imports System.ComponentModel
Imports System.Collections
Partial Class _Default
Inherits System.Web.UI.Page
Dim mdofeb As String = CType(Session.Item("mbdate"), String)
Dim mdofee As String = CType(Session.Item("medate"), String)
Dim mcountry As String = CType(Session.Item("mcountry"), String)
Dim mbrand As String = CType(Session.Item("mbrand"), String)
Dim mproduct As String = CType(Session.Item("mproduct"), String)
Dim mrpttype As String = CType(Session.Item("mrpttype"), String)
'
Dim x2, _
mqty, _
mshipped, _
mback, _
mreturned, _
mordered, _
mdisc_per As Integer
Dim mucost, _
mextcost, _
mroyalty, _
muprice, _
mextprice, _
mdiscount, _
mrcost, _
mprofit, _
mqty_back_ordered, _
TDATA As Decimal
Dim _mextprice As Decimal = 0
Dim mitem As String
Dim mcriteria, _
grabline As String
Dim itemlist As New DataTable
Dim invitems As New DataSet
Public GRAB As New SqlConnection("data source = sqlprod; user id = FOXPRO; password = $SBT@123")
Dim grab1 As New SqlCommand
'****************************************
'* initialize the table for the results *
'****************************************
Public rs1 As New DataTable
Dim G2 As New DataSet
Dim dr As DataRow
Dim item As DataColumn
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Me.lblbdate.Text = mdofeb
Me.lbledate.Text = mdofee
Me.lblbrand.Text = mbrand
Me.lblcountry.Text = mcountry
Me.lblsku.Text = mproduct
Me.lblreport_type.Text = mrpttype
'
Call initvar()
mitem = ""
'Call getitems()
Me.getitems()
Call create_report()
'
mcriteria = " ecommerce.dbo.orders.order_date >= "
mcriteria = mcriteria + " convert(datetime, '" + mdofeb + " 00:00:00') "
mcriteria = mcriteria + " and "
mcriteria = mcriteria + " ecommerce.dbo.orders.order_date <= "
mcriteria = mcriteria + " convert(datetime, '" + mdofee + " 23:59:59') "
If mcountry <> "" Then
mcriteria = mcriteria + " and "
mcriteria = mcriteria + " ecommerce.dbo.orders.ship_country = '" + mcountry + "' "
End If
If mproduct <> "" Then
mcriteria = mcriteria + " and "
mcriteria = mcriteria + " ecommerce.dbo.order_line_items.item_number like '%" + mproduct + "%' "
End If
mcriteria = mcriteria + " and "
Select Case mrpttype
Case "RMA'"
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 3 "
Case "Back Order"
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 4 "
Case Else
mcriteria = mcriteria + " (ecommerce.dbo.orders.ordertypeid = 1 or "
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 8 or "
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 11 or "
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 12 or "
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 13 or "
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 15) "
End Select
mcriteria = mcriteria + " and "
mcriteria = mcriteria + " ((ecommerce.dbo.order_status.shipping_status_id >= 3 and "
mcriteria = mcriteria + " ecommerce.dbo.order_status.shipping_status_id <= 7) or "
mcriteria = mcriteria + " (ecommerce.dbo.order_status.shipping_status_id = 9 or "
mcriteria = mcriteria + " ecommerce.dbo.order_status.shipping_status_id = 11)) "
'
grabline = "Select ecommerce.dbo.order_line_items.item_number, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.quantity) as tquantity, "
grabline = grabline + " ecommerce.dbo.order_line_items.cost as price, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.cost_subtotal) as extprice, "
grabline = grabline + " ecommerce.dbo.order_line_items.pv_value, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.pv_subtotal) as total_pv, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.qty_shipped) as shipped, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.qty_bo) as back_ordered, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.qty_received) as returned, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.RoyaltyValue) as total_royalty, "
grabline = grabline + " ecommerce.dbo.orders.ordertypeid, "
grabline = grabline + " ecommerce.dbo.orders.discount_percent "
grabline = grabline + " from ecommerce.dbo.order_line_items "
grabline = grabline + " left outer join "
grabline = grabline + " ecommerce.dbo.orders "
grabline = grabline + " on "
grabline = grabline + " ecommerce.dbo.orders.order_id = "
grabline = grabline + " ecommerce.dbo.order_line_items.order_id "
grabline = grabline + " left outer join "
grabline = grabline + " ecommerce.dbo.order_status "
grabline = grabline + " on "
grabline = grabline + " ecommerce.dbo.orders.order_id = "
grabline = grabline + " ecommerce.dbo.order_status.order_id "
grabline = grabline + " where " + mcriteria
grabline = grabline + " group by item_number, cost, pv_value, ordertypeid, discount_percent"
grabline = grabline + " order by ecommerce.dbo.order_line_items.item_number "
'
GRAB.Open()
Dim grab2 As New SqlCommand(grabline, GRAB)
'
Dim G1 As New SqlDataAdapter(grab2)
'
G1.Fill(G2, "THEREPORT")
GRAB.Close()
'
mitem = G2.Tables("thereport").Rows(1).Item("Item_number").ToString
mdisc_per = G2.Tables("thereport").Rows(1).Item("discount_percent")
Dim mtunits As Integer = 0
Dim x3 = G2.Tables("thereport").Rows.Count
For x2 = 0 To x3 - 1
If mitem <> G2.Tables("thereport").Rows(x2).Item("Item_number").ToString Then
Me.storerow()
mitem = G2.Tables("thereport").Rows(x2).Item("Item_number").ToString
mdisc_per = G2.Tables("thereport").Rows(x2).Item("discount_percent")
Me.get_muscost(mucost)
End If
mordered = mordered + G2.Tables("thereport").Rows(x2).Item("tquantity")
mshipped = mshipped + G2.Tables("thereport").Rows(x2).Item("shipped")
Try
mqty_back_ordered = mqty_back_ordered + G2.Tables("thereports").Rows(x2).Item("back_ordered")
Catch
End Try
Try
mreturned = mreturned + G2.Tables("thereports").Rows(x2).Item("returned")
Catch
End Try
Try
mroyalty = mroyalty + G2.Tables("thereport").Rows(x2).Item("royaltyvalue")
Catch
End Try
Try
muprice = muprice + G2.Tables("thereport").Rows(x2).Item("price")
Catch
End Try
Try
mextprice = mextprice + G2.Tables("thereport").Rows(x2).Item("extprice")
Catch
End Try
Call storerow()
Call initvar()
mtunits = mtunits + 1
LBLTotal_Units.Text = mtunits
Next
Me.lbltotal_pv.Text = Me._mextprice
GRDSales.DataSource = rs1
GRDSales.DataBind()
'
GRDSales.Visible = True
Me.txtmsg.Text = grabline
End Sub
Public Sub create_report()
With rs1.Columns
.Add("Item", Type.GetType("System.String"))
.Add("Qty_Ordered", Type.GetType("System.Int16"))
.Add("Qty_Shipped", Type.GetType("System.Int16"))
.Add("Qty_Back_Ordered", Type.GetType("System.Int16"))
.Add("Qty_Returned", Type.GetType("System.Int16"))
.Add("Unit_Cost", Type.GetType("System.Decimal"))
.Add("Extended_Cost", Type.GetType("System.Decimal"))
.Add("Royalty", Type.GetType("System.Decimal"))
.Add("Unit_Price", Type.GetType("System.Decimal"))
.Add("Extended_Price", Type.GetType("System.Decimal"))
.Add("Est_Discount", Type.GetType("System.Decimal"))
.Add("Discount_percent", Type.GetType("System.Decimal"))
.Add("Return_Cost", Type.GetType("System.Decimal"))
.Add("Est_Profit", Type.GetType("System.Decimal"))
End With
rs1.PrimaryKey = New DataColumn() {rs1.Columns("item")}
End Sub
Public Sub initvar()
mqty = 0
mdisc_per = 0
mshipped = 0
mback = 0
mreturned = 0
mucost = 0.0
mextcost = 0.0
mroyalty = 0.0
muprice = 0.0
mextprice = 0.0
mdiscount = 0.0
mrcost = 0.0
mprofit = 0.0
mordered = 0.0
mqty_back_ordered = 0.0
End Sub
Private Sub storerow()
Try
Dim mtdiscount As Decimal
mtdiscount = Math.Round(mextprice * mdisc_per, 2)
dr = rs1.NewRow
dr("item") = mitem
dr("qty_ordered") = mordered
dr("qty_shipped") = mshipped
dr("qty_back_ordered") = mqty_back_ordered
dr("qty_returned") = mreturned
dr("Royalty") = mroyalty
dr("Unit_price") = muprice
dr("extended_price") = mextprice
dr("unit_cost") = mucost
dr("extended_cost") = Math.Round(mucost * mordered, 2)
dr("est_discount") = mtdiscount
dr("discount_percent") = mdisc_per
dr("unit_cost") = mucost
dr("extended_cost") = mextcost
dr("est_profit") = Math.Round(mextprice - (mextcost + mroyalty + mtdiscount), 2)
_mextprice = _mextprice + mextprice
rs1.Rows.Add(dr)
rs1.AcceptChanges()
Catch
End Try
End Sub
Private Sub getitems()
itemlist.Columns.Add(New DataColumn("Item", Type.GetType("System.String")))
itemlist.Columns.Add(New DataColumn("Description", Type.GetType("System.String")))
itemlist.Columns.Add(New DataColumn("Cost", Type.GetType("System.String")))
'
grabline = "Select item, "
grabline = grabline + " itmdesc, "
grabline = grabline + " stdcost "
grabline = grabline + " from s:\pro60\data01\icitem01 "
'
Dim g3B As New Odbc.OdbcCommand
Dim Gab As New Odbc.OdbcDataAdapter
Dim sbtcon As New Odbc.OdbcConnection
'
sbtcon.ConnectionString = "Dsn=Visual FoxPro Tables;source=s:\PRO60\data01;sourcetype=DBF;exclusive=No;backgroundfetch=Yes;collate=Machine"
sbtcon.Open()
g3B.CommandText = grabline
g3B.Connection = sbtcon
Gab.SelectCommand = g3B
Gab.Fill(invitems, "TheItems")
Dim x3b = invitems.Tables("TheItems").Rows.Count
Dim xi As Integer
For xi = 1 To x3b - 1
dr = itemlist.NewRow
dr("item") = invitems.Tables("TheItems").Rows(xi).Item("Item").ToString
dr("description") = invitems.Tables("TheItems").Rows(xi).Item("Itmdesc").ToString
dr("cost") = invitems.Tables("TheItems").Rows(xi).Item("stdcost").ToString
Next
sbtcon.Close()
End Sub
Private Sub get_muscost(ByVal muscost)
'Dim s As String = "primaryKeyValue"
Try
Dim foundRow As DataRow = invitems.Tables("TheItems").Rows.Find(mitem)
If foundRow IsNot Nothing Then
muscost = foundRow.Item("cost")
Else
muscost = 0.01
End If
Catch
muscost = -100
End Try
End Sub
End Class
I am sorry to say that this code is a mess and needs a rework. If you have coded like this in VFP too you would succeed making it slow either. Some suggestions:
1) Discard the problem of this code being slow. First correct the obvious hazard. It is screaming "come and hack my database". You can do that easily by removing the hardcoding of your parameters as string into T-SQL and instead add parameters (from VFP it is the case too). Check:
SqlCommand.Parameters.AddWithValue(...)
in help.
2) Prevent using ODBC driver to access VFP tables. Install and use VFPOLEDB driver instead.
3) Study Linq, check OleDbDataReader and SqlDataReader classes, DataTable.Load(IDbDataReader) in help.
4) Rework your T-SQL (maybe it is at its best based on your data, you could check execution plans and client statistics and create necessary indexes).
5) Instead of creating strings using + try using StringBuilder class (in C# there is @"..." style string writing that looks like text...endtext in VFP. I don't know VB, if there is something like that in VB then prefer it for a more readable and faster code.
Good luck.
Cetin Basoz
MS Foxpro MVP, MCP
1) Discard the problem of this code being slow. First correct the obvious hazard. It is screaming "come and hack my database". You can do that easily by removing the hardcoding of your parameters as string into T-SQL and instead add parameters (from VFP it is the case too). Check:
SqlCommand.Parameters.AddWithValue(...)
in help.
2) Prevent using ODBC driver to access VFP tables. Install and use VFPOLEDB driver instead.
3) Study Linq, check OleDbDataReader and SqlDataReader classes, DataTable.Load(IDbDataReader) in help.
4) Rework your T-SQL (maybe it is at its best based on your data, you could check execution plans and client statistics and create necessary indexes).
5) Instead of creating strings using + try using StringBuilder class (in C# there is @"..." style string writing that looks like text...endtext in VFP. I don't know VB, if there is something like that in VB then prefer it for a more readable and faster code.
Good luck.
Cetin Basoz
MS Foxpro MVP, MCP
- Thread starter
- #16
Not sure stringbuilder would work looked at it in help and it looks like it is a fixed group.
Particularly on the criteria I use different criteria depending on what the user chooses to request. Thus sometimes the only criteria would be date. In some cases I would grab 1 group of order types and in others I would grab a different group of order types. The group size varies in each case I.e. I can be looking for 5 order types in one case and 1 in another case.
Is Stringbuilder flexible enough to handle that?
Particularly on the criteria I use different criteria depending on what the user chooses to request. Thus sometimes the only criteria would be date. In some cases I would grab 1 group of order types and in others I would grab a different group of order types. The group size varies in each case I.e. I can be looking for 5 order types in one case and 1 in another case.
Is Stringbuilder flexible enough to handle that?
StringBuilder only handles the:
grabline = "... "
grabline = grabline + "..."
grabline = grabline + "..."
grabline = grabline + "..."
like code. String in .Net is an immutable reference type. That means when you concatenate a string with another one or modify it any way then you are actually creating a new string instead of simply appending in memory. Because of this behavior, the more strings you concatenate the slower it gets to create the big final string. StringBuilder() class handles this problem and looks like VFP's set textmerge to ... set textmerge off style string building. ie:
Based on your criteria you can build different sql strings and add different parameters. If you look into Linq (that would take some time to understand and learn it - and learning never ends) than you could utilize its SQL like syntax to create much easier 'queries'. It is Object Query instead of the T-SQL that we are accustomed to but is on streoids IMHO (especially with the new innovations in SQL server 2008R2 and .Net 4.0 - your query might runs on multiple CPUs and threads for free for example using PLinq).
The downside of this is the long and winded learning curve
I found out that the path I walked (and I am still learning) was well worth it.
Cetin Basoz
MS Foxpro MVP, MCP
grabline = "... "
grabline = grabline + "..."
grabline = grabline + "..."
grabline = grabline + "..."
like code. String in .Net is an immutable reference type. That means when you concatenate a string with another one or modify it any way then you are actually creating a new string instead of simply appending in memory. Because of this behavior, the more strings you concatenate the slower it gets to create the big final string. StringBuilder() class handles this problem and looks like VFP's set textmerge to ... set textmerge off style string building. ie:
Code:
StringBuilder sb = new StringBuilder();
sb.Append("select ....")
sb.Append(" sum(ecommerce...) ")
// ...
string sqlCommand = sb.ToString();Based on your criteria you can build different sql strings and add different parameters. If you look into Linq (that would take some time to understand and learn it - and learning never ends) than you could utilize its SQL like syntax to create much easier 'queries'. It is Object Query instead of the T-SQL that we are accustomed to but is on streoids IMHO (especially with the new innovations in SQL server 2008R2 and .Net 4.0 - your query might runs on multiple CPUs and threads for free for example using PLinq).
The downside of this is the long and winded learning curve
I found out that the path I walked (and I am still learning) was well worth it. Cetin Basoz
MS Foxpro MVP, MCP
- Status
Similar threads
- Question