|
The user saves an excel file to the system, then I grab that save directory path of that file. That much is done. I want to know how to open that excel file and send all the records (row by row or however) into a SQL Server table. I think I already have a connection established.
I don't want to use DTS, it should be done programmatically.
Thank you in advance for any help!
|
|
|
|
|
You're going to have to use the Excel Automation library to open Excel, load your sheet, and parse each line of your sheet and add it to the database yourself. You'll have to write the SQL statements, build the database command object and parameters, then assign the data from each cell to the appropriate parameter object, and finally execute it. You'll have to do this for each and every line of the Excel sheet you want to add to the database.
This[^] little article should get you started on Excel Automation. It does NOT cover any database code.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
That example would help me get from sql to populate an excel sheet, but I'm looking for the opposite order. I've got an excel file saved, now I can get the excel file to open. Now I just need to have maybe a loop go through taking each row from the xl file and shooting it into an existing sql table.
Thank you for your help.
|
|
|
|
|
Sorry i just read the title and its obvious that you are using vb.net but this should actually still work, i have it working in both, but this is a very cut down copy of what i've used as what i am doing is completely different
Not sure if this will help but if it is VB6 you are using it might so let me know, cheers
Private Sub Form_Load()
Dim oExcel As Object
Dim oWB As Object
Dim ows As Object
Dim oRS As New ADODB.Recordset
Dim i As Long
Dim newI As Long
'ammended
Dim oConn As New ADODB.Connection
oConn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=tempdb;Data Source=JETSTREAM"
oConn.Open
'Set yourself up a Table with all the fields required and fill a recordset
oRS.Open "SELECT * FROM TempTable", oConn, adOpenKeyset, adLockOptimistic
'Create yourself the excel application
Set oExcel = CreateObject("Excel.Application")
Set oWB = oExcel.Workbooks.Open(PathtoASpreadSheet)
Set ows = oWB.Worksheets("Sheet1")
'Loop through the first column until you hit the first blank cell
'This is a quick and dirty way there is probably a better way out there.
'ammended can't remember bloody overflow thing, also can't remember if i have the cells
'reference (i, A) round the right way.
For i = 1 To 65535
If ows.Cells(i, "A") = "" Then
newI = i
Exit For
End If
Next
'Then loop through every column and every cell adding the data to the recordset
'in the correct fields
For i = 1 To newI
'Can't remember if .value is required for the cells.
oRS.AddNew
oRS.Fields("FirstField").Value = ows.Cells(i, "A").Value
oRS.Fields("SecondField").Value = ows.Cells(i, "B").Value
'etc, etc,
oRS.MoveNext
'updates the recordset and the table
oRS.Update
Next
"If i was king cigarettes would be free."
|
|
|
|
|
I already have a connection established, the following is code that I came up with that opens the .xls that I am after. I just don't know how to finish this code off to loop through the XL rows and put those records into a SQL table.
Public Sub Batch(ByVal Path As String)
Dim objExcel As New Excel.Application()
Dim objWrkBk As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim objRng As Excel.Range
objWrkBk = objExcel.Workbooks.Open(Path)
End Sub
Thank you for any continued support.
|
|
|
|
|
That stuff you emailed me looks much cleaner, thank you.
I'm having trouble with the connection string though:
oConn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=TempExcel;Data Source=JETSTREAM"
I have a windows auth to connect, in other words, I don't need to enter username or password to get in. I'm not sure how to adjust this.
Also,
in "SELECT * FROM TheTable", what is TheTable?
Is it some name from the .xls somehow?
Thank you for your patience.
|
|
|
|
|
Hey All,
If anyone can tell me a good book\web-site that can help me build a visual component..
Can I add my new component to the toolbox window?
Thanks
Roy.
|
|
|
|
|
I don't know of any book that just covers components or even comes close to covering every aspect of component development. You can start with Visual Basic(r).NET: The Complete Reference[^] by Jeff Shapiro. It did pretty well for me.
As for your second question, of course you can add your own controls to the ToolBox. All you need to do is right-click the ToolBox window somewhere and select Add/Remove Item...
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I am new to vb.net.
I have created a form using vb.net.
The form has the following fields and a Datagrid:
a. Revision
b. Installation
c. Part No
d. Qty
e. Description
f. Change Rev (Field is Disable - autofill based on criteria)
The form also has the command buttons: Save and Close
For initial revision "-", I am able to capture and saved what was key-in the above fields (except "Change Rev").
For making subsequent revision, "A" (for example), the previous revision data is recaptured onto the fields.
However, I have a problem. Is it possible when there is any changes made to any of the following field (Part
No, Qty and Description) that is different from the previous revision, the field "Change Rev" will auto-fill
the current revision "A" in Change Rev. The purpose is to tell the user which set of data is change and in
what revision, if there is no change make to data, the Change Rev remain as "".
Any advise how to approach the above is appreciated. Many Thanks.
|
|
|
|
|
Hi,
I'm working on the VB.NET DDE project. I'm facing the trouble to establish a DDE connection. Does anybody here has an experience with the DDE for VB.NET?
If yes, could you share, please.
Many thanks,
Valdo
|
|
|
|
|
VB.NET doesn't support DDE at all. All support for DDE in VB ended with VB6. You'll have to P/Invoke all of the Win32 API functions and redefine all the structures your going to need. Docs on NetDDE begin here[^]. But be warned, all the example code is written in C++ and would need to be converted to VB.NET. There are various musings on the 'Net concerning DDE and VB.NET, but nothing extensive. You can look at the search results here[^].
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Hi
Can anybody tell me how can I have a treeview like window explorer, If one client updates (adds any file to it) other clients on same or different machine will reflect the change
Thanks
Vikas...
|
|
|
|
|
Ewww.. Difficult to implement. Your going to have to have some kind of central server that each of your clients connects to so it can relay changes back and forth.
Windows Explorer can do this because it monitors for changes in the file system on the server. Your app is not going to have this little blessing because your not looking at a file system.
Or are you? In that case, you can use the FileSystemWatcher[^] class in VB.NET. There is no equivilent in VB6.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I'm building a multi-project solution in VB.Net and it tells me that 1 failed. How do I locate what project and where in that project is causing the failure?
|
|
|
|
|
The task list will usually list out any errors discovered. You can open it using CTRL-ALT-K or going to View | Other Windows | Task List to open the window. You can then double click on any error and it will automatically open up the offending file.
|
|
|
|
|
in addition to what Chris mentioned, you should also check the output window. It gives a blow by blow description of the build process, listing any errors that may have occured.
Ctrl & Alt & O
View - Other Windows - Output Window
-jim
|
|
|
|
|
I am looking at the Output window and it is telling me that there where 2 build failures. However the blow-by-blow account of the build process shows no errors. There are no compiler errors in my task list and just that 2 build failures. How come?
|
|
|
|
|
if there were compilation errors, they should definitely be listed in the output window.
look in between the portion of the build description that says "Performing Main Compilation..." and "Building Satellite Assemblies...". If there is anything in-between, chances are it's an error. You may have to scroll over to the right a little to see it's description, but it should be there.
As for the task list, give it a right click, go to "Show Tasks" and make sure that either "All", or "Build Errors" is checked. This way you're not filtering them out.
other than that i'm not sure what could be wrong, but make sure that all of your assembly/project references are solid.
hope this helps,
-jim
|
|
|
|
|
I am looking for code examples for VB6(SP5) that will enable the following
I receive a file that has been encrypted with RSA public key, it contains a number of transactions for processing. Encryption is used for transmission of the transaction file from behind one sites firewall to behind another sites firewall.
I have the private key.
How do I decrypt the file for processing. Since the application is running in a secure environment it does not need to retain the security protocol.
|
|
|
|
|
You might want to take a look at this[^] on vbRad. Crypto on VB6 is notoriously difficult because of the lake of unsigned data types.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
okay.. i have a combobox filled with list of item from my database using DataReader.. my problem is.. i would like to add new items into my database by typing in the combobox which is not shown in the combobox list.. but the program prompted "couldn't insert record: system.NullReferenceException...." it wants me to choose 1 of the items in the list..!
is there a way of doing this? or a better way? Please help!
|
|
|
|
|
Okay, I know how to control whole strings in VB .NET, but is there any way to control specific characters in a string? If so, how?
O_o
|
|
|
|
|
|
Thanks, the string.substring() really helped after I experimented with it a little bit.
O_o
|
|
|
|
|
I'd like to write a program in VB.NET that performs some actions synchronized to the music of a wav or mp3 audio file.
Something like: at 12.35 seconds into the playback a button appears, then at 45.5 seconds it disappears, etc. I'd like it to be precise and reapeatable so that at the particular beat or crescendo, something happens.
Any suggestions of how to get started?
Thanks
STEVE
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
