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

最新推荐文章于 2024-04-26 15:39:50 发布

原创

最新推荐文章于 2024-04-26 15:39:50 发布 · 2.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #integer #exception #string #ini #function

这个博客介绍了如何在VB.NET中使用datagridview组件进行数据库的实时新增、更新和删除操作。首先，它展示了如何连接到两个不同的数据库服务器，然后通过SQL查询获取业务分类和品种数据，并填充到列表框中。当用户选择特定条件并点击搜索按钮时，程序将根据选定的业务类型和品种查询销售数据。此外，提供了编辑和停止编辑的功能，允许用户修改和保存datagridview中的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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("日