> I have to take an excel sheet and for each of the 40,000 some customer
> records look up their address from a Sql Server database using their
> customer number found in the first column of the excel table and add the
> address columns addr1, addr2, city, state, zip to the end of the excel
> table records so I can return the spreadsheet to accounting. I'm
> thinking I like the approach of accessing the excel sheet via oledb
> database like below.  I don't need to display it in the datagrid in the
> final program I was just making sure I had the data loaded into a
> datatable.  Actually I don't know that I need the datatable.  How do I
> loop through this oledb table record by record?  How do I add fields to
> the end of the records once I've found the address?  Do I need to add
> columns to the spreadsheet ahead of time for the address data?  Any
> ideas would be appreciated.  I'll post this while I continue to try to
> figure it out.
>
>   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
>          Button1.Enabled = False
>          MyOpenFileDialog = New OpenFileDialog
>          MyOpenFileDialog.Title = "Open Excel file"
>          MyOpenFileDialog.FileName = ""
>          MyOpenFileDialog.InitialDirectory = "c:\"
>          MyOpenFileDialog.DefaultExt = ".XLS"
>          MyOpenFileDialog.Filter = "XLS files (*.XLS)|*.XLS|All files
> (*.*)|*.*"
>          MyOpenFileDialog.RestoreDirectory = True
>
>          If MyOpenFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
>              Dim filename As String = MyOpenFileDialog.FileName
>
>              Dim mydt 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(mydt)
>              DataGridView1.DataSource = mydt
>          End If
>          Button1.Enabled = True
>      End Sub

> On Nov 13, 9:42 am, cj wrote:
>> I have to take an excel sheet and for each of the 40,000 some customer
>> records look up their address from a Sql Server database using their
>> customer number found in the first column of the excel table and add the
>> address columns addr1, addr2, city, state, zip to the end of the excel
>> table records so I can return the spreadsheet to accounting. I'm
>> thinking I like the approach of accessing the excel sheet via oledb
>> database like below.  I don't need to display it in the datagrid in the
>> final program I was just making sure I had the data loaded into a
>> datatable.  Actually I don't know that I need the datatable.  How do I
>> loop through this oledb table record by record?  How do I add fields to
>> the end of the records once I've found the address?  Do I need to add
>> columns to the spreadsheet ahead of time for the address data?  Any
>> ideas would be appreciated.  I'll post this while I continue to try to
>> figure it out.
>>
>>   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
>> System.EventArgs) Handles Button1.Click
>>          Button1.Enabled = False
>>          MyOpenFileDialog = New OpenFileDialog
>>          MyOpenFileDialog.Title = "Open Excel file"
>>          MyOpenFileDialog.FileName = ""
>>          MyOpenFileDialog.InitialDirectory = "c:\"
>>          MyOpenFileDialog.DefaultExt = ".XLS"
>>          MyOpenFileDialog.Filter = "XLS files (*.XLS)|*.XLS|All files
>> (*.*)|*.*"
>>          MyOpenFileDialog.RestoreDirectory = True
>>
>>          If MyOpenFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
>>              Dim filename As String = MyOpenFileDialog.FileName
>>
>>              Dim mydt 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(mydt)
>>              DataGridView1.DataSource = mydt
>>          End If
>>          Button1.Enabled = True
>>      End Sub
>
> I found when working with Excel and having to add it was easier to
> pull out the Excel interop objects.  They're a little tricky to work
> with but you can wield a lot more power when it comes to adding and
> removing columns.  Otherwise I would think that you would add columns
> to your Excel sheet the same way you would a SQL table = "ALTER TABLE
> [" & "all ANIs" & "$] ADD City Varchar(50) NOT NULL"
>

> Hello cj,
>
> From your post, my understanding on this issue is: you wonder how to update
> the rows in Excel according to the query in Database with customer No. If
> I'm off base, please feel free to let me know.
>
> According to your pseudo code:
> update excel set excel.street=sql.street set excel.city=sql.city set
> excel.state=sql.state set excel.zip=sql.zip where excel.cust_no=sql.cust_no
> I think we could follow the logic below:
> 1. Use a OleDbDataReader object to read the customer no. in excel one by
> one.
> 2. For the current customer number read by the OleDbDataReader object, use
> a SqlDataReader object to query the other customer information from Database
> 3. Use a OleDbCommand object to update the row in Excel.
>
> Here are some pseudo codes for your reference:
> ' 1. Use a OleDbDataReader object to read the customer no. in excel one by
> one.
> ' create a OleDbCommand object here: cmd
> cmd.Connection = excelConn ' the excel connection
> cmd.CommandText = "SELECT custom_no FROM [Sheet1$]"
> ' create a OleDbDataReader object: cuNoReader
> cuNoReader = cmd.ExecuteReader()
>
> ' 2. For the current customer no read by the OleDbDataReader object, use a
> SqlDataReader
> ' object to query the other customer information from Database
> ' create a SqlCommand object here: sqlCmd
> sqlCmd.Connection = sqlConn ' the sql connection
> sqlCmd.CommandText = "SELECT * FROM [CUTable] WHERE custom_no=@custom_no"
> sqlCmd.Parameters.Add("@custom_no", SqlDbType.Int)
>
> ' 3. Use a OleDbCommand object to update the row in Excel.
> ' create a OleDbCommand object here: updateCmd
> updateCmd.Connection = excelConn ' the excel connection
> updateCmd.CommandText = "UPDATE [Sheet1$] SET street=@street,city=@city
> WHERE custom_no=@custom_no"
> updateCmd.Parameters.Add("@street", OleDbType.VarChar, 255)
> updateCmd.Parameters.Add("@city", OleDbType.VarChar, 255)
> updateCmd.Parameters.Add("@custom_no", OleDbType.Integer)
>
> While cuNoReader.Read()  ' read the customer number one by one
>      Dim cu_no as integer = (int)cuNoReader["custom_no"] // get the
> customer no
>      ' read the customer info for the customer from sql db
>      sqlCmd.Parameters["@custom_no"].Value = cu_no
>      Dim sqlReader as SqlDataReader = sqlCmd.ExecuteReader();
>      if sqlReader.Read() then
>           ' update the row in the excel
>           updateCmd.Parameters["@custom_no"].Value = cu_no
>           updateCmd.Parameters["@street"].Value =
> sqlReader["street"].ToString()
>           updateCmd.Parameters["@city"].Value = sqlReader["city"].ToString()
>           updateCmd.ExecuteNonQuery()
>      End If
>      sqlReader.Close()
> End While
> ' close the connection and readers
>
> Please let me know if you have any other concerns, or need anything else.
>
> Sincerely,
> Jialiang Ge (jia***@online.microsoft.com, remove 'online.')
> Microsoft Online Community Support
>
> ==================================================
> For MSDN subscribers whose posts are left unanswered, please check this
> document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx
>
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications. If you are using Outlook Express/Windows Mail, please make sure
> you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
> see your reply promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>