Join

[OuTCasT]

hi all

i have got a win app that has 2 screens
SalaryScreen and EmployeeScreen



now i bind the data to a dataTable manually in the code like this.

Try
            sqlConnection = New SqlConnection("Data Source=DONOVANPC\SQLEXPRESS;Initial Catalog=DemoDB;Integrated Security=True;Pooling=False")
            sqlConnection.Open()
            sqlcommand = New SqlCommand("USE " + My.Settings.sqlDataBaseName + vbCrLf & _
                                        "SELECT * FROM EmployeeDetails", sqlConnection)
            sqlAdapter = New SqlDataAdapter
            sqlAdapter.SelectCommand = sqlcommand
            sqlDataTable = New DataTable
            sqlAdapter.Fill(sqlDataTable)
 
            ' Bind Controls with datatable
            txtEmployeeCode.DataBindings.Add("text", sqlDataTable, "EmployeeCode", True)
            txtSurname.DataBindings.Add("text", sqlDataTable, "EmployeeSurname", True)
            txtInitials.DataBindings.Add("Text", sqlDataTable, "EmployeeInitials", True)
            txtFirstNames.DataBindings.Add("text", sqlDataTable, "EmployeeFirstName", True)
            cbTitle.DataBindings.Add("Text", sqlDataTable, "EmployeeTitle", True)
            txtNickName.DataBindings.Add("text", sqlDataTable, "EmployeeNickName", True)
            txtPhysAddress1.DataBindings.Add("text", sqlDataTable, "EmployeePhysicalAddress1", True)
            txtPhysAddress2.DataBindings.Add("text", sqlDataTable, "EmployeePhysicalAddress2", True)
            txtPhysAddress3.DataBindings.Add("text", sqlDataTable, "EmployeePhysicalAddress3", True)
            txtPhysPCode.DataBindings.Add("text", sqlDataTable, "EmployeePhysicalPostalCode", True)
            cbGroup.DataBindings.Add("text", sqlDataTable, "EmployeeGroup", True)
            cbGender.DataBindings.Add("text", sqlDataTable, "EmployeeGender", True)
            cbLanguage.DataBindings.Add("text", sqlDataTable, "EmployeeLanguage", True)
            cbMaritalStatus.DataBindings.Add("text", sqlDataTable, "EmployeeMaritalStatus", True)
            txtTelHome.DataBindings.Add("text", sqlDataTable, "EmployeeTelHome", True)
            txtCellNo.DataBindings.Add("text", sqlDataTable, "EmployeeCellNo", True)
            txtTelWork.DataBindings.Add("text", sqlDataTable, "EmployeeTelWork", True)
            txtWorkExt.DataBindings.Add("text", sqlDataTable, "employeeWorkExt", True)
            txtRoomNo.DataBindings.Add("text", sqlDataTable, "EmployeeRoomNo", True)
            txtEmailAddress.DataBindings.Add("text", sqlDataTable, "EmployeeEmailAddress", True)
            txtPostalAdd1.DataBindings.Add("text", sqlDataTable, "EmployeePostalAddress1", True)
            txtPostalAdd2.DataBindings.Add("text", sqlDataTable, "EmployeePostalAddress2", True)
            txtPostalAdd3.DataBindings.Add("text", sqlDataTable, "EmployeePostalAddress3", True)
            txtPCode.DataBindings.Add("text", sqlDataTable, "EmployeePostalCode", True)
            txtID.DataBindings.Add("text", sqlDataTable, "EmployeeIdNumber", True)
            txtPassport.DataBindings.Add("text", sqlDataTable, "EmployeePassportNumber", True)
            cbNature.DataBindings.Add("text", sqlDataTable, "EmployeeNature", True)
            txtTaxNo.DataBindings.Add("text", sqlDataTable, "EmployeeTaxNumber", True)
            dtDateOfBirth.DataBindings.Add("Text", sqlDataTable, "EmployeeDateBirth", True)
            dtEngageDate.DataBindings.Add("Text", sqlDataTable, "EmployeeEngageDate", True)
            dtIRP5Date.DataBindings.Add("Text", sqlDataTable, "EmployeeIRP5Date", True)
            dtPensionStart.DataBindings.Add("Text", sqlDataTable, "EmployeePensionStartDate", True)
            dtProvidentStart.DataBindings.Add("Text", sqlDataTable, "EmployeeProvidentStartDate", True)
            dtMedicalStart.DataBindings.Add("Text", sqlDataTable, "EmployeeMedicalStartDate", True)
            dtRAStart.DataBindings.Add("Text", sqlDataTable, "EmployeeRAStartDate", True)
            dtStudyExpirey.DataBindings.Add("Text", sqlDataTable, "EmployeeStudyExpireyDate", True)
            cbPaymentType.DataBindings.Add("text", sqlDataTable, "EmployeePaymentType", True)
            cbTaxStatus.DataBindings.Add("text", sqlDataTable, "EmployeeTaxStatus", True)
            cbNormalStatus.DataBindings.Add("text", sqlDataTable, "EmployeeNormalStatus", True)
            dtResign.DataBindings.Add("Text", sqlDataTable, "EmployeeResignationDate", True)
            txtResignReason.DataBindings.Add("Text", sqlDataTable, "EmployeeResignationReason", True)
            dtContractExpirey.DataBindings.Add("Text", sqlDataTable, "EmployeeContractExpireyDate", True)
            txtPensionE.DataBindings.Add("text", sqlDataTable, "EmployeePensionEmp", True)
            txtPensionC.DataBindings.Add("text", sqlDataTable, "EmployeePensionCompany", True)
            cbAdultDeps.DataBindings.Add("text", sqlDataTable, "EmployeeMedAdultDeps", True)
            cbChildDeps.DataBindings.Add("text", sqlDataTable, "EmployeeMedChildDeps", True)
            cbPaymentMethod.DataBindings.Add("text", sqlDataTable, "EmployeePayMethod", True)
            txtBranchCode.DataBindings.Add("text", sqlDataTable, "EmployeeBankCode", True)
            txtBankAccNumber.DataBindings.Add("text", sqlDataTable, "EmployeeBankAccNo", True)
            cbAccountType.DataBindings.Add("text", sqlDataTable, "EmployeeBankAccType", True)
            txtArea.DataBindings.Add("text", sqlDataTable, "EmployeeArea", True)
            txtCategory.DataBindings.Add("text", sqlDataTable, "EmployeeCategory", True)
            txtCostCentre.DataBindings.Add("text", sqlDataTable, "EmployeeCostCentre", True)
            txtDepartment.DataBindings.Add("text", sqlDataTable, "EmployeeDepartment", True)
            txtOccupation.DataBindings.Add("text", sqlDataTable, "EmployeeOccupation", True)
            txtPayPoint.DataBindings.Add("text", sqlDataTable, "EmployeePayPoint", True)
            sqlConnection.Close()
 
            ' Establish Navigation Manager
            sqlManager = DirectCast(Me.BindingContext(sqlDataTable), CurrencyManager)
        Catch sqlexc As SqlException
            MsgBox(sqlexc.Message, MsgBoxStyle.OkOnly, "SQL Exception Error")
        Catch exc As Exception
            MsgBox(exc.Message, MsgBoxStyle.OkOnly, "Connection Failed")
        End Try
        SetText()
    End Sub

the query used is

USE + My.Settings.sqlDataBaseName + vbCrLf & _
                                        "SELECT * FROM EmployeeDetails", sqlConnection

now i have 2 tables, earnings and employees with a commen column called EmployeeID

now i want to for instance get information from both tables at the same time and update the values of both tables at the same time when im working with the dataTable. the tables information well some of it will need to update each other.

is this possible with a JOIN... ???

[ck9663]

Is it possible to update two tables at the same time? Technically, no. Stored proc, batches still has to be processed sequentially (the first statement on the batch has to be executed before the second one). But there are work around: Use view.

Also if you're trying to access table or tables that would require you to build this query, I would recommend you consider views or table-valued functions.

-- CK