|
Hi Eddy,
Thanks for replying point by point for all my questions
Here is my main query for fetching the records from 3 tables after collecting all the checked Items from my CheckedListBox control in a string called ITEMS:
select Table1.Column2, Table2.Column2, Table3.Column2 from Table2
full outer join Table1 on Table1.Column1 = Table2.Column1
full outer join Table3 on Table3.Column1 = Table2.Column2
where Table1.Column1 in (ITEMS) and Table2.Dates between 'FromDate' and 'ToDate'
Order By Table1.Column2
This is exactly what I am using.
Does it help you to help me further?
I noticed one more thing today, creating a long string ITEMS, which I am using in above query, is also taking a very long time.
As you explained in the third portion or your reply about the checked items in CheckedListBox, "you'll only need to convert that selection into something that your query accepts." can you please help me with this part. How do I need to convert the selected items which can be used in above query? Sorry but I could not think solution for this part pls help.
arun_peswani@yahoo.co.in
modified 20-Feb-14 13:30pm.
|
|
|
|
|
If you got a few days, we'll take it in a few steps.
Arun Peswani wrote: Does it help you to help me further? Yes, familiar pattern, might actually have something similar - without dates. Can you also tell us the column-types used? I'd also like to know if there's any indexes defined on that. If yes, then we'll look at optimizing the query tomorrow.
Arun Peswani wrote: I noticed one more thing today, creating a long string ITEMS, which I am using in above query, is also taking a very long time. Doing a lot costs a lot. Let's see;
Dim ITEMS as String = "'" & CheckListBox.Item(0) & "'"
For int i = 1 To CheckListBox.Count - 1
ITEMS = ITEMS & "'" & CheckListBox(i) & "'"
Next
Private CheckListBox As New CheckedListBox
Sub method1()
Dim sb = New System.Text.StringBuilder()
For Each checkedThingy In CheckListBox.CheckedItems
sb.AppendFormat("'{0}',", checkedThingy)
Next
Dim ITEMS As String = sb.ToString()
End Sub
Arun Peswani wrote: How do I need to convert the selected items which can be used in above query?
Ideally, we'd have a list of the keys (Id's) coming from that selection, not words. You're selecting on a varchar field?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi Eddy,
Thanks for nice replies
Though I am not able to post my replies in a format the way you do but I am trying to explain as much as I can.
1) All my columns are either 'int' or 'varchar', moreover unfortunately there are no indexes defined as well , perhaps I should try if it helps improving the speed.
2) String builder looks better for sure and I am going to try it today.
Table1 has details of Suppliers (Column1 = Supplier ID, int, not null and no index),
Table2 has details of Products (Column1 = Product ID, int, not null and no index),
Table3 is PurchaseEntry table which has details of all the products' purchases and joins other two tables on their ID columns as per my query above.
Time is not a constrain for me as this is my own project, so I have enough time
You may reply as per convenience with your valuable suggestions.
Thanks a lot for all your help so far
arun_peswani@yahoo.co.in
|
|
|
|
|
Arun Peswani wrote: 1) All my columns are either 'int' or 'varchar', moreover unfortunately there are no indexes defined as well , perhaps I should try if it helps improving the speed. That will indeed improve performance. The keys need one, and the To- and From-date might help too.
Arun Peswani wrote: 2) String builder looks better for sure and I am going to try it today. I expect it to help, but optimizing the database will have a larger impact.
Having integers as the primary key is sweet
Theoretically, one could save the selection as a datatable; you now have an ITEMS string containing "11, 17, 321, 205", right? If you'd insert those into a temporary table (Table0), then you could use another join to limit your selection by changing the Sql statement;
select Table1.Column2, Table2.Column2, Table3.Column2 from Table2
full outer join Table1 on Table1.Column1 = Table2.Column1
full outer join Table3 on Table3.Column1 = Table2.Column2
JOIN Table0 on Table1.Column1 = Table0.Column0
where Table2.Dates between 'FromDate' and 'ToDate'
Order By Table1.Column2 The products table, does it hold a column called 'category' or something similar?
Are you familiar with the stopwatch class? If yes, try timing the code that does the loading, the part doing the actual query, and the part that shows it on screen. We can look into optimizing that last part once these two are done
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
Arun Peswani wrote: 1) I tried creating ITEMS string as you explained however it required a little extra work on formatting but all went well.
Arun Peswani wrote: 2) I liked the concept of "JOIN Table0 on Table1.Column1 = Table0.Column0" I never thought about this one Dumping integers might be faster than creating a lot of parameters, but then again, there won't be much to gain here - people rarely select 50 000 items by placing checkboxes. Perhaps it'd be wiser to not optimize this bit and go for readability.
Arun Peswani wrote: 3) My product table doesn't have category column however I have a separate table for product categories. My PURCHASE table uses data from Product, Supplier and Category. Wouldn't it be more efficient to have the products grouped by category when you ask for a selection? Expand a category before it shows the products it contains?
Arun Peswani wrote: 4) Moreover, the stopwatch class which you are asking about, is this similar to progress-bar? If yes then I have used it but if it is something different then please give me some hint. What is it and how to use it. A stopwatch is a dandy way of timing your code;
Stopwatch sw = new Stopwatch();
sw.Start();
[..bunch of code..].sw.Stop();
Debugger.WriteLine("Your code took {0} ms", sw.ElapsedMilliseconds); It's on MSDN[^] - there's an example near the bottom of the page.
Arun Peswani wrote: You are truly superb Nah, just trying to help in the same way others helped me. That's what most of us do here, and sometimes there even goes a little effort into an answer. That'd also be the reason for the reaction on a duplicate question; have I been 'wasting' time explaining something that Dave already explained five minutes ago?
You owe the Q&A guys a
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Select * ....... very bad
|
|
|
|
|
Adding 100,000 items to a list is just bad design, and there is nothing you can do to improve its speed. It is also extermely annoying for a user trying to navigate such a list. You need to find ways of categorising your data so you have smaller lists and create them dynamically in response to the user's actions.
|
|
|
|
|
Reported as "abusive" for your responses to me and others in the copy of this post over in Q&A.
|
|
|
|
|
I am trying to populate a ComboBox thru my business and data layer and it's not working for some reason. The data is there but the combobox isn't being populated. I'm using a Class that controls all passing of data from my UI to my Business Layer to my Data Layer. In my Class, I have a ComboBox control along with other controls and Data Types.
Below is the code that pertains to this functionality.
Private Sub frmEquipmentSetup_Load(sender As Object, e As EventArgs) Handles MyBase.Load
LoadIntervalComboBox()
.
.
.
End Sub
Private Sub LoadIntervalComboBox()
Try
InitializeErrorClass(EH)
BL.LoadIntervalComboBox(EH)
If EH.ErrorMessage = "" Then
cmbCDInterval = EH.Combo
End If
EH.ErrorMessage = ""
Catch ex As Exception
EH.ErrorMessage = "LoadIntervalComboBox() - " & ex.Message & "~E"
End Try
EH.ProcessMessages(Me, sbr, EH.ErrorMessage)
End Sub
Public Sub InitializeErrorClass(ByRef EH As ErrorHandling.ErrorHandler)
Try
EH.DataSet = New DataSet
EH.DataTable = New DataTable
EH.ErrorMessage = ""
EH.Character = ""
EH.Bool = False
EH.Number = 0
EH.Combo = New ComboBox
EH.Exception = Nothing
Catch ex As Exception
EH.ErrorMessage = "cmbInitializeErrorClass() - " & ex.Message & "~E"
End Try
End Sub
Public Function LoadIntervalComboBox(ByRef EH As ErrorHandling.ErrorHandler)
Try
Dim strFields As String = "interval_ID,interval"
Dim strTblID As String = "LKUP_CalInterval"
Dim strCriteria As String = "active <> 0"
Dim strOrderBy As String = ""
DL.GetData(strTblID, strFields, strCriteria, strOrderBy, EH)
If EH.DataSet.Tables(0).Rows.Count > 0 Then
EH.Combo.DataSource = Nothing
EH.Combo.DataSource = EH.DataSet.Tables(0)
EH.Combo.DisplayMember = "interval"
EH.Combo.ValueMember = "interval_ID"
EH.Combo.SelectedIndex = -1
End If
Catch ex As Exception
EH.ErrorMessage = "LoadIntervalComboBox() - " & ex.Message & "~E"
End Try
Return EH
End Function
Imports System.Windows.Forms
Public Class ErrorHandler
Public gTimeOut As Integer = 0
Public gSplashTimer As Integer = 0
Public gMessageTimer As Integer = 0
Public gblnHalt As Boolean = False
Public gblnEscape As Boolean = False
Public gblnPause As Boolean = False
Public gblnMessageTimer As Boolean = False
Public gblnFlashMessage As Boolean = False
Public gblnHideMenuIcons As Boolean = False
Public WithEvents tmrMessage As New Timer
Public gSBR As New StatusStrip
Public Property ErrorMessage As String
Public Property Number As Integer
Public Property Character As String
Public Property DataTable As DataTable
Public Property DataSet As DataSet
Public Property Exception As Exception
Public Property Bool As Boolean
Public Property Miscellaneous As String
Public Combo As ComboBox
.
.
.
Blake McKenna
modified 18-Feb-14 18:23pm.
|
|
|
|
|
Where is the form created? And where is the ErrorHandler class instantiated?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Dear All,
I have a spreadsheet with a list of names and e-mail addresses. I have created a code that when clicked will open my default mail program. I can then click send. Start of the code is below (the full code works but I must manually click each time)
Quote: ="mailto:"&E2&"?subject="&B2&
I would like to know if it's possible to create a VBA code or similar within NeoOffice that will automatically load up these 30 hyperlinks so I don't have to click 30 times.
Please help!
|
|
|
|
|
If the cells containing the links are in sequence then you can just code a simple loop which repeats the same code through all the relevant cells.
|
|
|
|
|
Hey Richard! It's great to hear from you! Thank you for the reply.
I probably should have added - I'm a complete beginner to Visual Basic and have never tried coding anything. Is there a tutorial for how to start coding / implement the code you suggested?
Yes all the links are in order. Any help would be amazing.
|
|
|
|
|
Member 10603840 wrote: I'm a complete beginner to Visual Basic But your question is about using VBA which is a slightly different beast. If you want to learn it then you can use the help system in Microsoft Office, or use Google to find tutorials. I know nothing abot NeoOffice, so I cannot comment further.
|
|
|
|
|
How to declare a global variable and access it's value anywhere..
|
|
|
|
|
|
A couple of ways
1. use a module not my favourite suggestion
public module GlobalVariables
public foo as string
end module
2. shared class with shared variables
public shared class FooBar
public shared SomeValue as integer =1
end class
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
I have one using excel 2000 vba.now we upgrade use window 8 64 bit and excel 2010 so
when I add control listview, it show message "Library not registered"
anybody has idea?
Best regards
|
|
|
|
|
Samoeurn wrote: I have one using excel 2000 One what?
Samoeurn wrote: when I add control listview, it show message "Library not registered" What library, where does the message appear, what line of code ... ?
Please be clear and specific with the details of your problem. Remember, we cannot see your screen.
Veni, vidi, abiit domum
|
|
|
|
|
I have One VBAProject that is excel add in. If I use window 8 x86, it works perfectly but now our company upgrade using window x64.it has one problem,one control is not support "Listview" and shows error message below:
1.Library not registered
2.Compile error in hidden module: frmChart
this error commonly occurs when code is incompatible with the version,platform, or architecure
of this application. Click "help" for information on how to correct this error.
please kindly help appreciate
Thanks
|
|
|
|
|
Samoeurn wrote: code is incompatible with the version,platform, or architecure Rebuild your addin on the new platform.
Veni, vidi, abiit domum
|
|
|
|
|
Samoeurn wrote: anybody has idea?
You've referenced a COM-library in your sheet that's 32-bit. It does not get loaded in the 64-bit version of Excel. Then it crashes.
Solution; get a 64 bit version of the library, or switch back to 32-bit Office.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Top of each form, modules and classes that have references. Fill in the blanks.
#If Win64 Then
put your 64 bit library references here
#ElseIf Win32 Then
put your 32 bit library references here
#Else
Msgbox "You are either trying to run this from a computer " _
"you found at a yard sale or you bought an Apple"
#End If
|
|
|
|
|
Send a Text Message to a Cell Phone from a VB.NET Application
|
|
|
|