Writing Updated DataSet Out

RibTime

Member
Joined
Aug 11, 2005
Messages
10
Location
South Coast UK
Programming Experience
10+
Still trying to complete my first VB/ADO.Net piece of code. The final piece of help I need is how to write out my DataSet which I have updated back to the original Excel spreadsheet or to a new spreadsheet?

Below is my code..

Many thanks RibTime
PHP:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
Dim conn As System.Data.OleDb.OleDbConnection 
 
Dim comm As System.Data.OleDb.OleDbDataAdapter
 
Dim strConn As String
 
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=v:\From Outlook.xls;"
 
strConn = strConn & "Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""
 
conn = New System.Data.OleDb.OleDbConnection(strConn)
 
comm = New System.Data.OleDb.OleDbDataAdapter("Select * From [Sheet1$]", conn)
 
Dim Excel As New DataSet("Data")
 
Dim strName As String
 
Dim strArray() As String
 
Dim intRead As Integer
 
Try
 
conn.Open()
 
comm.Fill(Excel)
 
Me.DataGrid.DataSource = Excel.DefaultViewManager
 
For intRead = 6 To (Excel.Tables(0).Rows.Count - 1)
 
strName = Excel.Tables(0).Rows(intRead).Item(5)
 
Excel.Tables(0).Rows(intRead).Item(10) = WordCount(strName)
 
Next
 
Catch ex As Exception
 
MessageBox.Show(ex.Message)
 
Finally
 
conn.Close()
 
conn.Dispose()
 
comm.Dispose()
 
End Try
 
Clean_Up:
 
conn = Nothing
 
comm = Nothing
 
End Sub
 
Private Function WordCount(ByVal input As String) As String
 
Dim separators As Char() = {" "c}
 
Dim words As String() = input.Split(separators)
 
Dim strEmail As String
 
Dim intCheck As Integer
 
'***********************************************************************************************
 
' First check to see if the name has a "()" in the string in which case we will ignore it
 
'***********************************************************************************************
 
intCheck = input.IndexOfAny("()", 1)
 
If intCheck > 0 Then
 
strEmail = "invalid"
 
Return strEmail
 
End If
 
'***********************************************************************************************
 
' Take first and last name and build2 xx Email Address.
 
' Ignore any name that is longer than 3 wrods
 
'***********************************************************************************************
 
Select Case (words.Length)
 
Case (1)
 
strEmail = "Invalid Address"
 
Case (2)
 
strEmail = words(1).ToLower & "_" & words(0).ToLower & "@xx.com"
 
Case (3)
 
strEmail = words(2).ToLower & "_" & words(0).ToLower & "@xx.com"
 
Case Else
 
strEmail = "No Email Found"
 
End Select
 
Return strEmail
 
End Function
 
 
 
End Class
 
I'm a little short on replies so I'll try to quantify my question.


With the above code I create an Email address which I then want to save to the same Excel Spreadsheet.

Ideally I don't want to update each row.

So is there a way to save the whole DataSet in one go?

Any example / pointer to a book / online reference would be great.

Thanks in advance
 
Thanks for the reply.

Actually I downloaded the 101 examples a while back. Within the sample code there is 1 ADO 2.6 example and based around. Although I had looked at it several times before I reviewed it again.

This may be where my knowledge of SQL and ADO.Net let's me down, but he update example is using the "UPDATE" SQL parm and specifying the update parm's within the statement.

Whereas I have applied updates to my dataset which I now want to apply to the Excel spreadsheet.

Looking through the 101 examples I cannot find this code
 
You've used exactly the same method of retrieving data from an Excel spreadsheet as you would to retrieve data from an Access database. The only differences are the connection string and the table names in the SQL statement. Now you simply use the exact same method to commit data back to an Excel spreadsheet as you would to commit it back to an Access database, with the same differences as before. I think all, or at least most of, the 101 samples use SQL Server, but you can simply substitute classes from the OleDb namespace for the corresponding classes from the SqlClient namespace and everything works exactly the same way. That's one of the great things about ADO.NET: various objects are optimised for various data sources but they all work in almost exactly the same way.

Note also that ADO 2.6 is not ADO.NET. ADO is Microsoft's pre-.NET method of data access and should be avoided if at all possible. It really only exists in .NET for compatibility with existing applications.
 
Top man...

Many thanks again.

I've spent quite a lot of time looking into this but suspect I have some code downloaded / documentation giving me the answer.

Cheers

RibTime
 
Back
Top