none
DATA INSERTS and RETRIVALS in a Single Command Execution. RRS feed

  • Question

  • I was under the impression that you need to perform "command.ExecuteNonQuery()" for INSERT, UPDATE, DELETE operations and command.ExecuteReader() for Retrievals. Till you don't execute that statement, the operations will not take effect.

    Now, have a look below at the following code that Inserts values + Retrieves data (see commandtext).

     

    private void button1_Click(object sender, EventArgs e)

    {

     

    string commandText = "insert into employee(eid,ename,salary,age)values(@eid,@ename,@salary,@age)"+

     

    " select * from employee where eid=@eid";

    command =

    new SqlCommand("inerst_emp", connection);

    command.CommandType =

    CommandType.StoredProcedure;

    command.Parameters.AddWithValue(

    @"eid", textBox1.Text);

    command.Parameters.AddWithValue(

    @"ename", textBox2.Text);

    command.Parameters.AddWithValue(

    @"salary", textBox3.Text);

    command.Parameters.AddWithValue(

    @"age", textBox4.Text);

    connection.Open();

    int id = command.ExecuteNonQuery();

    reader = command.ExecuteReader();

    listBox1.Items.Clear();

     

    while (reader.Read())

    {

    listBox1.Items.Add(reader[0].ToString());

     

    MessageBox.Show(reader[1].ToString());

     

    MessageBox.Show(reader[2].ToString());

     

    MessageBox.Show(reader[3].ToString());

    }

    reader.Close();

    connection.Close();

    }

    THIS IS NOT WORKING and I have no clue as to why it is not working! Gives me an "Error Message" of the sort that

    "Duplicate Values cannot be inserted........"

    Can I please have a clear explanation from an Expert!

    Thanks in advance!

    Regards

    Ganesh

    • Moved by eryang Thursday, October 22, 2009 2:11 AM wrong forum (From:.NET Base Class Library)
    Wednesday, October 21, 2009 7:36 AM

Answers

  • You can't use both ExecuteNonQuery and ExecuteReader for same SqlCommand. In the above code ExecuteNonQuery is inserting data into the table and ExecuteReader is again trying to insert data in the table. Remove one of the two to make it work properly.
    Gaurav Khanna
    • Marked as answer by Zhipeng Lee Monday, October 26, 2009 10:13 AM
    Wednesday, October 21, 2009 1:48 PM

All replies

  • You can't use both ExecuteNonQuery and ExecuteReader for same SqlCommand. In the above code ExecuteNonQuery is inserting data into the table and ExecuteReader is again trying to insert data in the table. Remove one of the two to make it work properly.
    Gaurav Khanna
    • Marked as answer by Zhipeng Lee Monday, October 26, 2009 10:13 AM
    Wednesday, October 21, 2009 1:48 PM
  • In your case you execute both, INSERT and SELECT statements twice - first time when you call ExecuteNonQuery, and second time when you call ExecuteReader. You need to split your tasks into 2 separate one. The first one would insert data executing INSERT SQL statement calling ExecuteNonQuery method of SqlCommand. The second task will use SELECT query with SqlDataReader to retrieve the data.  
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, October 22, 2009 10:14 AM
    Moderator
  • You might want to look at the below example since it's rather similar to what you're trying to do.

    http://www.davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, October 22, 2009 8:59 PM
  • Hi....

    Just use the ExecuteReader() Itslef, it will do the both and returns 2 results sets.. I Hope...

    Onemore option, Fill the Command using DataAdapter, will do both and return 2 results in the DataSet where you are filling.

    Thanks..

    Best Regards,
    Gopi V

    If you have found this post helpful, please click the Vote as Helpful link (the green triangle and number on the top-left).

    If this post answers your question, click the Mark As Answered link below. It helps others who experience the same issue in future to find the solution.

    Saturday, October 24, 2009 6:17 AM