$sql = $wpdb->prepare("INSERT INTO {$wpdb->prefix}tbl_my_table
            ( 
              user_id,
              CreatedOn
            ) VALUES (
              %d,
              Now()
            )", $user_id);
  if (current_user_can('administrator'))
    $wpdb->show_errors();
  $wpdb->query($sql);

Was INSERT sucessful?

  if ($wpdb->query($sql) === False)   //Returns number of rows inserted, or False on error
  {
    //INSERT failed
  }

Get Auto ID Of New Record

  $Result = $wpdb->query($sql);
  if ($Result == True)
    return($wpdb->insert_id);
  else
    return(False);

The insert_id() function returns the auto generated id used in the latest querywith a column having the AUTO_INCREMENT attribute.