Related questions

Inserting a row above the current row

2018-10-11 20:00 Andrew B imported from Stackoverflow

I have a very simple problem but am new to VB and cant figure out why I am getting errors.

What I am trying to do is set up a macro that copies row # 10 and inserts it above the current active row.

I have searched multiple web pages but everyone seems to code things differently and I cant get a grasp on understanding the VBA commands via analyzing codes.

I keep getting:

  1. "Insert/Copy Method of Range Class failed", and
  2. An error regarding disconnections.

I also am noticing something strange, after running the code more than once, I receive changing error messages and am not sure if there is a problem with my version of Excel.

Here is the code I am trying to use:

Sub Insert_Row()

Dim rng As Range
Dim act As Range

Set rng = ActiveCell.Rows
Set act = Rows("10:10")


Range(act).Copy

rng.Select

rng.Insert Shift:=xlDown

End Sub

See also questions close to this topic

  • Insert a value into a table while creating a record in another table

    I'm out of my league on this... Another developer before me did something similar to what I want to do by adding a value in a table while updating another table. However, he was running updates as well as inserting, and his primary key was text. Mine PK is integer. Here's his code (works great) that I am trying to reverse engineer and apply to my situation:

    Dim sqlQuery As String
sqlQuery = "IF EXISTS (SELECT ReportPK FROM 
ACIST_MobilePipelineReportReviewed WHERE ReportPK = '" & ReportPk & "') " & 
_
    " UPDATE ACIST_MobilePipelineReportReviewed set Status = 'Approved' 
WHERE ReportPK = '" & ReportPk & "'" & _
    " ELSE " & _
    " INSERT INTO ACIST_MobilePipelineReportReviewed ([ReportPK], 
   [PipelineUID],[ReportDate],[ReportInspector],[Status]) VALUES (" & _
    "'" & ReportPk & "','" & Me!PipelineUID & "','" & Me!ReportDate & "','" 
& Me!ReportInspector & "','Approved')"
End Sub

    Here's what I'm doing: I have a combo box on a form called FacilityEntryF. That form is tied to my FacilityT table. I am selecting "CampaignID" from the CampaignT table and adding it to the FacilityT using that combo box in the aforementioned form. No biggie there... Works great.

    The FacilityT has many columns of which I have [FacilityID] which is the primary key and is an autogenerated integer. It also has a column for [CampaignID] which is a foreign key from the CampaignT table.

    After adding the CampaignID and starting a new FacilityID in FacilityT, I also want to create a new record in my CampaignFacilityParticipationT table. That table consists of only three columns: CampaignFacilityParticipationID, CampaignID, ParticipantFacilityID. I want to take the new [FacilityID] and the [CampaignID] I added to that table and insert them into the CampaignFacilityParticipationT table (FacilityID goes into the ParticipantFacilityID column). Here's the code below that didn't work (which I'm not surprised because I don't know what I'm doing):

    Dim sqlQuery As String
sqlQuery = "IF EXISTS (SELECT FacilityID FROM FacilityT WHERE FacilityID = 
" & FacilityID) " & _
" INSERT INTO    CampaignFacilityParticipationT ([CampaignFacilityParticipationID],[CampaignID],[ParticipantFacilityID]) VALUES (" & _
 "" & CampaignFacilityParticipationID,'" & Me!CampaignID," & Me!ParticipantFacilityID, CampaignID)"
End Sub

    Using MS Access 2013 with Microsoft SQL backend.

    Thanks!!!

  • Hive Insert Error "ERROR : Failed to execute tez graph. java.io.IOException: Previous writer likely failed to write "

    We are on HDP 3.0 and when I run any select queries on Hive, it works fine but if I try to insert records into the Hive table, I get the following error during the insert both using nifi and from command line. Have researched solutions but no luck so far. I had made sure all the HDFS folders have full permission to read, write and execute. Any help would be greatly appreciated.

    ***0: jdbc:hive2://diaihdpmaster1b-p.coc.ca:2181> Insert into nginxlogprocessing.NGINX_ERROR_LOG
. . . . . . . . . . . . . . . . . . . . . . .> SELECT dateanderror,client, server, request, null as upstream, host,
. . . . . . . . . . . . . . . . . . . . . . .> null as referrer,environment FROM nginxlogprocessing.host_nginx_error_log_temp;
INFO : Compiling command(queryId=hive_20181018123706_cf101d3f-8d22-4b65-9020-85c415df7466): Insert into nginxlogprocessing.NGINX_ERROR_LOG
SELECT dateanderror,client, server, request, null as upstream, host,
null as referrer,environment FROM nginxlogprocessing.host_nginx_error_log_temp
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:_col0, type:string, comment:null), FieldSchema(name:_col1, type:string, comment:null), FieldSchema(name:_col2, type:string, comment:null), FieldSchema(name:_col3, type:string, comment:null), FieldSchema(name:_col4, type:string, comment:null), FieldSchema(name:_col5, type:string, comment:null), FieldSchema(name:_col6, type:string, comment:null), FieldSchema(name:_col7, type:string, comment:null)], properties:null)
INFO : Completed compiling command(queryId=hive_20181018123706_cf101d3f-8d22-4b65-9020-85c415df7466); Time taken: 0.967 seconds
INFO : Executing command(queryId=hive_20181018123706_cf101d3f-8d22-4b65-9020-85c415df7466): Insert into nginxlogprocessing.NGINX_ERROR_LOG
SELECT dateanderror,client, server, request, null as upstream, host,
null as referrer,environment FROM nginxlogprocessing.host_nginx_error_log_temp
INFO : Query ID = hive_20181018123706_cf101d3f-8d22-4b65-9020-85c415df7466
INFO : Total jobs = 3
INFO : Launching Job 1 out of 3
INFO : Starting task [Stage-1:MAPRED] in serial mode
INFO : Subscribed to counters: [] for queryId: hive_20181018123706_cf101d3f-8d22-4b65-9020-85c415df7466
INFO : Tez session hasn't been created yet. Opening session
ERROR : Failed to execute tez graph.
java.io.IOException: Previous writer likely failed to write hdfs://diaihdpmaster1a-p.coc.ca:8020/user/hive/.hiveJars/hive-exec-3.1.0.3.0.0.0-1634-910a4521e48e1f226fc0a0dffdb1a77bc247011285140107e7035089dc876cb6.jar. Failing because I am unlikely to write too.
at org.apache.hadoop.hive.ql.exec.tez.DagUtils.localizeResource(DagUtils.java:1286) ~[hive-exec-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hadoop.hive.ql.exec.tez.TezSessionState.createJarLocalResource(TezSessionState.java:917) ~[hive-exec-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hadoop.hive.ql.exec.tez.TezSessionState.makeCombinedJarMap(TezSessionState.java:349) ~[hive-exec-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hadoop.hive.ql.exec.tez.TezSessionState.openInternal(TezSessionState.java:418) ~[hive-exec-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hadoop.hive.ql.exec.tez.TezSessionPoolSession.openInternal(TezSessionPoolSession.java:124) ~[hive-exec-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hadoop.hive.ql.exec.tez.TezSessionState.open(TezSessionState.java:373) ~[hive-exec-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hadoop.hive.ql.exec.tez.TezTask.ensureSessionHasResources(TezTask.java:368) ~[hive-exec-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hadoop.hive.ql.exec.tez.TezTask.execute(TezTask.java:195) ~[hive-exec-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:205) ~[hive-exec-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:97) ~[hive-exec-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:2668) ~[hive-exec-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:2339) ~[hive-exec-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:2015) ~[hive-exec-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1713) ~[hive-exec-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1707) ~[hive-exec-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hadoop.hive.ql.reexec.ReExecDriver.run(ReExecDriver.java:157) ~[hive-exec-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:224) ~[hive-service-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hive.service.cli.operation.SQLOperation.access$700(SQLOperation.java:87) ~[hive-service-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at org.apache.hive.service.cli.operation.SQLOperation$BackgroundWork$1.run(SQLOperation.java:316) ~[hive-service-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at java.security.AccessController.doPrivileged(Native Method) ~[?:1.8.0_112]
at javax.security.auth.Subject.doAs(Subject.java:422) ~[?:1.8.0_112]
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1688) ~[hadoop-common-3.1.0.3.0.0.0-1634.jar:?]
at org.apache.hive.service.cli.operation.SQLOperation$BackgroundWork.run(SQLOperation.java:329) ~[hive-service-3.1.0.3.0.0.0-1634.jar:3.1.0.3.0.0.0-1634]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[?:1.8.0_112]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_112]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[?:1.8.0_112]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_112]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) ~[?:1.8.0_112]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) ~[?:1.8.0_112]
at java.lang.Thread.run(Thread.java:745) [?:1.8.0_112]
ERROR : FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.tez.TezTask
INFO : Completed executing command(queryId=hive_20181018123706_cf101d3f-8d22-4b65-9020-85c415df7466); Time taken: 25.087 seconds
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.tez.TezTask (state=08S01,code=1)***

    Thanks,

  • PHP PDO prepared statment - unable to insert boolen value of false

    I am trying to insert a boolean value with PDO into MySQLdatabase. If $data['age2'] is false the insertion doesn't take place but if true then it works. If I use the ->bindParam() method it works again. But if it was possible I would pass my variables in an associative array. Do you have an idea what I am doing wrong. at the bottom of the code I have inserted the code for the creation of the table (I want to insert into) as well. thanks

    <?php
$host = 'localhost';
$user = 'root';
$password = '12345';
$dbName = 'pdo';

// set DSN data sorce name
$dsn = "mysql:host={$host};dbname={$dbName}";

// create pdo instance
$pdoConn = new PDO($dsn, $user, $password);
$pdoConn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);

$data = [
    ':fname' => 'Bob',
    ':sname' => 'Desaunois',
    ':age' => '18',
    ':age2' => true
];
$sql = 'INSERT INTO posts (title, body, author, is_published) VALUES (:fname, :sname, :age, :age2)';
$statement = $pdoConn->prepare($sql);

$statement->execute($data);

/*
SHOW CREATE TABLE posts;
'posts', 'CREATE TABLE `posts` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `title` char(255) NOT NULL,\n  `body` text NOT NULL,\n  `author` char(255) NOT NULL,\n  `is_published` tinyint(1) DEFAULT \'0\',\n  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=latin1'
posts, CREATE TABLE `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` char(255) NOT NULL,
  `body` text NOT NULL,
  `author` char(255) NOT NULL,
  `is_published` tinyint(1) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=latin1


*/