Using Select statement for Insert Statement Value

Question

Can you please help me with this statement, i am trying to retrieve data using SELECT statement and use the date in the INSERT statement.

I want to use the data retrieved for ProfileId Value.

 // Get the UserId of the just-added user
    MembershipUser currentUser = Membership.GetUser();
    Guid currentUserId = (Guid)currentUser.ProviderUserKey;

// Insert a new record into UserPro
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

string insertSql = "INSERT INTO User_Friend(ProfileId1, ProfileId) VALUES(@FriendProfileId) SELECT ProfileId FROM User_Profile WHERE UserId = (@UserId)";


using (SqlConnection myConnection = new SqlConnection(connectionString))
{
    myConnection.Open();
    SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
    myCommand.Parameters.AddWithValue("@FriendProfileId", Request.QueryString["ProfileId"].ToString());

    myCommand.Parameters.AddWithValue("@UserId", currentUserId);
    myCommand.ExecuteNonQuery();
    myConnection.Close();
}

How do I use the SELECT result as the ProfileId Value.

possible duplicate of t-sql insert - select - with parameters — BCdotWEB
– BCdotWEB, Commented Dec 24, 2014 at 22:14

Gert Arnold · Accepted Answer · 2014-12-24 22:18:46Z

2

The insert statement should be

INSERT INTO User_Friend(ProfileId1, ProfileId)
VALUES ( @FriendProfileId, 
         (SELECT ProfileId FROM User_Profile WHERE UserId = @UserId))

or maybe SELECT TOP(1) ProfileId to make sure you will never get more than 1 value.

answered Dec 24, 2014 at 22:18

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

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

Comments

competent_tech · Accepted Answer · 2014-12-24 22:19:03Z

1

The insert SQL should be:

string insertSql = "INSERT INTO User_Friend(ProfileId1, ProfileId) SELECT @FriendProfileId, ProfileId FROM User_Profile WHERE UserId = (@UserId)";

You just include the variables directly in the SELECT statement in the position corresponding to the column name.

answered Dec 24, 2014 at 22:19

competent_tech

45.1k12 gold badges96 silver badges114 bronze badges

2 Comments

Carter Willams Over a year ago

thanks for your replay, i am only select ProfileId from userId not both

competent_tech Over a year ago

When you include the parameter like this, it doesn't select from the user table, it substitutes the value directly in the SQL. So lets say @FriendProfileId is set to 23, the SELECT statement actually ends up being SELECT 23, ProfileID FROM user_profile...

Collectives™ on Stack Overflow

Using Select statement for Insert Statement Value

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related