datagridview的使用，即时新增，更新，删除数据库数据

Public Class Form1 Dim CNUF As New SqlClient.SqlConnection Dim CNCOST As New SqlClient.SqlConnection Dim SQLSelect As New SqlClient.SqlCommand Dim Adapter As New SqlClient.SqlDataAdapter Dim str As System.Text.StringBuilder Dim Datatable As DataTable Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed CNUF.Close() CNCOST.Close() End Sub Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim Dt As DataTable Dim IA As Integer CNUF.ConnectionString = UF_CnStr() Try CNUF.Open() Catch ex As Exception MsgBox("无法连接用友服务器", MsgBoxStyle.Critical, Me.Text) Me.Close() Exit Sub End Try CNCOST.ConnectionString = COST_CnStr() Try CNCOST.Open() Catch ex As Exception MsgBox("无法连接COST服务器", MsgBoxStyle.Critical, Me.Text) Me.Close() Exit Sub End Try '查询业务分类 str = New System.Text.StringBuilder str.Append(" Select distinct ccode,ccode_name from code ") str.Append(" where ccode between 510101 and 510103 ") SQLSelect = New SqlClient.SqlCommand(str.ToString, CNUF) '=====タイムアウト設定===== SQLSelect.CommandTimeout = 0 Adapter = New SqlClient.SqlDataAdapter(SQLSelect) Dt = New DataTable Try Adapter.Fill(Dt) Catch EX As Exception MsgBox(EX.ToString, MsgBoxStyle.Critical, Me.Text) Exit Sub End Try If Dt.Rows.Count = 0 Then MsgBox("没有业务类型数据", MsgBoxStyle.Critical, Me.Text) Exit Sub End If Me.ClassListBox.Items.Add("ALL") For IA = 0 To Dt.Rows.Count - 1 Me.ClassListBox.Items.Add(Dt.Rows(IA).Item(0) & "&" & Dt.Rows(IA).Item(1)) Next ClassListBox.SetItemChecked(0, True) 'BACKUP str = New System.Text.StringBuilder str.Append(" Select distinct ccode,ccode_name from xcode ") str.Append(" where ccode between 510101 and 510103 ") SQLSelect = New SqlClient.SqlCommand(str.ToString, CNCOST) '=====タイムアウト設定===== SQLSelect.CommandTimeout = 0 Adapter = New SqlClient.SqlDataAdapter(SQLSelect) Dt = New DataTable Try Adapter.Fill(Dt) Catch EX As Exception MsgBox(EX.ToString, MsgBoxStyle.Critical, Me.Text) Exit Sub End Try If Dt.Rows.Count = 0 Then MsgBox("备份数据中没有业务类型数据", MsgBoxStyle.Critical, Me.Text) Exit Sub End If Me.BKClassListBox.Items.Add("ALL") For IA = 0 To Dt.Rows.Count - 1 Me.BKClassListBox.Items.Add(Dt.Rows(IA).Item(0) & "&" & Dt.Rows(IA).Item(1)) Next BKClassListBox.SetItemChecked(0, True) '查询品种 str = New System.Text.StringBuilder str.Append(" SELECT DISTINCT CDEPCODE,CDEPNAME FROM DEPARTMENT ") str.Append(" WHERE LEN(CDEPCODE)>4 AND (CDEPCODE LIKE 'C606%' OR CDEPCODE LIKE 'E4%' OR CDEPCODE LIKE 'J%' OR CDEPCODE LIKE 'N%'OR CDEPCODE LIKE 'L%' OR CDEPCODE LIKE 'K%') ") SQLSelect = New SqlClient.SqlCommand(str.ToString, CNUF) '=====タイムアウト設定===== SQLSelect.CommandTimeout = 0 Adapter = New SqlClient.SqlDataAdapter(SQLSelect) Dt = New DataTable Try Adapter.Fill(Dt) Catch EX As Exception MsgBox(EX.ToString, MsgBoxStyle.Critical, Me.Text) Exit Sub End Try If Dt.Rows.Count = 0 Then MsgBox("没有品种数据", MsgBoxStyle.Critical, Me.Text) Exit Sub End If Me.KindListBox.Items.Add("ALL") For IA = 0 To Dt.Rows.Count - 1 Me.KindListBox.Items.Add(Dt.Rows(IA).Item(0) & "&" & Dt.Rows(IA).Item(1)) Next KindListBox.SetItemChecked(0, True) 'backup str = New System.Text.StringBuilder str.Append(" SELECT DISTINCT CDEPCODE,CDEPNAME FROM xDEPARTMENT ") str.Append(" WHERE LEN(CDEPCODE)>4 AND (CDEPCODE LIKE 'C606%' OR CDEPCODE LIKE 'E4%' OR CDEPCODE LIKE 'J%' OR CDEPCODE LIKE 'N%'OR CDEPCODE LIKE 'L%' OR CDEPCODE LIKE 'K%') ") SQLSelect = New SqlClient.SqlCommand(str.ToString, CNCOST) '=====タイムアウト設定===== SQLSelect.CommandTimeout = 0 Adapter = New SqlClient.SqlDataAdapter(SQLSelect) Dt = New DataTable Try Adapter.Fill(Dt) Catch EX As Exception MsgBox(EX.ToString, MsgBoxStyle.Critical, Me.Text) Exit Sub End Try If Dt.Rows.Count = 0 Then MsgBox("备份数据没有品种数据", MsgBoxStyle.Critical, Me.Text) Exit Sub End If Me.BKKindListBox.Items.Add("ALL") For IA = 0 To Dt.Rows.Count - 1 Me.BKKindListBox.Items.Add(Dt.Rows(IA).Item(0) & "&" & Dt.Rows(IA).Item(1)) Next BKKindListBox.SetItemChecked(0, True) End Sub Private Sub Searchbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Searchbtn.Click Dim Dt As DataTable Dim DataTab As DataTable Dim SW As Boolean = False Dim i, j As Integer Dim IA As Integer Dim TMP, STRTMP As String str = New System.Text.StringBuilder str.Append(" Select A.INO_ID AS 凭证号,B.CCUSNAME AS 客户,A.NC_S AS 数量,mc_f AS 金额USD,mc AS 金额RMB,cdigest AS 摘要 ,A.CCODE ,A.DBILL_DATE ,A.CDEPT_ID,C.CDEPNAME,A.CCODE,D.CCODE_NAME from GL_ACCVOUCH AS A") str.Append(" INNER JOIN (") str.Append(" SELECT A.INO_ID, A.CCUS_ID,B.CCUSNAME FROM GL_ACCVOUCH AS A") str.Append(" INNER JOIN CUSTOMER AS B ") str.Append(" ON A.CCUS_ID=B.CCUSCODE ") str.Append(" WHERE INID=1 AND CCODE in(113102,113101) ") str.Append(" AND A.DBILL_DATE BETWEEN '").Append(Me.Fromdtp.Value.ToString("yyyy-MM-dd 00:00:00.000")).Append("'") str.Append(" AND '").Append(Me.Todtp.Value.ToString("yyyy-MM-dd 00:00:00.000")).Append("') AS B") str.Append(" ON A.INO_ID=B.INO_ID") str.Append(" INNER JOIN DEPARTMENT AS C ") str.Append(" ON A.CDEPT_ID=C.CDEPCODE ") str.Append(" INNER JOIN CODE AS D ") str.Append(" ON A.CCODE=D.CCODE ") str.Append(" WHERE A.DBILL_DATE BETWEEN '").Append(Me.Fromdtp.Value.ToString("yyyy-MM-dd 00:00:00.000")).Append("'") str.Append(" AND '").Append(Me.Todtp.Value.ToString("yyyy-MM-dd 00:00:00.000")).Append("'") str.Append(" AND A.CCODE IN (") If ClassListBox.GetItemCheckState(0) = CheckState.Checked Then 'ALLがチェックされている 'ALLは除くのでiは１からstart For i = 1 To ClassListBox.Items.Count - 1 'だぶん顧客コードは4桁だと思うがmid(xxxx,1,4) では心許ないので '区切り文字の"-"を探してmidする j = ClassListBox.Items(i).ToString.IndexOf("&") If SW = False Then '初回 str.Append("'").Append(Mid(ClassListBox.Items(i).ToString, 1, j)).Append("'") SW = True Else '2回目以降 str.Append(",'").Append(Mid(ClassListBox.Items(i).ToString, 1, j)).Append("'") End If Next Else For i = 1 To ClassListBox.Items.Count - 1 If ClassListBox.GetItemCheckState(i) = CheckState.Checked Then 'だぶん顧客コードは4桁だと思うがmid(xxxx,1,4) では心許ないので '区切り文字の"-"を探してmidする j = ClassListBox.Items(i).ToString.IndexOf("&") If SW = False Then '初回 str.Append("'").Append(Mid(ClassListBox.Items(i).ToString, 1, j)).Append("'") SW = True Else '2回目以降 str.Append(",'").Append(Mid(ClassListBox.Items(i).ToString, 1, j)).Append("'") End If End If Next End If str.Append(")") SW = False str.Append(" AND A.CDEPT_ID IN (") If KindListBox.GetItemCheckState(0) = CheckState.Checked Then 'ALLがチェックされている 'ALLは除くのでiは１からstart For i = 1 To KindListBox.Items.Count - 1 'だぶん顧客コードは4桁だと思うがmid(xxxx,1,4) では心許ないので '区切り文字の"-"を探してmidする j = KindListBox.Items(i).ToString.IndexOf("&") If SW = False Then '初回 str.Append("'").Append(Mid(KindListBox.Items(i).ToString, 1, j)).Append("'") SW = True Else '2回目以降 str.Append(",'").Append(Mid(KindListBox.Items(i).ToString, 1, j)).Append("'") End If Next Else For i = 1 To KindListBox.Items.Count - 1 If KindListBox.GetItemCheckState(i) = CheckState.Checked Then 'だぶん顧客コードは4桁だと思うがmid(xxxx,1,4) では心許ないので '区切り文字の"-"を探してmidする j = KindListBox.Items(i).ToString.IndexOf("&") If SW = False Then '初回 str.Append("'").Append(Mid(KindListBox.Items(i).ToString, 1, j)).Append("'") SW = True Else '2回目以降 str.Append(",'").Append(Mid(KindListBox.Items(i).ToString, 1, j)).Append("'") End If End If Next End If str.Append(")") If Trim(Invoicetxt.Text) <> "" Then str.Append(" AND A.CDIGEST LIKE '%").Append(Invoicetxt.Text).Append("%'") End If If Trim(Inoidtxt.Text) <> "" Then str.Append(" AND A.INO_ID=").Append(Inoidtxt.Text) End If str.Append(" ORDER BY A.INO_ID") SQLSelect = New SqlClient.SqlCommand(str.ToString, CNUF) '=====タイムアウト設定===== SQLSelect.CommandTimeout = 0 Adapter = New SqlClient.SqlDataAdapter(SQLSelect) Dt = New DataTable Try Adapter.Fill(Dt) Catch EX As Exception MsgBox(EX.ToString, MsgBoxStyle.Critical, Me.Text) Exit Sub End Try If Dt.Rows.Count = 0 Then MsgBox("没有业务数据", MsgBoxStyle.Critical, Me.Text) Exit Sub End If DataTab = New DataTable DataTab.Columns.Add("凭证号") DataTab.Columns.Add("摘 要") DataTab.Columns.Add("客 户") DataTab.Columns.Add("数 量") DataTab.Columns.Add("金额USD") DataTab.Columns.Add("金额RMB") DataTab.Columns.Add("单 价") DataTab.Columns.Add("运 费") DataTab.Columns.Add("发票号") DataTab.Columns.Add("日 期")