none
C# and MSSQL INSERT Problem RRS feed

  • Question

  • Hello,

     

    I have a problem inserting rows into a MSSQL database table. Below is just a part of the code.

     

    When the excute the code it works fine and checkIntsert shows me 1, but when I check the table in visual studio 2005 no rows are inserted. I checked reopening the project and even restarting the PC. Nothing solved the porblem

     

    I can insert row by manually executing the query but not via code. Please tell me whats wrong.

     

    Code Block

    //update category table

    SqlCommand addCatCommand = new SqlCommand();

    addCatCommand.Connection = cons.myConnection;

    cons.myConnection.Open();

    addCatCommand.CommandText = @"INSERT INTO category (slave_id, cat_name, cat_desc) VALUES (" + catMasterId + ", '" + catName + "', '" + catDesc + "')";

    checkInsert = addCatCommand.ExecuteNonQuery();

    MessageBox.Show(checkInsert.ToString());

     

     

    Thanks for any help.
    Wednesday, October 10, 2007 7:33 PM

All replies

  • Just found out that DELETE and UPDATE doesnt work either, but gives me the correct number of rows returned!!

    However SELECT query works and i can retieve data and display it.

     

    Please someone help me with this.

    Wednesday, October 10, 2007 8:03 PM
  • Did you close the connection? Try this

    cons.myConnection.Close();

    Wednesday, October 10, 2007 8:08 PM
  • I tried this and it works fine for me. are you checking the correct database?

    Code Block


                SqlCommand addCatCommand = new SqlCommand();

                addCatCommand.Connection = new SqlConnection(@"Server=localhost\sqlexpress;Database=master;Trusted_Connection=yes;");

                addCatCommand.Connection.Open();

                addCatCommand.CommandText = "INSERT INTO category (slave_id, cat_name, cat_desc) VALUES (1, 'test1', 'test 1')";

                Console.WriteLine(addCatCommand.ExecuteNonQuery());


    Wednesday, October 10, 2007 8:13 PM
  • You can refer the following code which works well for me . I am using SQL Server 2005 Express.

    Code Snippet

    SqlConnection conn = new SqlConnection();
                string connstr = @"Data Source=.\Sqlexpress;Initial Catalog=Autolot;Integrated Security=SSPI;";
               conn.ConnectionString = connstr;
              
             
             
                try
                {
                    SqlDataAdapter dbAdapter = new SqlDataAdapter("select* from Inventory", conn);
                    DataSet ds = new DataSet("Autolot");
                    dbAdapter.Fill(ds, "Inventory");
                    DataRow newRecord = ds.Tables["Inventory"].NewRow();
                    newRecord["CarID"] = 16;
                    newRecord["Make"] = "bmw";
                    newRecord["Color"] = "coffee";
                    newRecord["PetName"] = "catmimi";

                    ds.Tables["Inventory"].Rows.Add(newRecord);
                    SqlCommandBuilder InsertCommand = new SqlCommandBuilder(dbAdapter);
                    dbAdapter.InsertCommand = InsertCommand.GetInsertCommand();
                    dbAdapter.Update(ds, "Inventory");
                }
                catch (Exception e)
                {
                    Console.WriteLine("<!-- DATABASE ERROR: " + e.Message + " -->");
                }

           
                Console.ReadLine();

     

     

    Best Regards

    Jing

    Wednesday, April 9, 2008 11:16 AM
  • I am pretty sure that you are querying and updating one database, but then checking another one. Make sure you are not working with another copy of the database.

     

    Thursday, April 10, 2008 10:23 AM
    Moderator