I have an old VB.NET application in production that recently developed an issue (probably caused while fixing something else, you know how that goes). The grid uses data from two SQL Server tables displayed on the same grid like this:
TableAcol1 TableAcol2 TableBcol3 TableBcol4 TableBcol5
col1 and col 2 are common keys in the database, with cascading enabled from TableA to TableB so when TableA updates col1 and/or col2 those changes are automatically applied to TableB in the database.
My problem comes in when updating TableB which gets a foreign key error (TableB is trying to update col1 and col2, but those original keys no longer exist) so I get a foreign key error.
Is there any sample code for this situation using Ultragrid with two tables?
I have a couple of other similar situations and would like to gig hire an UltraGrid ninja just to solve these issues. Where is the best place to do get qualified help?
Please provide an isolated sample project demonstrating the behavior and mockup of what your desired outcome would be I'd be more than happy to investigate this for you. Let me know if you have any questions.
I don't think that's correct. We've been doing it this way for many years. Only recently has the issue developed with updating the key fields - possibly since we added the SQL cascading. The code has two SqlDataAdapters like this:rdoPlcyAt = New DataSet()strSql = "select AcctNum,AcctDate,AcctRevNum,PlcyNum,PlcyIncDate,PlcyExpDate,CancelDate,PlcyAgentID,CompanyCode from Plcy " &" WHERE AcctNum='" & mstrPassAcctNum & "' AND AcctDate='" & mstrPassAcctDate & "' AND AcctRevNum='" & mstrPassAcctRevNum & "' " & " ORDER BY AcctNum,PlcyNum "sqlCmd = New SqlCommand(strSql, New SqlConnection(My.Application.GlobalAuditDB.PAuditDB.PGetConnection.ConnectionString))PlcyAdapt = New SqlDataAdapter(sqlCmd)rdoCmdBuld = New SqlCommandBuilder(PlcyAdapt)rdoCmdBuld.ConflictOption = ConflictOption.OverwriteChangesPlcyAdapt.Fill(rdoPlcyAt, "Policy")strSql = "select AcctNum,AcctDate,AcctRevNum,PlcyNum,PlcyExpDate,LOB,AuditType,AuditorID,AuditDate,AuditFromDate,AuditToDate,InsLimitsOcc,InsLimitsAgg from Audits" &" WHERE AcctNum='" & mstrPassAcctNum & "' AND AcctDate='" & mstrPassAcctDate & "' AND AcctRevNum='" & mstrPassAcctRevNum & "' " & " ORDER BY AcctNum,PlcyNum "sqlCmd = New SqlCommand(strSql, New SqlConnection(My.Application.GlobalAuditDB.PAuditDB.PGetConnection.ConnectionString))AuditAdapt = New SqlDataAdapter(sqlCmd)rdoCmdBuld = New SqlCommandBuilder(AuditAdapt)rdoCmdBuld.ConflictOption = ConflictOption.OverwriteChangesAuditAdapt.Fill(rdoPlcyAt, "Audits")=============<somewhere in here the data is bound to the grid>strSQL = "SELECT Plcy.AcctNum,Plcy.AcctDate,Plcy.AcctRevNum,Plcy.PlcyNum,Plcy.PlcyIncDate,Plcy.PlcyExpDate,Plcy.CancelDate,Plcy.PlcyAgentID,Plcy.CompanyCode,Audits.LOB,Audits.AuditType,Audits.AuditorID,Audits.AuditDate,Audits.AuditFromDate,Audits.AuditToDate,Audits.InsLimitsOcc,Audits.InsLimitsAgg FROM Plcy " &" LEFT OUTER JOIN Audits ON Plcy.AcctNum=Audits.AcctNum And Plcy.AcctDate=Audits.AcctDate And Plcy.AcctRevNum=Audits.AcctRevNum And Plcy.PlcyNum=Audits.PlcyNum And Plcy.PlcyExpDate=Audits.PlcyExpDate " &" WHERE Plcy.AcctNum='" & mstrPassAcctNum & "' AND Plcy.AcctDate='" & mstrPassAcctDate & "' AND Plcy.AcctRevNum='" & mstrPassAcctRevNum & "' " &" ORDER BY Plcy.AcctNum,Plcy.PlcyNum "rdoRs = New DataSetrdoRs = My.Application.GlobalAuditDB.MPrepareDataSet(strSql)grdGrid1.DataSource = rdoRsgrdGrid1.DataMember = rdoRs.Tables(0).TableName=============And when updating the tables are updating separately (code stripped down for clarity):Private Sub grdGrid1_AfterUpdate(ByVal eventSender As System.Object, ByVal eventArgs As RowEventArgs) Handles grdGrid1.AfterRowUpdate PlcyAdapt.Update(rdoPlcyAt.Tables("Policy")) AuditAdapt.Update(rdoPlcyAt.Tables("Audits"))End Sub
The grid has no way to reposition the tables alongside each other, only hierarchical with expanded rows, your datasource would need to be constructed in that manner where you have 1 new table with all the columns from other tables in your dataset manually.
Thanks for the responses. To clarify, this is a hierarchical situation, a one-to-many relationship between the two tables.
I see on the link posted by Michael that Georgi is using a DataRelation, which I have not used before. In my situation, however, the tables are displayed on the same line, so there is no drop-down, popup, or subgrid as shown in the example by Georgi in the same post about DataRelation. Is this possible?
I'm having a hard time understanding what you are trying to do here. Presumably, you have a join that creates a table that includes columns from two different database table. But that would have to be done on the DataSource, not the grid. Likewise, any cascading updates that need to happen here would also have to be handle by the DataSource. I'm not sure if DataTable or DataSet have the ability to set up updates like that automatically. You might have to code it yourself. But either way, the grid won't do it for you.