Select within an insert 2

[ksbrace]

Hello,
I am trying to perform an insert with a select embedded within the insert....with little success. I am making 2 inserts, the first would be the parent table and the second, the child. I'm attempting to get the customerid from the parent table and insert it into the child table as the foreign key. I was hoping someone could either right my wrong or explain what needs to be done. Thanks in advance!

Dim strSQL As String = "Insert into Customers(FirstName, LastName ) Values('" & fNameText.Text & "','" & lNameText.Text & "');"

----Second Insert
strSQL = "Insert into Jobs(CustomerID, Days, SpringRaking," & _
            "LawnCutting, FallLeaves, SnowPlowing, Labor, Materials, Misc) Values(Select Top 1 Customers.CustomerID from Customers order by Customers.CustomerID desc;,'" & fNameText.Text & _
            "','" & lNameText.Text & "','" & ddDays.SelectedValue & "'," & sprngRak & "," & lwnCttng & "," & fallLvs & _
            "," & snwPlwng & "," & laborText.Text & "," & MaterialsCost.Text & "," & misc & ");"

 

[lynchg]

In your second insert take out the keyword Values, and I don't think you need to put parentheses around your Select statement, and Top 1 will only bring back one record, which makes your Order By clause meaningless.

HTH

 

[ksbrace]

lynchg,
Thanks for the reply, but I'm not following you when you say 'take out the keyword values' Also, for the Order by clause, I want the very last insert into the customers table, not the first one. Thanks

 

[lespaul]

your inside SELECT query is structured wrong. It needs to be:

Select Top 1 Customers.CustomerID,'" & fNameText.Text & _
"','" & lNameText.Text & "','" & ddDays.SelectedValue & "'," & sprngRak & "," & lwnCttng & "," & fallLvs & _
"," & snwPlwng & "," & laborText.Text & "," & MaterialsCost.Text & "," & misc & " from Customers order by Customers.CustomerID desc;"

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[ksbrace]

lespaul,
Thanks, after a coffee I realized what lynchg was saying in their post.

strSQL = "Insert into Jobs(CustomerID, Days, SpringRaking," & _
            "LawnCutting, FallLeaves, SnowPlowing, Labor, Materials, Misc) Select Top 1 Customers.CustomerID," & _
            "'" & ddDays.SelectedValue & "'," & sprngRak & "," & lwnCttng & "," & fallLvs & _
            "," & snwPlwng & "," & laborText.Text & "," & MaterialsCost.Text & "," & misc & " from Customers order by customers.customerid;"