locked
Adding a new row to SQL table RRS feed

  • Question

  • Hello all,

    I'm trying to add a new row to my SQL table.

    Here is my Code:

    private void AddBtn_Click(object sender, EventArgs e)
        {
          WorldDA.InsertCommand = new SqlCommand("INSERT INTO RaceTbl VALUES (@ID, @RaceName, @Adulthood, @MiddleAge, @OldAge, @MaxAge, @MinHeight, @MaxHeight, @MinWeight, @MaxWeight, @MaxSpeed, @Vision)", WorldConn);
    
          if (Validate() && (RaceBS != null))
          {
            RaceBS.AddNew();
            RaceDGView.ClearSelection();
          }
    

    I know that I need to write an sqlcommand somewhere so the data could be saved into the table.

    But I don't know where or how.

    What the code do now is to show new row when I press the add button but the incremental number is not being updated automatically and the SQL table not updated when I press the update button.

    One more thing I use some variables like: VarChar, Int, Float

    Need some advice.

    Thanks.


    lioritshiran@gmail.com
    Friday, October 8, 2010 10:00 AM

Answers

  • Hi,

    You should use SQLCommandBuilder rather than to specify explicitly to your WorldDA.InsertCommand property. Please change your code as:

    private void AddBtn_Click(object sender, EventArgs e)
        {
                string constr = ""; // Provide your connection string here
                SqlConnection con = new SqlConnection(constr);
                SqlDataAdapter da = new SqlDataAdapter("select * from RaceTbl",con);
                SqlCommandBuilder comBuilder = new SqlCommandBuilder(da);

                DataSet ds = new DataSet();
                da.Fill(ds);

                ////
                //// Manipulate your dataset here e.g. insert, update, delete any row/rows
                //// and update to the database as below.

                
          if (Validate() && (RaceBS != null))
          {

            da.Update(ds);

            RaceDGView.ClearSelection();
          }


    Satya Prakash Jugran


    I have tried to follow your code but it didn't work

    Here is the new code I've programed it does half the work:

        private void AddBtn_Click(object sender, EventArgs e)
        {
          if (Validate() && (RaceBS != null))
          {
            RaceBS.AddNew();
    
            SqlCommand RaceAddRow = new SqlCommand("INSERT INTO RaceTbl (ID, RaceName, Adulthood, MiddleAge, OldAge, MaxAge, MinHeight, MaxHeight, MinWeight, MaxWeight, MaxSpeed, Vision) VALUES (@ID, @RaceName, @Adulthood, @MiddleAge, @OldAge, @MaxAge, @MinHeight, @MaxHeight, @MinWeight, @MaxWeight, @MaxSpeed, @Vision)", WorldConn);
    
            RaceAddRow.Parameters.AddWithValue("ID", RaceDGView.SelectedCells[0].RowIndex);
            RaceAddRow.Parameters.AddWithValue("@raceName", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@Adulthood", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@MiddleAge", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@OldAge", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@MaxAge", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@MinHeight", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@MaxHeight", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@MinWeight", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@MaxWeight", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@MaxSpeed", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@Vision", raceNameTxt.Text);
    
            //WorldConn.Open();
            //RaceAddRow.ExecuteNonQuery();
            //WorldConn.Close();
            WorldDA.Fill(RaceDS, "RaceTbl");
          }
        }
    

    The code adds a blank new row.

    The new row not saved.

    Only the first existing row is updated and the new row disappear.

     


    lioritshiran@gmail.com
    • Marked as answer by lioritshiran Saturday, October 9, 2010 8:36 AM
    Friday, October 8, 2010 5:40 PM

All replies

  • to execute to your mssql use something like this.

    SQLConnection = new SqlConnection(Connectionstring);
    SqlCommand mySqlCommand = SQLConnection.CreateCommand();
        mySqlCommand.CommandText = SQLQuery;
        mySqlCommand.ExecuteNonQuery();
    

    Connectionstring = your connection string to your Database
    SQLQuery = Your insert string

    But I don't think your insert string its right.
    could be just me though.

    • Edited by SWR1988 Friday, October 8, 2010 10:46 AM typo
    Friday, October 8, 2010 10:45 AM
  • Hi,

    You should use SQLCommandBuilder rather than to specify explicitly to your WorldDA.InsertCommand property. Please change your code as:

    private void AddBtn_Click(object sender, EventArgs e)
        {
                string constr = ""; // Provide your connection string here
                SqlConnection con = new SqlConnection(constr);
                SqlDataAdapter da = new SqlDataAdapter("select * from RaceTbl",con);
                SqlCommandBuilder comBuilder = new SqlCommandBuilder(da);

                DataSet ds = new DataSet();
                da.Fill(ds);

                ////
                //// Manipulate your dataset here e.g. insert, update, delete any row/rows
                //// and update to the database as below.

                
          if (Validate() && (RaceBS != null))
          {

            da.Update(ds);

            RaceDGView.ClearSelection();
          }


    Satya Prakash Jugran
    Friday, October 8, 2010 11:55 AM
  • Hi,

    Confirm one thing that your 'ID ' column name haing 'Identity ' property or not.

    If it has 'Identity ' property then no need to insert  value of that column

     

    (or)

     

    You have to write

    cmd.ExecuteNonQuery(); after the Insert command.


    PS.Shakeer Hussain
    Friday, October 8, 2010 11:55 AM
  • Hi Syed,

    This is connected mode approach of ADO.Net and as per the scenario he is using disconnected mode (DataAdapter) that's why I have written such code.

    The connected mode approach you have mentioned is also correct.


    Satya Prakash Jugran
    Friday, October 8, 2010 12:27 PM
  • Hi,

    You should use SQLCommandBuilder rather than to specify explicitly to your WorldDA.InsertCommand property. Please change your code as:

    private void AddBtn_Click(object sender, EventArgs e)
        {
                string constr = ""; // Provide your connection string here
                SqlConnection con = new SqlConnection(constr);
                SqlDataAdapter da = new SqlDataAdapter("select * from RaceTbl",con);
                SqlCommandBuilder comBuilder = new SqlCommandBuilder(da);

                DataSet ds = new DataSet();
                da.Fill(ds);

                ////
                //// Manipulate your dataset here e.g. insert, update, delete any row/rows
                //// and update to the database as below.

                
          if (Validate() && (RaceBS != null))
          {

            da.Update(ds);

            RaceDGView.ClearSelection();
          }


    Satya Prakash Jugran


    I have tried to follow your code but it didn't work

    Here is the new code I've programed it does half the work:

        private void AddBtn_Click(object sender, EventArgs e)
        {
          if (Validate() && (RaceBS != null))
          {
            RaceBS.AddNew();
    
            SqlCommand RaceAddRow = new SqlCommand("INSERT INTO RaceTbl (ID, RaceName, Adulthood, MiddleAge, OldAge, MaxAge, MinHeight, MaxHeight, MinWeight, MaxWeight, MaxSpeed, Vision) VALUES (@ID, @RaceName, @Adulthood, @MiddleAge, @OldAge, @MaxAge, @MinHeight, @MaxHeight, @MinWeight, @MaxWeight, @MaxSpeed, @Vision)", WorldConn);
    
            RaceAddRow.Parameters.AddWithValue("ID", RaceDGView.SelectedCells[0].RowIndex);
            RaceAddRow.Parameters.AddWithValue("@raceName", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@Adulthood", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@MiddleAge", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@OldAge", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@MaxAge", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@MinHeight", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@MaxHeight", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@MinWeight", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@MaxWeight", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@MaxSpeed", raceNameTxt.Text);
            RaceAddRow.Parameters.AddWithValue("@Vision", raceNameTxt.Text);
    
            //WorldConn.Open();
            //RaceAddRow.ExecuteNonQuery();
            //WorldConn.Close();
            WorldDA.Fill(RaceDS, "RaceTbl");
          }
        }
    

    The code adds a blank new row.

    The new row not saved.

    Only the first existing row is updated and the new row disappear.

     


    lioritshiran@gmail.com
    • Marked as answer by lioritshiran Saturday, October 9, 2010 8:36 AM
    Friday, October 8, 2010 5:40 PM
  • Need Help

    PLZ


    lioritshiran@gmail.com
    Saturday, October 9, 2010 7:04 AM
  • Hi,

    Update the code as:

     private void AddBtn_Click(object sender, EventArgs e)
        {
          if (Validate() && (RaceBS != null))
          {

    DataSet RaceDS = new DataSet();
                SqlDataAdapter WorldDA = new SqlDataAdapter("select * from RaceTbl", con);
                RaceBS.AddNew();

                //SqlCommand RaceAddRow = new SqlCommand("INSERT INTO RaceTbl (ID, RaceName, Adulthood, MiddleAge, OldAge, MaxAge, MinHeight, MaxHeight, MinWeight, MaxWeight, MaxSpeed, Vision) VALUES (@ID, @RaceName, @Adulthood, @MiddleAge, @OldAge, @MaxAge, @MinHeight, @MaxHeight, @MinWeight, @MaxWeight, @MaxSpeed, @Vision)", WorldConn);

                //RaceAddRow.Parameters.AddWithValue("ID", RaceDGView.SelectedCells[0].RowIndex);
                //RaceAddRow.Parameters.AddWithValue("@raceName", raceNameTxt.Text);
                //RaceAddRow.Parameters.AddWithValue("@Adulthood", raceNameTxt.Text);
                //RaceAddRow.Parameters.AddWithValue("@MiddleAge", raceNameTxt.Text);
                //RaceAddRow.Parameters.AddWithValue("@OldAge", raceNameTxt.Text);
                //RaceAddRow.Parameters.AddWithValue("@MaxAge", raceNameTxt.Text);
                //RaceAddRow.Parameters.AddWithValue("@MinHeight", raceNameTxt.Text);
                //RaceAddRow.Parameters.AddWithValue("@MaxHeight", raceNameTxt.Text);
                //RaceAddRow.Parameters.AddWithValue("@MinWeight", raceNameTxt.Text);
                //RaceAddRow.Parameters.AddWithValue("@MaxWeight", raceNameTxt.Text);
                //RaceAddRow.Parameters.AddWithValue("@MaxSpeed", raceNameTxt.Text);
                //RaceAddRow.Parameters.AddWithValue("@Vision", raceNameTxt.Text);

                SqlCommandBuilder builder = new SqlCommandBuilder(WorldDA);

                //WorldConn.Open();
                //RaceAddRow.ExecuteNonQuery();
                //WorldConn.Close();
                WorldDA.Fill(RaceDS, "RaceTbl");

                DataRow row = RaceDS.Tables[0].NewRow();
                row["ID"] = RaceDGView.SelectedCells[0].RowIndex;
                row["RaceName"] = raceNameTxt.Text;
                row["Adulthood"] = raceNameTxt.Text;
                row["MiddleAge"] = raceNameTxt.Text;
                row["OldAge"] = raceNameTxt.Text;
                row["MaxAge"] = raceNameTxt.Text;
                row["MinHeight"] = raceNameTxt.Text;
                row["MaxHeight"] = raceNameTxt.Text;
                row["MinWeight"] = raceNameTxt.Text;
                row["MaxWeight"] = raceNameTxt.Text;
                row["MaxSpeed"] = raceNameTxt.Text;
                row["Vision"] = raceNameTxt.Text;
                RaceDS.Tables[0].Rows.Add(row);

                WorldDA.Update(RaceDS);

          }

    }

    You should add a new row in Dataset and then do an update in dataadapter. This will not update without CommandBuilder. If you find any problem, you have to share your source code (.cs) file.


    Satya Prakash Jugran
    Monday, October 11, 2010 1:45 PM