asked on

SQL INSERT SELECT Query

Experts, I am trying to create an insert query. I need to insert some data from a user form and then need to get some data from two other tables. I cannot figure this out. Is what I am trying to do possible?

Please Help...

        strSql = "INSERT INTO ProjectProfile (ProjectName,QuoteNumber,LogDate,Freight,FOB,LineItems,GroupTotal,TotalList,IncludeService,Discount," & _
                 "QuoteLife,Onsite,OffSite,Training,Overtime,Saturday,SaturdayOvertime,Sunday,Hold,Travel,WeekendTravel,Mileage,Software) " & _
                 "VALUES (" & _
                 "'" & txtProjectName.Text & "','" & txtQuoteNumber.Text & "',#" & Today & "#),(" & _
                 "SELECT Freight,FOB,Lineitems,GroupTotal,TotalList,IncludeService,Discount,QuoteLife FROM QuoteDefaults),(" & _
                 "SELECT Onsite,Offsite,Training,Overtime,Saturday,SaturdayOvertime,Sunday,Hold,Travel,WeekendTravel,Mileage,Software FROM ServiceRates)"

Open in new window

plusone3055

your question is a little unclear...
are you asking for how to write the following query in VB.NET Syntax ?

jayakrishnabh

write a single select statement where you can pass first 3 values from the form values.

something like this..
Insert into ProjectProfile(columns...)
select txtProjectName.Text, txtQuoteNumber.Text, other columns...
FROM table

Basicfarmer

ASKER

Plusone3055, I was hoping that my attempt to create this query would show what I wanted to do. This query does not work. How should this be written so it works? I dont need any vb specifc syntax im just creating the string.

ASKER CERTIFIED SOLUTION

Carl Tawn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Basicfarmer

ASKER

Jayakrishnabh, this gave me an error at "Select Freight".

        'strSql = "INSERT INTO ProjectProfile (ProjectName,QuoteNumber,LogDate,Freight,FOB,LineItems,GroupTotal,TotalList,IncludeService,Discount," & _
        '         "QuoteLife,Onsite,OffSite,Training,Overtime,Saturday,SaturdayOvertime,Sunday,Hold,Travel,WeekendTravel,Mileage,Software) " & _
        '         "SELECT " & _
        '         "'" & txtProjectName.Text & "','" & txtQuoteNumber.Text & "',#" & Today & "#," & _
        '         "SELECT Freight,FOB,Lineitems,GroupTotal,TotalList,IncludeService,Discount,QuoteLife FROM QuoteDefaults," & _
        '         "SELECT Onsite,Offsite,Training,Overtime,Saturday,SaturdayOvertime,Sunday,Hold,Travel,WeekendTravel,Mileage,Software FROM ServiceRates"

Open in new window

Basicfarmer

ASKER

That was exactly what I needed, Thanks Carl...

AndyAinscow

>>I need to insert some data from a user form and then need to get some data from two other tables.

Is the data being inserted ONLY from the user form?
The get some data - is that after the INSERT have completed?

If the answer to both of the above questions is true then you need to perform the action in two (or more) distinct steps. First run the INSERT SQL command then the SELECT command(s) to return the data.