Visual Basic

15-Nov-07 8:53

Paul you helped me complete the mission using the excel spreadsheet as a linked server and a single sql query run on the sql server but I would like to know how to do this in VB now too. I feel I need to be able to do things like this in VB quickly and easily and I'm just not there yet. I'm hoping someone can help me understand how this can be done in VB.

I've loaded the excel spreadsheet via oledb into a datatable. Every record in this sheet needs name and address info added to it (the fields are there but are blank). I'm looping through the datatable and looking up required data in a sql server database. I need some help reading in the values returned by the query. I assume I have only 3 options ExecuteReader to return a DataReader object, ExecuteScalar to return a single value, and ExecuteNonQuery which returns nothing. I've never worked with ExecuteReader before. Is this the simpliest way to retrieve a couple pieces of data from a database query? See code below.

If MyOpenFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
Dim filename As String = MyOpenFileDialog.FileName

Dim myExcelDt As New System.Data.DataTable

Dim myExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & filename & "; Extended Properties=""Excel 8.0""")
Dim myExcelSqlCommand As String = "SELECT * FROM [all ANIs$]"
Dim myExcelAdapter = New System.Data.OleDb.OleDbDataAdapter(myExcelSqlCommand, myExcelConnection)
myExcelAdapter.fill(myExcelDt)

Dim mySqlDt As New System.Data.DataTable

Dim Sql2000Connection As New System.Data.SqlClient.SqlConnection(Sql2000ConnectionString)
Dim Sql2000Command As New SqlClient.SqlCommand
Sql2000Command.Connection = Sql2000Connection

For Each excelrow As DataRow In myExcelDt.Rows
Sql2000Command.CommandText = "select fullname, address, city, state, zip from addresslist where phone = '" & excelrow("phone") & "'"

**** need help here ****

Next
End If

pmarfleet15-Nov-07 9:48

15-Nov-07 9:48

Hi there.

The syntax for retrieving data using a DataReader would be something like this:

For Each excelrow As DataRow In myExcelDt.Rows
Sql2000Command.CommandText = "select fullname, address, city, state, zip from addresslist where phone = '" & excelrow("phone") & "'"
Using reader As SqlDataReader = Sql2000Command.ExecuteReader()
While reader.Read()
Dim fullname As String = reader.GetString(0)
Dim address As String = reader.GetString(1)
' And so on...
End While
End Using
Next

As an aside, what are you trying to achieve with your code? Do you want to check whether a phone number in the Excel file is valid? If so, it would be better to write a stored procedure that takes a phone number as a parameter, does a lookup on the addresslist table to check whether the phone number exists and returns a boolean value indicating whether it is valid. This method is more efficient than doing the validation in your VB code.

One other thing. Whilst it is nice to be asked personally for help, it is probably better to address questions generally to the forum rather than to specific people. I have to fit my CodeProject excursions round a day job/commute Sigh | :sigh:

and I may not be able to answer a question.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

cj433115-Nov-07 10:10

15-Nov-07 10:10

Thanks for the code. I'll play with it.

The object of yesterdays question was to add the name and address associated with each of the phone numbers in that excel spreadsheet to the spreadsheet for our accountant.

The object now is for me to know how to retrieve, update and otherwise manipulate data in ways I think I'll need to use in the future. I can only imagine I'll be asked many times in the future to get, update, add or delete data from a table or most likely multiple tables and I'll have to use VB. I just don't feel as ready to do that as I could in say Visual FoxPro. It's surprising when something like this comes up and I'm thinking hey I really haven't done that before. My VB database access to date has been with stored procedures and most of my heavy database work has been in Visual FoxPro as so, so, so much of our data is in VFP tables. But times are a-changing and we want to move away from VFP when and if we can. I'm already supposed to be writing a web service to lookup customer data. This customer data will come from multiple, you guessed it, VFP databases. So I'm learning all I can about accessing databases from VB using OleDb and Sql connections. Frankly I think my intimate knowledge of old dbase type databases and their access methods is preventing me from grasping how this is done in VB. It seems so much more complicated and inflexible.

So I'm wondering what if the results of the query contain more than one record? Will this ExecuteReader accept that? I'll start playing with it and see what I can figure out. Thanks for responding again.

P.S. I didn't mean to address the question to you personally I just didn't want you to see it and say, that's the same thing I helped him with yesterday.

pmarfleet15-Nov-07 10:25

15-Nov-07 10:25

JABOSL wrote:
So I'm wondering what if the results of the query contain more than one record? Will this ExecuteReader accept that?

ExecuteReader returns a DataReader object, which provides a fast, forward-only means of reading data. As my example demonstrates, you typically use a While...End While loop to traverse the contents of a DataReader, calling its Read() method to advance the DataReader to the next record.

The new LINQ[^] stuff in .NET 3.0 may be of interest to you. It provides a more intuitive means of manipulating data from within your .NET code, especially if you are coming from a 4GL background.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

cj433115-Nov-07 10:37

15-Nov-07 10:37

Yes, I understand the while end while now. Somehow I was thinking that was to get each of the fields and not each row as I know there will only be one row the need for a while loop hadn't crossed my mind until after I read the code but it popped in as I though about the future so I added it into my response to you. I'm interested in .net 3.0 but that opens another line of questions. How do I go about using it in VB2005? I'm pretty sure I can but...

pmarfleet15-Nov-07 10:50

15-Nov-07 10:50

If you know that only 1 row of data will be returned, you don't need to use a While loop. You still need to call the Read() method to move the record pointer to the first row.

LINQ isn't available in VS2005. It is available in the Beta 2 of VS2008 though. You can download it for free from MSDN[^] if you are interested in playing with this new technology.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

cj433115-Nov-07 10:56

15-Nov-07 10:56

I'm pretty sure they wouldn't support me going to 2008 quite yet around here. Can I install 2008 on the same pc as 2005 and 2003 and not affect them? I have some program in 2003 and some in 2005. For any major changes to a 2003 program I move it to 2005.

pmarfleet15-Nov-07 11:03