1

I have a CodeIgniter based web application that was previously using a MySQL database. I changed the database.php to connect it to SQL Server after migrating all the data/tables. I'm testing the application now and login worked. However, when adding an object from a form in the web app, it throws an error on insert query.

The error mentions that it cannot insert to guests table when ID Insert property is on.

A Database Error Occurred

Error Number: 23000/544

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert explicit value for identity column in table 'guests' when IDENTITY_INSERT is set to OFF.

INSERT INTO "guests" ("id", "firstname", "lastname", "gender", "dob",
 "email", "country_id", "state_id", "city_id", "address", "mobile",
 "id_type", "id_no", "remark", "vip", "password", "added") VALUES (0,
 'abc', '123', '1', '', 'abc123@123.com', '', '', '', '', '899834534',
 '', '', '', 0, '6367c48dd193d56ea7b0baad25b19455e529f5ee', '2019-01-21
 09:58:11')

Filename: C:/wamp64/www/hms/system/database/DB_driver.php Line Number: 691

I know the workaround is T-SQL SET IDENTITY_INSERT guests ON but why are these insert queries structured this way at all?

I checked DB_Driver.php and here's perhaps the relevant portion

/**
     * Generate an insert string
     *
     * @param   string  the table upon which the query will be performed
     * @param   array   an associative array data of key/values
     * @return  string
     */
    public function insert_string($table, $data)
    {
        $fields = $values = array();

        foreach ($data as $key => $val)
        {
            $fields[] = $this->escape_identifiers($key);
            $values[] = $this->escape($val);
        }

        return $this->_insert($this->protect_identifiers($table, TRUE, NULL, FALSE), $fields, $values);
    }

    // --------------------------------------------------------------------

    /**
     * Insert statement
     *
     * Generates a platform-specific insert string from the supplied data
     *
     * @param   string  the table name
     * @param   array   the insert keys
     * @param   array   the insert values
     * @return  string
     */
    protected function _insert($table, $keys, $values)
    {
        return 'INSERT INTO '.$table.' ('.implode(', ', $keys).') VALUES ('.implode(', ', $values).')';
    }

How can I make changes to the project so that it does not try to insert the Identity column in every insert query?

Edit: The above code excerpt is likely not the place where query is formed because I changed it and the error remains.

Nimesh Patel
  • 796
  • 1
  • 7
  • 23
nts
  • 21
  • 5

1 Answers1

0

You misunderstood the error message. Let's quote it again:

Cannot insert explicit value for identity column in table 'guests' when IDENTITY_INSERT is set to OFF

You have an identity column for your table, which is the id. The error message specifically tells you that you cannot specify an explicit value for the identity column (id), when IDENTITY_INSERT is set to off. IDENTITY_INSERT is a setting which determines whether explicit values are allowed for identity columns. Since it is not allowed, the error message is expected. You can either turn on the IDENTITY_INSERT, or avoid specifying the value of the id when you insert a row in the table.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • Hello Lajos, Perhaps I was unclear -- english isn't my native language. `DB_Driver.php` is core code that is part of all CodeIgniter projects. [example here](https://github.com/greganswer/codeigniter-project/blob/master/system/database/DB_driver.php) I asked the question because is there something I am missing here? How does everyone else use CI with SQL Server? Is there a different `DB_Driver.php` file I am supposed to use? – nts Jan 22 '19 at 06:39
  • @nts I have never worked with Code Igniter, but the problem comes from SQL Server. The problem is that CodeIgniter eventually tries to insert records, specifying their ids, yet this is not allowed for the table. So you either insert records without the id (so the id will get a value automatically from SQL Server), or you change the setting of the table. – Lajos Arpad Jan 22 '19 at 11:30