|
Hi everyone,
would like to import data from an excel table into a SQL table. The problem is that the data from only one Excel table has to be imported into 3 different tables of the same sql-database. So the main problem in my eyes is to associate every excelcolumn to the matching column of one of the trhee diffferent sql-tables. Of course this has to be done manually by the user. I could immagine two listboxes on a form. First-one for the excelcolumns (source) and anotherone for the sqlcolumns(destination) A third listbox could display the columns the user put together.
I have already managed to get the excel-data displayed in a datagridview without opening the excelfile with this code.
oAdapter = New System.Data.OleDb.OleDbDataAdapter( _
"SELECT * FROM [" & strSheetName & "]", OleDbExcelConn)
thanks in advance,
Jeiss
|
|
|
|
|
Why not select * to a data set, get the column names and do it yourself if you know where they should map up to? You could use LINQ or call a bunch of queries to load the data. Alternatively create a stored procedure that selects * into, processes it, and then frees the temporary table if you use one.
|
|
|
|
|
Hi Ted2102,
i suppose that your suggested solutions would do for an excel file with known columnnames and a constant number of columns. But that's not my case. The files i want to be able to import will not always have the same number of columns and their columnames will change from file to file. The only thing these files have in common is that they will contain contact informantions, like name, address, telephonenumber... a.s.o. That's why i wanted to be able to assign the columns of the Excel-file manually (in the listboxes of the form) to the ones of my SQL database.
I'm sorry for your efforts in helping me, but in the mean time the situation has changed and i have found a nice code snippet which does the kind of work i need.
As a beginner in VB i would like to progress in programming. And that's why i think it would be more important to practice a little bit with the technics used in this snippet. So my new question is now: Does anybody have a link to a good tutorial about learning "List(of Type)" and about "LINQ-queries" for beginners?
Any link or code-snippet would be appreciated.
Thanks for your help
|
|
|
|
|
Unfortunately, I have not found an ideal reference yet. I only found some documentation on a Microsoft website so far.
|
|
|
|
|
Hi Ted2102,
never mind. The documentation from Microsoft is not that bad. But there are not really enough easy code examples. They tend to explain everything only once, showing only one example. All that i can tell by now is that working with linq seems to be much more efficient.
By the way i discovered a pretty well-filled collection (not a collection in the Visual basic sense) in a separate CodeProject Forum.
Last but not least, don't bother about the continuously changing name under my messages. I only experienced little logon problems.... But everything should be fine now.
Anyway, thanks you for your help.
|
|
|
|
|
|
Ask this in the ASP.NET forum. You're question is specific to the functionality of the GridView control, not VB.NET.
|
|
|
|
|
Thanks I wasn't sure which one to ask in.
Carolyn
If you can’t have fun at work, then why go to work?
|
|
|
|
|
I have a form that displays a list of records in a DataGridView. The recordset in question is about 9,000 records. I know that is a lot but is required by the application.
The problem I am experiencing is the form closing. I call the dispose methods of some class level variables which slows the close down quite a bit. I have seen it take anywhere from 10 seconds to 2+ minutes. This is a hinderence to the users of the application. I have written some Debug.Writeline() statements with timestamps to see how long each dispose call is taking. The slowdown is on my unfiltered datatables which are bound to the DGV combobox columns. A filtered version is bound to each individual cell that is edited but after the edit the original unfiltered datatable is bound to the cell again. The unfiltered tables only contain 20 or so records but are taking 8+ seconds minimum to clear and dispose compared to the milliseconds it is taking all of the other tables.
Here is my current iteration of the close (please note that the original call was only to dispose of the _Utilities object, I have added the other calls to try tracking down the issue):
uxRecords.Clear()
uxRecordsBindingSource.DataSource = Nothing
uxRecordDataGrid.DataSource = Nothing
uxRecordDataGrid.Rows.Clear()
CType(uxRecordDataGrid.Columns("uxVersionNameGrid"), DataGridViewComboBoxColumn).DataSource = Nothing
For Each versionNameCell As DataGridViewComboBoxCell In CType(uxRecordDataGrid.Columns("uxVersionNameGrid"), DataGridViewComboBoxColumn).Items
versionNameCell.DataSource = Nothing
Next
CType(uxRecordDataGrid.Columns("uxDepartmentNameGrid"), DataGridViewComboBoxColumn).DataSource = Nothing
For Each departmentNameCell As DataGridViewComboBoxCell In CType(uxRecordDataGrid.Columns("uxDepartmentNameGrid"), DataGridViewComboBoxColumn).Items
departmentNameCell.DataSource = Nothing
Next
CType(uxRecordDataGrid.Columns("uxSizeGrid"), DataGridViewComboBoxColumn).DataSource = Nothing
For Each sizeCell As DataGridViewComboBoxCell In CType(uxRecordDataGrid.Columns("uxSizeGrid"), DataGridViewComboBoxColumn).Items
sizeCell .DataSource = Nothing
Next
_Utilities.Dispose()
The _Utilities.Dispose call disposes of each table and that is where the slowdown is. Everything up to that call occurs within the same second.
CleaKO
"Now, a man would have opened both gates, driven through and not bothered to close either gate." - Marc Clifton (The Lounge)
|
|
|
|
|
Why are you calling Dispose on your DataTables? You don't have to unless you've created your own DataTable objects and there is stuff that needs to be freed/done before your DataTable object is destroyed.
The tables will get Destroyed/Collected by the GC when they go out of scope automatically. Unless you have a valid reason to, by default, there is no need to call Dipose on DataTables.
|
|
|
|
|
The DataTables that I am disposing of are instantiated at the class level for specific data. It is not the DataSet bound DataTables that I am disposing of, it is those individual datatables. Didnt I have a discussion with you a few years ago where you said to dispose of anything that implements IDisposable?
CleaKO
"Now, a man would have opened both gates, driven through and not bothered to close either gate." - Marc Clifton (The Lounge)
|
|
|
|
|
I can't remember what I had for lunch yesterday, let alone who I talked to about what a few years ago.
Probably so, though, my position has changed on various aspects of the .NET Framework over the years. It used to be that it was suggested you called Dispose on anything implementing IDisposable, but now, that's changed. You should know WHY you're calling Dispose on an object and understand that an implementation of it may only be there to let you override it in your own inherited classes.
|
|
|
|
|
Dave Kreskowiak wrote: I can't remember what I had for lunch yesterday
Turkey leftovers with fried onion rings on the side?
Dave Kreskowiak wrote: it was suggested you called Dispose on anything implementing IDisposable, but now, that's changed.
That is horrible. It used to be simple: it is available, there may or may not be unmanaged resources involved (and that may change from one version to the next), so call Dispose().
As to the original question: wouldn't it be wise, and maybe even help, to remove the data binding first?
|
|
|
|
|
Luc Pattyn wrote: Turkey leftovers with fried onion rings on the side?
I hate onion rings... -bleck-
Luc Pattyn wrote: As to the original question: wouldn't it be wise, and maybe even help, to remove the data binding first?
Maybe. The only way to tell would be to try it. I'm just not that into testing it myself this week.
|
|
|
|
|
I am working on that path now. I tried simply setting the DataSource = Nothing for the columns, datagridview, and cells within the combobox columns. Apparently that isnt working good enough because the dispose is forcing the grid to go through and remove bindings which is raising events which is slowing down the dispose.
CleaKO
"Now, a man would have opened both gates, driven through and not bothered to close either gate." - Marc Clifton (The Lounge)
|
|
|
|
|
Hi
I have created a .NET application with a custom installer, that all works fine.
After the installation process I need to access a file that is located inside the folder where the MSI installer / setup is located.
The problem I have is I cannot work out how to get the path for the MSI installer / setup.
Can anybody please help and advice me the best way to get this path while I am still inside the "Commmited" of the installer
vb.net or C# either will be fine.
Thanks
|
|
|
|
|
Your installer should not be accessing files from the folder that the .MSI was launched from. Any files it needs should be inside the .MSI itself.
Windows Installer, AFAIK, doesn't have a facility to tell the code inside an .MSI where it was launched from.
|
|
|
|
|
I beg to differ. We often use Active Directory to roll out updates and installs company-wide. It is a common requirement to pick up some client specific customisation data which they supply in an xml file accompanying the msi. When installing the same update across multiple clients it would be impractical to do a specific build for each client containing their special data to roll out to the end users.
|
|
|
|
|
Fair enough. We just never do that around here.
In our apps, we have client specific information in the users metadata in our databases. No need to send an XML file with each install.
|
|
|
|
|
I have to say the only bit we let them customise at this point is the webservice URL of their server. We said to them, just tell your end users what to type in when it is prompted on first run. Not an accepted, but as they say, 'the client is always right', at least until they pay the invoice.
|
|
|
|
|
Thankfully, just about all of my work in internal. I don't have very many clients on the outside and don't have to customize anything for a client before it goes out the door.
|
|
|
|
|
you can use the SourceDir property of the Windows Installer.
You can pass this info as a CustomAction in your deployment project : /SrcDir="[SourceDir]\"
You can then use it in c# with : this.Context.Parameters["SrcDir"]
or in vb with : Me.Context.Parameters("SrcDir")
|
|
|
|
|
Thank you very much, worked great
|
|
|
|
|
Hello people i have an ftp question in my application for employe monitoring i capture screen and then i upload it to server every 1 seccond for now i use this:
Uploader:
<br />
On Error Resume Next<br />
FileUploadedSuccessfuly = False<br />
Dim local_file As String = whatfile<br />
Dim remote_file As String = WORKSERVER & "/" & System.IO.Path.GetFileName(whatfile).ToString<br />
Dim cls_request As System.Net.FtpWebRequest = DirectCast(System.Net.WebRequest.Create(remote_file), System.Net.FtpWebRequest)<br />
Dim user_name As String = "root"<br />
Dim password As String = "administrator"<br />
<br />
'Establish credentials for logging into ftp site<br />
cls_request.Credentials = New System.Net.NetworkCredential(user_name, password)<br />
<br />
'Set properties<br />
cls_request.KeepAlive = False<br />
cls_request.Proxy = Nothing<br />
cls_request.Method = System.Net.WebRequestMethods.Ftp.UploadFile<br />
cls_request.UseBinary = True<br />
<br />
'Read in the file<br />
Dim b_file() As Byte = System.IO.File.ReadAllBytes(local_file)<br />
<br />
'Upload the file<br />
<br />
Dim cls_stream As System.IO.Stream = cls_request.GetRequestStream()<br />
cls_stream.Write(b_file, 0, b_file.Length)<br />
cls_stream.Close()<br />
cls_stream.Dispose()<br />
FileUploadedSuccessfuly = True<br />
Exit Sub<br />
And Timer:
<br />
If FileUploadedSuccessfuly = True Then<br />
FileUploadedSuccessfuly = False<br />
CatchScreen()<br />
UPfile("C:\EMON\bin\tmp_" + THISPCUsername + ".jpg")<br />
End If<br />
so timer every seccond check if last file was uploaded and if it is then it grabs new picture and overwrite it and again uploading but problem is that every seccond it connects to server again from begining with pass and user name i want that it connects when i start application and stay connected and uploads same file every seccond from timer but waits until last one is uploaded successfully and if connection is droped that it reconects! thanks i tried to do that but i didnt succed for now so if u can help thanks!!!
|
|
|
|
|
First, your software is illegal if used in the United States without first warning the user you are watching them.
Second, you're design is entirely wrong for this. You're saturating the network and the FTP server with images constantly being copied to the FTP server. You're also assuming that it takes 1 second for the app to connect to the FTP server and upload the picture. This will simply not be the case all the time.
You really should be redesigning this to only capture the screens that you need to look at when you need to look at them AND not use FTP for this task. Your central monitoring app should be getting direct streams from something like VNC to minimize traffic on the network.
|
|
|
|