INSERT INTO with SELECTS

[acent]

Greetings,

I'm rethinking some previously written queries for a website. The site is VB.NET 2 with Access2k3 on the backside.

Right now, I have a series of queries that mixes with user input to build another query. Here's some pseudocode to illustrate

Dim user_rowid As Integer = "SELECT user_rowid FROM tblUsers Where username = '" & User.Identity.Name & "'"

Dim paytype_rowid As Integer = "SELECT paytype_rowid FROM tblPayTypes Where title = '" & Request.Form("ddlPayType") & "'"

Dim strSQL As String = "INSERT INTO tblData (user_rowid, fld1, fld2, ... , paytype_rowid) VALUES (" & user_rowid & "," & Request.Form("fld1") & "," & Request.Form("fld2") & "," & ... & "," & paytype_rowid & ")"

Question can and how exactly do I do something to the effect of:

Dim strSQL As String = "INSERT INTO tblData (user_rowid, fld1, fld2, ... , paytype_rowid) VALUES ((SELECT user_rowid FROM tblUsers WHERE username = " & User.Identity.Name & ")," & Request.Form("fld1") & "," & Request.Form("fld2") & "," & ... & ",(SELECT paytype_rowid FROM tblPayTypes WHERE title  = " & Request.Fomr("ddlPayType") & "))"

And even if the above is possible, is there a better way? My SQL optimization skills are a little subpar.

Thanks in advance for help.

"If it's stupid but works, it isn't stupid."
-Murphy's Military Laws

 

[HarleyQuinn]

If you're using a SELECT to drive your INSERT you don't need to include the VALUES keyword.

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com