Strange result with

Question

When i run this as my first commend i get an exception an error near "last_insert_rowid". This is referring to the last last_insert_rowid();. If i set the curser to the line command.CommandText = and run it again it is fine.

What gives? The last_insert_rowid seems to be working properly why doesnt the last_insert_rowid after the 2nd insert work.

I tried moving last_insert_rowid() to after the execute and i still get an error. What gives?

        using (var trans = connection.BeginTransaction())
        {
            command.CommandText = 
                "INSERT INTO link_list(link, status, hash_type, hash_id) " +
                "VALUES(@link, @status, @hash_type, @hash_id);" +
                "INSERT INTO active_dl(linkId, orderNo) " +
                "VALUES(last_insert_rowid(), (SELECT COUNT(*) FROM active_dl)); last_insert_rowid();";
            command.Parameters.Add("@link", System.Data.DbType.String).Value = link;
            command.Parameters.Add("@status", System.Data.DbType.Int32).Value = SiteBase.Status.none;
            command.Parameters.Add("@hash_type", System.Data.DbType.Int32).Value = 0;
            command.Parameters.Add("@hash_id", System.Data.DbType.Int32).Value = 0;
            int rowid = command.ExecuteNonQuery();
            trans.Commit();
        }

Answer 1

Why is the last last_insert_rowid() there? After the second insert you call last_insert_rowid with no select or anything to identify what you want to do with it.

You would have to put a select in front of it to retrieve the value wouldn't you?

Answer 2

You're trying to execute 2 sql commands in the one statement. You'll need to split the 2 statements up into 2 calls and return the inserted id from the first statement.

An alternative suggestion is a stored procedure but I don't think sqlite supports these.