Visual Basic

2-Sep-04 7:45

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!

Dave Kreskowiak2-Sep-04 9:35

2-Sep-04 9:35

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

partt2-Sep-04 9:58

2-Sep-04 9:58

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.

Purple Monk2-Sep-04 22:33

Purple Monk

2-Sep-04 22:33

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."

partt3-Sep-04 3:28

3-Sep-04 3:28

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.

partt3-Sep-04 8:34

Re: 2 Component questions

3-Sep-04 8:34

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.

2 Component questions

RoyRose782-Sep-04 6:57

RoyRose78

2-Sep-04 6:57

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.

Dave Kreskowiak2-Sep-04 9:48

Question on comparing data

2-Sep-04 9:48

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

albert20042-Sep-04 6:36

albert2004

2-Sep-04 6:36

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.

VB.NET DDE project

valdo772-Sep-04 2:32

valdo77

2-Sep-04 2:32

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

Re: VB.NET DDE project

Dave Kreskowiak2-Sep-04 5:19

2-Sep-04 5:19

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

hanksTreeView

Member 12256032-Sep-04 0:59

Member 1225603

2-Sep-04 0:59

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...

Re: hanksTreeView

Dave Kreskowiak2-Sep-04 4:58

2-Sep-04 4:58

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

Build Failures

beowulfagate2-Sep-04 0:26

beowulfagate

2-Sep-04 0:26

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?

Chris Quick2-Sep-04 2:28

Chris Quick

2-Sep-04 2:28

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.

Jim Matthews2-Sep-04 2:46

Jim Matthews

2-Sep-04 2:46

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

beowulfagate2-Sep-04 13:38

beowulfagate

2-Sep-04 13:38

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?

RSA Decryption code example VB6

Jim Matthews2-Sep-04 17:00

Jim Matthews

2-Sep-04 17:00

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

gpcare1-Sep-04 23:23

gpcare

1-Sep-04 23:23

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.

Re: RSA Decryption code example VB6

Dave Kreskowiak2-Sep-04 4:52