Using Multiple SQL Queries in One Statement

Question

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!

You should use Using especially with those connections. Aside from that, this is really a "debugging help" question. Does AddSalary run successfully? Any errors or warnings? Debug with locals and copy out the full SQL Query that is generated and run it against the database, does it insert the record? — Jacob H
– Jacob H, Commented Feb 13, 2018 at 13:35
First you create the new staff record, then in the AddSalary procedure you want to create it again (both sql statements use insert and insert statement does not have a where clause). The AddSalary should use update. Even better, obtain the salary information either wih a separate select, or transform your main insert into an insert ... select ... that retrieves the salary data on the fly. — Shadow
– Shadow, Commented Feb 13, 2018 at 13:51
@Shadow good call, didn't even notice they were inserting into the same table. The reason this doesn't work is that there's no StaffID in the Role table (because you just created the record in Staff). Just make this a single query and join in Role as part of your insert into staff. Then you will have a salary value. — Jacob H
– Jacob H, Commented Feb 13, 2018 at 14:26

Jacob H · Accepted Answer · 2018-02-14 21:58:58Z

1

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

edited Feb 14, 2018 at 21:58

answered Feb 13, 2018 at 14:30

Jacob H

2,5341 gold badge14 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Zochonis Over a year ago

Sorry for the late response! This is good but I am trying to retrieve the salary value from the 'Role' table and that is under the 'Salary' field and insert it into the new record that is created in the 'Staff' table, they don't use the same field names so it becomes a bit of a pain

Jacob H Over a year ago

Yes, this will select the salary value from the Role table (matching RoleID), and insert it into the Staff table with the other values. That is what you wanted right?

Jacob H Over a year ago

Ok I updated the post to show the SQL query by itself. Hopefully that helps you with understanding the change I made. Note that Salary is a column from Role and not a variable like the other values.

Zochonis Over a year ago

Yes but they have different field names. In the 'Staff' table which is where the new record goes to, the field name in which the salary value should go to is called 'StaffMemberWages'. That salary value is taken from the 'Role' table but it is under the field name 'Salary'. The INSERT INTO statement thinks there is a field in the 'Staff' table called 'Salary' when it is actually called 'StaffMemberWages'. It's a bit tricky to explain through text.

Jacob H Over a year ago

Ah that's not too bad, just change the column name in the insert statement. I updated the post.

Collectives™ on Stack Overflow

Using Multiple SQL Queries in One Statement

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related