|
I reinstalled them but the program will not run.
|
|
|
|
|
|
I think you have to do this in your forms so the platform is recognized. Remember that SYSWOW is also going to be a location that windows will decide to run in.
#If Win64 Then
#ElseIf Win32 Then
#Else
#End If
|
|
|
|
|
Hello VB Code people,
I'm having trouble finding the correct code that when I select or multi-select an item from a list box in a Word 2007 userform and then press a button. I want it to create a table in Word 2007 from the items that I've selected in the list box. The userform gets populated from Excel and it works fine. Below is the VB Code for populating the userform with Excel data. Any help is greatly appreciated. Thank you
Private Sub Userform_Initialize()
Dim strOffice As String
Dim i As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
strOffice = "Test"
Set db = OpenDatabase("C:Testdata.xlsx", False, False, "Excel 12.0; IMEX=1;")
Set rs = db.OpenRecordset("SELECT * FROM `Test`")
ListBox1.ColumnCount = rs.Fields.Count
With rs
.MoveLast
i = .RecordCount
.MoveFirst
End With
ListBox1.Column = rs.GetRows(i)
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
lbl_Exit:
Exit Sub
End Sub
|
|
|
|
|
Hello folks,
This issue has me baffled for a few days now. I have tried everything I can possibly think of but nothing seems to work.
In this project, which is just for practice purposes, I insert data to SQL via a small form of three TextBoxes. This works fine and the data is inserted fine. The data is then shown in ComboBox Dropdown where, by selecting a value, shows related content in three other textboxes. This all works fine also.
I can delete content from a third form, which has a DataGridView and by selecting a row and clicking a button, the data is removed from SQL and also the DataGrid updates itself immediately.
The issue I am having is the ComboBox on the main form "Form1" is not updated unless I close the application and reopen it again. I was told on another site that I would need to "Fill the Dataset again". But have no clue what they meant or how that is done.
I tried a few suggestions that I had found on a Google Search but alas, nothing works, such as Form1.Refresh, ComboBox1.Refresh etc.
Here is my code for deleting, which is in the DeleteEntry.vb form:
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim myconnect As New SqlClient.SqlConnection
myconnect.ConnectionString = "Data Source=.\INFLOWSQL;Initial Catalog=RioDiary;Integrated Security=True"
Dim mycommand As SqlClient.SqlCommand = New SqlClient.SqlCommand()
mycommand.Connection = myconnect
myconnect.Open()
Try
mycommand.CommandText = "DELETE FROM MyDiary WHERE Title = @Title"
mycommand.Parameters.Add("@Title", SqlDbType.VarChar)
For Each row As DataGridViewRow In DataGridView1.SelectedRows
mycommand.Parameters(0).Value = row.Cells(0).Value
mycommand.ExecuteNonQuery()
DataGridView1.Rows.Remove(row)
Next row
MsgBox("Data Successfully Deleted")
Catch ex As System.Data.SqlClient.SqlException
MsgBox(ex.Message)
End Try
ds = New DataSet()
da.SelectCommand = mycommand
da.Fill(ds)
mycommand.Dispose()
Me.Close()
myconnect.Close()
End Sub
And here is Form1:
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.MyDiaryTableAdapter.Fill(Me.RioDiaryDataSet5.MyDiary)
Dim con As New SqlConnection(" Data Source=.\INFLOWSQL;Initial Catalog=RioDiary;Integrated Security=True")
cmd.CommandText = "Select Title,DiaryContent,DiaryDate FROM MyDiary "
ComboBox1.Refresh()
Dim da As New SqlDataAdapter
da.SelectCommand = cmd
cmd.Connection = con
da.Fill(ds, "MyDiary")
con.Open()
ComboBox1.DataSource = ds.Tables(0)
ComboBox1.DisplayMember = "Title"
ComboBox1.ValueMember = "DiaryContent"
End Sub
Private Sub NewEntryToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles NewEntryToolStripMenuItem.Click
AddEntry.Show()
End Sub
Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
If (Not Me.ComboBox1.SelectedItem Is Nothing) Then
Dim SelectedItem = TryCast(ComboBox1.SelectedItem, DataRowView)
Me.TextBox1.Text = SelectedItem.Row("DiaryDate").ToString()
Me.TextBox2.Text = SelectedItem.Row("Title").ToString()
Me.TextBox3.Text = SelectedItem.Row("DiaryContent").ToString()
ComboBox1.Refresh()
End If
con.Close()
End Sub
Would anyone please help in shedding some light on this? I have been battling my brains and searching google with three days.
Thank you in advance.
|
|
|
|
|
Dan O'Riordan wrote: and searching google with three days.
You'll need to conquer the word "dataset[^]".
A dataset is a collection of in-memory DataTable[^]'s. The query that is executed returns data, and that's stored in that memory-table. You're telling the combobox where to find these values on the line below;
ComboBox1.DataSource = ds.Tables(0)
..and that needs to be filled anew when you want new values.
Dan O'Riordan wrote: The issue I am having is the ComboBox on the main form "Form1" is not updated unless I close the application and reopen it again.
Things in a combobox are usually snapshots of that moment; I wouldn't expect it's contents to change while I'm navigating. Would you?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi.
Thanks for that.
I placed
ComboBox1.DataSource = ds.Tables(0)
Into Form1.vb but it made no difference. Is there something else I should be doing?
Thanks.
|
|
|
|
|
Dan O'Riordan wrote: Is there something else I should be doing?
Yes; stop trial-and-error, and try to understand the code your going to modify.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Jee man. Thanks for your "help" Bless us all if we have to understand it before we can learn it.
|
|
|
|
|
I can help with explaining code, but I refuse to simply point out what you should change. Sorry, I can't help here.
Also looks like I should take a break from the forum, as these kinds of posts don't add anything valuable 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
"these kinds of posts don't add anything valuable "
True. But thanks anyway.
|
|
|
|
|
I have a UserControl (call it SpiffyCombo) that consists of a ComboBox and a Label. The SpiffyCombo has a property (call it "Code") that has TwoWay databinding specified for it.
My Form has a BindingSource and an associated ErrorProvider. A property of the BindingSource's DataSource is bound to the Code property SpiffyCombo.
In a normal control, when an error occurs on the DataSource's property the error icon would appear next to the control. I can't get this to happen with SpiffyCombo. Is there some sort of Interface I need in the it or something I need to override to receive the error from the ErrorProvider? I want to control how the error is displayed within SpiffyCombo.
Any suggestions are greatly appreciated.
|
|
|
|
|
Don't crosspost.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi,
I am using VB.Net for my program where I have designed a form.
On the left side, I have a list of 100,000+ Items which load in the CheckListBox control on the form load. On the right hand side, I have a ListView control which has multiple columns and shows values related to the Items selected in CheckListBox.
I have created a string variable called ITEMS like this:
Dim ITEMS as String = "'" & CheckListBox.Item(0) & "'"
For int i = 1 To CheckListBox.Count - 1
ITEMS = ITEMS & "'" & CheckListBox(i) & "'"
Next
This stores all the item names in my ITEMS string variable
Now, my Problem here is that when I use following query:
Select * from TableA where Item in(ITEMS)
This query runs for each of the selected items and takes a very long time (perhaps a couple of minutes) to display result in ListView control. I know, the reason here is that I have 100,000+ Items in my list which I am using in IN operator of SQL Query but I am not sure what alternate way I can use to reduce the output time for these many Items.
If there is any other work around to get quicker results, can anyone please help me?
arunpeswani@gmail.com
|
|
|
|
|
Arun Peswani wrote: If there is any other work around to get quicker results,
Fewer items in your CheckListBox, that amount is impossible to navigate. Check out the TreeView - you can add checkboxes there too, and it'd be easy to categorize.
There are two points you can optimize; creating the string that contains the selection, and executing the query. If you're going to build a large string, consider a StringBuilder.
Optimizing the query will take some experimenting; perhaps it is faster if you create select statements that are glued together with a UNION, or if you insert the keys you'd like to select into a temp-table and join against that.
..nearly forgot the most obvious; don't select every column, select what you actually use! You don't want to pull a large blob over the network if you're not using it
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks for reply dear.
1) I am not exactly using SELECT * FROM TABLE, Actually I have 3 tables to fetch the data from, and I am using OUTER JOINT as well to get the data from selected columns. I doubt if UNION will help me increasing the speed where I want to pull records of my 100,000 Items in my store. Can you share an example query with UNION for my issue?
2) I have never used StringBuilder. Will surely look at this and will give a shot if it is of some help in order to increase the speed.
3) I think TreeView will also give me the same slow speed because in this control also I will have to find the CHECKED items first and then run the query based on those Items.
Please advise if there is something else which can help me in this challenging situation of retrieving the details of 100,000+ items in my application.
I am wondering how does't it work for those professional Inventory applications available in market, how do they manage to maintain the speed for such bulk items' queries?
Any help in order to increase speed of this particular query will be a great help.
arun_peswani@yahoo.co.in
modified 20-Feb-14 0:31am.
|
|
|
|
|
Arun Peswani wrote: I am not exactly using SELECT * FROM TABLE
Then you'd need to check if that query can be optimized.
Arun Peswani wrote: Can you share an example query with UNION for my issue?
No. There are enough examples on the internet.
Arun Peswani wrote: <layer>I think TreeView will also give me the same slow speed because in this control also I will have to find the CHECKED items first and then run the query based on those Items.
You don't need to "find" the selected items; the CheckedListBox has a CheckedItem[^] property, and the treeview has a CheckedNodes[^] property. Both controls can tell you which items have been selected - you'll only need to convert that selection into something that your query accepts. That's the query with the joins that we did not see.
Also, the TreeNode does not have to be loaded completely; one could load everything for the current category, and not load any other (non-expanded) categories.
Arun Peswani wrote: Any help in order to increase speed of this particular query will be a great help.
You didn't post the query; if you're going to, I'd suggest using the database-forum.
Arun Peswani wrote: I am wondering how does't it work for those professional Inventory applications available in market, how do they manage to maintain the speed for such bulk items' queries?
They don't use a WinControl to show thousands of items and they don't fetch all items on a select.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
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 ![Beer | [beer]](https://codeproject.freetls.fastly.net/script/Forums/Images/beer.gif)
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.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
