帮助两个数据表之间的比较语法 [英] Help With Comparison Syntax Between Two Datatables
问题描述
我正在尝试比较两个数据表,并根据客户编号生成不匹配的行的结果数据表.主表客户"包含已放置在我们系统中的所有客户.我正在尝试生成一个客户表"dtResults",我们从未从名为"CurrentCust"的表生成发票.
我希望有人查看所附的代码,看看如何处理"InvalidCastException未处理",无法转换"类型为'System.String'的对象,以键入'CustomerDataTable'错误.错误发生在第43行.
I am trying to compare two datatables and generate a resultant datatable of rows that do not match based on customer number. The main table "Customers" contains all customers that have been placed in our system. I am trying to generate a table of customers, "dtResults," that we have not ever generated an invoice from a table called "CurrentCust".
I would like someone to look at the attached code and see how I can handle the 'InvalidCastException was unhandled', 'Unable to cast object of type 'System.String' to type 'CustomerDataTable' error. Error occurs at line 43.
|
选项 显式 打开 |
|
选项 严格关闭 |
|
|
|
导入 excel = Microsoft.Office. Interop.Excel.Application |
|
导入 wBook = Microsoft.Office.Interop.Excel.Workbook |
|
导入 wSheet = Microsoft.Office. Interop.Excel.Sheets |
|
|
|
公共 类 frmOpenCustomers |
|
|
|
私有 子 frmOpenCustomers_Load( ByVal 发件人 As 系统. 对象 , ByVal 为 句柄 MyBase "TODO":这行代码将数据加载到"dbSA_CUSTOMERLISTDataSet.Customer"表中.您可以根据需要移动或删除它. |
|
我 .CustomerTableAdapter.Fill( 我 .dbSA_CUSTOMERLISTDataSet.Customer) |
|
|
|
结束 子 |
|
|
|
私有 子 btnRun_Click( ByVal 发件人 As 系统. 对象 , ByVal 为 句柄 设置数据表以保存所有客户的比较结果到已在系统中生成发票的客户数据表中. |
|
昏暗 dtResults 如 新 暗 行 按 DataRow |
|
为dtResults定义数据列. |
|
dtResults.Columns.Add( " CSCODE" , GetType ( String dtResults.Columns.Add( " SANAME" GetType String )) |
|
dtResults.Columns.Add( " CSNAME" , GetType ( String dtResults.Columns.Add( " CSADDR1" GetType String )) |
|
dtResults.Columns.Add( " CSADDR2" , GetType ( String dtResults.Columns.Add( " CSCITY" GetType String )) |
|
dtResults.Columns.Add( " CSST" , GetType ( String dtResults.Columns.Add( " CSZIP" GetType String )) |
|
dtResults.Columns.Add( " CSTYPE" , GetType ( String dtResults.Columns.Add( " CSENTDATE" GetType String )) |
|
dtResults.Columns.Add( " CSMODDATE" , GetType ( String dtResults.Columns.Add( " CSPHONE" GetType String )) |
|
'定义查询参数. |
|
暗 wasfound 如 布尔值 错误 暗 a 按 整数 = 0 |
|
昏暗 b 如 整数 暗 MaxRowCurrentCust 整数 == 输入 ( 我 .CustomerTableAdapter.ScalarQueryCurrentCustNumber.Value)-1 |
|
昏暗 MaxRowCustomer 如 整数 配音 我 '对数据表进行搜索比较,以生成从未出售过的客户列表. |
|
For a = 0 要 MaxRowCustomer |
|
wasFound == 假 如果找到匹配项,``此布尔值将设置为True |
|
For b = 0 要 MaxRowCurrentCust |
|
如果 我 .CustomerTableAdapter.FillByCurrentCust(( 我 " CSCODE" 我 .CustomerTableAdapter.FillByCustomer((( 我 .dbSA_CUSTOMERLISTDataSet.Customer).Rows(a)( " CSCODE" ))) 然后 |
|
wasdFound = True |
|
退出 For 这违反了"For b ="循环无需进一步检查.在当前客户表中找到了此客户公司代码. |
|
结束 如果 |
|
下一个 b |
|
如果 wasFound == 错误 然后 |
|
在当前客户数据表中找不到客户代码. |
|
Row = dtResults.NewRow |
|
dtResults.NewRow() |
|
Row.Item( " CSCODE" )= 我 .CustomerTableAdapter.FillByCustomer( .dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)( )) |
|
Row.Item( " SANAME" )= 我 .CustomerTableAdapter.FillByCustomer( 我 .dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)( &"SANAME"" Row.Item( " CSNAME" )= 我 .CustomerTableAdapter.FillByCustomer( .dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)( )) |
|
Row.Item( " CSADDR1" )= 我 .CustomerTableAdapter.FillByCustomer( 我 .dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)( " CSADDR1" Row.Item( " CSADDR2" )= 我 .CustomerTableAdapter.FillByCustomer( .dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)( )) |
|
``Row.Item( " CSCITY" )= 我 .CustomerTableAdapter.FillByCustomer( 我 .dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)( &"CSCITY"" Row.Item( " CSST" )= 我 .CustomerTableAdapter.FillByCustomer( .dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)( )) |
|
" CSZIP" )= 我 .CustomerTableAdapter.FillByCustomer( 我 .dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)( &"CSZIP"" Row.Item( " CSTYPE" )= 我 .CustomerTableAdapter.FillByCustomer( .dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)( )) |
|
Row.Item( " CSENTDATE" )= 我 .CustomerTableAdapter.FillByCustomer( 我 .dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)( &"CSENTDATE"" Row.Item( " CSMODDATE" )= 我 .CustomerTableAdapter.FillByCustomer( .dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)( )) |
|
Row.Item( " CSPHONE" )= 我 .CustomerTableAdapter.FillByCustomer( 我 .dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)( &"CSPHONE" dtResults.Rows.Add(Row) |
|
其他 |
|
在当前客户数据表中找到了客户代码. |
|
结束 如果 |
|
下一个 a |
|
txtResults.Text = CStr (dtResults.Rows.Count) 在dtResults中找到显示的行数. |
|
如果 txtResults.Text = " 或 txtResults.Text = 0 然后 |
|
退出 Sub |
|
结束 如果 |
|
dgResults.DataSource = dtResults 显示输出到datagridview. |
|
创建用于导出为Excel文件格式的数据集". |
|
暗 博士 按 DataRow |
|
昏暗 dsResults 如 新 '将表添加到数据集. |
|
dsResults.Tables.Add() |
|
|
|
对于 i 为 整数 = 0 = 要 dgResults.RowCount-1 |
|
dr = dsResults.Tables(0).NewRow |
|
用于 j 按 整数 = 0 = 要 dgResults.ColumnCount-1 |
|
dr(j)= dgResults.Rows(i).Cells(j).Value |
|
下一个 |
|
dsResults.Tables(0).Rows.Add(dr) '将行添加到表中. |
|
下一个 |
|
|
|
暗 excel 按 新建 Microsoft.Office .Interop.Excel.Application |
|
昏暗 wBook 为 Microsoft.Office.Interop.Excel.Workbook |
|
暗 wSheet 按 Microsoft.Office.Interop.Excel.Worksheet |
|
|
|
wBook = excel.Workbooks.Add |
|
wSheet = wBook.ActiveSheet |
|
|
|
昏暗 dt 如 System.Data.DataTable = dsResults.Tables(0) |
|
暗 dccol As System.Data.DataColumn |
|
昏暗 卓尔 如 DataRow |
|
暗 colIndex As 整数 = 0 |
|
暗 rowIndex 如 整数 For 每个 dccol 在 excel.Cells(1,colIndex)= dccol.ColumnName |
|
下一个 |
|
|
|
对于 每个 卓尔 在 dtResults.Rows |
|
rowIndex = rowIndex + 1 |
|
colIndex = 0 |
|
对于 每个 dccol 在 excel.Cells(rowIndex + 1,colIndex)= drow(dccol.ColumnName) |
|
下一个 |
|
|
|
wSheet.Columns.AutoFit() |
|
Dim strFilename <字体样式="color:blue">为 字符串 " C:\ Documents and Settingss \ All \ Users \ Desktop \ OpenCustomers.xls" Dim binFileOpen As 布尔值 == 错误 |
|
尝试 |
|
暗 fileTemp System.IO.FileStream = System.IO.File.OpenWrite(strFilename) |
|
fileTemp.Close() |
|
捕获 例如 例外 |
|
binFileOpen = False |
|
结束 尝试 |
|
|
|
如果 System.IO.File.Exists(strFilename) 然后 |
|
System.IO.File.Delete(strFilename) |
|
结束 如果 |
|
|
|
wBook.SaveAs(strFilename) |
|
excel.Workbooks.Open(strFilename) |
|
excel.Visible = True |
Next | |
'Me.ReportViewer1.RefreshReport() | |
End Sub | |
End Class | |
Option Explicit On | |
Option Strict Off | |
Imports excel = Microsoft.Office.Interop.Excel.Application | |
Imports wBook = Microsoft.Office.Interop.Excel.Workbook | |
Imports wSheet = Microsoft.Office.Interop.Excel.Sheets | |
Public Class frmOpenCustomers | |
Private Sub frmOpenCustomers_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load | |
'TODO: This line of code loads data into the 'dbSA_CUSTOMERLISTDataSet.Customer' table. You can move, or remove it, as needed. | |
Me.CustomerTableAdapter.Fill(Me.dbSA_CUSTOMERLISTDataSet.Customer) | |
End Sub | |
Private Sub btnRun_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRun.Click | |
'Setup data table to hold results from comparison of all customers to data table of customers that have generated an invoice in system. | |
Dim dtResults As New DataTable | |
Dim Row As DataRow | |
'Define columns of data for dtResults. | |
dtResults.Columns.Add("CSCODE", GetType(String)) | |
dtResults.Columns.Add("SANAME", GetType(String)) | |
dtResults.Columns.Add("CSNAME", GetType(String)) | |
dtResults.Columns.Add("CSADDR1", GetType(String)) | |
dtResults.Columns.Add("CSADDR2", GetType(String)) | |
dtResults.Columns.Add("CSCITY", GetType(String)) | |
dtResults.Columns.Add("CSST", GetType(String)) | |
dtResults.Columns.Add("CSZIP", GetType(String)) | |
dtResults.Columns.Add("CSTYPE", GetType(String)) | |
dtResults.Columns.Add("CSENTDATE", GetType(String)) | |
dtResults.Columns.Add("CSMODDATE", GetType(String)) | |
dtResults.Columns.Add("CSPHONE", GetType(String)) | |
'define query parameters. | |
Dim wasFound As Boolean = False | |
Dim a As Integer = 0 | |
Dim b As Integer = 0 | |
Dim MaxRowCurrentCust As Integer = CInt(Me.CustomerTableAdapter.ScalarQueryCurrentCustNumber.Value) - 1 | |
Dim MaxRowCustomer As Integer = CInt(Me.CustomerTableAdapter.ScalarQueryCustomerCount.Value) - 1 | |
'perform search compaison of data tables to generate a list of customers that have never been sold to. | |
For a = 0 To MaxRowCustomer | |
wasFound = False 'This boolean will be set to True if a match is found | |
For b = 0 To MaxRowCurrentCust | |
If Me.CustomerTableAdapter.FillByCurrentCust((Me.dbSA_CUSTOMERLISTDataSet.Customer).Rows(b)("CSCODE")) = Me.CustomerTableAdapter.FillByCustomer((Me.dbSA_CUSTOMERLISTDataSet.Customer).Rows(a)("CSCODE")) Then | |
wasFound = True | |
Exit For 'This breaks out of the "For b =" loop No need to check any further. This customer's Company Code was found in the current customer table. | |
End If | |
Next b | |
If wasFound = False Then | |
'That customer's code was not found in current customer data table. | |
Row = dtResults.NewRow | |
dtResults.NewRow() | |
Row.Item("CSCODE") = Me.CustomerTableAdapter.FillByCustomer(Me.dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)("CSCODE")) | |
Row.Item("SANAME") = Me.CustomerTableAdapter.FillByCustomer(Me.dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)("SANAME")) | |
Row.Item("CSNAME") = Me.CustomerTableAdapter.FillByCustomer(Me.dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)("CSNAME")) | |
Row.Item("CSADDR1") = Me.CustomerTableAdapter.FillByCustomer(Me.dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)("CSADDR1")) | |
Row.Item("CSADDR2") = Me.CustomerTableAdapter.FillByCustomer(Me.dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)("CSADDR2")) | |
Row.Item("CSCITY") = Me.CustomerTableAdapter.FillByCustomer(Me.dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)("CSCITY")) | |
Row.Item("CSST") = Me.CustomerTableAdapter.FillByCustomer(Me.dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)("CSST")) | |
Row.Item("CSZIP") = Me.CustomerTableAdapter.FillByCustomer(Me.dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)("CSZIP")) | |
Row.Item("CSTYPE") = Me.CustomerTableAdapter.FillByCustomer(Me.dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)("CSTYPE")) | |
Row.Item("CSENTDATE") = Me.CustomerTableAdapter.FillByCustomer(Me.dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)("CSENTDATE")) | |
Row.Item("CSMODDATE") = Me.CustomerTableAdapter.FillByCustomer(Me.dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)("CSMODDATE")) | |
Row.Item("CSPHONE") = Me.CustomerTableAdapter.FillByCustomer(Me.dbSA_CUSTOMERLISTDataSet.Customer.Rows(a)("CSPHONE")) | |
dtResults.Rows.Add(Row) | |
Else | |
'That customer's code was found in the curreent customer data table. | |
End If | |
Next a | |
txtResults.Text = CStr(dtResults.Rows.Count) 'Display number of rows found in dtResults. | |
If txtResults.Text = "" Or txtResults.Text = 0 Then | |
Exit Sub | |
End If | |
dgResults.DataSource = dtResults 'Display output to datagridview. | |
'Create dataset for exporting to Excel file format. | |
Dim dr As DataRow | |
Dim dsResults As New DataSet 'Add table to dataset. | |
dsResults.Tables.Add() | |
For i As Integer = 0 To dgResults.RowCount - 1 | |
dr = dsResults.Tables(0).NewRow | |
For j As Integer = 0 To dgResults.ColumnCount - 1 | |
dr(j) = dgResults.Rows(i).Cells(j).Value | |
Next | |
dsResults.Tables(0).Rows.Add(dr) 'Add rows to the table. | |
Next | |
Dim excel As New Microsoft.Office.Interop.Excel.Application | |
Dim wBook As Microsoft.Office.Interop.Excel.Workbook | |
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet | |
wBook = excel.Workbooks.Add | |
wSheet = wBook.ActiveSheet | |
Dim dt As System.Data.DataTable = dsResults.Tables(0) | |
Dim dccol As System.Data.DataColumn | |
Dim drow As DataRow | |
Dim colIndex As Integer = 0 | |
Dim rowIndex As Integer = 0 | |
For Each dccol In dtResults.Columns | |
colIndex = colIndex + 1 | |
excel.Cells(1, colIndex) = dccol.ColumnName | |
Next | |
For Each drow In dtResults.Rows | |
rowIndex = rowIndex + 1 | |
colIndex = 0 | |
For Each dccol In dtResults.Columns | |
colIndex = colIndex + 1 | |
excel.Cells(rowIndex + 1, colIndex) = drow(dccol.ColumnName) | |
Next | |
wSheet.Columns.AutoFit() | |
Dim strFilename As String = "C:\Documents and Settings\All Users\Desktop\OpenCustomers.xls" | |
Dim binFileOpen As Boolean = False | |
Try | |
Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFilename) | |
fileTemp.Close() | |
Catch ex As Exception | |
binFileOpen = False | |
End Try | |
If System.IO.File.Exists(strFilename) Then | |
System.IO.File.Delete(strFilename) | |
End If | |
wBook.SaveAs(strFilename) | |
excel.Workbooks.Open(strFilename) | |
excel.Visible = True | |
Next | |
'Me.ReportViewer1.RefreshReport() | |
End Sub | |
End Class | |
推荐答案
Still Around?
I think a better approach would be to loop through the rows of customer table and then use rowfilter property on the currentcust table to see if the invoice has been sent (i am assuming that you need to compare customer table with currentcust and if a customer does not exist in currentcust then add that to resultset).
For i as integer = 0 to tblCustomer.Rows.Count - 1
tblCurrentCust.DefaultView.RowFilter = "CustomerID = " & tblCustomer.Rows(i).item("CustomerID")
if tblcurrentcust.DefaultView.Count = 0 Then
'Add row to resultset
End if
tblCurrentCust.DefaultView.RowFilter = ""
Next
Still Around?
I think a better approach would be to loop through the rows of customer table and then use rowfilter property on the currentcust table to see if the invoice has been sent (i am assuming that you need to compare customer table with currentcust and if a customer does not exist in currentcust then add that to resultset).
For i as integer = 0 to tblCustomer.Rows.Count - 1
tblCurrentCust.DefaultView.RowFilter = "CustomerID = " & tblCustomer.Rows(i).item("CustomerID")
if tblcurrentcust.DefaultView.Count = 0 Then
'Add row to resultset
End if
tblCurrentCust.DefaultView.RowFilter = ""
Next
这篇关于帮助两个数据表之间的比较语法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!