Very Complecated insert query, divide it or not? And how?

[assimang]

I have this query:

NewUserSaveSettingsSql = "Insert into UserSettingsBookmarks (UserID,SettingsID) values ('" & _
TempUsername & "'," & "select SettingsID from Settings where SelectionValue='" & True & "')"

I am getting syntax error when I am trying to execute it.
NewUserSaveSettingsCmd = New SqlCommand(NewUserSaveSettingssql, MyConn)
NewUserSaveSettingsCmd.ExecuteNonQuery()

Well, I want to insert into UserSettingsBookmarks in fields UserID the value of variable TempUserName and in field SettingsID the result of query "select SettingsID from Settings where SelectionValue=True" Is it possible to do this in one sql statement or must I have to seperate it in 2 queries? Any help will be much appreciated.

Thank you
in advanced.

 

[SgtJarrow]

Three things..
First, what error are you getting? That will help in determining where the problem is.
Second, you might need to check the inner select. are your values actually TEXT, such as 'TRUE' and 'FALSE'. Or are the a bit field? If they are bit, you need: "select SettingsID from Settings where SelectionValue=1)" instead of what you currently have.
Third, based on what you posted, you are not opening and closing your connection to SQL, so the error you are getting might be related to the connection state not being open. Something like:

Dim con As New SQLConnection(ConnectionString)
Dim com As Bew SQLCommand(SQLString, con)
Try
    con.Open
    com.ExecuteNonQuery
Catch ex As Exception
Finally
    con.Close
End Try

It's always best to enclose this in a Try/Catch block as I did here to handle connection state and other expected errors.

Hope that helps...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[assimang]

Thank you much mstrmage1768 I just solve my problem. I was getting syntax error in NewUserSaveSettingsCmd.ExecuteNonQuery()

and the solution is
NewUserSaveSettingsSql = "Insert into UserSettingsBookmarks (UserID,SettingsID) " & _
"select Users.UserID, Settings.SettingsID from Users, Settings where Settings.SelectionValue='" & True & _
"' and Users.UserID='" & TempUsername & "'"

My problem was that I didn't know how to structure the query to include both the value of TempUsername variable and the result of subquery.