Transfering Items between 2 Listviews - Visual Basic Discussion Boards

Would using p/invoke to open the Excel sheet as a COM object work as an alternate option for you?

I'm not certain, never used that before, so I am googleing it as type this;

Maybe a bit of background on what I'm doing will help

Every day our phone system gives us an excel spreadsheet of over 2000 rows, other then the aforementioned un-flat data in the 7 lines, the sheet contains;

this wont show up properly here but

-A small header

Workgroup: WG-NBA-Montreal (continued)
Username: username (Active)
Agent Name: Testing, The fish

-Titles for every column:

" Logged-in Time Frame or Reason Unavailable""Logged-in Time or Time Unavailable" | Begin Time | End Time | Originated

Logged-in Time Frame 11/5/2007 12:04:17 11/5/2007 20:25:54 08:21:37 23

Pre-Shift Unavailable 11/5/2007 12:04:17 11/5/2007 12:08:30 00:04:13

Initializing Unavailable 11/5/2007 12:04:17 11/5/2007 12:04:17 00:00:00

Break Unavailable 11/5/2007 14:44:15 11/5/2007 14:44:15 00:00:00

-several more rows for one agent

- then the header portion for the next agent and row of data for him, and so on and so forth.

This is from the sample I was given so the data is fake. but the format is right

anyway what my application does is load the sheet into a table and then I parse for each agent the various states they are in during the day and build a report that says when they are late, when they take breaks that are too short or too long based on bounds that are configured.

right now this is done manually so I'm just automating the process to shave a few hours of aggravation for the person who has to read those 2000 lines

everything was working fine with the sample data, but once those 7 lines of header came in everything got scrapped and most of what was there before is now null.

Since you will be opening the Excel file not as a recordset, but as a spreadsheet, you will be able to write your parser anyway you want, so I imagine your requirement should not be so difficult to implement.

Hope you got the information you needed on p/invoke?

It may just be my Google skills that are sub par but all I'm finding is books for sale and how to insert a spreadsheet as an object in a form.

I guess I should have also mentioned that you can find this as Microsoft Office Interop too... sorry Frown | :(

Does this help? http://www.codeproject.com/KB/office/office_automation.aspx[^]

I know it talks about exporting to Excel (and is in C#), but the code to open an Excel file is present and a few of the Excel cell and range operations are also demonstrated...

There are also a few other Interop articles on CP, though I haven't actually read through those.

Hope this helps Smile | :)

how do you delete empty elements ("") from an 1 D array? I have mixture of numbers (0-24) and "" characters. I want to strip out all the elements with the " char.

Thanks

EM

You can't delete any elements from an array. You can't change the size of an array once it's created.

You can create a list and add the items to it, like:

Dim result As New List(Of String)()
For Each s As String in someArray
If s.Length > 0 Then result.Add(s)
Next

Experience is the sum of all the mistakes you have done.

I have a form with an imagecontrol (2 icons) and 2 listviews. I have loaded file names and icons into the first listview control. I have set the tag property of the item in the first listview to the directory path of the file.

I need to allow the user to pick the files he/she wants from the first listview and add them to the second listview via a button. When I use the code below the icon does not appear. Furthermore I have to use the .Text property of the NewItem variable in order to add it to the second listview. My goal here is to be able to add the item to the second listview and I also need to add the tag property of the item in focus from the first lisview to the tag property of the item in the second listview.

Once the files have all been added to the second listview I load them into an array via another button. I am noticing that as I loop through the items in the secondlistview that although the variable X shows the files, both the name and tag properties are "".

Is this because I used the .TEXT property as stated above? How do I work around this?

Private Sub Button10_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button10.Click
Dim NewItem As ListViewItem
NewItem = listView1.FocusedItem
NewItem.ImageIndex = 2
listView2.Items.Add(NewItem.Text)
NewItem.Tag = listView1.FocusedItem.Tag 'Transfer Tag of item in listview1 to item in listview2
listView2.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize)
End Sub

"***************************************************************************************
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

'Load selected files into array
Dim Y As Integer
Dim X As ListViewItem

Dim Counter As Double
Y = listView2.Items.Count

ReDim FileArray(Y-1, 1)

Counter = 0
For Each X In listView2.Items
FileArray(Counter, 0) = X.Name
FileArray(Counter, 1) = X.Tag
Counter = Counter + 1
Next
End Sub

To move items from one ListView to another, you don't have to create a new ListViewItem. Just remove it from one ListView and add it to the other:

For Each item As ListViewItem In SourceFilesListView.SelectedItems()
    SourceFilesListView.Items.Remove(item)
    SelectedFilesListView.Items.Add(item)
Next

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Hello,

In pascal, it's possible to create a record of different data types, like:

coordinate = record <br />
    x: integer; <br />
    y: integer; <br />
end;

How could I create one like that in vb.net?

Thankful,
Vykintas

Create either a Structure[^] or Class[^].

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Thanks! Structure is just what I was looking for Smile | :)

Vykintas

Ro[CK]eT wrote:
Thanks! Structure is just what I was looking for

Be careful. A structure is a value type. That means it is copied each time you assign it to something (like a parameter in a method call). If the structure is large this could become a real performance hit.

Upcoming FREE developer events:
* Developer Day Scotland

Recent blog posts:
* Follow up on hiring a software developer
* The Value of Smaller Methods

My website | blog

Good afternoon all

i have recently upgraded to SQL2005 from 2000, am using vb2003. when i develop am no longer using a wizard, especially on my database development.So now i have dropped a sql adapter on my form and follow the wizard as usual and let the adapter to generate the selected command, Updated command and deleted Command, but the adapter reports the Errors for all commands excluding the select, most of the time this will happen when the table has no primary key defined, but in my table, it is defined.

What could be the Problem.

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

You cannot use any Update, Delete, or Insert commands on a DataAdapter that is tied to a result set with no Primary Key. Your table MUST have a primary key AND the SQL Select command you gave MUST return the primary key field in it's result set in order for the other commands to work.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

hi Dave,
what you are saying its true, but my query is returning a Primary key. here is the code in my Adapter.

SELECT Primary_ID, Boundary_area, Attrib_Code, Lis_Key, Func_Key, Actual_Extent, Improved_Value, Site_Value, Purchase_price, Purchase_date, prop_id, confidence_score, fin_ls_prediction, new_lis_key, new_func_key, Tshwane_Prediction, matchflag, source, flagtype, Property_id FROM Property_mass WHERE (SUBSTRING(Lis_Key, 1, 4) = @Lis_key)

And the Following is my Structure of my table.

USE [Tshwane_Valuations]
GO
/****** Object:  Table [dbo].[Property_mass]    Script Date: 01/02/2008 10:06:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Property_mass](
	[Primary_ID] [int] NOT NULL,
	[Property_id] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Boundary_area] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Attrib_Code] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Lis_Key] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Func_Key] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Roll_Remark] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[New_ADJ_Extent_1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[New_ADJ_Extent_2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Actual_Extent] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Improved_Value] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Site_Value] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Density] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[New_Net_Usable_Extent] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[New_Total_Land_Factor] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[New_Points_1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[New_Points_2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Purchase_price] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Purchase_date] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[source] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[owner_type] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[prop_id] [float] NULL,
	[matchflag] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[confidence_score] [float] NULL,
	[Tshwane_Prediction] [float] NULL,
	[fin_ls_prediction] [float] NULL,
	[new_lis_key] [nvarchar](174) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[new_func_key] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[flagtype] [nvarchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SSMA_TimeStamp] [timestamp] NOT NULL,
	[Locked] [bit] NULL,
	[Lockedby] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Updatedby] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[UpdateDT] [datetime] NULL,
 CONSTRAINT [Property_mass$PrimaryKey] PRIMARY KEY CLUSTERED 
(
	[Primary_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

.

Thanks Dave

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Then you're going to have to post the exact error message you get and what the code setting up the DataAdapter looks like.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

hi Dave here is my Error

http://nedsecurity.3host.biz/Adapter.htm[^]

thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

That's hardly a descriptive error, but...

The reason it fails is because of the parameterized WHERE clause. The SqlCommandBuilder cannot use the WHERE clause you've specified. You'll have to generate the remaining commands yourself, leaving out the parameterized WHERE clause and specifying just the Primary Key in the WHERE clause and it should work. It also doesn't work with SELECT commands that return multiple result sets and/or gets data from multiple tables at once.

This is why I don't use the Wizards to do my data layers.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Thanks, i need to learn doing things without a wizard, i have grown .

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Guys, I need an idea about implementing multithreading in vb.net.

Here's the scenario:

I've to bulk insert multiple (100's) of files into SQL Server. The list of files are stored in a table and there's an SSIS package that loops through the resultset and bulkloads each file. The downside to this approach is at any given time only one file is being loaded. I'd like to introduce parallelism into this approach. ( I can have multiple loops, but it breaks the workflow)
I redesigned the package to load multiple files into the db, but the structure is rigid. For example, if in a batch of 5 files loading simultaneously, if the first file were smaller and the rest very big, one connection would be idle until all have finished to exit out of the task.
To alleviate this, I thought of an approach that uses threading.

What I need:
An idea that uses a multithreading wherein there's a parent thread that loops through the list of files and assigns each file to child threads which do the bulk load. As soon as a child thread is done, it must notify the parent to give it the next file to load. The number if child threads must be user configurable.

Here are the questions:

1) Is the idea of any worth, if not, is there a better way of doing it?
2) I'm trying to use the ThreadPool class and callback delegates, is this correct? (I read from Marc's article that ThreadPool is not worth it's salt)
3) Would the Async callback method of the SQLCommand work?(I kinda figured that this is not feasible, since the callback delegate does not notify the success or failure of the query)
4) Any good articles that can goad me in the correct direction.

Requirement:
The VB.NET module must execute from within an SSIS Script task.
And it has to be in VB.NET, C# is not supported in SSIS scripts.

Hope I've been clear enough.

Thanks.

SG
Cause is effect concealed. Effect is cause revealed.

modified on Wednesday, January 02, 2008 9:16:45 AM

SimulationofSai wrote:
I'd like to introduce parallelism into this approach.

First question: Why? What's the point of doing multiple threads? One of the biggest misconceptions about multithreading is that is make the process run faster. In general, no, it doesn't. Depending on where the processing bottlenecks are, you might see a small performance benefit, you might not.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Because in a server with multiple hard drives and multiple CPU's, and with the SQL Server data files located in different volumes, the I/O might get divided across different processors.
I particularly refer to I/O because the most intensive operation in a BULKLOAD is I/O.

Executing 5 BULK LOAD commands in a single SQL connection uses only one processor, but splitting it across multiple connections actually fan out to different processors and cut short the load time. I know that it can only run as fast as the fastest I/O thats taking place.

Dave Kreskowiak wrote:
Depending on where the processing bottlenecks are, you might see a small performance benefit, you might not.

When I'm loading more than 250 files of 500MB each, I'd be willing to take any time gain thats available. A difference of 2 minutes gain for a couple of files might mean 2-4 hours gain for all my files.

SG
Cause is effect concealed. Effect is cause revealed.

Fair enough.

It's possible to do with threads, but I'd probably think about managing my own thread pool for this, keeping the number of threads down to the number of processors in the box. I'd even consider "numProcessors - 1" given the length of time this operation takes. This would be to keep the SQL server free to do its work for other clients.

Now, I'm no expert in SSIS for sure. I have precious little experience with it. (I think this question might be geared more for the SQL forum and not the VB.NET forum.) But, from what I've read, I'd probably create a package that works with it's own connection and file, then terminates. A second, "master" package would then launch your VB.NET code, which organizes the files to be imported, manages it's own thread pool, and launches instances of a class that executes the first SSIS package.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Hi Everyone,

I am writing a vb.net window application. I want to add some rows into datatable.
The problem I am having is when i first add a new row to the datatable, I get very
slow performance (20-30 second delay). The add to datatable works but I
get slow performance. Any record adds or deletes after this first add
are fast. I get slow performance on the first add. I have no idea what
is causing this slow performance. I cannot figure out what is
delaying the add. Any thoughts on where I might be going wrong?
Thanks in advanced.

Here is my codes:-

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
If myTable Is Nothing Then InitGrid()

Dim Row As DataRow
Try

For Each Row In myTable.Rows
If Row.Item("HBarCode").ToString = Me.txtHBarCode.Text Then
MessageBox.Show("Housing Serial Number already scanned.Duplicated.")
txtHBarCode.Focus()
Exit Sub
End If

If Row.Item("EMIBarCode").ToString = Me.txtEMIBarCode.Text Then
MessageBox.Show("EMI Serial Number already scanned.Duplicated.")
txtEMIBarCode.Focus()
Exit Sub
End If
Next

Row = myTable.NewRow()
Me.txtCounter.Text = myTable.Rows.Count + 1
Row.Item("HBarCode") = Me.txtHBarCode.Text
Row.Item("EMIBarCode") = Me.txtEMIBarCode.Text

'to check any colume without data, then only add row at myTable
If Me.txtHBarCode.Text.Trim.Length = 0 Then
MessageBox.Show("Please Scan in Housing BarCode.")
Me.txtHBarCode.Focus()
Exit Sub
End If

If Me.txtEMIBarCode.Text.Trim.Length = 0 Then
MessageBox.Show("Please Scan in EMI BarCode.")
Me.txtEMIBarCode.Focus()
Exit Sub
End If

myTable.Rows.Add(Row)
Me.txtHBarCode.Text = ""
Me.txtEMIBarCode.Text = ""
Me.txtHBarCode.Focus()

If Me.txtCounter.Text = Me.txtQty.Text Then
txtQty.Enabled = False
btnComplete.Enabled = True
txtHBarCode.Enabled = False
txtEMIBarCode.Enabled = False
End If

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

dgResult.Update()

End Sub

Public Sub InitGrid()

myTable = New DataTable("LotNumber")

Dim HBarCode As DataColumn = New DataColumn("HBarCode")
HBarCode.DataType = System.Type.GetType("System.String")
myTable.Columns.Add(HBarCode)

Dim EMIBarCode As DataColumn = New DataColumn("EMIBarCode")
EMIBarCode.DataType = System.Type.GetType("System.String")
myTable.Columns.Add(EMIBarCode)

ds = New DataSet
ds.Tables.Add(myTable)
dgResult.SetDataBinding(ds, "LotNumber")

End Sub

thanks,eunice