经过实验,在VB.net下通过OleDb读取EXCEL文件时并不支持UNION, LEFT JION等语句。IN语句能够取得正确数据,NOT IN却无法取得正确数据。
经过验证,可以用 NOT EXISTS语句代替NOT IN,代码如下:
代码
Protected
Sub
btnUnmatch_Click(
ByVal
sender
As
Object
,
ByVal
e
As
System.EventArgs)
Handles
btnUnmatch.Click
Dim con As OleDbConnection
Dim cmd As OleDbCommand
Dim arrRSFundCd(), arrCustodyFundCd() As String
Dim strSQL As String
Dim strTemp As String = ""
' 接続文字列
Dim sConnectionString As String = " Provider=Microsoft.Jet.OLEDB.4.0; " _
& " Data Source= " & Server.MapPath( " ~/Temp/balance.xls " ) & " ; " _
& " Extended Properties=""Excel 8.0;HDR=YES"" "
arrRSFundCd = WebConfigurationManager.AppSettings( " GC_PassiveRecoRSFundCd " ).Split( " , " c)
arrCustodyFundCd = WebConfigurationManager.AppSettings( " GC_PassiveRecoJPMorganFundCd " ).Split( " , " c)
' RS残高インポートファイルの存在性チェック
If Not File.Exists(Server.MapPath( " ~/Temp/balance.xls " )) Then
lblInformation.ForeColor = Drawing.Color.Red
lblInformation.Text = " RS残高ファイルを先にインポートしてください。 "
Exit Sub
End If
' ファンド件数チェック
If arrRSFundCd.Length <> arrCustodyFundCd.Length Then
lblInformation.ForeColor = Drawing.Color.Red
lblInformation.Text = " ConfigファイルのRSファンドとCustodyファンドの件数が合っていません。もう一度確認してください。 "
End If
' OleDbConnectionの生成
con = New OleDbConnection(sConnectionString)
' 接続
con.Open()
For i As Integer = 0 To arrRSFundCd.Length - 1
strSQL = " INSERT INTO [UnMatchReport$] " & _
" SELECT R.[ポートフォリオ] AS [RSポートフォリオ], C.[Account Number] AS [Custodyファンドコード], R.[銘柄コード] AS [RS銘柄コード], C.[ISIN] AS [Custody銘柄コード], R.[銘柄名(日本語)] AS [銘柄名], R.[保有数量(通常)] AS [RS保有数量], C.[Total Units] AS [Custody保有数量] " & _
" FROM [RS残高$] R, [Custody残高$] C WHERE " & _
" R.[ポートフォリオ] = " & arrRSFundCd(i) & " AND " & _
" C.[Account Number] = " & arrCustodyFundCd(i) & " AND " & _
" R.[銘柄コード] = C.[ISIN] AND " & _
" R.[保有数量(通常)] <> CSTR(C.[Total Units]) "
cmd = New OleDbCommand(strSQL, con)
cmd.ExecuteNonQuery()
strSQL = " INSERT INTO [UnMatchReport$] " & _
" SELECT [ポートフォリオ] AS [RSポートフォリオ], NULL AS [Custodyファンドコード], [銘柄コード] AS [RS銘柄コード], NULL AS [Custody銘柄コード], [銘柄名(日本語)] AS [銘柄名], [保有数量(通常)] AS [RS保有数量], NULL AS [Custody保有数量] " & _
" FROM [RS残高$] WHERE [ポートフォリオ] = " & arrRSFundCd(i) & " AND NOT EXISTS (SELECT * FROM [Custody残高$] WHERE [Account Number] = " & arrCustodyFundCd(i) & " AND [ISIN] = [RS残高$].[銘柄コード]) "
cmd = New OleDbCommand(strSQL, con)
cmd.ExecuteNonQuery()
Next
' 接続閉じる
con.Close()
lblInformation.ForeColor = Drawing.Color.Black
lblInformation.Text = " UnMatchレポートを作成しました。 "
End Sub
Dim con As OleDbConnection
Dim cmd As OleDbCommand
Dim arrRSFundCd(), arrCustodyFundCd() As String
Dim strSQL As String
Dim strTemp As String = ""
' 接続文字列
Dim sConnectionString As String = " Provider=Microsoft.Jet.OLEDB.4.0; " _
& " Data Source= " & Server.MapPath( " ~/Temp/balance.xls " ) & " ; " _
& " Extended Properties=""Excel 8.0;HDR=YES"" "
arrRSFundCd = WebConfigurationManager.AppSettings( " GC_PassiveRecoRSFundCd " ).Split( " , " c)
arrCustodyFundCd = WebConfigurationManager.AppSettings( " GC_PassiveRecoJPMorganFundCd " ).Split( " , " c)
' RS残高インポートファイルの存在性チェック
If Not File.Exists(Server.MapPath( " ~/Temp/balance.xls " )) Then
lblInformation.ForeColor = Drawing.Color.Red
lblInformation.Text = " RS残高ファイルを先にインポートしてください。 "
Exit Sub
End If
' ファンド件数チェック
If arrRSFundCd.Length <> arrCustodyFundCd.Length Then
lblInformation.ForeColor = Drawing.Color.Red
lblInformation.Text = " ConfigファイルのRSファンドとCustodyファンドの件数が合っていません。もう一度確認してください。 "
End If
' OleDbConnectionの生成
con = New OleDbConnection(sConnectionString)
' 接続
con.Open()
For i As Integer = 0 To arrRSFundCd.Length - 1
strSQL = " INSERT INTO [UnMatchReport$] " & _
" SELECT R.[ポートフォリオ] AS [RSポートフォリオ], C.[Account Number] AS [Custodyファンドコード], R.[銘柄コード] AS [RS銘柄コード], C.[ISIN] AS [Custody銘柄コード], R.[銘柄名(日本語)] AS [銘柄名], R.[保有数量(通常)] AS [RS保有数量], C.[Total Units] AS [Custody保有数量] " & _
" FROM [RS残高$] R, [Custody残高$] C WHERE " & _
" R.[ポートフォリオ] = " & arrRSFundCd(i) & " AND " & _
" C.[Account Number] = " & arrCustodyFundCd(i) & " AND " & _
" R.[銘柄コード] = C.[ISIN] AND " & _
" R.[保有数量(通常)] <> CSTR(C.[Total Units]) "
cmd = New OleDbCommand(strSQL, con)
cmd.ExecuteNonQuery()
strSQL = " INSERT INTO [UnMatchReport$] " & _
" SELECT [ポートフォリオ] AS [RSポートフォリオ], NULL AS [Custodyファンドコード], [銘柄コード] AS [RS銘柄コード], NULL AS [Custody銘柄コード], [銘柄名(日本語)] AS [銘柄名], [保有数量(通常)] AS [RS保有数量], NULL AS [Custody保有数量] " & _
" FROM [RS残高$] WHERE [ポートフォリオ] = " & arrRSFundCd(i) & " AND NOT EXISTS (SELECT * FROM [Custody残高$] WHERE [Account Number] = " & arrCustodyFundCd(i) & " AND [ISIN] = [RS残高$].[銘柄コード]) "
cmd = New OleDbCommand(strSQL, con)
cmd.ExecuteNonQuery()
Next
' 接続閉じる
con.Close()
lblInformation.ForeColor = Drawing.Color.Black
lblInformation.Text = " UnMatchレポートを作成しました。 "
End Sub