locked
Row being added twice RRS feed

  • Question

  • User-1662567569 posted

    Hi

    I have written the following code to  insert values from text boxes into a database and to retrieve the ID of the new row. The problem is that the code seems to insert two rows. When I comment the sqldateader code it works fine. I've pasted the code below. Would be very grateful for some pointers...

    // Establish connection to the database and add the values from the Summary, Description and Resoltion text boxes.

    string strConn = "Data Source=siopssqldb01;Initial Catalog=cccguidev2;User ID=cccguidewebuser;Password=CcCw3b!@";

    SqlConnection CCCguidev2 = new SqlConnection(strConn);
    CCCguidev2.Open();

    string SummaryIns = Summary.Text;
    string DescIns = Description.Text;
    string ResIns = Resolution.Text;

    String strSQL = "INSERT INTO Docs (Summary, Description, Resolution) VALUES ('" + SummaryIns + "', '" + DescIns + "', '" + ResIns + "'); SELECT NewID = SCOPE_IDENTITY()";

    SqlCommand cmdSQL = new SqlCommand
    (strSQL, CCCguidev2);
    cmdSQL.ExecuteNonQuery();

    // Get the DocID value for the row just added and store as a variable.

    SqlDataReader datareader = cmdSQL.ExecuteReader();
    datareader.Read();
    int newRowID = Convert.ToInt32(datareader["newID"]);

    datareader.Close();

    Thursday, June 9, 2011 12:49 PM

Answers

  • User860067021 posted

    Change your code to this:

    string strConn = "Data Source=siopssqldb01;Initial Catalog=cccguidev2;User ID=cccguidewebuser;Password=CcCw3b!@";
    
    SqlConnection CCCguidev2 = new SqlConnection(strConn);
    CCCguidev2.Open();
    
    string SummaryIns = Summary.Text;
    string DescIns = Description.Text;
    string ResIns = Resolution.Text;
    
    String strSQL = "INSERT INTO Docs (Summary, Description, Resolution) VALUES (@Summary, @Description,@Resolution); SET @id = SCOPE_IDENTITY()";
    
    SqlCommand cmdSQL = new SqlCommand
    (strSQL, CCCguidev2);
    cmdSQL.Parameters.AddWithValue("@Summary",SummaryIns);
    cmdSQL.Parameters.AddWithValue("@Description",DescIns);
    cmdSQL.Parameters.AddWithValue("@Resolution",ResIns);
    cmdSQL.Parameters.Add("@id",SqlDbType.Int,4).Direction = ParameterDirection.Output;
    cmdSQL.ExecuteNonQuery();
    
    // Get the DocID value for the row just added and store as a variable.
    
    int newRowID = Convert.ToInt32(cmdSQL.Parameters["@id"].Value);
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 9, 2011 12:55 PM

All replies

  • User860067021 posted

    Change your code to this:

    string strConn = "Data Source=siopssqldb01;Initial Catalog=cccguidev2;User ID=cccguidewebuser;Password=CcCw3b!@";
    
    SqlConnection CCCguidev2 = new SqlConnection(strConn);
    CCCguidev2.Open();
    
    string SummaryIns = Summary.Text;
    string DescIns = Description.Text;
    string ResIns = Resolution.Text;
    
    String strSQL = "INSERT INTO Docs (Summary, Description, Resolution) VALUES (@Summary, @Description,@Resolution); SET @id = SCOPE_IDENTITY()";
    
    SqlCommand cmdSQL = new SqlCommand
    (strSQL, CCCguidev2);
    cmdSQL.Parameters.AddWithValue("@Summary",SummaryIns);
    cmdSQL.Parameters.AddWithValue("@Description",DescIns);
    cmdSQL.Parameters.AddWithValue("@Resolution",ResIns);
    cmdSQL.Parameters.Add("@id",SqlDbType.Int,4).Direction = ParameterDirection.Output;
    cmdSQL.ExecuteNonQuery();
    
    // Get the DocID value for the row just added and store as a variable.
    
    int newRowID = Convert.ToInt32(cmdSQL.Parameters["@id"].Value);
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 9, 2011 12:55 PM
  • User-1662567569 posted

    Thank you very much, that's perfect. Don't suppose you could help me out even more with a bit of an explanation?

    Friday, June 10, 2011 7:06 AM