CODEWITHSUNDEEP
c#sqlsql-server
user1027620
user1027620

Reputation: 2785

Nested SELECT Issue

I am having a small issue with SqlServerCe. When using the below sqlString to perform a ExecuteNonQuery(); command I get the error below.

string sqlString = "INSERT INTO Images (UID, Name) " +
            "VALUES ((SELECT ID FROM Contributors WHERE ID = @UID), @Name);";

gives this error:

There was an error parsing the query. [ Token line number = 1,Token line offset = 41,Token in error = SELECT ]

Any suggestions?

Method

public static void InsertImage(Contributor contObj, string ImageName)
{
    string sqlString = "INSERT INTO Images (UID, Name) " +
        "VALUES ((SELECT ID FROM [Contributors] WHERE ID = @UID), @Name);";
    using (SqlCeConnection sqlConnection =
        new SqlCeConnection(WebConfigurationManager.ConnectionStrings["DefaultSQL"].ConnectionString))
    {
        SqlCeCommand sqlCommand = new SqlCeCommand(sqlString, sqlConnection);
        sqlCommand.Parameters.AddWithValue("@UID", contObj.ID);
        sqlCommand.Parameters.AddWithValue("@Name", ImageName);
        sqlConnection.Open();
        sqlCommand.ExecuteNonQuery();
        sqlConnection.Close();
    }
}

Upvotes: 0

Views: 213

Answers (3)

Taryn
Taryn

Reputation: 247850

you will want to use INSERT INTO...SELECT..FROM

string sqlString = "INSERT INTO Images (UID, Name) " +
            "SELECT ID, @Name FROM Contributors WHERE ID = @UID;";

If you already knew the value for @ID, then you could use the INSERT INTO... VALUES... query

INSERT INTO Images (UID, Name)
VALUES (@UID, @Name)

Edit, here is your code with the update query

public static void InsertImage(Contributor contObj, string ImageName)
{
    string sqlString = "INSERT INTO Images (UID, Name) " +
        "VALUES (@UID, @Name)";
    using (SqlCeConnection sqlConnection =
        new SqlCeConnection(WebConfigurationManager.ConnectionStrings["DefaultSQL"].ConnectionString))
    {
        SqlCeCommand sqlCommand = new SqlCeCommand(sqlString, sqlConnection);
        sqlCommand.Parameters.AddWithValue("@UID", contObj.ID);
        sqlCommand.Parameters.AddWithValue("@Name", ImageName);
        sqlConnection.Open();
        sqlCommand.ExecuteNonQuery();
        sqlConnection.Close();
    }
}

Upvotes: 1

Richard
Richard

Reputation: 30628

You can do it this way instead:

string sqlString = @"INSERT INTO Images(UID, Name)
                     SELECT ID, @Name
                     FROM Contributors
                     WHERE ID = @UID"

Upvotes: 0

SPFiredrake
SPFiredrake

Reputation: 3892

Why are you using a nested select here? Since @UID already references the ID column that you're selecting, can't you just do:

INSERT INTO Images (UID, Name) 
VALUES (@UID, @Name)

If you really MUST use a select, then take a look at bluefeet's answer. He's supplying the param that you specified (@Name) as one of the values being inserted, so its OK.

Upvotes: 2

Related Questions