none
如何在SQL更新父表時, 同時把SQL 派給父表的ID, 指派給相關的子表外鍵欄位??? RRS feed

  • 問題

  • Imports System.Text
    Imports System.Data.SqlClient
    Public Class TEST
        Private daTest01, daTest02 As New SqlDataAdapter
        Private dsTest As DataSet
    
        Private Conn As SqlConnection
        Private dbTrans As SqlTransaction = Nothing
        Private SqlStrBdr As New StringBuilder
        'Private REL As DataRelation
        Private mDrHdr, mDrDtl As DataRow
    
        Public Sub New()
    
            ' This call is required by the designer.
            InitializeComponent()
    
            ' Add any initialization after the InitializeComponent() call.
    
        End Sub
    
        Private Sub TEST_Load(sender As Object, e As EventArgs) Handles Me.Load
            Try
                Me.TextBox1.ReadOnly = True
                Me.TextBox2.ReadOnly = True
                Dim objCon As New CL.Security.Conn
                Conn = New SqlConnection(objCon.ConnStrBdrComm.ToString)
                EnquireRst()
                SetRelation()
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, ex.Source.ToString)
            End Try
        End Sub
    
    
    
        Private Function EnquireRst() As DataSet
            Try
                If Conn.State <> ConnectionState.Open Then Conn.Open()
                dsTest = New DataSet
                daTest01.SelectCommand = New SqlCommand("SELECT * FROM TEST01", Conn)
                daTest01.Fill(dsTest, "T1")
                daTest02.SelectCommand = New SqlCommand("SELECT * FROM TEST02", Conn)
                daTest02.Fill(dsTest, "T2")
                Return dsTest
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, ex.Source.ToString)
            Finally
                If Conn.State <> ConnectionState.Closed Then Conn.Close()
            End Try
        End Function
        Private Sub SetRelation()
            Try
                Dim PKey(1) As DataColumn
                PKey(0) = dsTest.Tables(0).Columns("ID")
                dsTest.Tables(0).Columns("ID").AutoIncrement = True
                dsTest.Tables(0).Columns("ID").AutoIncrementSeed = -1
                dsTest.Tables(0).Columns("ID").AutoIncrementStep = -1
                dsTest.Tables(0).PrimaryKey = PKey
                Dim REL As DataRelation = New DataRelation("REL1", dsTest.Tables(0).Columns("ID"), dsTest.Tables(1).Columns("HdrID"))
                dsTest.Relations.Add(REL)
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, ex.Source.ToString)
            End Try
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Try
                'Save
                Me.TextBox1.ReadOnly = True
                Me.TextBox2.ReadOnly = True
                mDrHdr.Item("HDRTEXT") = Me.TextBox1.Text
                dsTest.Tables(0).Rows.Add(mDrHdr)
                mDrDtl.Item("DTLTEXT") = Me.TextBox2.Text
                mDrDtl.SetParentRow(mDrHdr)
                dsTest.Tables(1).Rows.Add(mDrDtl)
                SaveDB()
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, ex.Source.ToString)
            End Try
        End Sub
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Try
                'Add New
                Me.TextBox1.ReadOnly = False
                Me.TextBox2.ReadOnly = False
                Me.TextBox1.Text = ""
                Me.TextBox2.Text = ""
                mDrHdr = dsTest.Tables(0).NewRow
                mDrDtl = dsTest.Tables(1).NewRow
    
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, ex.Source.ToString)
            End Try
        End Sub
    
        Private Sub SaveDB()
            Try
                If Conn.State <> ConnectionState.Open Then Conn.Open()
                dbTrans = Conn.BeginTransaction
                SqlStrBdr.Clear()
                With SqlStrBdr
                    .Append("INSERT INTO TEST01 (HDRTEXT) VALUES (@HDRTEXT)")
                End With
                Me.daTest01.InsertCommand = New SqlCommand(SqlStrBdr.ToString, Conn, dbTrans)
                daTest01.InsertCommand.Parameters.Add("@HDRTEXT", SqlDbType.NVarChar, 50, "HDRTEXT")
                Dim NUMROW As Integer = daTest01.Update(dsTest.Tables(0))
                If NUMROW > 0 Then
    
                    SqlStrBdr.Clear()
                    With SqlStrBdr
                        .Append("INSERT INTO TEST02 (HDRID,DTLTEXT) VALUES (@HDRID,@DTLTEXT)")
                    End With
                    Me.daTest02.InsertCommand = New SqlCommand(SqlStrBdr.ToString, Conn, dbTrans)
                    daTest02.InsertCommand.Parameters.Add("@HDRID", SqlDbType.BigInt, 0, "HDRID")
                    daTest02.InsertCommand.Parameters.Add("@DTLTEXT", SqlDbType.NVarChar, 50, "DTLTEXT")
                    daTest02.Update(dsTest.Tables(1))
                    dsTest.Tables(0).AcceptChanges()
                    dsTest.Tables(1).AcceptChanges()
                End If
                MsgBox("Save Completed!")
                dbTrans.Commit()
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, ex.Source.ToString)
            End Try
        End Sub
    
    End Class

    這是我用作測試的FORM.. 

    我已在DATATABLE 之間建了RELATION...

    ADD NEW ROW 時亦用了SETPARENTROW()

    但我更新到SQL 后, 沒有把SQL 派給父表的ID, 更新到子表的DATATABLE裡???

    是那裡出錯了? 求大大們幫忙呢...

    2014年1月23日 上午 03:18

解答

所有回覆