CODEWITHSUNDEEP
mysqlsql.netdatabasevb.net
Zochonis
Zochonis

Reputation: 81

Using Multiple SQL Queries in One Statement

Hey guys I have a bit of a problem on my hands,

Using VB.Net; I am trying to copy a field in one record in one table to a different field in another record in another table, whilst also inserting a new record using values that a user will have inserted into textboxes on one of my VB forms.

The two tables which are used are called 'Staff' and 'Role' and each of their contents are below:

Staff:

Will update later!

Role:

Will update later!

When a user has entered some values on to my form and presses a button, the following code is triggered

Private Sub AddSMButton_Click(sender As Object, e As EventArgs) Handles AddSMButton.Click
    If DbConnect() Then
        Dim SQLCmd As New OleDbCommand
        If StaffID = -1 Then
            With SQLCmd
                .Connection = cn
                .CommandText = "Insert into Staff (StaffMemberFirstName, StaffMemberLastName, StaffMemberDOB, StaffMemberGender, StaffMemberContactNumber, StaffMemberEmail, StaffMemberPostcode, StaffMemberHomeAddress, StaffMemberHoursWorked, UnitID, RoleID) " &
                    "Select @StaffMemberFirstName, @StaffMemberLastName, @StaffMemberDOB, @StaffMemberGender, @StaffMemberContactNumber, @StaffMemberEmail, @StaffMemberPostcode, @StaffMemberHomeAddress, @StaffMemberHoursWorked, @UnitID, @RoleID"
                .Parameters.AddWithValue("@StaffMemberFirstName", AddSM_FNInput.Text)
                .Parameters.AddWithValue("@StaffMemberLastName", AddSM_LNInput.Text)
                .Parameters.AddWithValue("@StaffMemberDOB", AddDOB_DTP.Value.Date)
                .Parameters.AddWithValue("@StaffMemberGender", AddSM_GInput.Text)
                .Parameters.AddWithValue("@StaffMemberContactNumber", AddSM_CNInput.Text)
                .Parameters.AddWithValue("@StaffMemberEmail", AddSM_EInput.Text)
                .Parameters.AddWithValue("@StaffMemberPostcode", AddSM_PInput.Text)
                .Parameters.AddWithValue("@StaffMemberHomeAddress", AddSM_AInput.Text)
                .Parameters.AddWithValue("@StaffMemberHoursWorked", AddHW_TB.Text)
                .Parameters.AddWithValue("@UnitID", AddU_CB.SelectedItem.Identifier)
                .Parameters.AddWithValue("@RoleID", AddR_CB.SelectedItem.Identifier)
                .ExecuteNonQuery()
                .CommandText = "Select @@Identity"
                StaffID = .ExecuteScalar
                AddSalary(StaffID)
            End With
            MessageBox.Show("The new record has been inserted!")
            AddSM_FNInput.Text = ""
            AddSM_LNInput.Text = ""
            AddSM_GInput.Text = ""
            AddSM_CNInput.Text = ""
            AddSM_EInput.Text = ""
            AddSM_PInput.Text = ""
            AddSM_AInput.Text = ""
            AddHW_TB.Text = ""
        End If
        cn.Close()
        StaffID = -1
    End If
End Sub

The code above is able to add a new record with the specified values correctly.

But it doesn't add the salary to the new record and I don't know why?

The following code is the AddSalary procedure

Private Sub AddSalary(StaffID)
    If DbConnect() Then
        Dim SQLCmd As New OleDbCommand
        With SQLCmd
            .Connection = cn
            .CommandText = "Insert into Staff (StaffMemberWages) " &
                "Select Salary " &
                "From Role " &
                "Where Role.RoleID = @RoleID and Staff.StaffID = @StaffID"
            .Parameters.AddWithValue("@RoleID", AddR_CB.SelectedItem.Identifier)
            .Parameters.AddWithValue("@StaffID", StaffID)
        End With
    End If
End Sub

There are a few reasons why I don't think it's working but am not sure if they are correct.

So to summarise, I am trying to create 1 SQL statement which will allow me to insert a new record into the 'Staff' table whilst also copying, into that new record, a value from the 'Role' table.

Any help would be greatly appreciated,

Thanks for reading and have a good day!

Upvotes: 0

Views: 624

Answers (1)

Jacob H
Jacob H

Reputation: 2505

You don't even need AddSalary here at all. You already have the RoleID, so select the Salary from Role as part of the Insert.

Private Sub AddSMButton_Click(sender As Object, e As EventArgs) Handles AddSMButton.Click
If DbConnect() Then
    Dim SQLCmd As New OleDbCommand
    If StaffID = -1 Then
        With SQLCmd
            .Connection = cn
            .CommandText = "Insert into Staff (StaffMemberFirstName, StaffMemberLastName, StaffMemberDOB, StaffMemberGender, StaffMemberContactNumber, StaffMemberEmail, StaffMemberPostcode, StaffMemberHomeAddress, StaffMemberHoursWorked, UnitID, RoleID, StaffMemberWages) " & //Add Salary to the fields being inserted
                "Select @StaffMemberFirstName, @StaffMemberLastName, @StaffMemberDOB, @StaffMemberGender, @StaffMemberContactNumber, @StaffMemberEmail, @StaffMemberPostcode, @StaffMemberHomeAddress, @StaffMemberHoursWorked, @UnitID, " &
                "@RoleID, Role.Salary FROM Role WHERE Role.RoleID = @RoleID" //I added this line
            .Parameters.AddWithValue("@StaffMemberFirstName", AddSM_FNInput.Text)
            .Parameters.AddWithValue("@StaffMemberLastName", AddSM_LNInput.Text)
            .Parameters.AddWithValue("@StaffMemberDOB", AddDOB_DTP.Value.Date)
            .Parameters.AddWithValue("@StaffMemberGender", AddSM_GInput.Text)
            .Parameters.AddWithValue("@StaffMemberContactNumber", AddSM_CNInput.Text)
            .Parameters.AddWithValue("@StaffMemberEmail", AddSM_EInput.Text)
            .Parameters.AddWithValue("@StaffMemberPostcode", AddSM_PInput.Text)
            .Parameters.AddWithValue("@StaffMemberHomeAddress", AddSM_AInput.Text)
            .Parameters.AddWithValue("@StaffMemberHoursWorked", AddHW_TB.Text)
            .Parameters.AddWithValue("@UnitID", AddU_CB.SelectedItem.Identifier)
            .Parameters.AddWithValue("@RoleID", AddR_CB.SelectedItem.Identifier)
            .ExecuteNonQuery()
            .CommandText = "Select @@Identity"
            StaffID = .ExecuteScalar
            // AddSalary(StaffID) Don't need this function anymore
        End With
        MessageBox.Show("The new record has been inserted!")
        AddSM_FNInput.Text = ""
        AddSM_LNInput.Text = ""
        AddSM_GInput.Text = ""
        AddSM_CNInput.Text = ""
        AddSM_EInput.Text = ""
        AddSM_PInput.Text = ""
        AddSM_AInput.Text = ""
        AddHW_TB.Text = ""
    End If
    cn.Close()
    StaffID = -1
End If
End Sub

Here is the SQL query separately:

Insert into Staff 
    (StaffMemberFirstName
    , StaffMemberLastName
    , StaffMemberDOB
    , StaffMemberGender
    , StaffMemberContactNumber
    , StaffMemberEmail
    , StaffMemberPostcode
    , StaffMemberHomeAddress
    , StaffMemberHoursWorked
    , UnitID
    , RoleID
    , StaffMemberWages) 
Select 
    @StaffMemberFirstName
    , @StaffMemberLastName
    , @StaffMemberDOB
    , @StaffMemberGender
    , @StaffMemberContactNumber
    , @StaffMemberEmail
    , @StaffMemberPostcode
    , @StaffMemberHomeAddress
    , @StaffMemberHoursWorked
    , @UnitID
    , @RoleID
    , Role.Salary 
FROM Role 
WHERE Role.RoleID = @RoleID

Upvotes: 1

Related Questions