none
Inser Code did not work RRS feed

  • Question

  • public int Insert(string sqlIn)
            {
                int newID = -1;

                string connectionString = getConnectionString();

                OleDbConnection myConnection = new OleDbConnection(connectionString);
                myConnection.Open();

                OleDbCommand myCommand = new OleDbCommand(sqlIn, myConnection);

                myCommand.ExecuteNonQuery();

                myCommand = new OleDbCommand("SELECT CAST(SCOPE_IDENTITY() AS int)", myConnection);
                newID = (int)myCommand.ExecuteScalar();//ERROR HERE

                myConnection.Close();

                return newID;
            }

    Saturday, May 21, 2011 10:44 AM

Answers

  • Hi farina86,

    Welcome!

    I think you can try in this way:

    static public int AddProductCategory(string newName, string connString)
    {
      Int32 newProdID = 0;
      string sql =
        "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); "
        + "SELECT CAST(scope_identity() AS int)";
      using (SqlConnection conn = new SqlConnection(connString))
      {
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@Name", SqlDbType.VarChar);
        cmd.Parameters["@name"].Value = newName;
        try
        {
          conn.Open();
          newProdID = (Int32)cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
          Console.WriteLine(ex.Message);
        }
      }
      return (int)newProdID;
    }
    
     http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, June 1, 2011 5:59 AM
    Moderator

All replies

  • Please post the exception and the value in the sqlIn variable (the SQL INSERT Statement).
    Ali Hamdar (alihamdar.com - www.ids.com.lb)
    Saturday, May 21, 2011 10:55 AM
  • Try wrapping both statements in a transaction. I think that the later returns null because implicit transactions was used.

    Miha Markic [MVP C#] http://blog.rthand.com
    Saturday, May 21, 2011 1:19 PM
  • Hi farina86,

    Welcome!

    I think you can try in this way:

    static public int AddProductCategory(string newName, string connString)
    {
      Int32 newProdID = 0;
      string sql =
        "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); "
        + "SELECT CAST(scope_identity() AS int)";
      using (SqlConnection conn = new SqlConnection(connString))
      {
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@Name", SqlDbType.VarChar);
        cmd.Parameters["@name"].Value = newName;
        try
        {
          conn.Open();
          newProdID = (Int32)cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
          Console.WriteLine(ex.Message);
        }
      }
      return (int)newProdID;
    }
    
     http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, June 1, 2011 5:59 AM
    Moderator