CODEWITHSUNDEEP
c#mysql
Phil
Phil

Reputation: 147

How to execute two queries in one go in C#?

I'm having trouble executing two queries in C#. When I attempt to execute the second, it gives me the following message:

Column 'FIRST_NAME' cannot be null

...which is referring to my first query that was previously executed successfully. I'm thinking the second query still has this column name in it even though I'm not using it here.

As you can see, I created a new StringBuilder object and cleared my parameters for the second query, but somehow I get this message still. 

        // Executed first query. Now moving onto second query without calling .Close()

        // Store the last inserted id as the child's local id
        child.ID = cmd.LastInsertedId;

        // Store the child id into the parent record
        query = new StringBuilder();
        cmd.Parameters.Clear(); 

        query.Append("UPDATE players SET child_id = @child_id WHERE player_id = @player_id;");

        cmd.Parameters.AddWithValue("child_id", child.ID);
        cmd.Parameters.AddWithValue("player_id", child.ParentID);

        // Execute query
        rows_affected = cmd.ExecuteNonQuery();

        conn.Close();

Upvotes: 0

Views: 87

Answers (1)

Dan D
Dan D

Reputation: 2523

Looks like you're not setting the CommandText property to your new query. You are still executing the first query with the parameters of the second.

    // Executed first query. Now moving onto second query without calling .Close()

    // Store the last inserted id as the child's local id
    child.ID = cmd.LastInsertedId;

    // Store the child id into the parent record
    query = new StringBuilder();
    cmd.Parameters.Clear(); 

    query.Append("UPDATE players SET child_id = @child_id WHERE player_id = @player_id;");

    //*** ADD THIS LINE HERE ***//
    cmd.CommandText = query.ToString();

    cmd.Parameters.AddWithValue("child_id", child.ID);
    cmd.Parameters.AddWithValue("player_id", child.ParentID);

    // Execute query
    rows_affected = cmd.ExecuteNonQuery();

    conn.Close();

Upvotes: 1

Related Questions