数据表问题 [英] DataTable Issue
问题描述
我的数据集中有两个数据表,它们使用代码(不是设计器)编写的语句加载到form_load的表单上.当我通过文本框更改第一个表的值时,对数据进行了更改设置.但是当我对第二张表尝试相同时,切换到另一家公司并返回时更改不会保留在数据集中.我使用第一张表的主键查询第二张表,但没有代码当我更改第一个表的值时需要使用它,因此我不明白为什么当我通过数据绑定的文本框更改值时第二个表却不起作用....
这是我最初绑定数据的代码:
I have two data tables within my data set that are loaded onto my form on form_load using statements written in code (not the designer. When I change a value for the first table via a text box the change is made to the data set. But when I try the same for the second table, the change doesn't remain in the data set when I switch to a different company and come back. I query the second table using the primary key of the first table but no code is needed when I change the values for the first table so I don't understand why the same doesn't work for the second when I change a value via a data-bound text box....
Here is my code that initially binds the data:
'This is used to read our data from the data base
Public Sub Read_Data_SQL()
Call Clear_Bindings()
Dim ConnectionString As String = "Data Source=BASF-DBS02; Initial Catalog=Goldmine_DayOld;Integrated Security=SSPI;"
Dim thisConnection As New SqlConnection(ConnectionString)
Dim qContact1 As String = "SELECT * FROM CONTACT1"
Dim qContact2 As String = "SELECT * FROM CONTACT2"
Dim qContSupp As String = "SELECT * FROM CONTSUPP"
Try
'Open Connection
thisConnection.Open()
'Create Data Adapter
'Dim daContact1 As New SqlDataAdapter(qContact1, thisConnection)
daContact1 = New SqlDataAdapter(qContact1, thisConnection)
daContact2 = New SqlDataAdapter(qContact2, thisConnection)
daContSupp = New SqlDataAdapter(qContSupp, thisConnection)
daContact1.Fill(dsGOLDMINE, "Contact1")
daContact2.Fill(dsGOLDMINE, "Contact2")
daContSupp.Fill(dsGOLDMINE, "ContSupp")
'Assign the command builders so that we don't have to write our own freakin' update commands. WISH I KNEW THIS FOUR DAYS AGO!!!
Dim Contact1CommandBuilder As New SqlCommandBuilder(daContact1)
Dim Contact2CommandBuilder As New SqlCommandBuilder(daContact2)
Dim ContSuppCommandBuilder As New SqlCommandBuilder(daContSupp)
'Set our unique constraints for the tables
Dim tContact1 As DataTable = dsGOLDMINE.Tables("Contact1")
Dim cContact1 As UniqueConstraint = New UniqueConstraint(tContact1.Columns("ACCOUNTNO"), True)
tContact1.Constraints.Add(cContact1)
Dim tContact2 As DataTable = dsGOLDMINE.Tables("Contact2")
'Set our relation up for the tables.
Dim relContact1 As DataRelation = New DataRelation("AccountNoRelation", tContact1.Columns("ACCOUNTNO"), tContact2.Columns("ACCOUNTNO"), True)
dsGOLDMINE.Relations.Add(relContact1)
dsGOLDMINE.EnforceConstraints = True
fMain.cmbCompany.DataSource = dsGOLDMINE
fMain.cmbCompany.DisplayMember = "Contact1.COMPANY"
'cmbvarious.DataSource = dsContact1
'cmbVarious.DisplyaMember = "Contact1.
fMain.cmbClientID.DataSource = dsGOLDMINE
fMain.cmbClientID.DisplayMember = "Contact1.KEY3"
'-------------------------------------------------------------------------------------------------------------'
' MAIN SECTION OF FORM '
'-------------------------------------------------------------------------------------------------------------'
fMain.lblCompanyDis.DataBindings.Add("text", dsGOLDMINE, "Contact1.COMPANY")
fMain.lblVariousDis.DataBindings.Add("text", dsGOLDMINE, "Contact2.UDBAAKALBL")
fMain.lblNotesDis.DataBindings.Add("text", dsGOLDMINE, "Contact1.KEY5")
fMain.lblClientIDDis.DataBindings.Add("text", dsGOLDMINE, "Contact1.KEY3")
fMain.lblTaxIDDis.DataBindings.Add("text", dsGOLDMINE, "Contact2.UTAXID")
fMain.lblSourceDis.DataBindings.Add("text", dsGOLDMINE, "Contact1.SOURCE")
fMain.lblAddress1Dis.DataBindings.Add("text", dsGOLDMINE, "Contact1.ADDRESS1")
fMain.lblAddress2Dis.DataBindings.Add("text", dsGOLDMINE, "Contact1.ADDRESS2")
fMain.lblAddress3Dis.DataBindings.Add("text", dsGOLDMINE, "Contact1.ADDRESS3")
fMain.lblCityDis.DataBindings.Add("text", dsGOLDMINE, "Contact1.CITY")
fMain.lblStateDis.DataBindings.Add("text", dsGOLDMINE, "Contact1.STATE")
fMain.lblZipDis.DataBindings.Add("text", dsGOLDMINE, "Contact1.ZIP")
fMain.lblMergeDis.DataBindings.Add("text", dsGOLDMINE, "Contact1.MERGECODES")
fMain.lblPhone1Dis.DataBindings.Add("text", dsGOLDMINE, "Contact1.PHONE1")
fMain.lblPhone2Dis.DataBindings.Add("text", dsGOLDMINE, "Contact1.PHONE2")
fMain.lblPhone3Dis.DataBindings.Add("text", dsGOLDMINE, "Contact1.PHONE3")
fMain.lblFaxDis.DataBindings.Add("text", dsGOLDMINE, "Contact1.FAX")
fMain.lblExt1Dis.DataBindings.Add("text", dsGOLDMINE, "Contact1.EXT1")
fMain.lblExt2Dis.DataBindings.Add("text", dsGOLDMINE, "Contact1.EXT2")
fMain.lblExt3Dis.DataBindings.Add("text", dsGOLDMINE, "Contact1.EXT3")
fMain.lblExt4Dis.DataBindings.Add("text", dsGOLDMINE, "Contact1.EXT4")
'lblWebDis.DataBindings.Add("text", dsContact1, "Contact1. Don't know what table this value is located
fMain.lblContactTypeDis.DataBindings.Add("text", dsGOLDMINE, "Contact1.KEY1")
fMain.lblPayAgency.DataBindings.Add("text", dsGOLDMINE, "Contact2.UAGNCDIRP")
fMain.lblBSRdis.DataBindings.Add("text", dsGOLDMINE, "Contact1.KEY4")
'lblBPSRDis.DataBindings.Add("text", dsGOLDMINE, "ubpsr") Don't know what table this value is located
'lblWebDis.DataBindings.Add("text", dsGOLDMINE, "website") Don't know what table this value is located
fMain.lblIndustryDis.DataBindings.Add("text", dsGOLDMINE, "Contact1.KEY2")
fMain.lblBAAReceivedDis.DataBindings.Add("text", dsGOLDMINE, "Contact2.UBAADRCVD")
fMain.lblEmployeeCountDis.DataBindings.Add("text", dsGOLDMINE, "Contact2.UTOTNUMEE")
fMain.lblSICdis.DataBindings.Add("text", dsGOLDMINE, "Contact2.USICCODE")
'-------------------------------------------------------------------------------------------------------------'
' MEMBERSHIP TAB '
'-------------------------------------------------------------------------------------------------------------'
fMain.lblSstLOS.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSSST")
fMain.lblCobraLOS.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSCOBRA")
fMain.lblFMLALOS.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSFMLA")
fMain.lblFsaLOS.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSFSA")
fMain.lblHraLOS.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSHRA")
fMain.lblHsaLOS.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSHSA")
fMain.lblParkLOS.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSPARK")
fMain.lblMbgLOS.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSMBG")
fMain.lblPayLOS.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSPAY")
fMain.lblPremLOS.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSPRMCON")
fMain.lblConConLOS.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSCONCON")
fMain.lblConProLOS.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSCONPRJ")
fMain.lblUnEmpLOS.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSUNEMP")
fMain.lblWrkCompLOS.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSWRKCMP")
fMain.lblHrAnsLOS.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSALINK")
fMain.lblWellLOS.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSWELL")
fMain.lblSstLC.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSDSST")
fMain.lblCobraLC.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSDCOBRA")
fMain.lblFmlaLC.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSDFMLA")
fMain.lblFsaLC.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSDFSA")
fMain.lblHraLC.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSDHRA")
fMain.lblHsaLC.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSDHSA")
fMain.lblParkLC.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSDPARK")
fMain.lblMbgLC.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSDMBG")
fMain.lblPayLC.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSDPAY")
fMain.lblPremLC.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSDPRCON")
fMain.lblConConLC.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSDCNCON")
fMain.lblConProLC.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSDCNPRJ")
fMain.lblUnEmpLC.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSDUNEMP")
fMain.lblWrkCompLC.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSDWRKCP")
fMain.lblHrAnsLC.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSDALINK")
fMain.lblWellLC.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSDWELL")
fMain.lblSstCSR.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCSRNMSST")
fMain.lblCobraCSR.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCSRNMCOB")
fMain.lblFmlaCSR.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCSRNMFMLA")
fMain.lblFsaCSR.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCSRNMFSA")
fMain.lblHraCSR.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCSRNMHRA")
fMain.lblHsaCSR.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCSRNMHSA")
fMain.lblParkCSR.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCSRNMPARK")
fMain.lblMbgCSR.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCSRNMMBG")
fMain.lblPayCSR.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCSRNMPAY")
'-------------------------------------------------------------------------------------------------------------'
' COBRA TAB '
'-------------------------------------------------------------------------------------------------------------'
fMain.lblCobStatus.DataBindings.Add("text", dsGOLDMINE, "Contact2.ULOSCOBRA")
fMain.lblCobCSR.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCSRNMCOB")
fMain.lblCobAgentOR.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCOBATOR")
fMain.lblCobAgencyOR.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCOBAYOR")
fMain.lblCobConRcvd.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCOBDCNRCV")
fMain.lblCobEff.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCOBDEFFWB")
fMain.lblCobHlth.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCOBNPART")
fMain.lblCobTermDate.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCOBDTERM")
fMain.lblCobTermReason.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCOBCWHYT")
fMain.lblCobAccNum.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCOBACCNUM")
fMain.lblCobUID.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCOBID")
fMain.lblCobPass.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCOBPWD")
fMain.lblCobSetup.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCOBDSETUP")
fMain.lblCobNotified.DataBindings.Add("text", dsGOLDMINE, "Contact2.UCOBDNOTIF")
'-------------------------------------------------------------------------------------------------------------'
' COBRA TAB '
'-------------------------------------------------------------------------------------------------------------'
Call fMain.Update_Contact_Tab()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Error")
Finally
'Close Connection
thisConnection.Close()
End Try
End Sub
这是当我的公司组合框的选定索引更改为使第二个表反映第一个表时所调用的代码:
Here is the code I call when the selected index of my company combo box changes to have the second table reflect that of the first:
'This sub populates any information contained in the Contact2 table
Private Sub Update_Contact_2()
Dim Company As String = cmbCompany.Text.Replace("'", "''")
Dim Expression As String = "COMPANY = '" & Company & "'"
Dim Row() As DataRow = dsGOLDMINE.Tables("Contact1").Select(Expression)
If Row.Length > 0 Then
Dim Account As String = Row(0)("ACCOUNTNO")
Dim Results() As DataRow = dsGOLDMINE.Tables("Contact2").Select("ACCOUNTNO= '" & Account & "'")
For Each dRow In Results
'Main Portion of Form
lblVariousDis.Text = dRow("UDBAAKALBL").ToString
lblTaxIDDis.Text = dRow("UTAXID").ToString
lblPayAgency.Text = dRow("UAGNCDIRP").ToString
lblBAAReceivedDis.Text = dRow("UBAADRCVD").ToString
lblEmployeeCountDis.Text = dRow("UTOTNUMEE").ToString
'Membership Tab LOS
lblSICdis.Text = dRow("USICCODE").ToString
lblSstLOS.Text = dRow("ULOSSST").ToString
lblCobraLOS.Text = dRow("ULOSCOBRA").ToString
lblFMLALOS.Text = dRow("ULOSFMLA").ToString
lblFsaLOS.Text = dRow("ULOSFSA").ToString
lblHraLOS.Text = dRow("ULOSHRA").ToString
lblHsaLOS.Text = dRow("ULOSHSA").ToString
lblParkLOS.Text = dRow("ULOSPARK").ToString
lblMbgLOS.Text = dRow("ULOSMBG").ToString
lblPayLOS.Text = dRow("ULOSPAY").ToString
lblPremLOS.Text = dRow("ULOSPRMCON").ToString
lblConConLOS.Text = dRow("ULOSCONCON").ToString
lblConProLOS.Text = dRow("ULOSCONPRJ").ToString
lblUnEmpLOS.Text = dRow("ULOSUNEMP").ToString
lblWrkCompLOS.Text = dRow("ULOSWRKCMP").ToString
lblHrAnsLOS.Text = dRow("ULOSALINK").ToString
lblWellLOS.Text = dRow("ULOSWELL").ToString
'Membership Tab Last Changed
lblSstLC.Text = dRow("ULOSDSST").ToString
lblCobraLC.Text = dRow("ULOSDCOBRA").ToString
lblFmlaLC.Text = dRow("ULOSDFMLA").ToString
lblFsaLC.Text = dRow("ULOSDFSA").ToString
lblHraLC.Text = dRow("ULOSDHRA").ToString
lblHsaLC.Text = dRow("ULOSDHSA").ToString
lblParkLC.Text = dRow("ULOSDPARK").ToString
lblMbgLC.Text = dRow("ULOSDMBG").ToString
lblPayLC.Text = dRow("ULOSDPAY").ToString
lblPremLC.Text = dRow("ULOSDPRCON").ToString
lblConConLC.Text = dRow("ULOSDCNCON").ToString
lblConProLC.Text = dRow("ULOSDCNPRJ").ToString
lblUnEmpLC.Text = dRow("ULOSDUNEMP").ToString
lblWrkCompLC.Text = dRow("ULOSDWRKCP").ToString
lblHrAnsLC.Text = dRow("ULOSDALINK").ToString
lblWellLC.Text = dRow("ULOSDWELL").ToString
'Membership Tab CSR
lblSstCSR.Text = dRow("UCSRNMSST").ToString
lblCobraCSR.Text = dRow("UCSRNMCOB").ToString
lblFmlaCSR.Text = dRow("UCSRNMFMLA").ToString
lblFsaCSR.Text = dRow("UCSRNMFSA").ToString
lblHraCSR.Text = dRow("UCSRNMHRA").ToString
lblHsaCSR.Text = dRow("UCSRNMHSA").ToString
lblParkCSR.Text = dRow("UCSRNMPARK").ToString
lblMbgCSR.Text = dRow("UCSRNMMBG").ToString
lblPayCSR.Text = dRow("UCSRNMPAY").ToString
'Cobra Tab
lblCobStatus.Text = dRow("ULOSCOBRA").ToString
lblCobCSR.Text = dRow("UCSRNMCOB").ToString
lblCobAgentOR.Text = dRow("UCOBATOR").ToString
lblCobAgencyOR.Text = dRow("UCOBAYOR").ToString
lblCobConRcvd.Text = dRow("UCOBDCNRCV").ToString
lblCobEff.Text = dRow("UCOBDEFFWB").ToString
lblCobHlth.Text = dRow("UCOBNPART").ToString
lblCobTermDate.Text = dRow("UCOBDTERM").ToString
lblCobTermReason.Text = dRow("UCOBCWHYT").ToString
lblCobAccNum.Text = dRow("UCOBACCNUM").ToString
lblCobUID.Text = dRow("UCOBID").ToString
lblCobPass.Text = dRow("UCOBPWD").ToString
lblCobSetup.Text = dRow("UCOBDSETUP").ToString
lblCobNotified.Text = dRow("UCOBDNOTIF").ToString
Next
End If
End Sub
推荐答案
不要大惊小怪,但是您当前正在运行什么版本的Visual Studio?如果它是> = 2005,请重新考虑当前正在使用的整个数据绑定方法.请确保在table1和table2上的数据集表适配器或命令构建器中都有一条update语句.如果数据模型不正确,将无法正确保存.这意味着您至少需要一个主键字段,我也将建议使用[index] identity列.谢谢查理
Not to be rue or anything but what version of Visual Studio are you currently running? If it is >= 2005 please reconsider the entire data binding methods currently being used. Please make sure you have an update statement either in dataset table adapter or command builder on both table1 and table2. If the data model is not correct it will not save correctly. Meaning you need at least one primary key field and I would also recommend and [index] identity column as well. Thanks Charlie
这篇关于数据表问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!